H5zJJUxA

1#----------------------------------------------------------------------------------------------------------#
2-- here is the failsafe method to turn off contraints and allows
3-- you to drop the tables safely then turn contraints back on
4SET FOREIGN_KEY_CHECKS=0;
5	#table drops
6DROP TABLE IF EXISTS Hospital;
7DROP TABLE IF EXISTS Patients;
8DROP TABLE IF EXISTS Staff;
9DROP TABLE IF EXISTS Medication;
10DROP TABLE IF EXISTS Ward;
11	#logical table drops
12DROP TABLE IF EXISTS patientMedication;
13DROP TABLE IF EXISTS patientsOnWard;
14DROP TABLE IF EXISTS prescribesMedication;
15DROP TABLE IF EXISTS hospitalPatients;
16	#trigger drops
17DROP TABLE IF EXISTS table1_seq;
18DROP TABLE IF EXISTS table2_seq;
19DROP TABLE IF EXISTS table3_seq;
20DROP TABLE IF EXISTS table4_seq;
21DROP TABLE IF EXISTS table5_seq;
22SET FOREIGN_KEY_CHECKS=1;
23#----------------------------------------------------------------------------------------------------------#
24
25CREATE TABLE IF NOT EXISTS Hospital (
26    hospitalID VARCHAR(7) NOT NULL PRIMARY KEY DEFAULT '0',
27    hospitalName VARCHAR(50) NOT NULL,
28    postcode CHAR(8) NOT NULL,
29    generalPhone VARCHAR(14) NOT NULL,
30    generalEmail VARCHAR(50),
31    hospitalManager VARCHAR(50)
32)  ENGINE=INNODB;
33
34CREATE TABLE table1_seq (
35    hospitalID INT NOT NULL AUTO_INCREMENT PRIMARY KEY
36);
37
38DELIMITER $$
39CREATE TRIGGER tg_table1_insert
40BEFORE INSERT ON Hospital
41FOR EACH ROW
42BEGIN
43  INSERT INTO table1_seq VALUES (NULL);
44  SET NEW.hospitalID = CONCAT('NHS', LPAD(LAST_INSERT_ID(), 3, '0'));
45END$$
46DELIMITER ;
47
48CREATE TABLE IF NOT EXISTS Patients (
49    nationalInsuranceNo VARCHAR(7) NOT NULL PRIMARY KEY DEFAULT '0',
50    patientForename VARCHAR(50) NOT NULL,
51    patientSurname VARCHAR(50) NOT NULL,
52    phoneNo CHAR(14),
53    nextOfKin VARCHAR(50)
54);
55
56CREATE TABLE table2_seq (
57    nationalInsuranceNo INT NOT NULL AUTO_INCREMENT PRIMARY KEY
58);
59
60DELIMITER $$
61CREATE TRIGGER tg_table2_insert
62BEFORE INSERT ON Patients
63FOR EACH ROW
64BEGIN
65  INSERT INTO table2_seq VALUES (NULL);
66  SET NEW.nationalInsuranceNo = CONCAT('PAT', LPAD(LAST_INSERT_ID(), 3, '0'));
67END$$
68DELIMITER ;
69
70CREATE TABLE IF NOT EXISTS Staff (
71    staffID VARCHAR(7) NOT NULL PRIMARY KEY DEFAULT '0',
72    staffForename VARCHAR(50) NOT NULL,
73    staffSurname VARCHAR(50) NOT NULL,
74    salary DECIMAL(8 , 2 ),
75    position VARCHAR(50)
76);
77
78CREATE TABLE table3_seq (
79    staffID INT NOT NULL AUTO_INCREMENT PRIMARY KEY
80);
81
82DELIMITER $$
83CREATE TRIGGER tg_table3_insert
84BEFORE INSERT ON Staff
85FOR EACH ROW
86BEGIN
87  INSERT INTO table3_seq VALUES (NULL);
88  SET NEW.staffID = CONCAT('STA', LPAD(LAST_INSERT_ID(), 3, '0'));
89END$$
90DELIMITER ;
91
92CREATE TABLE IF NOT EXISTS Medication (
93    medID VARCHAR(7) NOT NULL PRIMARY KEY DEFAULT '0',
94    medicationName VARCHAR(50) NOT NULL,
95    contraIndicator VARCHAR(50),
96    medDose VARCHAR(50) NOT NULL,
97    medUsage VARCHAR(50) NOT NULL
98);
99
100CREATE TABLE table4_seq (
101    medID INT NOT NULL AUTO_INCREMENT PRIMARY KEY
102);
103
104DELIMITER $$
105CREATE TRIGGER tg_table4_insert
106BEFORE INSERT ON Medication
107FOR EACH ROW
108BEGIN
109  INSERT INTO table4_seq VALUES (NULL);
110  SET NEW.medID = CONCAT('MED', LPAD(LAST_INSERT_ID(), 3, '0'));
111END$$
112DELIMITER ;
113
114CREATE TABLE IF NOT EXISTS Ward (
115    wardID VARCHAR(10) NOT NULL PRIMARY KEY DEFAULT '0',
116    staffID CHAR(10) NOT NULL,
117    hospitalID CHAR(10) NOT NULL,
118    wardName VARCHAR(50) NOT NULL,
119    specialism VARCHAR(50) NOT NULL,
120    FOREIGN KEY (staffID)
121        REFERENCES Staff (staffID),
122    FOREIGN KEY (hospitalID)
123        REFERENCES Hospital (hospitalID)
124);
125
126CREATE TABLE table5_seq (
127    wardID INT NOT NULL AUTO_INCREMENT PRIMARY KEY
128);
129
130DELIMITER $$
131CREATE TRIGGER tg_table5_insert
132BEFORE INSERT ON Ward
133FOR EACH ROW
134BEGIN
135  INSERT INTO table5_seq VALUES (NULL);
136  SET NEW.wardID = CONCAT('WAR', LPAD(LAST_INSERT_ID(), 3, '0'));
137END$$
138DELIMITER ;
139
140-- logical
141CREATE TABLE IF NOT EXISTS hospitalPatients (
142    nationalInsuranceNo CHAR(10) NOT NULL,
143    hospitalID CHAR(10) NOT NULL,
144    dateOfAdmission DATE NOT NULL,
145    PRIMARY KEY (nationalInsuranceNo , hospitalID),
146    FOREIGN KEY (nationalInsuranceNo)
147        REFERENCES Patients (nationalInsuranceNo),
148    FOREIGN KEY (hospitalID)
149        REFERENCES Hospital (hospitalID)
150);
151
152-- logical
153CREATE TABLE IF NOT EXISTS patientsOnWard (
154    wardID CHAR(10) NOT NULL,
155    nationalInsuranceNo CHAR(10) NOT NULL,
156    hospitalID CHAR(10) NOT NULL,
157    PRIMARY KEY (wardID , nationalInsuranceNo , hospitalID),
158    FOREIGN KEY (wardID)
159        REFERENCES Ward (wardID),
160    FOREIGN KEY (nationalInsuranceNo)
161        REFERENCES Patients (nationalInsuranceNo),
162    FOREIGN KEY (hospitalID)
163        REFERENCES Hospital (hospitalID)
164);
165
166-- logical
167CREATE TABLE IF NOT EXISTS patientMedication (
168    medID CHAR(10) NOT NULL,
169    nationalInsuranceNo CHAR(10) NOT NULL,
170    medStart DATE NOT NULL,
171    medEnd DATE NOT NULL,
172    PRIMARY KEY (medID , nationalInsuranceNo),
173    FOREIGN KEY (medID)
174        REFERENCES Medication (medID),
175    FOREIGN KEY (nationalInsuranceNo)
176        REFERENCES Patients (nationalInsuranceNo)
177);
178
179-- logical
180CREATE TABLE IF NOT EXISTS prescribesMedication (
181    medID CHAR(10) NOT NULL,
182    staffID CHAR(10) NOT NULL,
183    PRIMARY KEY (medID , staffID),
184    FOREIGN KEY (medID)
185        REFERENCES Medication (medID),
186    FOREIGN KEY (staffID)
187        REFERENCES Staff (staffID)
188);
189
190#----------------------------------------------------------------------------------------------------------#