uEFGxCUg

1-- MySQL Workbench Forward Engineering
2DROP schema `hospital_db`;
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`.`Role` (
21  `ID` INT NOT NULL,
22  `NAME` VARCHAR(100) NOT NULL,
23  PRIMARY KEY (`ID`))
24ENGINE = InnoDB;
25  
26CREATE TABLE IF NOT EXISTS `hospital_db`.`Patient` (
27  `ID` INT NOT NULL,
28  `NAME` VARCHAR(100) NOT NULL,
29  `TELEPHONE` VARCHAR(11) NULL,
30  `GENDER` VARCHAR(6) NOT NULL,
31  `REGISTERATIONDATE` DATETIME NULL,
32  PRIMARY KEY (`ID`))
33ENGINE = InnoDB;
34
35-- -----------------------------------------------------
36-- Table `hospital_db`.`PATIENTALERGIES`
37-- -----------------------------------------------------
38CREATE TABLE IF NOT EXISTS `hospital_db`.`PATIENTALERGIES`(
39	`PATIENTID` INT NOT NULL,
40	`PATIENTALERGIE` VARCHAR(45) NOT NULL,	
41	PRIMARY KEY (`PATIENTID`, `PATIENTALERGIE`),
42	CONSTRAINT `fk_PATIENTALERGIES_1`
43    FOREIGN KEY (`PATIENTID`)
44    REFERENCES `hospital_db`.`Patient` (`ID`)
45    ON DELETE NO ACTION
46    ON UPDATE NO ACTION
47	
48)
49ENGINE = InnoDB;
50
51
52-- -----------------------------------------------------
53-- Table `hospital_db`.`STAFF`
54-- -----------------------------------------------------
55
56CREATE TABLE IF NOT EXISTS `hospital_db`.`STAFF` (
57  `ID` INT NOT NULL AUTO_INCREMENT,
58  `NAME` VARCHAR(45) NOT NULL,
59  `ROLE` VARCHAR(15) NOT NULL,
60  `TELEPHONE` VARCHAR(12) NOT NULL UNIQUE,
61  `SALARY` DOUBLE NOT NULL,
62  `PASS` VARCHAR(45) DEFAULT '1',
63  PRIMARY KEY (`ID`))
64  
65ENGINE = InnoDB;
66
67-- -----------------------------------------------------
68-- Table `hospital_db`.`APPOINTMENT`
69-- -----------------------------------------------------
70CREATE TABLE IF NOT EXISTS `hospital_db`.`APPOINTMENT` (
71  `ID` INT NOT NULL AUTO_INCREMENT,
72  `PATIENTID` INT NOT NULL,
73  `DOCTORID` INT NOT NULL,
74  `HOUR` VARCHAR(10) NULL,
75  `APPOINTMENTDATE` DATETIME NULL,
76  PRIMARY KEY (`ID`),
77  INDEX `fk_APPOINTMENT_1_idx` (`PATIENTID` ASC),
78  INDEX `fk_APPOINTMENT_2_idx` (`DOCTORID` ASC),
79  CONSTRAINT `fk_APPOINTMENT_1`
80    FOREIGN KEY (`PATIENTID`)
81    REFERENCES `hospital_db`.`Patient` (`ID`)
82    ON DELETE cascade
83    ON UPDATE cascade,
84  CONSTRAINT `fk_APPOINTMENT_2`
85    FOREIGN KEY (`DOCTORID`)
86    REFERENCES `hospital_db`.`STAFF` (`ID`)
87    ON DELETE NO ACTION
88    ON UPDATE NO ACTION)
89ENGINE = InnoDB;
90
91
92-- -----------------------------------------------------
93-- Table `hospital_db`.`MEDICATION`
94-- -----------------------------------------------------
95CREATE TABLE IF NOT EXISTS `hospital_db`.`MEDICATION` (
96  `PRICE` INT NOT NULL,
97  `NAME` VARCHAR(20) NOT NULL,
98  PRIMARY KEY (`NAME`))
99ENGINE = InnoDB;
100
101
102-- -----------------------------------------------------
103-- Table `hospital_db`.`PATIENTCASE`
104-- -----------------------------------------------------
105CREATE TABLE IF NOT EXISTS `hospital_db`.`PATIENTCASE` (
106  `ID` INT NOT NULL AUTO_INCREMENT,
107  `PATIENTID` INT NOT NULL,
108  `DISEASE` VARCHAR(45) NOT NULL,
109  `MEDICATION` VARCHAR(20) NOT NULL,
110  PRIMARY KEY (`ID`),
111  INDEX `fk_PATIENTCASE_1_idx` (`PATIENTID` ASC),
112  INDEX `fk_PATIENTCASE_2_idx` (`MEDICATION` ASC),
113  CONSTRAINT `fk_PATIENTCASE_1`
114    FOREIGN KEY (`PATIENTID`)
115    REFERENCES `hospital_db`.`Patient` (`ID`)
116    ON DELETE cascade
117    ON UPDATE cascade,
118  CONSTRAINT `fk_PATIENTCASE_2`
119    FOREIGN KEY (`MEDICATION`)
120    REFERENCES `hospital_db`.`MEDICATION` (`NAME`)
121    ON DELETE cascade
122    ON UPDATE cascade)
123ENGINE = InnoDB;
124
125CREATE TABLE IF NOT EXISTS `hospital_db`.`AVAILABILITY` (
126  `ID` INT NOT NULL AUTO_INCREMENT,
127  `DOCTORID` INT NOT NULL,
128  `DATE` date NOT NULL,
129  `HOUR` DATETIME NOT NULL,
130  PRIMARY KEY (`ID`),
131  CONSTRAINT `fk_AVAILABILITY_1`
132    FOREIGN KEY (`DOCTORID`)
133    REFERENCES `hospital_db`.`STAFF` (`ID`)
134    ON DELETE CASCADE
135    ON UPDATE CASCADE )
136ENGINE = InnoDB;
137
138
139SET SQL_MODE=@OLD_SQL_MODE;
140SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
141SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;