MySQL Forums
Forum List  »  MySQL Workbench

Er Diagram
Posted by: Dennis Onggo
Date: April 10, 2018 06:52AM

Hello guys
I'm new here.I got a little problem about my er diagram.
Here is the code

-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`Doctor`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Doctor` (
`DoctorID` INT NOT NULL AUTO_INCREMENT,
`PatientID` INT NOT NULL,
`First Name` VARCHAR(45) NOT NULL,
`Last Name` VARCHAR(45) NULL,
`Department Name` VARCHAR(150) NOT NULL,
`Specialization` VARCHAR(150) NOT NULL,
`Years of Experience` INT NOT NULL,
`Degree` VARCHAR(150) NOT NULL,
PRIMARY KEY (`DoctorID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Patient`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Patient` (
`PatientID` INT NOT NULL AUTO_INCREMENT,
`First Name` VARCHAR(45) NOT NULL,
`Last Name` VARCHAR(45) NULL,
`Birthday` DATE NOT NULL,
`Gender` VARCHAR(6) NOT NULL,
`Age` INT NOT NULL,
`Height` INT NOT NULL,
`Weight` INT NOT NULL,
`Address` VARCHAR(150) NOT NULL,
`Contact Number` VARCHAR(45) NULL,
PRIMARY KEY (`PatientID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Lab`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Lab` (
`TestID` INT NOT NULL AUTO_INCREMENT,
`PatientID` INT NOT NULL,
`DoctorID` INT NOT NULL,
`Date` DATE NOT NULL,
PRIMARY KEY (`TestID`),
CONSTRAINT `TestID`
FOREIGN KEY (`TestID`)
REFERENCES `mydb`.`Test_and_Diagnosis` (`Lab_TestID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Test_and_Diagnosis`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Test_and_Diagnosis` (
`Patient_PatientID` INT NOT NULL,
`Lab_TestID` INT NOT NULL,
`Body Temperature` DOUBLE NOT NULL,
`Blood Pressure` VARCHAR(45) NOT NULL,
`Blood Type` VARCHAR(5) NOT NULL,
`X-ray Result` VARCHAR(45) NULL,
`Urine Examination` VARCHAR(45) NULL,
`Other tests` VARCHAR(150) NULL,
`Diagnosis` VARCHAR(150) NOT NULL,
PRIMARY KEY (`Patient_PatientID`, `Lab_TestID`),
CONSTRAINT `fk_Patient_has_Lab_Patient1`
FOREIGN KEY (`Patient_PatientID`)
REFERENCES `mydb`.`Patient` (`PatientID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Patient_has_Lab_Lab1`
FOREIGN KEY (`Lab_TestID`)
REFERENCES `mydb`.`Lab` (`TestID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Inpatient`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Inpatient` (
`PatientID` INT NOT NULL AUTO_INCREMENT,
`RoomID` INT NOT NULL,
`TestID` INT NOT NULL,
`Date of admitted` DATE NOT NULL,
`Date of discharge` DATE NOT NULL,
PRIMARY KEY (`PatientID`),
CONSTRAINT `PatientID`
FOREIGN KEY (`PatientID`)
REFERENCES `mydb`.`Test_and_Diagnosis` (`Patient_PatientID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Room`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Room` (
`RoomID` INT NOT NULL AUTO_INCREMENT,
`PatientID` INT NOT NULL,
`Floor Number` INT NOT NULL,
`Room Number` INT NOT NULL,
`Room Type` VARCHAR(45) NOT NULL,
`Date Admitted` DATE NOT NULL,
`Date Released` DATE NOT NULL,
PRIMARY KEY (`RoomID`, `PatientID`),
INDEX `PatientID_idx` (`PatientID` ASC),
CONSTRAINT `PatientID`
FOREIGN KEY (`PatientID`)
REFERENCES `mydb`.`Inpatient` (`PatientID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Diagnosis`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Diagnosis` (
`Doctor_DoctorID` INT NOT NULL,
`Patient_PatientID` INT NOT NULL,
`Diagnosis` VARCHAR(150) NOT NULL,
PRIMARY KEY (`Doctor_DoctorID`, `Patient_PatientID`),
CONSTRAINT `fk_Doctor_has_Patient_Doctor`
FOREIGN KEY (`Doctor_DoctorID`)
REFERENCES `mydb`.`Doctor` (`DoctorID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Doctor_has_Patient_Patient1`
FOREIGN KEY (`Patient_PatientID`)
REFERENCES `mydb`.`Patient` (`PatientID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Medication`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Medication` (
`MedicationID` INT NOT NULL AUTO_INCREMENT,
`PatientID` INT NOT NULL,
`Medication1 Name` VARCHAR(150) NULL,
`Medication1 Description` VARCHAR(150) NULL,
`Medication1 Dosage` VARCHAR(150) NULL,
`Medication2 Name` VARCHAR(150) NULL,
`Medication2 Description` VARCHAR(150) NULL,
`Medication2 Dosage` VARCHAR(150) NULL,
`Etc` VARCHAR(150) NULL,
PRIMARY KEY (`MedicationID`, `PatientID`),
INDEX `PatientID_idx` (`PatientID` ASC),
CONSTRAINT `PatientID`
FOREIGN KEY (`PatientID`)
REFERENCES `mydb`.`Patient` (`PatientID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Outpatient`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Outpatient` (
`PatientID` INT NOT NULL AUTO_INCREMENT,
`TestID` INT NOT NULL,
PRIMARY KEY (`PatientID`),
CONSTRAINT `PatientID`
FOREIGN KEY (`PatientID`)
REFERENCES `mydb`.`Test_and_Diagnosis` (`Patient_PatientID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Department`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Department` (
`DepartmentID` INT NOT NULL AUTO_INCREMENT,
`DoctorID` INT NOT NULL,
`Department Name` VARCHAR(150) NOT NULL,
PRIMARY KEY (`DepartmentID`, `DoctorID`),
INDEX `DoctorID_idx` (`DoctorID` ASC),
CONSTRAINT `DoctorID`
FOREIGN KEY (`DoctorID`)
REFERENCES `mydb`.`Doctor` (`DoctorID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Bill`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Bill` (
`Room_RoomID` INT NULL,
`Inpatient_PatientID` INT NOT NULL,
`Outpatient_PatientID` INT NOT NULL,
`Bill No` INT NOT NULL AUTO_INCREMENT,
`Medicine Charge` DOUBLE NULL,
`Room Charge` DOUBLE NULL,
`Number of Days` DOUBLE NULL,
`Health Card` DOUBLE NULL,
`Lab Charge` DOUBLE NOT NULL,
`Doctor Charge` DOUBLE NOT NULL,
`Bill` DOUBLE NOT NULL,
PRIMARY KEY (`Bill No`, `Inpatient_PatientID`, `Outpatient_PatientID`),
INDEX `PatientID_idx` (`Outpatient_PatientID` ASC),
UNIQUE INDEX `Inpatient_PatientID_UNIQUE` (`Inpatient_PatientID` ASC),
UNIQUE INDEX `Outpatient_PatientID_UNIQUE` (`Outpatient_PatientID` ASC),
UNIQUE INDEX `Room_RoomID_UNIQUE` (`Room_RoomID` ASC),
UNIQUE INDEX `Medicine Charge_UNIQUE` (`Medicine Charge` ASC),
UNIQUE INDEX `Room Charge_UNIQUE` (`Room Charge` ASC),
UNIQUE INDEX `Number of Days_UNIQUE` (`Number of Days` ASC),
UNIQUE INDEX `Health Card_UNIQUE` (`Health Card` ASC),
CONSTRAINT `PatientID`
FOREIGN KEY (`Inpatient_PatientID`)
REFERENCES `mydb`.`Inpatient` (`PatientID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `PatientID`
FOREIGN KEY (`Outpatient_PatientID`)
REFERENCES `mydb`.`Outpatient` (`PatientID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

I forwarded it from er diagram.But when I copied the same code in a new query tab and I reverse it back to er diagram,It showed a different er diagram.Not all table are linked.Can someone explain why it is not same?Is it how it works or I made a mistake?
Please help me out.
Thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
Er Diagram
167
April 10, 2018 06:52AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.