· 5 years ago · Oct 03, 2020, 03:20 AM
1AUTO INCREMENT (1)
2
3//Creating Establishment Table
4CREATE TABLE IF NOT EXISTS `mydb`.`establishment` (
5 `id` INT NOT NULL AUTO_INCREMENT,
6 `name` VARCHAR(45) NULL,
7 `duration` INT NULL,
8 `is_airconditioned` TINYINT NULL,
9 `location_id` INT NOT NULL,
10 `establishment_category_id` INT NOT NULL,
11 PRIMARY KEY (`id`),
12 INDEX `fk_establishment_location1_idx` (`location_id` ASC) VISIBLE,
13 INDEX `fk_establishment_establishment_category1_idx` (`establishment_category_id` ASC) VISIBLE,
14 CONSTRAINT `fk_establishment_location1`
15 FOREIGN KEY (`location_id`)
16 REFERENCES `mydb`.`location` (`id`)
17 ON DELETE NO ACTION
18 ON UPDATE NO ACTION,
19 CONSTRAINT `fk_establishment_establishment_category1`
20 FOREIGN KEY (`establishment_category_id`)
21 REFERENCES `mydb`.`establishment_category` (`id`)
22 ON DELETE NO ACTION
23 ON UPDATE NO ACTION)
24ENGINE = InnoDB;
25------------------------------------------------------------------------------
26CREATE INDEX (1)
27
28//Creating Address Table
29CREATE TABLE IF NOT EXISTS `mydb`.`address` (
30 `house_no` INT NULL,
31 `lot` VARCHAR(45) NULL,
32 `barangay` VARCHAR(45) NULL,
33 `street` VARCHAR(45) NULL,
34 `building` VARCHAR(45) NULL,
35 `municipality` VARCHAR(45) NULL,
36 `city` VARCHAR(45) NULL,
37 `province` VARCHAR(45) NULL,
38 `region` VARCHAR(45) NULL,
39 `person_id` INT NOT NULL,
40 INDEX `fk_address_person_idx` (`person_id` ASC) VISIBLE,
41 CONSTRAINT `fk_address_person`
42 FOREIGN KEY (`person_id`)
43 REFERENCES `mydb`.`person` (`id`)
44 ON DELETE NO ACTION
45 ON UPDATE NO ACTION)
46ENGINE = InnoDB;
47------------------------------------------------------------------------------
48FULLTEXT (1)
49
50// Creating Person Table
51CREATE TABLE IF NOT EXISTS `mydb`.`person` (
52 `id` INT NOT NULL AUTO_INCREMENT,
53 `first_name` VARCHAR(45) NULL,
54 `last_name` VARCHAR(45) NULL,
55 `suffix` VARCHAR(45) NULL,
56 `nationality` VARCHAR(45) NULL,
57 `birthday` DATE NULL,
58 `gender` VARCHAR(45) NULL,
59 `civil_status` VARCHAR(45) NULL,
60 PRIMARY KEY (`id`),
61 FULLTEXT INDEX `full_name` (`first_name`, `last_name`, `suffix`) INVISIBLE)
62ENGINE = InnoDB;
63------------------------------------------------------------------------------
64JOINS (INNER,LEFT OUTER, RIGHT OUTER, SELF) (4)
65
66//Maximum number of establishment occuring in the same place
67SELECT MAX(establishment.location_id)
68FROM location
69INNER JOIN establishment
70ON location.id = establishment.location_id
71HAVING COUNT(location.id) > 1;
72
73//Check the number of current recorded disease
74SELECT disease.name
75FROM disease
76LEFT JOIN person_disease
77ON disease.id = person_disease.disease_id
78GROUP BY disease.name
79
80//Display all person that has Novel Coronavirus Cases
81SELECT COUNT(*)
82FROM person
83LEFT JOIN person_disease
84ON person.id = person_disease.person_id
85RIGHT JOIN disease
86ON person_disease.disease_id = disease.id
87WHERE disease.name = 'Novel Coronavirus'
88AND person_disease.datetime <= DATE_SUB(NOW(), INTERVAL 6 MONTH);
89
90//Check all person with the same last name
91SELECT A.first_name , A.last_name
92FROM person A, person B
93WHERE A.id <> B.id
94AND A.last_name = B.last_name
95GROUP BY A.first_name
96------------------------------------------------------------------------------
97UNION (1)
98
99// Get All Travel History
100SELECT all_person.person_id, person.first_name, person.last_name, person.suffix, disease.name as disease, classification.name as classification
101FROM
102(SELECT person_id, datetime, temperature, duration
103FROM person_establishment
104UNION
105SELECT person_id, datetime, temperature, duration
106FROM person_event
107UNION
108SELECT person_id, datetime, temperature, duration
109FROM person_transportation) AS all_person
110INNER JOIN person
111ON all_person.person_id = person.id
112LEFT JOIN person_disease
113ON person.id = person_disease.person_id
114LEFT JOIN disease
115ON person_disease.disease_id = disease.id
116LEFT JOIN classification
117ON person_disease.classification_id = classification.id
118GROUP BY all_person.person_id
119------------------------------------------------------------------------------
120MINUS/NOT IN (1)
121------------------------------------------------------------------------------
122EXISTS/IN (1)
123
124// Get All Person that attended an event with temperature of greater than 36
125SELECT person.id, person.first_name, person.last_name, person.suffix, disease.name as disease, classification.name as classification
126FROM person
127LEFT JOIN person_disease
128ON person.id = person_disease.person_id
129LEFT JOIN disease
130ON person_disease.disease_id = disease.id
131LEFT JOIN classification
132ON person_disease.classification_id = classification.id
133WHERE EXISTS (SELECT * FROM person_event WHERE person_event.person_id = person.id AND temperature > 36)
134------------------------------------------------------------------------------
135SUBQUERY (FROM Clause, WHERE clause ) (2)
136
137// Get All Travel History
138SELECT all_person.person_id, person.first_name, person.last_name, person.suffix, disease.name as disease, classification.name as classification
139FROM
140(SELECT person_id, datetime, temperature, duration
141FROM person_establishment
142UNION
143SELECT person_id, datetime, temperature, duration
144FROM person_event
145UNION
146SELECT person_id, datetime, temperature, duration
147FROM person_transportation) AS all_person
148INNER JOIN person
149ON all_person.person_id = person.id
150LEFT JOIN person_disease
151ON person.id = person_disease.person_id
152LEFT JOIN disease
153ON person_disease.disease_id = disease.id
154LEFT JOIN classification
155ON person_disease.classification_id = classification.id
156GROUP BY all_person.person_id
157
158// Get All Person that attended an event with temperature of greater than 36
159SELECT person.id, person.first_name, person.last_name, person.suffix, disease.name as disease, classification.name as classification
160FROM person
161LEFT JOIN person_disease
162ON person.id = person_disease.person_id
163LEFT JOIN disease
164ON person_disease.disease_id = disease.id
165LEFT JOIN classification
166ON person_disease.classification_id = classification.id
167WHERE EXISTS (SELECT * FROM person_event WHERE person_event.person_id = person.id AND temperature > 36)
168------------------------------------------------------------------------------
169CASE (1)
170
171//Checking temperature status of a person who entered the establishment
172SELECT person.first_name, person.last_name, person.suffix,
173 CASE
174 WHEN person_establishment.temperature > 36 THEN "Not normal"
175 ELSE "Normal"
176 END AS temperature_status
177FROM person
178LEFT JOIN person_establishment
179ON person.id = person_establishment.person_id
180RIGHT JOIN establishment
181ON person_establishment.establishment_id = establishment.id
182------------------------------------------------------------------------------
183SELECT INTO/INSERT INTO SELECT (1)
184------------------------------------------------------------------------------
185VIEWS (2)
186
187//Views for all Suspected Person
188USE `mydb`;
189CREATE OR REPLACE VIEW `suspected_person` AS
190SELECT person.first_name, person.last_name, person.suffix
191FROM person
192LEFT JOIN person_disease
193ON person.id = person_disease.person_id
194LEFT JOIN classification
195ON person_disease.classification_id = classification.id
196WHERE classification.name = 'Suspected';
197
198USE `mydb`;
199CREATE OR REPLACE VIEW `probable_person` AS
200SELECT person.first_name, person.last_name, person.suffix
201FROM person
202LEFT JOIN person_disease
203ON person.id = person_disease.person_id
204LEFT JOIN classification
205ON person_disease.classification_id = classification.id
206WHERE classification.name = 'Probable';
207
208USE `mydb`;
209CREATE OR REPLACE VIEW `confirmed_person` AS
210SELECT person.first_name, person.last_name, person.suffix
211FROM person
212LEFT JOIN person_disease
213ON person.id = person_disease.person_id
214LEFT JOIN classification
215ON person_disease.classification_id = classification.id
216WHERE classification.name = 'Confirmed';
217------------------------------------------------------------------------------
218TRIGGERS (4)
219
220DROP TRIGGER IF EXISTS `mydb`.`person_BEFORE_DELETE`;
221
222DELIMITER $$
223USE `mydb`$$
224CREATE DEFINER = CURRENT_USER TRIGGER `mydb`.`person_BEFORE_DELETE` BEFORE DELETE ON `person` FOR EACH ROW
225BEGIN
226 DELETE FROM mydb.address WHERE address.person_id = old.id;
227 DELETE FROM mydb.employment WHERE employment.person_id = old.id;
228 DELETE FROM mydb.contact_detail WHERE employment.contact_detail = old.id;
229 DELETE FROM mydb.person_disease WHERE person_disease.person_id = old.id;
230 DELETE FROM mydb.person_establishment WHERE person_establishment.person_id = old.id;
231 DELETE FROM mydb.person_event WHERE person_establishment.person_id = old.id;
232 DELETE FROM mydb.person_transportation WHERE person_establishment.person_id = old.id;
233END$$
234DELIMITER ;
235
236DROP TRIGGER IF EXISTS `mydb`.`disease_BEFORE_DELETE`;
237
238DELIMITER $$
239USE `mydb`$$
240CREATE DEFINER = CURRENT_USER TRIGGER `mydb`.`disease_BEFORE_DELETE` BEFORE DELETE ON `disease` FOR EACH ROW
241BEGIN
242 DELETE FROM mydb.person_disease WHERE person_disease.disease_id = old.id;
243END$$
244DELIMITER ;
245
246
247DROP TRIGGER IF EXISTS `mydb`.`establishment_category_type_BEFORE_DELETE`;
248
249DELIMITER $$
250USE `mydb`$$
251CREATE DEFINER = CURRENT_USER TRIGGER `mydb`.`establishment_category_type_BEFORE_DELETE` BEFORE DELETE ON `establishment_category_type` FOR EACH ROW
252BEGIN
253 UPDATE mydb.establishment
254 SET establishment.establishment_category_type_id = null
255 WHERE establishment.establishment_category_type_id = old.id;
256END$$
257DELIMITER ;
258
259DROP TRIGGER IF EXISTS `mydb`.`transportation_BEFORE_DELETE`;
260
261DELIMITER $$
262USE `mydb`$$
263CREATE DEFINER=`root`@`localhost` TRIGGER `transportation_BEFORE_DELETE` BEFORE DELETE ON `transportation` FOR EACH ROW BEGIN
264 DELETE FROM mydb.person_transportation WHERE person_transportation.transportation_id = old.id;
265 DELETE FROM mydb.transportation_route WHERE transportation_route.transportation_id = old.id;
266END$$
267DELIMITER ;
268
269
270DROP TRIGGER IF EXISTS `mydb`.`establishment_BEFORE_DELETE`;
271
272DELIMITER $$
273USE `mydb`$$
274CREATE DEFINER = CURRENT_USER TRIGGER `mydb`.`establishment_BEFORE_DELETE` BEFORE DELETE ON `establishment` FOR EACH ROW
275BEGIN
276 DELETE FROM mydb.person_establishment WHERE person_establishment.establishment_id = old.id;
277END$$
278DELIMITER ;
279
280DROP TRIGGER IF EXISTS `mydb`.`event_BEFORE_DELETE`;
281
282DELIMITER $$
283USE `mydb`$$
284CREATE DEFINER = CURRENT_USER TRIGGER `mydb`.`event_BEFORE_DELETE` BEFORE DELETE ON `event` FOR EACH ROW
285BEGIN
286 DELETE FROM mydb.person_event WHERE person_event.event_id = old.id;
287END$$
288DELIMITER ;
289
290------------------------------------------------------------------------------
291STORED PROCEDURES (4)
292
293//Person Diseases
294USE `mydb`;
295DROP procedure IF EXISTS `person_disease`;
296
297DELIMITER $$
298USE `mydb`$$
299CREATE DEFINER=`root`@`localhost` PROCEDURE `person_disease`(IN disease char(45), classification char(45))
300BEGIN
301 SELECT person.first_name, person.last_name, person.suffix, disease.name AS disease, classification.name AS classification
302 FROM person
303 LEFT JOIN person_disease
304 ON person.id = person_disease.person_id
305 RIGHT JOIN disease
306 ON person_disease.disease_id = disease.id
307 RIGHT JOIN classification
308 ON person_disease.classification_id = classification.id
309 WHERE disease.name = disease AND classification.name = classification;
310END$$
311
312DELIMITER ;
313
314//Disease Classification
315USE `mydb`;
316DROP procedure IF EXISTS `disease_classification`;
317
318DELIMITER $$
319USE `mydb`$$
320CREATE PROCEDURE `disease_classification`
321(IN disease char(45))
322BEGIN
323 SELECT disease.name AS disease, classification.name as classification
324 FROM disease
325 LEFT JOIN disease_classification
326 ON disease.id = disease_classification.disease_id
327 RIGHT JOIN classification
328 ON disease_classification.classification_id = classification.id
329WHERE disease.name = disease;
330END$$
331
332DELIMITER ;
333
334//Event
335USE `mydb`;
336DROP procedure IF EXISTS `person_event`;
337
338DELIMITER $$
339USE `mydb`$$
340CREATE PROCEDURE `person_event` (IN event char(45))
341BEGIN
342 SELECT person_event.datetime, person.first_name, person.last_name, person.suffix, event.name AS event, event.duration AS event_duration, person_event.temperature, person_event.duration
343 FROM person
344 LEFT JOIN person_event
345 ON person.id = person_event.person_id
346 RIGHT JOIN event
347 ON person_event.event_id = event.id
348 WHERE event.name = event;
349END$$
350
351DELIMITER ;
352
353//Establishment
354USE `mydb`;
355DROP procedure IF EXISTS `person_establishment`;
356
357DELIMITER $$
358USE `mydb`$$
359CREATE PROCEDURE `person_establishment` (IN establishment char(45))
360BEGIN
361 SELECT person_establishment.datetime,
362 person.first_name,
363 person.last_name,
364 person.suffix,
365 establishment.name AS establishment,
366 establishment_category.name AS establishment_category,
367 establishment_category_type.name AS establishment_category_type,
368 person_establishment.temperature,
369 person_establishment.duration
370 FROM person
371 LEFT JOIN person_establishment
372 ON person.id = person_establishment.person_id
373 RIGHT JOIN establishment
374 ON person_establishment.establishment_id = establishment.id
375 LEFT JOIN establishment_category_type
376 ON establishment.establishment_category_type_id = establishment_category_type.id
377 LEFT JOIN establishment_category
378 ON establishment_category_type.establishment_category_id = establishment_category.id
379 WHERE establishment.name = establishment;
380END$$
381
382DELIMITER ;
383------------------------------------------------------------------------------
384STORED FUNCTIONS/USER-DEFINED FUNCTIONS (UDFs) (4)
385------------------------------------------------------------------------------
386NATIVE FUNCTIONS (COUNT(),MAX()/MIN(),UCASE/LCASE,NOW()) (4)
387
388//Display the total number of Novel Coronavirus Cases
389SELECT COUNT(*)
390FROM person
391LEFT JOIN person_disease
392ON person.id = person_disease.person_id
393RIGHT JOIN disease
394ON person_disease.disease_id = disease.id
395WHERE disease.name = 'Novel Coronavirus'
396AND person_disease.datetime <= DATE_SUB(NOW(), INTERVAL 6 MONTH);
397
398//Maximum number of establishment occuring in the same place
399SELECT MAX(establishment.location_id)
400FROM location
401INNER JOIN establishment
402ON location.id = establishment.location_id
403HAVING COUNT(location.id) > 1;
404
405//Display classification in uppercase
406SELECT UCASE(classification.name)
407FROM classification;
408
409//Display all person that has Novel Coronavirus Cases
410SELECT COUNT(*)
411FROM person
412LEFT JOIN person_disease
413ON person.id = person_disease.person_id
414RIGHT JOIN disease
415ON person_disease.disease_id = disease.id
416WHERE disease.name = 'Novel Coronavirus'
417AND person_disease.datetime <= DATE_SUB(NOW(), INTERVAL 6 MONTH);
418------------------------------------------------------------------------------
419GROUP BY (1)
420
421//Check the current recorded disease
422SELECT disease.name, COUNT(disease.id) AS cases
423FROM disease
424LEFT JOIN person_disease
425ON disease.id = person_disease.disease_id
426GROUP BY disease.name
427------------------------------------------------------------------------------
428HAVING (1)
429
430//Check the count of establishment occuring on the same location
431SELECT establishment.name
432FROM establishment
433INNER JOIN location
434ON establishment.location_id = location.id
435HAVING COUNT(location.id) > 1;
436------------------------------------------------------------------------------