· 8 years ago · Mar 26, 2018, 01:12 AM
1use faraj;
2
3DROP TABLE Tasks_Staff;
4
5DROP TABLE Tasks;
6
7DROP TABLE Comments;
8
9DROP TABLE Reserve_Facility;
10
11DROP TABLE Staff;
12
13DROP TABLE CSFacility;
14
15DROP TABLE Student;
16
17DROP TABLE Occupation;
18
19CREATE TABLE IF NOT EXISTS Occupation
20(
21 O_ID INT(5) NOT NULL UNIQUE,
22 O_Desc VARCHAR(100) NOT NULL,
23 PRIMARY KEY(O_ID)
24);
25
26CREATE TABLE IF NOT EXISTS Staff
27(
28 SF_ID INT(5) NOT NULL UNIQUE AUTO_INCREMENT,
29 SF_LName VARCHAR(100) NOT NULL,
30 SF_FName VARCHAR(100) NOT NULL,
31 O_ID INT(5) NOT NULL,
32 PRIMARY KEY(SF_ID),
33 FOREIGN KEY(O_ID) REFERENCES Occupation(O_ID)
34);
35
36CREATE TABLE IF NOT EXISTS CSFacility
37(
38 CF_ID INT(5) NOT NULL UNIQUE,
39 CF_Desc VARCHAR(100) NOT NULL,
40 PRIMARY KEY(CF_ID)
41);
42
43CREATE TABLE IF NOT EXISTS Student
44(
45 ST_ID INT(5) NOT NULL UNIQUE AUTO_INCREMENT,
46 ST_LName VARCHAR(100) NOT NULL,
47 ST_FName VARCHAR(100) NOT NULL,
48 PRIMARY KEY(ST_ID)
49);
50
51CREATE TABLE IF NOT EXISTS Reserve_Facility
52(
53 RF_ID INT(5) NOT NULL UNIQUE AUTO_INCREMENT,
54 RF_Date DATE NOT NULL,
55 ST_ID INT(5) NOT NULL,
56 SF_ID INT(5) NOT NULL,
57 CF_ID INT(5) NOT NULL,
58 PRIMARY KEY(RF_ID),
59 FOREIGN KEY(CF_ID) REFERENCES CSFacility(CF_ID),
60 FOREIGN KEY(ST_ID) REFERENCES Student(ST_ID),
61 FOREIGN KEY(SF_ID) REFERENCES Staff(SF_ID)
62);
63
64CREATE TABLE IF NOT EXISTS Comments
65(
66 C_ID INT(5) NOT NULL UNIQUE AUTO_INCREMENT,
67 C_Body VARCHAR(1000) NOT NULL,
68 ST_ID INT(5),
69 SF_ID INT(5),
70 PRIMARY KEY(C_ID),
71 FOREIGN KEY(ST_ID) REFERENCES Student(ST_ID),
72 FOREIGN KEY(SF_ID) REFERENCES Staff(SF_ID)
73);
74
75CREATE TABLE IF NOT EXISTS Tasks
76(
77 T_ID INT(5) NOT NULL UNIQUE,
78 T_Desc VARCHAR(500) NOT NULL,
79 PRIMARY KEY(T_ID)
80);
81
82CREATE TABLE IF NOT EXISTS Tasks_Staff
83(
84 TS_ID INT(5) NOT NULL UNIQUE AUTO_INCREMENT,
85 TS_Completed BOOL NOT NULL DEFAULT FALSE,
86 T_ID INT(5) NOT NULL,
87 SF_ID INT(3) NOT NULL,
88 PRIMARY KEY(TS_ID),
89 FOREIGN KEY(T_ID) REFERENCES Tasks(T_ID),
90 FOREIGN KEY(SF_ID) REFERENCES Staff(SF_ID)
91);
92
93
94INSERT INTO Occupation VALUES(0,"Professor");
95INSERT INTO Occupation VALUES(1,"Administrator");
96INSERT INTO Occupation VALUES(2,"Janitorial");
97INSERT INTO Occupation VALUES(3,"Temporary");
98
99INSERT INTO Staff (SF_FName,SF_LName,O_ID) VALUES("Victoria","Nanafua",0);
100INSERT INTO Staff (SF_FName,SF_LName,O_ID) VALUES("Eric","Sanchez",0);
101INSERT INTO Staff (SF_FName,SF_LName,O_ID) VALUES("Pamela","Nanafua",0);
102INSERT INTO Staff (SF_FName,SF_LName,O_ID) VALUES("Ahmed","Masri",0);
103INSERT INTO Staff (SF_FName,SF_LName,O_ID) VALUES("Robert","McKilliam",1);
104INSERT INTO Staff (SF_FName,SF_LName,O_ID) VALUES("Enrique","delMonte",1);
105INSERT INTO Staff (SF_FName,SF_LName,O_ID) VALUES("Susan","McJenningston",1);
106INSERT INTO Staff (SF_FName,SF_LName,O_ID) VALUES("Jennifer","McJenningston",1);
107INSERT INTO Staff (SF_FName,SF_LName,O_ID) VALUES("Marco","Guapote",2);
108INSERT INTO Staff (SF_FName,SF_LName,O_ID) VALUES("Barbie","Smith",2);
109INSERT INTO Staff (SF_FName,SF_LName,O_ID) VALUES("Hubert","McCain",2);
110INSERT INTO Staff (SF_FName,SF_LName,O_ID) VALUES("Tony","Volpe",2);
111INSERT INTO Staff (SF_FName,SF_LName,O_ID) VALUES("Tonia","Volpina",3);
112INSERT INTO Staff (SF_FName,SF_LName,O_ID) VALUES("Gilbert","Hofman",3);
113INSERT INTO Staff (SF_FName,SF_LName,O_ID) VALUES("Tulip","Petunia",3);
114INSERT INTO Staff (SF_FName,SF_LName,O_ID) VALUES("Nanana","Tabuleh",3);
115
116INSERT INTO Tasks VALUE(0,"Interview students");
117INSERT INTO Tasks VALUE(1,"Interview staff");
118INSERT INTO Tasks VALUE(2,"Clean 1st Floor");
119INSERT INTO Tasks VALUE(3,"Clean 2nd Floor");
120INSERT INTO Tasks VALUE(4,"Clean 3rd Floor");
121INSERT INTO Tasks VALUE(5,"Clean 4th Floor");
122INSERT INTO Tasks VALUE(6,"Evaluate Professors");
123INSERT INTO Tasks VALUE(7,"Evaluate Administrators");
124INSERT INTO Tasks VALUE(8,"Evaluate Janitors");
125INSERT INTO Tasks VALUE(9,"Assit Professors");
126INSERT INTO Tasks VALUE(10,"Restock Supplies");
127
128INSERT INTO Tasks_Staff (T_ID,SF_ID) VALUES (0,1);
129INSERT INTO Tasks_Staff (T_ID,SF_ID) VALUES (1,2);
130INSERT INTO Tasks_Staff (T_ID,SF_ID) VALUES (2,11);
131INSERT INTO Tasks_Staff (T_ID,SF_ID) VALUES (5,11);
132INSERT INTO Tasks_Staff (T_ID,SF_ID) VALUES (1,2);
133
134INSERT INTO Tasks_Staff (T_ID,SF_ID,TS_Completed) VALUES (3,10,TRUE);
135INSERT INTO Tasks_Staff (T_ID,SF_ID,TS_Completed) VALUES (4,10,TRUE);
136INSERT INTO Tasks_Staff (T_ID,SF_ID,TS_Completed) VALUES (5,10,TRUE);
137INSERT INTO Tasks_Staff (T_ID,SF_ID,TS_Completed) VALUES (1,2,TRUE);
138
139INSERT INTO Student (ST_FName,ST_LName) VALUES ("George","Faraj");
140INSERT INTO Student (ST_FName,ST_LName) VALUES ("Micah","Lacson");
141INSERT INTO Student (ST_FName,ST_LName) VALUES ("Alejandro","Casanova");
142INSERT INTO Student (ST_FName,ST_LName) VALUES ("Kevin","Casanova");
143INSERT INTO Student (ST_FName,ST_LName) VALUES ("Sarah","Timpani");
144INSERT INTO Student (ST_FName,ST_LName) VALUES ("Helena","al'Sawadi");
145
146INSERT INTO Comments(ST_ID,C_Body) VALUES(1,"Good person, tries really hard");
147INSERT INTO Comments(ST_ID,C_Body) VALUES(2,"Lazy student, but she hands in all her assignments and passes all her courses");
148INSERT INTO Comments(ST_ID,C_Body) VALUES(2,"Has submitted several sick notes, her professors are getting very suspicious");
149INSERT INTO Comments(ST_ID,C_Body) VALUES(3,"Good person, tries really hard");
150INSERT INTO Comments(ST_ID,C_Body) VALUES(4,"Good person, tries really hard");
151INSERT INTO Comments(ST_ID,C_Body) VALUES(5,"Good person, tries really hard");
152INSERT INTO Comments(SF_ID,C_Body) VALUES(1,"One of the best teachers out there");
153INSERT INTO Comments(SF_ID,C_Body) VALUES(13,"Made a complaint regarding one of the students, Kevin (ID: 4)");
154INSERT INTO Comments(SF_ID,C_Body) VALUES(13,"This person is an excelent addition to the staff");
155INSERT INTO Comments(SF_ID,C_Body) VALUES(2,"Not much is known about this mysterious lady");
156INSERT INTO Comments(SF_ID,C_Body) VALUES(1,"One of the best teachers out there");
157INSERT INTO Comments(SF_ID,C_Body) VALUES(2,"Not much is known about this mysterious lady");
158
159INSERT INTO CSFacility VALUES(0,"Second Cup");
160INSERT INTO CSFacility VALUES(1,"Learning Center");
161INSERT INTO CSFacility VALUES(2,"Network Lab");
162INSERT INTO CSFacility VALUES(3,"CS-127 Lecture Hall");
163INSERT INTO CSFacility VALUES(4,"ShiftKey Labs");
164INSERT INTO CSFacility VALUES(5,"Computer Work Station-1");
165INSERT INTO CSFacility VALUES(6,"Computer Work Station-2");
166INSERT INTO CSFacility VALUES(7,"Computer Work Station-3");
167INSERT INTO CSFacility VALUES(8,"Computer Work Station-4");
168INSERT INTO CSFacility VALUES(9,"Computer Work Station-5");
169
170INSERT INTO Reserve_Facility (RF_Date,ST_ID,SF_ID,CF_ID) VALUES('2018-01-01',1,1,1);
171INSERT INTO Reserve_Facility (RF_Date,ST_ID,SF_ID,CF_ID) VALUES('2018-03-03',2,2,2);
172INSERT INTO Reserve_Facility (RF_Date,ST_ID,SF_ID,CF_ID) VALUES('2018-04-10',3,3,5);
173INSERT INTO Reserve_Facility (RF_Date,ST_ID,SF_ID,CF_ID) VALUES('2018-04-10',3,3,6);
174INSERT INTO Reserve_Facility (RF_Date,ST_ID,SF_ID,CF_ID) VALUES('2018-04-10',3,3,7);
175INSERT INTO Reserve_Facility (RF_Date,ST_ID,SF_ID,CF_ID) VALUES('2018-04-10',3,3,8);
176INSERT INTO Reserve_Facility (RF_Date,ST_ID,SF_ID,CF_ID) VALUES('2018-04-10',3,3,9);