NinesWUX

1DROP DATABASE IF EXISTS ITAS288Assign05_Hornick_Tighe_Wilhelm_01;
2CREATE DATABASE IF NOT EXISTS ITAS288Assign05_Hornick_Tighe_Wilhelm_01;
3
4USE ITAS288Assign04_Hornick_Tighe_Wilhelm_01;
5
6DROP TABLE IF EXISTS person;
7CREATE TABLE IF NOT EXISTS person
8(
9phone_number INT(10) NOT NULL,
10person_id INT(10) NOT NULL PRIMARY KEY,
11first_name CHAR(30) NOT NULL,
12last_name CHAR(30) NOT NULL,
13birth_date DATE NOT NULL,
14is_employee INT(1) NOT NULL,
15is_patient INT(1) NOT NULL
16);
17
18INSERT INTO person
19(phone_number, person_id, first_name, last_name, birth_date, is_employee, is_patient) 
20VALUES
21(381214214, 1, 'Dan', 'Letersky', STR_TO_DATE('1/9/1988', '%d/%m/%Y')), 0, 1),
22(541907901, 2, 'Dave', 'Altvater', STR_TO_DATE('1/9/1988', '%d/%m/%Y')), 0, 0),
23(661324324, 3, 'Doug', 'Aiken', STR_TO_DATE('1/9/1988', '%d/%m/%Y')), 1, 1),
24(701542542, 4, 'Dean', 'Marra', STR_TO_DATE('1/9/1988', '%d/%m/%Y')), 1, 0);
25(541907901, 5, 'Alice', 'Altvater', STR_TO_DATE('1/9/1988', '%d/%m/%Y')), 1, 0),
26(681214214, 6, 'Andy', 'Letersky', STR_TO_DATE('1/9/1988', '%d/%m/%Y')), 1, 0),
27(661324324, 7, 'Andrew', 'Aiken', STR_TO_DATE('1/9/1988', '%d/%m/%Y')), 1, 0),
28DROP TABLE IF EXISTS employee;
29CREATE TABLE IF NOT EXISTS employee
30(
31employee_id INT(10) NOT NULL,
32person_id INT(10) NOT NULL PRIMARY KEY,
33FOREIGN KEY (person_id) REFERENCES person (person_id) ON DELETE CASCADE,
34date_hired DATE NOT NULL,
35is_physician INT(1) NOT NULL,
36is_menial_staff INT(1) NOT NULL,
37is_technician INT(1) NOT NULL,
38is_volunteer INT(1) NOT NULL,
39is_nurse INT(1) NOT NULL
40);
41
42INSERT INTO employee
43(employee_id, person_id, date_hired, is_physician, is_menial_staff, is_technician, is_volunteer, is_nurse) 
44VALUES
45(681214214, 6, STR_TO_DATE('1/9/1988', '%d/%m/%Y')), 1, 0, 0, 0, 0),
46(641907901, 5, STR_TO_DATE('1/9/1988', '%d/%m/%Y')), 0, 1, 0, 0, 0),
47(661324324, 3, STR_TO_DATE('1/9/1988', '%d/%m/%Y')), 0, 0, 0, 1, 0),
48(661324324, 7, STR_TO_DATE('1/9/1988', '%d/%m/%Y')), 0, 0, 0, 1, 0),
49(601542542, 4, STR_TO_DATE('1/9/1988', '%d/%m/%Y')), 0, 0, 0, 0, 1);
50
51DROP TABLE IF EXISTS menial_staff
52CREATE TABLE IF NOT EXISTS menial_staff
53(
54job_class CHAR(10) NOT NULL,
55employee_id INT(10) NOT NULL,
56person_id INT(10) NOT NULL PRIMARY KEY,
57FOREIGN KEY (person_id) REFERENCES person (person_id) ON DELETE CASCADE,
58)
59
60('janitor', 661324324, 7)
61
62DROP TABLE IF EXISTS volunteer
63CREATE TABLE IF NOT EXISTS volunteer
64(
65skill CHAR(10) NOT NULL,
66employee_id INT(10) NOT NULL,
67person_id INT(10) NOT NULL PRIMARY KEY,
68FOREIGN KEY (person_id) REFERENCES person (person_id) ON DELETE CASCADE,
69)