CS5200 Practicum #1 by Group #1

A. UML Class Diagram

B. ERD

C. Definition of relational schema with proof that it is in BCNF

D. Table definition and creation SQL statements

(a). Table definition

CREATE DATABASE if not exists contact_tracing;

USE contact_tracing;


drop table if exists lab_result;
drop table if exists testing_sample;
drop table if exists testing_department;
drop table if exists treatment_plan;
drop table if exists treatment_department;
drop table if exists hospital;
drop table if exists contact_to_event;
drop table if exists contact;
drop table if exists contact_event;
drop table if exists patient;
drop table if exists person;
drop table if exists location;



CREATE TABLE location (
zipcode INT NOT NULL PRIMARY KEY,
street VARCHAR(100) NOT NULL,
city VARCHAR(50) NOT NULL,
state VARCHAR(30) NOT NULL);


INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (706, 'Madie Summit', 'North Chad', 'Massachusetts');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (2366, 'McCullough Station', 'West Ivy', 'NewHampshire');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (11266, 'Tobin Curve', 'East Sally', 'Massachusetts');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (12055, 'Boyer Spurs', 'Funkbury', 'Minnesota');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (14458, 'Royce Glens', 'Lake Daron', 'WestVirginia');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (14862, 'Homenick Stream', 'O\'Keefefort', 'Tennessee');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (17657, 'Lillian Radial', 'North Leviburgh', 'SouthCarolina');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (22413, 'Melba Valleys', 'Elmoport', 'Massachusetts');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (34529, 'Turner Route', 'Littelville', 'NewJersey');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (38307, 'Hintz Forges', 'Cleoport', 'Mississippi');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (39384, 'Batz Vista', 'East Shemarton', 'Louisiana');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (43567, 'Nader Common', 'Lake Valentin', 'Indiana');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (44072, 'Bergstrom Plain', 'South Caseyland', 'RhodeIsland');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (48644, 'Koelpin Green', 'East Kathrynborough', 'Connecticut');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (53327, 'Tito Mills', 'West Antonette', 'Wisconsin');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (53710, 'Rowe Fork', 'Wavaview', 'NewYork');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (55668, 'Lindgren Crescent', 'West Veldachester', 'Utah');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (56720, 'Ankunding Grove', 'Michaelafort', 'Alabama');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (62366, 'Brekke Ways', 'Annetteburgh', 'Maryland');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (65399, 'Emmerich Station', 'Roweborough', 'Massachusetts');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (67478, 'Marvin Corners', 'Leuschkeville', 'Texas');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (68925, 'Reed Station', 'Port Justyn', 'Oklahoma');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (69793, 'Aliza Courts', 'North Daisy', 'WestVirginia');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (72806, 'Leda Plains', 'Armstrongborough', 'Utah');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (75439, 'Carroll Club', 'Ericside', 'Connecticut');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (84760, 'Lang Divide', 'Jacintoburgh', 'Arizona');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (89729, 'Eliane Bridge', 'Lake Ora', 'NewHampshire');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (93183, 'Bogan Underpass', 'Croninhaven', 'Nevada');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (93649, 'Tierra Keys', 'Hallemouth', 'Kansas');
INSERT INTO `location` (`zipcode`, `street`, `city`, `state`) VALUES (96844, 'Dale Forge', 'Port Naomiefort', 'Maryland');


CREATE TABLE person (person_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
age INT NOT NULL,
gender ENUM('male', 'female') NOT NULL,
zipcode INT NOT NULL,
FOREIGN KEY (zipcode) REFERENCES location(zipcode));


INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (1, 'Thomas', 'O\'Kon', 89, 'female', 53327);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (2, 'Gaston', 'Yost', 87, 'female', 14862);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (3, 'Garth', 'Gorczany', 34, 'male', 53327);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (4, 'Cierra', 'Kessler', 5, 'female', 53327);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (5, 'Dexter', 'Koepp', 41, 'female', 72806);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (6, 'Kayley', 'Hyatt', 61, 'male', 89729);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (7, 'Lexus', 'Berge', 32, 'female', 39384);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (8, 'Anne', 'Pouros', 85, 'male', 75439);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (9, 'Jackson', 'Koelpin', 35, 'female', 55668);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (10, 'Granville', 'Veum', 87, 'female', 68925);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (11, 'Will', 'Monahan', 48, 'female', 93649);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (12, 'Heidi', 'Hand', 44, 'male', 48644);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (13, 'Troy', 'Mertz', 17, 'male', 53327);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (14, 'Anna', 'Sanford', 12, 'male', 11266);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (15, 'Brown', 'Volkman', 18, 'female', 38307);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (16, 'Brandyn', 'Jerde', 73, 'male', 12055);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (17, 'Norma', 'Kuvalis', 95, 'female', 39384);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (18, 'Sandy', 'Bogan', 33, 'female', 14862);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (19, 'Autumn', 'Mueller', 14, 'male', 65399);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (20, 'Moriah', 'Leannon', 20, 'male', 55668);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (21, 'Mia', 'Gerlach', 31, 'male', 55668);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (22, 'Devonte', 'Rohan', 38, 'male', 11266);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (23, 'Elena', 'Bruen', 36, 'male', 44072);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (24, 'Jeanne', 'Hessel', 93, 'male', 55668);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (25, 'May', 'Spinka', 29, 'female', 14458);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (26, 'Mariam', 'Mohr', 48, 'female', 11266);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (27, 'Trever', 'Johns', 25, 'male', 96844);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (28, 'Ila', 'Jenkins', 48, 'male', 2366);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (29, 'Donnie', 'Ernser', 88, 'female', 62366);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (30, 'Ted', 'Ebert', 69, 'male', 89729);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (31, 'Joshuah', 'Beahan', 70, 'female', 75439);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (32, 'Harvey', 'Sipes', 34, 'female', 12055);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (33, 'Marlee', 'Lowe', 41, 'female', 2366);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (34, 'Trycia', 'Wyman', 79, 'female', 34529);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (35, 'Herminio', 'Walker', 12, 'female', 56720);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (36, 'Margie', 'Hilpert', 36, 'male', 84760);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (37, 'Scarlett', 'Stamm', 38, 'female', 14862);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (38, 'Kameron', 'Bruen', 83, 'female', 93183);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (39, 'Maximillian', 'Leffler', 56, 'male', 65399);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (40, 'Helga', 'Barrows', 85, 'female', 68925);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (41, 'Violette', 'Sipes', 88, 'female', 65399);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (42, 'Jeanette', 'Harvey', 89, 'female', 56720);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (43, 'Sean', 'Wilkinson', 42, 'female', 14458);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (44, 'Lamont', 'Olson', 68, 'female', 12055);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (45, 'Dayton', 'Barrows', 72, 'male', 67478);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (46, 'Lavina', 'Corkery', 95, 'female', 53327);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (47, 'Verlie', 'Rogahn', 81, 'male', 22413);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (48, 'Devante', 'Harber', 71, 'male', 2366);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (49, 'Roel', 'Hayes', 32, 'male', 65399);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (50, 'Erik', 'Nikolaus', 28, 'female', 89729);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (51, 'Reyes', 'Maggio', 82, 'male', 56720);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (52, 'Dannie', 'White', 83, 'male', 17657);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (53, 'Shanon', 'Windler', 29, 'male', 93649);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (54, 'Zora', 'Hoeger', 84, 'female', 706);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (55, 'Dejon', 'King', 47, 'male', 72806);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (56, 'Georgianna', 'Robel', 25, 'male', 12055);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (57, 'Leatha', 'Schmitt', 49, 'male', 11266);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (58, 'Demetrius', 'Dare', 37, 'male', 69793);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (59, 'Cornell', 'Pollich', 3, 'female', 14862);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (60, 'Sigmund', 'Jacobs', 47, 'male', 67478);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (61, 'Lola', 'Pagac', 30, 'female', 65399);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (62, 'Kali', 'Schultz', 22, 'female', 96844);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (63, 'Geovany', 'Swift', 26, 'male', 75439);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (64, 'Foster', 'Grant', 25, 'female', 68925);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (65, 'Ashley', 'Hammes', 78, 'female', 34529);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (66, 'Rex', 'Cummerata', 11, 'male', 43567);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (67, 'Dewitt', 'Ondricka', 99, 'male', 55668);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (68, 'Lucas', 'McDermott', 35, 'female', 48644);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (69, 'Edmond', 'Turner', 100, 'male', 38307);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (70, 'Keegan', 'Herzog', 20, 'female', 14862);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (71, 'Joany', 'Deckow', 29, 'female', 14862);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (72, 'Jacynthe', 'Crona', 30, 'male', 96844);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (73, 'Bart', 'Walter', 46, 'female', 72806);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (74, 'Gerson', 'Crona', 78, 'male', 39384);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (75, 'Annabel', 'Christiansen', 23, 'male', 11266);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (76, 'Leta', 'Cruickshank', 19, 'female', 53327);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (77, 'Belle', 'Heidenreich', 23, 'female', 75439);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (78, 'Casey', 'Stroman', 18, 'male', 39384);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (79, 'Carlie', 'Dare', 67, 'female', 53710);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (80, 'Donny', 'Rohan', 54, 'male', 53327);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (81, 'Drew', 'Schimmel', 11, 'female', 17657);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (82, 'Kendrick', 'Hamill', 76, 'male', 12055);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (83, 'Marc', 'Schuster', 93, 'male', 67478);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (84, 'Bette', 'Harber', 38, 'male', 14862);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (85, 'Adriana', 'Green', 33, 'female', 12055);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (86, 'Lesley', 'Ledner', 39, 'male', 48644);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (87, 'Hans', 'Connelly', 97, 'female', 34529);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (88, 'Rahsaan', 'McDermott', 31, 'female', 17657);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (89, 'Kevin', 'Borer', 13, 'female', 17657);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (90, 'Elvis', 'Stiedemann', 43, 'male', 53327);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (91, 'Jaunita', 'Runte', 18, 'female', 93183);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (92, 'Nora', 'Hagenes', 31, 'male', 84760);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (93, 'Bobbie', 'Hartmann', 1, 'male', 53327);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (94, 'Florencio', 'Green', 62, 'female', 68925);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (95, 'Antonina', 'Crist', 40, 'male', 55668);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (96, 'Cicero', 'Hegmann', 100, 'male', 69793);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (97, 'Deon', 'Considine', 50, 'female', 12055);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (98, 'Korbin', 'Labadie', 12, 'female', 12055);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (99, 'Jerrold', 'Lueilwitz', 48, 'male', 17657);
INSERT INTO `person` (`person_id`, `first_name`, `last_name`, `age`, `gender`, `zipcode`) VALUES (100, 'Leanne', 'Watsica', 76, 'male', 44072);



CREATE TABLE patient (patient_id INT, 
    patient_condition ENUM('mild', 'medium', 'severe'),
    PRIMARY KEY (patient_id),
    FOREIGN KEY (patient_id) REFERENCES person(person_id)
);

INSERT INTO `patient` (`patient_id`, `patient_condition`) VALUES (1, 'severe');
INSERT INTO `patient` (`patient_id`, `patient_condition`) VALUES (9, 'severe');
INSERT INTO `patient` (`patient_id`, `patient_condition`) VALUES (12, 'medium');
INSERT INTO `patient` (`patient_id`, `patient_condition`) VALUES (13, 'severe');
INSERT INTO `patient` (`patient_id`, `patient_condition`) VALUES (20, 'severe');
INSERT INTO `patient` (`patient_id`, `patient_condition`) VALUES (53, 'severe');
INSERT INTO `patient` (`patient_id`, `patient_condition`) VALUES (67, 'severe');
INSERT INTO `patient` (`patient_id`, `patient_condition`) VALUES (75, 'medium');
INSERT INTO `patient` (`patient_id`, `patient_condition`) VALUES (87, 'medium');
INSERT INTO `patient` (`patient_id`, `patient_condition`) VALUES (95, 'severe');


CREATE TABLE contact_event(event_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
contact_date DATETIME,
zipcode INT,
FOREIGN KEY (zipcode) REFERENCES location(zipcode)
);


INSERT INTO `contact_event` (`event_id`, `contact_date`, `zipcode`) VALUES (1, '2020-07-16 02:40:04', 55668);
INSERT INTO `contact_event` (`event_id`, `contact_date`, `zipcode`) VALUES (2, '2020-07-01 23:26:26', 68925);
INSERT INTO `contact_event` (`event_id`, `contact_date`, `zipcode`) VALUES (3, '2020-03-26 01:25:06', 68925);
INSERT INTO `contact_event` (`event_id`, `contact_date`, `zipcode`) VALUES (4, '2020-01-12 01:19:00', 11266);
INSERT INTO `contact_event` (`event_id`, `contact_date`, `zipcode`) VALUES (5, '2020-04-12 10:25:41', 43567);
INSERT INTO `contact_event` (`event_id`, `contact_date`, `zipcode`) VALUES (6, '2020-04-13 00:03:36', 14458);
INSERT INTO `contact_event` (`event_id`, `contact_date`, `zipcode`) VALUES (7, '2020-07-16 13:24:08', 67478);
INSERT INTO `contact_event` (`event_id`, `contact_date`, `zipcode`) VALUES (8, '2020-06-01 09:54:29', 89729);
INSERT INTO `contact_event` (`event_id`, `contact_date`, `zipcode`) VALUES (9, '2020-06-12 22:56:16', 55668);
INSERT INTO `contact_event` (`event_id`, `contact_date`, `zipcode`) VALUES (10, '2020-01-14 11:25:00', 62366);




CREATE TABLE contact (person_id INT , 
    contact_degree ENUM('direct', 'indirect'),
    patient_id INT NOT NULL,
    PRIMARY KEY (person_id),
    FOREIGN KEY (person_id) REFERENCES person(person_id),
    FOREIGN KEY (patient_id) REFERENCES patient(patient_id),
    check (person_id<>patient_id)
);



INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (1, 'direct', 75);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (2, 'direct', 53);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (4, 'indirect', 9);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (7, 'indirect', 87);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (8, 'direct', 95);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (9, 'direct', 67);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (11, 'direct', 95);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (15, 'indirect', 95);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (16, 'indirect', 1);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (17, 'direct', 9);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (19, 'indirect', 1);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (24, 'direct', 1);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (27, 'indirect', 20);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (28, 'indirect', 75);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (29, 'direct', 53);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (31, 'indirect', 67);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (33, 'direct', 75);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (34, 'direct', 20);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (35, 'indirect', 75);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (37, 'indirect', 9);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (38, 'indirect', 12);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (39, 'direct', 1);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (40, 'direct', 12);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (41, 'direct', 67);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (44, 'direct', 13);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (46, 'direct', 1);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (49, 'direct', 95);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (53, 'direct', 75);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (55, 'indirect', 95);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (57, 'direct', 13);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (60, 'indirect', 20);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (64, 'indirect', 53);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (68, 'indirect', 75);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (69, 'indirect', 20);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (70, 'indirect', 12);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (71, 'direct', 9);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (74, 'indirect', 67);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (75, 'indirect', 1);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (77, 'indirect', 20);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (80, 'direct', 67);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (84, 'indirect', 1);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (86, 'direct', 13);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (88, 'direct', 53);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (90, 'indirect', 20);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (91, 'indirect', 9);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (92, 'direct', 75);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (93, 'indirect', 20);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (95, 'indirect', 1);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (96, 'indirect', 67);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (98, 'direct', 9);
INSERT INTO `contact` (`person_id`, `contact_degree`, `patient_id`) VALUES (99, 'indirect', 75);



CREATE TABLE contact_to_event(event_id INT,
person_id INT,
PRIMARY KEY (event_id, person_id),
FOREIGN KEY (event_id) REFERENCES contact_event(event_id),
FOREIGN KEY (person_id) REFERENCES contact(person_id)
);




INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (1, 7);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (1, 15);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (1, 38);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (1, 49);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (1, 53);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (1, 64);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (1, 68);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (1, 69);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (1, 74);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (1, 86);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (1, 90);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (2, 19);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (2, 24);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (2, 27);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (2, 35);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (2, 46);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (3, 9);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (3, 11);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (3, 31);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (3, 41);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (3, 46);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (3, 57);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (3, 77);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (3, 86);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (3, 88);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (4, 9);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (4, 19);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (4, 41);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (4, 95);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (4, 99);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (5, 4);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (5, 7);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (5, 24);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (5, 40);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (5, 69);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (5, 77);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (5, 80);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (5, 92);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (6, 17);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (6, 31);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (6, 44);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (6, 49);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (6, 68);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (6, 69);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (6, 88);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (6, 96);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (6, 99);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (7, 31);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (7, 60);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (7, 74);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (7, 98);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (8, 4);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (8, 60);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (8, 69);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (8, 71);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (8, 91);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (9, 4);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (9, 37);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (9, 40);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (9, 71);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (9, 91);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (10, 27);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (10, 31);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (10, 44);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (10, 46);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (10, 49);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (10, 57);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (10, 69);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (10, 74);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (10, 75);
INSERT INTO `contact_to_event` (`event_id`, `person_id`) VALUES (10, 84);


CREATE TABLE hospital(hospital_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
zipcode INT NOT NULL,
FOREIGN KEY (zipcode) REFERENCES location(zipcode)
);

INSERT INTO `hospital` (`hospital_id`, `name`, `zipcode`) VALUES (1, 'Lake Hospital', 75439);
INSERT INTO `hospital` (`hospital_id`, `name`, `zipcode`) VALUES (2, 'Glover-Von Hospital', 67478);
INSERT INTO `hospital` (`hospital_id`, `name`, `zipcode`) VALUES (3, 'Walsh Hospital', 14458);
INSERT INTO `hospital` (`hospital_id`, `name`, `zipcode`) VALUES (4, 'Turner Hospital', 93183);
INSERT INTO `hospital` (`hospital_id`, `name`, `zipcode`) VALUES (5, 'Crona-Wehner Hospital', 17657);
INSERT INTO `hospital` (`hospital_id`, `name`, `zipcode`) VALUES (6, 'Torphy-Lind Hospital', 2366);
INSERT INTO `hospital` (`hospital_id`, `name`, `zipcode`) VALUES (7, 'Baumbach Hospital', 65399);
INSERT INTO `hospital` (`hospital_id`, `name`, `zipcode`) VALUES (8, 'Nicolas-Terry Hospital', 69793);
INSERT INTO `hospital` (`hospital_id`, `name`, `zipcode`) VALUES (9, 'Marry Hospital', 44072);
INSERT INTO `hospital` (`hospital_id`, `name`, `zipcode`) VALUES (10, 'Ankunding-Hudson Hospital', 56720);


CREATE TABLE treatment_department(section_id INT NOT NULL check(section_id<=5),
patient_id INT,
hospital_id INT,
primary key(section_id, patient_id,hospital_id),
FOREIGN KEY (patient_id) REFERENCES patient(patient_id),
FOREIGN KEY (hospital_id) REFERENCES hospital(hospital_id)
);


INSERT INTO `treatment_department` (`section_id`, `patient_id`, `hospital_id`) VALUES (1, 12, 6);
INSERT INTO `treatment_department` (`section_id`, `patient_id`, `hospital_id`) VALUES (2, 9, 2);
INSERT INTO `treatment_department` (`section_id`, `patient_id`, `hospital_id`) VALUES (2, 13, 10);
INSERT INTO `treatment_department` (`section_id`, `patient_id`, `hospital_id`) VALUES (3, 20, 8);
INSERT INTO `treatment_department` (`section_id`, `patient_id`, `hospital_id`) VALUES (3, 95, 8);
INSERT INTO `treatment_department` (`section_id`, `patient_id`, `hospital_id`) VALUES (4, 1, 4);
INSERT INTO `treatment_department` (`section_id`, `patient_id`, `hospital_id`) VALUES (4, 87, 9);
INSERT INTO `treatment_department` (`section_id`, `patient_id`, `hospital_id`) VALUES (5, 75, 5);
INSERT INTO `treatment_department` (`section_id`, `patient_id`, `hospital_id`) VALUES (5, 53, 3);
INSERT INTO `treatment_department` (`section_id`, `patient_id`, `hospital_id`) VALUES (5, 67, 4);



CREATE TABLE treatment_plan(plan_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
patient_id INT,
hospital_id INT,
section_id INT,
plan ENUM('treatment_A', 'treatment_B', 'treatment_C', 'treatment_D'),
unique(section_id, patient_id, hospital_id),
FOREIGN KEY (section_id, patient_id, hospital_id) REFERENCES treatment_department(section_id, patient_id, hospital_id)

);



INSERT INTO `treatment_plan` (`plan_id`,`section_id`, `patient_id`, `hospital_id`, `plan`) VALUES (1, 1, 12, 6, 'treatment_A');
INSERT INTO `treatment_plan` (`plan_id`,`section_id`, `patient_id`, `hospital_id`, `plan`) VALUES (2, 2, 9, 2,  'treatment_B');
INSERT INTO `treatment_plan` (`plan_id`,`section_id`, `patient_id`, `hospital_id`, `plan`) VALUES (3, 2, 13, 10,'treatment_B');
INSERT INTO `treatment_plan` (`plan_id`,`section_id`, `patient_id`, `hospital_id`, `plan`) VALUES (4, 3, 20, 8, 'treatment_B');
INSERT INTO `treatment_plan` (`plan_id`,`section_id`, `patient_id`, `hospital_id`, `plan`) VALUES (5, 3, 95, 8, 'treatment_C');
INSERT INTO `treatment_plan` (`plan_id`,`section_id`, `patient_id`, `hospital_id`, `plan`) VALUES (6, 4, 1, 4,  'treatment_C');
INSERT INTO `treatment_plan` (`plan_id`,`section_id`, `patient_id`, `hospital_id`, `plan`) VALUES (7, 4, 87, 9, 'treatment_C');
INSERT INTO `treatment_plan` (`plan_id`,`section_id`, `patient_id`, `hospital_id`, `plan`) VALUES (8, 5, 75, 5, 'treatment_D');
INSERT INTO `treatment_plan` (`plan_id`,`section_id`, `patient_id`, `hospital_id`, `plan`) VALUES (9, 5, 53, 3, 'treatment_D');
INSERT INTO `treatment_plan` (`plan_id`,`section_id`, `patient_id`, `hospital_id`, `plan`) VALUES (10, 5, 67, 4,'treatment_A');





CREATE TABLE testing_department(section_id INT NOT NULL check(section_id<=5),
person_id INT,
hospital_id INT,
PRIMARY KEY(section_id, person_id, hospital_id),
FOREIGN KEY (person_id) REFERENCES contact(person_id),
FOREIGN KEY (hospital_id) REFERENCES hospital(hospital_id)
);

INSERT INTO `testing_department` (`section_id`, `person_id`, `hospital_id`) VALUES (1, 27, 5);
INSERT INTO `testing_department` (`section_id`, `person_id`, `hospital_id`) VALUES (1, 29, 5);
INSERT INTO `testing_department` (`section_id`, `person_id`, `hospital_id`) VALUES (1, 44, 6);
INSERT INTO `testing_department` (`section_id`, `person_id`, `hospital_id`) VALUES (1, 57, 3);
INSERT INTO `testing_department` (`section_id`, `person_id`, `hospital_id`) VALUES (2, 2, 4);
INSERT INTO `testing_department` (`section_id`, `person_id`, `hospital_id`) VALUES (2, 31, 3);
INSERT INTO `testing_department` (`section_id`, `person_id`, `hospital_id`) VALUES (2, 70, 5);
INSERT INTO `testing_department` (`section_id`, `person_id`, `hospital_id`) VALUES (2, 86, 4);
INSERT INTO `testing_department` (`section_id`, `person_id`, `hospital_id`) VALUES (3, 34, 5);
INSERT INTO `testing_department` (`section_id`, `person_id`, `hospital_id`) VALUES (3, 46, 1);
INSERT INTO `testing_department` (`section_id`, `person_id`, `hospital_id`) VALUES (3, 49, 7);
INSERT INTO `testing_department` (`section_id`, `person_id`, `hospital_id`) VALUES (3, 98, 4);
INSERT INTO `testing_department` (`section_id`, `person_id`, `hospital_id`) VALUES (4, 37, 1);
INSERT INTO `testing_department` (`section_id`, `person_id`, `hospital_id`) VALUES (4, 35, 9);
INSERT INTO `testing_department` (`section_id`, `person_id`, `hospital_id`) VALUES (4, 69, 2);
INSERT INTO `testing_department` (`section_id`, `person_id`, `hospital_id`) VALUES (4, 90, 7);
INSERT INTO `testing_department` (`section_id`, `person_id`, `hospital_id`) VALUES (4, 91, 8);
INSERT INTO `testing_department` (`section_id`, `person_id`, `hospital_id`) VALUES (5, 68, 2);
INSERT INTO `testing_department` (`section_id`, `person_id`, `hospital_id`) VALUES (5, 84, 10);
INSERT INTO `testing_department` (`section_id`, `person_id`, `hospital_id`) VALUES (5, 92, 9);




CREATE TABLE testing_sample(sample_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
section_id INT,
person_id INT,
hospital_id INT,
unique(section_id, person_id, hospital_id),
FOREIGN KEY (section_id, person_id, hospital_id) REFERENCES testing_department(section_id, person_id, hospital_id)

);


INSERT INTO `testing_sample` (`sample_id`, `section_id`, `person_id`, `hospital_id`) VALUES (1, 1, 27, 5);
INSERT INTO `testing_sample` (`sample_id`, `section_id`, `person_id`, `hospital_id`) VALUES (2, 1, 29, 5);
INSERT INTO `testing_sample` (`sample_id`, `section_id`, `person_id`, `hospital_id`) VALUES (3, 1, 44, 6);
INSERT INTO `testing_sample` (`sample_id`, `section_id`, `person_id`, `hospital_id`) VALUES (4, 1, 57, 3);
INSERT INTO `testing_sample` (`sample_id`, `section_id`, `person_id`, `hospital_id`) VALUES (5, 2, 2, 4);
INSERT INTO `testing_sample` (`sample_id`, `section_id`, `person_id`, `hospital_id`) VALUES (6, 2, 31, 3);
INSERT INTO `testing_sample` (`sample_id`, `section_id`, `person_id`, `hospital_id`) VALUES (7, 2, 70, 5);
INSERT INTO `testing_sample` (`sample_id`, `section_id`, `person_id`, `hospital_id`) VALUES (8, 2, 86, 4);
INSERT INTO `testing_sample` (`sample_id`, `section_id`, `person_id`, `hospital_id`) VALUES (9, 3, 34, 5);
INSERT INTO `testing_sample` (`sample_id`, `section_id`, `person_id`, `hospital_id`) VALUES (10, 3, 46, 1);
INSERT INTO `testing_sample` (`sample_id`, `section_id`, `person_id`, `hospital_id`) VALUES (11, 3, 49, 7);
INSERT INTO `testing_sample` (`sample_id`, `section_id`, `person_id`, `hospital_id`) VALUES (12, 3, 98, 4);
INSERT INTO `testing_sample` (`sample_id`, `section_id`, `person_id`, `hospital_id`) VALUES (13, 4, 37, 1);
INSERT INTO `testing_sample` (`sample_id`, `section_id`, `person_id`, `hospital_id`) VALUES (14, 4, 35, 9);
INSERT INTO `testing_sample` (`sample_id`, `section_id`, `person_id`, `hospital_id`) VALUES (15, 4, 69, 2);
INSERT INTO `testing_sample` (`sample_id`, `section_id`, `person_id`, `hospital_id`) VALUES (16, 4, 90, 7);
INSERT INTO `testing_sample` (`sample_id`, `section_id`, `person_id`, `hospital_id`) VALUES (17, 4, 91, 8);
INSERT INTO `testing_sample` (`sample_id`, `section_id`, `person_id`, `hospital_id`) VALUES (18, 5, 68, 2);
INSERT INTO `testing_sample` (`sample_id`, `section_id`, `person_id`, `hospital_id`) VALUES (19, 5, 84, 10);
INSERT INTO `testing_sample` (`sample_id`, `section_id`, `person_id`, `hospital_id`) VALUES (20, 5, 92, 9);



CREATE TABLE lab_result(result_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
sample_id int,
unique(sample_id),
result ENUM('positive', 'negative') NOT NULL,
FOREIGN KEY (sample_id) REFERENCES testing_sample(sample_id)

);


INSERT INTO `lab_result` (`result_id`, `sample_id`, `result`) VALUES (1, 1, 'positive' );
INSERT INTO `lab_result` (`result_id`, `sample_id`, `result`) VALUES (2, 3, 'positive' );
INSERT INTO `lab_result` (`result_id`, `sample_id`, `result`) VALUES (3, 5, 'positive' );
INSERT INTO `lab_result` (`result_id`, `sample_id`, `result`) VALUES (4, 7, 'positive' );
INSERT INTO `lab_result` (`result_id`, `sample_id`, `result`) VALUES (5, 9,'negative' );
INSERT INTO `lab_result` (`result_id`, `sample_id`, `result`) VALUES (6, 2, 'negative' );
INSERT INTO `lab_result` (`result_id`, `sample_id`, `result`) VALUES (7, 4, 'negative' );
INSERT INTO `lab_result` (`result_id`, `sample_id`, `result`) VALUES (8, 6, 'negative' );
INSERT INTO `lab_result` (`result_id`, `sample_id`, `result`) VALUES (9, 8, 'negative' );
INSERT INTO `lab_result` (`result_id`, `sample_id`, `result`) VALUES (10, 10, 'negative' );
INSERT INTO `lab_result` (`result_id`, `sample_id`, `result`) VALUES (11, 11, 'negative' );
INSERT INTO `lab_result` (`result_id`, `sample_id`, `result`) VALUES (12, 13, 'negative' );
INSERT INTO `lab_result` (`result_id`, `sample_id`, `result`) VALUES (13, 15, 'positive' );
INSERT INTO `lab_result` (`result_id`, `sample_id`, `result`) VALUES (14, 17, 'positive' );
INSERT INTO `lab_result` (`result_id`, `sample_id`, `result`) VALUES (15, 19, 'positive' );
INSERT INTO `lab_result` (`result_id`, `sample_id`, `result`) VALUES (16, 12, 'positive' );
INSERT INTO `lab_result` (`result_id`, `sample_id`, `result`) VALUES (17, 14, 'positive' );
INSERT INTO `lab_result` (`result_id`, `sample_id`, `result`) VALUES (18, 16, 'positive' );
INSERT INTO `lab_result` (`result_id`, `sample_id`, `result`) VALUES (19, 18, 'positive' );
INSERT INTO `lab_result` (`result_id`, `sample_id`, `result`) VALUES (20, 20, 'positive' );

(b). Queries

-- 1. All contacts who test positive 

SELECT person.first_name, person.last_name, lab_result.result
FROM person, contact, testing_department, testing_sample, lab_result
WHERE contact.person_id = person.person_id
AND contact.person_id = testing_department.person_id
AND testing_department.person_id = testing_sample.person_id
AND testing_department.section_id = testing_sample.section_id
AND testing_department.hospital_id = testing_sample.hospital_id
AND testing_sample.sample_id = lab_result.sample_id
AND lab_result.result = 'positive';


-- 2. Show all the location where the number of contacts who test positive are more than 2. 
SELECT zip as zipcode, str as street, cit as city, stt as state
FROM (
    SELECT COUNT(contact.person_id) AS total_pos, location.zipcode AS zip, location.street AS str, location.city AS cit, location.state AS stt
    FROM person, contact, testing_department, testing_sample, lab_result, location, contact_to_event, contact_event
    WHERE contact.person_id = person.person_id
    AND contact.person_id = testing_department.person_id
    AND testing_department.person_id = testing_sample.person_id
    AND testing_department.section_id = testing_sample.section_id
    AND testing_department.hospital_id = testing_sample.hospital_id
    AND testing_sample.sample_id = lab_result.sample_id
    AND lab_result.result = 'positive'
    AND contact.person_id = contact_to_event.person_id
    AND contact_to_event.event_id = contact_event.event_id
    AND contact_event.zipcode = location.zipcode
    GROUP BY zip
) as alias
WHERE total_pos > 2;



-- 3. Hospital that has treated more than or equal to 2 patients. 
SELECT hospital.name, COUNT(patient.patient_id) AS total_patient
FROM hospital, treatment_department, patient
WHERE hospital.hospital_id = treatment_department.hospital_id
AND treatment_department.patient_id = patient.patient_id
GROUP BY hospital.hospital_id
HAVING total_patient >= 2;


-- 4. Show all the contacts who are contacted in an indirect way and test positive. 
SELECT person.first_name, person.last_name, lab_result.result
FROM person, contact, testing_department, testing_sample, lab_result
WHERE contact.person_id = person.person_id
AND contact.person_id = testing_department.person_id
AND testing_department.person_id = testing_sample.person_id
AND testing_department.section_id = testing_sample.section_id
AND testing_department.hospital_id = testing_sample.hospital_id
AND testing_sample.sample_id = lab_result.sample_id
AND contact.contact_degree = 'indirect'
AND lab_result.result = 'positive';



-- 5. The classification of the number of contact made by each patient
SELECT 
    CASE
        WHEN num_of_contact >= 5 THEN 'num_of_contact >= 5 '
        ELSE 'num_of_contact < 5'
    END AS classification,
    ptt AS patient_id,
    num_of_contact
FROM
    (SELECT 
        patient.patient_id AS ptt,
            COUNT(contact.person_id) AS num_of_contact
    FROM
        contact, patient
    WHERE
        contact.patient_id = patient.patient_id
    GROUP BY patient.patient_id
    ORDER BY num_of_contact) AS alias;

E. Data retrievals into R and show database functions properly

(a). Connect DB with R

library(RMySQL)
mydb = dbConnect(MySQL(), user = 'root', password = 'HEPburn419403210', dbname = 'contact_tracing', host = 'localhost')
dbListTables(mydb)

 [1] "contact"              "contact_event"        "contact_to_event"     "hospital"            
 [5] "lab_result"           "location"             "patient"              "person"              
 [9] "testing_department"   "testing_sample"       "treatment_department" "treatment_plan"      

(b). Load data properly useing query

 rs = dbSendQuery(mydb, "SELECT 
     CASE
         WHEN num_of_contact >= 5 THEN 'num_of_contact >= 5 '
         ELSE 'num_of_contact < 5'
     END AS classification,
     ptt AS patient_id,
     num_of_contact
 FROM
     (SELECT 
         patient.patient_id AS ptt,
             COUNT(contact.person_id) AS num_of_contact
     FROM
         contact, patient
     WHERE
         contact.patient_id = patient.patient_id
     GROUP BY patient.patient_id
     ORDER BY num_of_contact) AS alias")
 output = fetch(rs, n = -1)
 output

(c).Draw a bar plot to show the patient and the number of its corresponding contact.

library(dbplot)
dbplot_bar(output,patient_id,num_of_contact)