Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2014
    Posts
    2

    Unanswered: relationships between tables, and diagrams

    my question is ,,
    how to build a diagram, I don't understand that many to many , or one to many and about primary key and foreign key and etc..

    here is my code. is about a hospital where i have ,

    NURSES, WARD, PATIENTS, DOCTORS.


    CREATE TABLE Nurses(
    `idNurses` INT NOT NULL AUTO_INCREMENT,
    `Fname` VARCHAR(45) NULL,
    `Lname` VARCHAR(45) NULL,
    `Speciality` VARCHAR(45) NULL,
    PRIMARY KEY (`idNurses`))
    ENGINE = InnoDB;


    CREATE TABLE Doctors (
    `idDoctors` INT NOT NULL AUTO_INCREMENT,
    `Fname` VARCHAR(45) NULL,
    `Lname` VARCHAR(45) NULL,
    `Speciality` VARCHAR(45) NULL,
    PRIMARY KEY (`idDoctors`))
    ENGINE = InnoDB;



    CREATE TABLE Ward(
    `idWard` INT NOT NULL AUTO_INCREMENT,
    `Name` VARCHAR(45) NULL,
    `Location` VARCHAR(45) NULL,
    `Nurses_idNurses` INT NOT NULL,
    `Doctors_idDoctors` INT NOT NULL,
    PRIMARY KEY (`idWard`, `Nurses_idNurses`, `Doctors_idDoctors`),
    INDEX `fk_Ward_Nurses_idx` (`Nurses_idNurses` ASC),
    INDEX `fk_Ward_Doctors1_idx` (`Doctors_idDoctors` ASC),
    CONSTRAINT `fk_Ward_Nurses`
    FOREIGN KEY (`Nurses_idNurses`)
    REFERENCES `mydb`.`Nurses` (`idNurses`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_Ward_Doctors1`
    FOREIGN KEY (`Doctors_idDoctors`)
    REFERENCES `mydb`.`Doctors` (`idDoctors`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;

    CREATE TABLE Patient (
    `idPatient` INT NOT NULL AUTO_INCREMENT,
    `Fname` VARCHAR(45) NULL,
    `Lname` VARCHAR(45) NULL,
    `DOB` DATE NULL,
    `Phone_number` INT NULL,
    `Ward_idWard` INT NOT NULL,
    `Ward_Nurses_idNurses` INT NOT NULL,
    `Ward_Doctors_idDoctors` INT NOT NULL,
    PRIMARY KEY (`idPatient`, `Ward_idWard`, `Ward_Nurses_idNurses`, `Ward_Doctors_idDoctors`),
    INDEX `fk_Patient_Ward1_idx` (`Ward_idWard` ASC, `Ward_Nurses_idNurses` ASC, `Ward_Doctors_idDoctors` ASC),
    CONSTRAINT `fk_Patient_Ward1`
    FOREIGN KEY (`Ward_idWard` , `Ward_Nurses_idNurses` , `Ward_Doctors_idDoctors`)
    REFERENCES `mydb`.`Ward` (`idWard` , `Nurses_idNurses` , `Doctors_idDoctors`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;
    Attached Thumbnails Attached Thumbnails 1.png   2.png   3.png  

  2. #2
    Join Date
    Jul 2014
    Posts
    2

    Question relationships between tables, and diagrams

    my question is ,,
    how to build a diagram, I don't understand that many to many , or one to many and about primary key and foreign key and etc..

    here is my code. is about a hospital where i have ,

    NURSES, WARD, PATIENTS, DOCTORS.


    CREATE TABLE Nurses(
    `idNurses` INT NOT NULL AUTO_INCREMENT,
    `Fname` VARCHAR(45) NULL,
    `Lname` VARCHAR(45) NULL,
    `Speciality` VARCHAR(45) NULL,
    PRIMARY KEY (`idNurses`))
    ENGINE = InnoDB;


    CREATE TABLE Doctors (
    `idDoctors` INT NOT NULL AUTO_INCREMENT,
    `Fname` VARCHAR(45) NULL,
    `Lname` VARCHAR(45) NULL,
    `Speciality` VARCHAR(45) NULL,
    PRIMARY KEY (`idDoctors`))
    ENGINE = InnoDB;



    CREATE TABLE Ward(
    `idWard` INT NOT NULL AUTO_INCREMENT,
    `Name` VARCHAR(45) NULL,
    `Location` VARCHAR(45) NULL,
    `Nurses_idNurses` INT NOT NULL,
    `Doctors_idDoctors` INT NOT NULL,
    PRIMARY KEY (`idWard`, `Nurses_idNurses`, `Doctors_idDoctors`),
    INDEX `fk_Ward_Nurses_idx` (`Nurses_idNurses` ASC),
    INDEX `fk_Ward_Doctors1_idx` (`Doctors_idDoctors` ASC),
    CONSTRAINT `fk_Ward_Nurses`
    FOREIGN KEY (`Nurses_idNurses`)
    REFERENCES `mydb`.`Nurses` (`idNurses`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_Ward_Doctors1`
    FOREIGN KEY (`Doctors_idDoctors`)
    REFERENCES `mydb`.`Doctors` (`idDoctors`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;

    CREATE TABLE Patient (
    `idPatient` INT NOT NULL AUTO_INCREMENT,
    `Fname` VARCHAR(45) NULL,
    `Lname` VARCHAR(45) NULL,
    `DOB` DATE NULL,
    `Phone_number` INT NULL,
    `Ward_idWard` INT NOT NULL,
    `Ward_Nurses_idNurses` INT NOT NULL,
    `Ward_Doctors_idDoctors` INT NOT NULL,
    PRIMARY KEY (`idPatient`, `Ward_idWard`, `Ward_Nurses_idNurses`, `Ward_Doctors_idDoctors`),
    INDEX `fk_Patient_Ward1_idx` (`Ward_idWard` ASC, `Ward_Nurses_idNurses` ASC, `Ward_Doctors_idDoctors` ASC),
    CONSTRAINT `fk_Patient_Ward1`
    FOREIGN KEY (`Ward_idWard` , `Ward_Nurses_idNurses` , `Ward_Doctors_idDoctors`)
    REFERENCES `mydb`.`Ward` (`idWard` , `Nurses_idNurses` , `Doctors_idDoctors`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;
    Attached Thumbnails Attached Thumbnails 1.png   2.png   3.png  

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Can there only be one nurse and one doctor per ward.
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •