td33N3R8

1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
6
7-- -----------------------------------------------------
8-- Schema hospital_db
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema hospital_db
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `hospital_db` DEFAULT CHARACTER SET utf8 ;
15USE `hospital_db` ;
16
17-- -----------------------------------------------------
18-- Table `hospital_db`.`Patient`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `hospital_db`.`Patient` (
21  `ID` INT NOT NULL,
22  `NAME` VARCHAR(100) NOT NULL,
23  `TELEPHONE` VARCHAR(11) NULL,
24  `GENDER` VARCHAR(6) NOT NULL,
25  `REGISTERATIONDATE` DATETIME NULL,
26  PRIMARY KEY (`ID`))
27ENGINE = InnoDB;
28
29
30-- -----------------------------------------------------
31-- Table `hospital_db`.`STAFF`
32-- -----------------------------------------------------
33CREATE TABLE IF NOT EXISTS `hospital_db`.`STAFF` (
34  `ID` INT NOT NULL,
35  `NAME` VARCHAR(45) NOT NULL,
36  `ROLE` VARCHAR(15) NOT NULL,
37  `TELEPHONE` VARCHAR(12) NOT NULL,
38  `SALARY` INT NOT NULL,
39  PRIMARY KEY (`ID`))
40ENGINE = InnoDB;
41
42
43-- -----------------------------------------------------
44-- Table `hospital_db`.`APPOINTMENT`
45-- -----------------------------------------------------
46CREATE TABLE IF NOT EXISTS `hospital_db`.`APPOINTMENT` (
47  `ID` INT NOT NULL,
48  `PATIENTID` INT NOT NULL,
49  `DOCTORID` INT NOT NULL,
50  `HOUR` VARCHAR(10) NULL,
51  `APPOINTMENTDATE` DATETIME NULL,
52  PRIMARY KEY (`ID`),
53  INDEX `fk_APPOINTMENT_1_idx` (`PATIENTID` ASC),
54  INDEX `fk_APPOINTMENT_2_idx` (`DOCTORID` ASC),
55  CONSTRAINT `fk_APPOINTMENT_1`
56    FOREIGN KEY (`PATIENTID`)
57    REFERENCES `hospital_db`.`Patient` (`ID`)
58    ON DELETE NO ACTION
59    ON UPDATE NO ACTION,
60  CONSTRAINT `fk_APPOINTMENT_2`
61    FOREIGN KEY (`DOCTORID`)
62    REFERENCES `hospital_db`.`STAFF` (`ID`)
63    ON DELETE NO ACTION
64    ON UPDATE NO ACTION)
65ENGINE = InnoDB;
66
67
68-- -----------------------------------------------------
69-- Table `hospital_db`.`MEDICATION`
70-- -----------------------------------------------------
71CREATE TABLE IF NOT EXISTS `hospital_db`.`MEDICATION` (
72  `PRICE` INT NOT NULL,
73  `NAME` VARCHAR(20) NOT NULL,
74  PRIMARY KEY (`NAME`))
75ENGINE = InnoDB;
76
77
78-- -----------------------------------------------------
79-- Table `hospital_db`.`PATIENTCASE`
80-- -----------------------------------------------------
81CREATE TABLE IF NOT EXISTS `hospital_db`.`PATIENTCASE` (
82  `ID` INT NOT NULL,
83  `PATIENTID` INT NOT NULL,
84  `DISEASE` VARCHAR(45) NOT NULL,
85  `MEDICATION` VARCHAR(20) NOT NULL,
86  PRIMARY KEY (`ID`),
87  INDEX `fk_PATIENTCASE_1_idx` (`PATIENTID` ASC),
88  INDEX `fk_PATIENTCASE_2_idx` (`MEDICATION` ASC),
89  CONSTRAINT `fk_PATIENTCASE_1`
90    FOREIGN KEY (`PATIENTID`)
91    REFERENCES `hospital_db`.`Patient` (`ID`)
92    ON DELETE NO ACTION
93    ON UPDATE NO ACTION,
94  CONSTRAINT `fk_PATIENTCASE_2`
95    FOREIGN KEY (`MEDICATION`)
96    REFERENCES `hospital_db`.`MEDICATION` (`NAME`)
97    ON DELETE NO ACTION
98    ON UPDATE NO ACTION)
99ENGINE = InnoDB;
100
101
102SET SQL_MODE=@OLD_SQL_MODE;
103SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
104SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;