· 8 years ago · Apr 26, 2018, 05:18 PM
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#----------------------------------------------------------------------------------------------------------#
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218#A----------------------------------------------------------------------------------------------------------#
219
220SELECT contraIndicator FROM Medication
221WHERE medicationName = 'Calpol';
222
223#B----------------------------------------------------------------------------------------------------------#
224
225SELECT AVG(salary) FROM Staff;
226
227#C----------------------------------------------------------------------------------------------------------#
228
229SELECT
230 hospitalName, wardName
231FROM
232 Ward B LEFT JOIN Hospital A
233 ON B.hospitalID = A.hospitalID
234WHERE specialism = 'Minor Injuries'
235ORDER BY hospitalName, wardName ASC;
236
237#D needs work----------------------------------------------------------------------------------------------------------#
238
239SELECT C.medicationName, C.medDose, C.medUsage
240FROM Medication C LEFT JOIN patientMedication B JOIN Medication A
241ON A.nationalInsuranceNo = B.nationalInsuranceNo
242ON C.medID = B.medID
243WHERE nationalInsuranceNo = 'PAT005' && dateOfAdmission = '2017-11-14';
244
245#E----------------------------------------------------------------------------------------------------------#
246
247SELECT nextOfKin FROM Patients
248WHERE patientForename = 'Daniel';
249
250#F----------------------------------------------------------------------------------------------------------#
251
252SELECT
253 staffForename
254FROM
255 prescribesMedication B LEFT JOIN Staff A
256 ON B.staffID = A.staffID
257WHERE medID = 'MED003';
258
259#G----------------------------------------------------------------------------------------------------------#
260
261SELECT B.nationalInsuranceNo,patientForename, patientSurname, phoneNo, nextOfKin
262FROM Patients B LEFT JOIN patientsOnWard A
263ON B.nationalInsuranceNo = A.nationalInsuranceNo
264WHERE hospitalID = 'NHS002';
265
266#H----------------------------------------------------------------------------------------------------------#
267
268SELECT *
269FROM Medication
270WHERE contraIndicator = 'Nerve Pain';
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304INSERT INTO Hospital (hospitalName, postcode, generalPhone, generalEmail, hospitalManager)VALUES
305 ('Ysbyty Bangor','LL57 2PW','01248 384384','',''),
306 ('Ysbyty Glan Clywyd','LL18 5UJ','01745 583910','',''),
307 ('Ysbyty Penrhos Stanley','LL65 2QA','01407 766000','',''),
308 ('Ysbyty Llandudno','LL30 1LB','01492 860066','','');
309
310INSERT INTO Patients(patientForename, patientSurname, phoneNo, nextOfKin) VALUES
311 ('Daniel','Griffin','07842 987451','Harrison Griffin'),
312 ('John','Lear','07892 544435','Judith Lear'),
313 ('Stanley','Roberts','07451 985479','Markus Roberts'),
314 ('Dave','Parker','07456 986587',''),
315 ('Debbie','Jones','07845 986578','');
316
317INSERT INTO Staff (staffForename, staffSurname, salary, position) VALUES
318 ('Nik','Abdullah','087000.50','Correctoral'),
319 ('Mhd','Banzaar','124000.49','Ear nose and throat'),
320 ('Jonathan','Roberts','187000.29','Brain '),
321 ('Gary','Owen','078000.00','General Surgery'),
322 ('Debbie','Rowlands','076100.00','Cardiologist'),
323 ('Xchi','Wong','167000.00','Nervous system'),
324 ('Anne','Parry','52000.00','General Nurse'),
325 ('Mark','Wylder','174000.00','Orthamology');
326
327
328INSERT INTO Medication (medicationName, contraIndicator, medDose, medUsage) VALUES
329 ('Paracetamol','Nerve Pain','Twice Daily','Swallow'),
330 ('Amoxicillin','Bacterial Infection','Four Daily','Swallow'),
331 ('Calpol','Throat Injury','Three Daily','Swallow'),
332 ('Metformin','Diabetes','Once Daily','Swallow'),
333 ('Levothyroxine','Thyroid Injury','Once Daily','Swallow'),
334 ('Epi-Pen','Allergic Reaction','Active Injury','Injection'),
335 ('Insulin','Diabetes','Active Injury','Injection'),
336 ('Germaline','Bacterial Infection','Twice Daily','Cream'),
337 ('Rigevidon','Contraception','Once Daily','Swallow'),
338 ('Aspirin','Nerve Pain','Twice Daily','Swallow');
339
340
341INSERT INTO Ward (staffID, hospitalID, wardName, specialism) VALUES
342((SELECT staffID FROM Staff WHERE staffID = 'STA002'),(SELECT hospitalID FROM Hospital WHERE hospitalName = 'Ysbyty Glan Clywyd'),'DINA','Ear nose and throat'),
343((SELECT staffID FROM Staff WHERE staffID = 'STA003'),(SELECT hospitalID FROM Hospital WHERE hospitalName = 'Ysbyty Bangor'),'OGWEN','Brain'),
344((SELECT staffID FROM Staff WHERE staffID = 'STA006'),(SELECT hospitalID FROM Hospital WHERE hospitalName = 'Ysbyty Penrhos Stanley'),'CYBI','Minor Injuries'),
345((SELECT staffID FROM Staff WHERE staffID = 'STA007'),(SELECT hospitalID FROM Hospital WHERE hospitalName = 'Ysbyty Llandudno'),'REMI','Minor Injuries'),
346((SELECT staffID FROM Staff WHERE staffID = 'STA007'),(SELECT hospitalID FROM Hospital WHERE hospitalName = 'Ysbyty Llandudno'),'EDWEN','XRay'),
347((SELECT staffID FROM Staff WHERE staffID = 'STA001'),(SELECT hospitalID FROM Hospital WHERE hospitalName = 'Ysbyty Bangor'),'CEFNI','Correctoral'),
348((SELECT staffID FROM Staff WHERE staffID = 'STA004'),(SELECT hospitalID FROM Hospital WHERE hospitalName = 'Ysbyty Glan Clywyd'),'FOX','General'),
349((SELECT staffID FROM Staff WHERE staffID = 'STA006'),(SELECT hospitalID FROM Hospital WHERE hospitalName = 'Ysbyty Penrhos Stanley'),'MUSA','XRay'),
350((SELECT staffID FROM Staff WHERE staffID = 'STA008'),(SELECT hospitalID FROM Hospital WHERE hospitalName = 'Ysbyty Bangor'),'ELYS','Orthamology');
351
352INSERT INTO hospitalPatients (nationalInsuranceNo, hospitalID, dateOfAdmission) VALUES
353((SELECT nationalInsuranceNo FROM Patients WHERE nationalInsuranceNo = 'PAT001'),(SELECT hospitalID FROM Hospital WHERE hospitalID = 'NHS001'), '2017-12-11'),
354((SELECT nationalInsuranceNo FROM Patients WHERE nationalInsuranceNo = 'PAT002'),(SELECT hospitalID FROM Hospital WHERE hospitalID = 'NHS001'), '2017-10-21'),
355((SELECT nationalInsuranceNo FROM Patients WHERE nationalInsuranceNo = 'PAT003'),(SELECT hospitalID FROM Hospital WHERE hospitalID = 'NHS002'), '2017-09-16'),
356((SELECT nationalInsuranceNo FROM Patients WHERE nationalInsuranceNo = 'PAT004'),(SELECT hospitalID FROM Hospital WHERE hospitalID = 'NHS003'), '2017-01-07'),
357((SELECT nationalInsuranceNo FROM Patients WHERE nationalInsuranceNo = 'PAT005'),(SELECT hospitalID FROM Hospital WHERE hospitalID = 'NHS004'), '2017-05-14');
358
359INSERT INTO patientsOnWard (wardID, nationalInsuranceNo, hospitalID) VALUES
360((SELECT wardID FROM Ward WHERE wardID = 'WAR006'),(SELECT nationalInsuranceNo FROM Patients WHERE nationalInsuranceNo = 'PAT001'),(SELECT hospitalID FROM Hospital WHERE hospitalID = 'NHS003')),
361((SELECT wardID FROM Ward WHERE wardID = 'WAR002'),(SELECT nationalInsuranceNo FROM Patients WHERE nationalInsuranceNo = 'PAT002'),(SELECT hospitalID FROM Hospital WHERE hospitalID = 'NHS001')),
362((SELECT wardID FROM Ward WHERE wardID = 'WAR003'),(SELECT nationalInsuranceNo FROM Patients WHERE nationalInsuranceNo = 'PAT003'),(SELECT hospitalID FROM Hospital WHERE hospitalID = 'NHS002')),
363((SELECT wardID FROM Ward WHERE wardID = 'WAR004'),(SELECT nationalInsuranceNo FROM Patients WHERE nationalInsuranceNo = 'PAT004'),(SELECT hospitalID FROM Hospital WHERE hospitalID = 'NHS002')),
364((SELECT wardID FROM Ward WHERE wardID = 'WAR005'),(SELECT nationalInsuranceNo FROM Patients WHERE nationalInsuranceNo = 'PAT005'),(SELECT hospitalID FROM Hospital WHERE hospitalID = 'NHS004'));
365
366INSERT INTO patientMedication (medID, nationalInsuranceNo, medStart, medEnd) VALUES
367((SELECT medID FROM Medication WHERE medID = 'MED001'),(SELECT nationalInsuranceNo FROM Patients WHERE nationalInsuranceNo = 'PAT001'), '2017-09-11', '2017-11-11'),
368((SELECT medID FROM Medication WHERE medID = 'MED002'),(SELECT nationalInsuranceNo FROM Patients WHERE nationalInsuranceNo = 'PAT001'), '2017-11-14', '2017-12-14'),
369((SELECT medID FROM Medication WHERE medID = 'MED005'),(SELECT nationalInsuranceNo FROM Patients WHERE nationalInsuranceNo = 'PAT002'), '2017-09-11', '2017-11-11'),
370((SELECT medID FROM Medication WHERE medID = 'MED005'),(SELECT nationalInsuranceNo FROM Patients WHERE nationalInsuranceNo = 'PAT003'), '2017-05-27', '2017-07-01'),
371((SELECT medID FROM Medication WHERE medID = 'MED006'),(SELECT nationalInsuranceNo FROM Patients WHERE nationalInsuranceNo = 'PAT004'), '2017-04-19', '2017-08-08'),
372((SELECT medID FROM Medication WHERE medID = 'MED002'),(SELECT nationalInsuranceNo FROM Patients WHERE nationalInsuranceNo = 'PAT005'), '2017-11-04', '2017-12-04');
373
374INSERT INTO prescribesMedication (medID, staffID) VALUES
375((SELECT medID FROM Medication WHERE medID = 'MED001'),(SELECT staffID FROM Staff WHERE staffID = 'STA007')),
376((SELECT medID FROM Medication WHERE medID = 'MED002'),(SELECT staffID FROM Staff WHERE staffID = 'STA001')),
377((SELECT medID FROM Medication WHERE medID = 'MED003'),(SELECT staffID FROM Staff WHERE staffID = 'STA007')),
378((SELECT medID FROM Medication WHERE medID = 'MED004'),(SELECT staffID FROM Staff WHERE staffID = 'STA005')),
379((SELECT medID FROM Medication WHERE medID = 'MED005'),(SELECT staffID FROM Staff WHERE staffID = 'STA001')),
380((SELECT medID FROM Medication WHERE medID = 'MED006'),(SELECT staffID FROM Staff WHERE staffID = 'STA005')),
381((SELECT medID FROM Medication WHERE medID = 'MED007'),(SELECT staffID FROM Staff WHERE staffID = 'STA006')),
382((SELECT medID FROM Medication WHERE medID = 'MED008'),(SELECT staffID FROM Staff WHERE staffID = 'STA007')),
383((SELECT medID FROM Medication WHERE medID = 'MED009'),(SELECT staffID FROM Staff WHERE staffID = 'STA003')),
384((SELECT medID FROM Medication WHERE medID = 'MED010'),(SELECT staffID FROM Staff WHERE staffID = 'STA008'));