· 8 years ago · Dec 04, 2017, 05:46 PM
1CREATE DATABASE dakare02_CECS535Project;
2
3USE dakare02_CECS535Project;
4
5CREATE TABLE `dakare02_Professor`
6(
7 `faculty-id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
8 `name` VARCHAR(100),
9 `age` INT UNSIGNED,
10 `office-building` VARCHAR(100),
11 `office-number` INT UNSIGNED,
12 `phone-number` VARCHAR(20),
13 `dept` VARCHAR(4)
14
15);
16
17CREATE TABLE `dakare02_Department`
18(
19 `dept-code`VARCHAR(4) PRIMARY KEY,
20 `name` VARCHAR(100),
21 `address` VARCHAR(100),
22 `school` VARCHAR(100),
23 `chair` INT UNSIGNED NOT NULL,
24 FOREIGN KEY `fk_chair`(`chair`) REFERENCES `dakare02_Professor`(`faculty-id`),
25 CONSTRAINT `chk_school` CHECK(`school` IN ('Magicka', 'Witchcraft'))
26
27);
28
29/*
30Add the foreign key from professor to department now that the department relation has been created and can be referenced
31*/
32ALTER TABLE `dakare02_Professor` ADD FOREIGN KEY(`dept`) REFERENCES `dakare02_Department`(`dept-code`);
33
34
35 CREATE TABLE `dakare02_Course`
36(
37 `did` VARCHAR(4),
38 `cnumber` INT UNSIGNED,
39 `title` VARCHAR(100),
40 `num-credits` INT UNSIGNED,
41 `teacher` INT UNSIGNED NOT NULL,
42 PRIMARY KEY(`did`, `cnumber`),
43 FOREIGN KEY `fk_did`(`did`) REFERENCES `dakare02_Department`(`dept-code`),
44 FOREIGN KEY `fk_teacher`(`teacher`) REFERENCES `dakare02_Professor`(`faculty-id`)
45
46);
47
48 CREATE TABLE `dakare02_Students`
49 (
50 `sid` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
51 `name` VARCHAR(100),
52 `address` VARCHAR(100),
53 `date-of-birth` VARCHAR(100)
54);
55
56 CREATE TABLE `dakare02_Enrolls`
57(
58 `sid` INT UNSIGNED NOT NULL,
59 `did` VARCHAR(4),
60 `cnumber` INT UNSIGNED,
61 `semester` VARCHAR(10),
62 `year` VARCHAR(25),
63
64 PRIMARY KEY(`sid`, `did`, `cnumber`,`semester`,`year`),
65 FOREIGN KEY `fk_sid`(`sid`) REFERENCES `dakare02_Students`(`sid`),
66 FOREIGN KEY `fk_course`(`did`,`cnumber`) REFERENCES `dakare02_Course`(`did`, `cnumber`),
67
68 CONSTRAINT `chk_sem` CHECK(`semester` IN ('Fall', 'Spring', 'Summer'))
69);
70
71 CREATE TABLE `dakare02_Grades`
72(
73 `sid` INT UNSIGNED NOT NULL,
74 `did` VARCHAR(4),
75 `cnumber` INT UNSIGNED,
76 `grade` VARCHAR(1),
77
78 FOREIGN KEY `fk_sid`(`sid`) REFERENCES `dakare02_Students`(`sid`),
79 FOREIGN KEY `fk_course`(`did`,`cnumber`) REFERENCES `dakare02_Course`(`did`, `cnumber`),
80 CONSTRAINT `chk_grade` CHECK(`grade` IN ('A', 'B', 'C', 'D', 'F', 'I'))
81
82);
83
84 CREATE TABLE `dakare02_StudentPerformance`
85(
86 `sid` INT UNSIGNED NOT NULL PRIMARY KEY,
87 `gpa` FLOAT,
88
89
90 FOREIGN KEY `fk_sid`(`sid`) REFERENCES `dakare02_Students`(`sid`)
91);
92
93
94
95
96/*
97This trigger ensures each grade for each student has a corresponding row in the enrolls relation before before a new grade is inserted into the 'Grades' relation
98*/
99DELIMITER
100CREATE TRIGGER `dakare02_chk_GradeTuples` BEFORE INSERT ON `dakare02_Grades`
101 FOR EACH ROW
102 BEGIN
103 IF (
104 EXISTS(
105 SELECT *
106 FROM `dakare02_Enrolls`
107 WHERE NEW.`sid` = `sid`
108 AND NEW.`did` = `did`
109 AND NEW.`cnumber` = `cnumber`
110 AND (NEW.`grade` = 'A' OR NEW.`grade` = 'B' OR NEW.`grade` = 'C' OR NEW.`grade` = 'D' OR NEW.`grade` = 'F' OR NEW.`grade` = 'I')
111 )
112 )
113 THEN INSERT INTO `dakare02_Grades`
114 VALUES
115 (NEW.`sid`, NEW.`did`, NEW.`cnumber`, NEW.`grade`);
116
117
118 END IF;
119 END
120DELIMITER ;
121
122
123
124DELIMITER
125CREATE TRIGGER `dakare02_UpdateGPA` BEFORE INSERT ON `dakare02_Grades`
126 FOR EACH ROW
127 BEGIN
128
129 UPDATE `dakare02_StudentPerformance`
130
131 IF(NEW.`grade` = 'A')
132 THEN SET `gpa` = SELECT (SUM
133 (CASE
134 WHEN `grade` = 'A' THEN 4.0
135 WHEN `grade` = 'B' THEN 3.5
136 WHEN `grade` = 'C' THEN 3.0
137 WHEN `grade` = 'D' THEN 2.5
138 WHEN `grade` = 'F' THEN 1.0
139 WHEN `grade` = 'I' THEN 0.0
140 END)+4.0) / (COUNT(*) +1) AS GPA
141 FROM `dakare02_Grades` AS GR
142 WHERE `sid` = NEW.`sid`
143 GROUP BY GR.`sid`
144
145 ELSE IF (NEW.`grade` = 'B')
146 THEN SET `gpa` = SELECT (SUM(CASE
147 WHEN `grade` = 'A' THEN 4.0
148 WHEN `grade` = 'B' THEN 3.5
149 WHEN `grade` = 'C' THEN 3.0
150 WHEN `grade` = 'D' THEN 2.5
151 WHEN `grade` = 'F' THEN 1.0
152 WHEN `grade` = 'I' THEN 0.0
153 END)+3.5) / (COUNT(*) +1) AS GPA
154 FROM `dakare02_Grades` AS GR
155 WHERE `sid` = NEW.`sid`
156 GROUP BY GR.`sid`
157
158 ELSE IF (NEW.`grade` = 'C')
159 THEN SET `gpa` = SELECT (SUM(CASE
160 WHEN `grade` = 'A' THEN 4.0
161 WHEN `grade` = 'B' THEN 3.5
162 WHEN `grade` = 'C' THEN 3.0
163 WHEN `grade` = 'D' THEN 2.5
164 WHEN `grade` = 'F' THEN 1.0
165 WHEN `grade` = 'I' THEN 0.0
166 END)+3.0) / (COUNT(*) +1) AS GPA
167 FROM `dakare02_Grades` AS GR
168 WHERE `sid` = NEW.`sid`
169 GROUP BY GR.`sid`
170 ELSE IF (NEW.`grade` = 'D')
171 THEN SET `gpa` = SELECT (SUM(CASE
172 WHEN `grade` = 'A' THEN 4.0
173 WHEN `grade` = 'B' THEN 3.5
174 WHEN `grade` = 'C' THEN 3.0
175 WHEN `grade` = 'D' THEN 2.5
176 WHEN `grade` = 'F' THEN 1.0
177 WHEN `grade` = 'I' THEN 0.0
178 END)+2.5) / (COUNT(*) +1) AS GPA
179 FROM `dakare02_Grades` AS GR
180 WHERE `sid` = NEW.`sid`
181 GROUP BY GR.`sid`
182
183 ELSE IF (NEW.`grade` = 'F')
184 THEN SET `gpa` = SELECT (SUM(CASE
185 WHEN `grade` = 'A' THEN 4.0
186 WHEN `grade` = 'B' THEN 3.5
187 WHEN `grade` = 'C' THEN 3.0
188 WHEN `grade` = 'D' THEN 2.5
189 WHEN `grade` = 'F' THEN 1.0
190 WHEN `grade` = 'I' THEN 0.0
191 END)+1.0) / (COUNT(*) +1) AS GPA
192 FROM `dakare02_Grades` AS GR
193 WHERE `sid` = NEW.`sid`
194 GROUP BY GR.`sid`
195
196 ELSE IF (NEW.`grade` = 'I')
197 THEN SET `gpa` = SELECT (SUM(CASE
198 WHEN `grade` = 'A' THEN 4.0
199 WHEN `grade` = 'B' THEN 3.5
200 WHEN `grade` = 'C' THEN 3.0
201 WHEN `grade` = 'D' THEN 2.5
202 WHEN `grade` = 'F' THEN 1.0
203 WHEN `grade` = 'I' THEN 0.0
204 END)+1.0) / (COUNT(*) +1) AS GPA
205 FROM `dakare02_Grades` AS GR
206 WHERE `sid` = NEW.`sid`
207 GROUP BY GR.`sid`
208
209 END IF
210
211 WHERE NEW.`sid` = `dakare02_StudentPerformance`.`sid`
212
213
214 END
215DELIMITER ;
216
217 /*
218Inserting values into the professor table
219*/
220INSERT INTO `dakare02_Professor`(`name`, `age`, `office-building`, `office-number`, `phone-number`)
221
222VALUES
223( 'Brian Tai', 27, 'Office A', 1, '502 664 4345'),
224( 'Dylan Tai', 33, 'Office A', 2, '502 634 4445'),
225( 'Wahandra Tai', 25, 'Office A', 1, '502 452 4345'),
226( 'Louis Lawry', 68, 'Office B', 2, '124 333 3333'),
227( 'Dion Devine', 44, 'Office B', 2, '845 335 9427');
228
229
230 /*
231Inserting values into the department table
232*/
233INSERT INTO `dakare02_Department`(`dept-code`, `name`, `address`, `school`, `chair`)
234
235VALUES
236('AQFL', 'Conjuration', '1154 Toad Lane', 'Magicka', 1),
237('AZFL', 'Blood Magic', '1155 Toad Lane', 'Magicka', 2),
238('BFZN', 'Illusion', '1156 Toad Lane', 'Magicka', 3),
239('MBFL', 'Alchemy', '1212 Toad Lane', 'Witchcraft', 4),
240('TNFZ', 'VooDoo', '1213 Toad Lane', 'Witchcraft', 5);
241
242 /*
243Update the professors to have a department
244*/
245UPDATE `dakare02_Professor` SET `dept` = 'AQFL' WHERE `faculty-id` = 1;
246UPDATE `dakare02_Professor` SET `dept` = 'AZFL' WHERE `faculty-id` = 2;
247UPDATE `dakare02_Professor` SET `dept` = 'BFZN' WHERE `faculty-id` = 3;
248UPDATE `dakare02_Professor` SET `dept` = 'MBFL' WHERE `faculty-id` = 4;
249UPDATE `dakare02_Professor` SET `dept` = 'TNFZ' WHERE `faculty-id` = 5;
250
251
252 /*
253Inserting values into the Course table
254*/
255INSERT INTO `dakare02_Course`(`did`, `cnumber`, `title`, `num-credits`, `teacher`)
256
257VALUES
258('AQFL', 101, 'Intro to Conjuration', 3, 1),
259('AZFL', 101, 'Intro to Blood Magic', 3, 2),
260('BFZN', 101, 'Intro to Blood Illusion', 3, 3),
261('MBFL', 101, 'Intro to Alchemy', 3, 4),
262('TNFZ', 101, 'Intro to VooDoo', 3, 5);
263
264
265 /*
266Inserting values into the Student table
267*/
268INSERT INTO `dakare02_Students`(`name`, `address`, `date-of-birth`)
269
270VALUES
271( 'Harry Potter', '1245 Hell Lane', '09/04/1995'),
272( 'Griffin Griffindor', '542 Lightning Street', '07/11/1995'),
273( 'Rachel Serpentithe', '117 Circle Lane', '03/06/1995'),
274( 'Dread Reaper', '7645 Abyss Avenue', '02/24/1994'),
275( 'Virgil Nine-Tails', '1245 Dragon Spire Rd', '12/30/1996');
276
277
278 /*
279Inserting values into the Enrolls table
280*/
281INSERT INTO `dakare02_Enrolls`(`sid`, `did`, `cnumber`, `semester`, `year`)
282
283VALUES
284(1, 'AQFL', 101, 'Summer', '2017' ),
285(2, 'AZFL', 101, 'Spring', '2017' ),
286(3, 'BFZN', 101, 'Fall', '2017' ),
287(4, 'MBFL', 101, 'Spring', '2017' ),
288(5, 'TNFZ', 101, 'Summer', '2017' );
289
290 /*
291Inserting values into the Grades table
292*/
293INSERT INTO `dakare02_Grades`(`sid`, `did`, `cnumber`, `grade`)
294VALUES
295(1, 'AQFL', 101, 'A' ),
296(2, 'AZFL', 101, 'I' ),
297(3, 'BFZN', 101, 'B' ),
298(4, 'MBFL', 101, 'A' ),
299(5, 'TNFZ', 101, 'C' );
300
301INSERT INTO `dakare02_studentperformance`(`sid`, `gpa`)
302 SELECT `sid`, AVG(CASE
303 WHEN `grade` = 'A' THEN 4.0
304 WHEN `grade` = 'B' THEN 3.5
305 WHEN `grade` = 'C' THEN 3.0
306 WHEN `grade` = 'D' THEN 2.5
307 WHEN `grade` = 'F' THEN 1.0
308 WHEN `grade` = 'I' THEN 0.0
309 END) AS GPA
310 FROM `dakare02_Grades` AS GR
311 GROUP BY GR.`sid`;
312
313
314INSERT INTO `dakare02_Enrolls`(`sid`, `did`, `cnumber`, `semester`, `year`)
315
316VALUES
317(1, 'AZFL', 101, 'Summer', '2017' );
318
319
320INSERT INTO `dakare02_Grades`(`sid`, `did`, `cnumber`, `grade`)
321VALUES
322(1, 'AZFL', 101, 'C' );