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