· 8 years ago · Dec 04, 2017, 06:08 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` AFTER INSERT ON `dakare02_Grades`
126 BEGIN
127
128 UPDATE `dakare02_StudentPerformance`
129
130 IF(NEW.`grade` = 'A')
131 THEN SET `gpa` = SELECT (SUM
132 (CASE
133 WHEN `grade` = 'A' THEN 4.0
134 WHEN `grade` = 'B' THEN 3.5
135 WHEN `grade` = 'C' THEN 3.0
136 WHEN `grade` = 'D' THEN 2.5
137 WHEN `grade` = 'F' THEN 1.0
138 WHEN `grade` = 'I' THEN 0.0
139 END)) / (COUNT(*)) AS GPA
140 FROM `dakare02_Grades` AS GR
141 WHERE `sid` = NEW.`sid`
142 GROUP BY GR.`sid`
143
144 ELSE IF (NEW.`grade` = 'B')
145 THEN SET `gpa` = SELECT (SUM(CASE
146 WHEN `grade` = 'A' THEN 4.0
147 WHEN `grade` = 'B' THEN 3.5
148 WHEN `grade` = 'C' THEN 3.0
149 WHEN `grade` = 'D' THEN 2.5
150 WHEN `grade` = 'F' THEN 1.0
151 WHEN `grade` = 'I' THEN 0.0
152 END)+3.5) / (COUNT(*) +1) AS GPA
153 FROM `dakare02_Grades` AS GR
154 WHERE `sid` = NEW.`sid`
155 GROUP BY GR.`sid`
156
157 ELSE IF (NEW.`grade` = 'C')
158 THEN SET `gpa` = SELECT (SUM(CASE
159 WHEN `grade` = 'A' THEN 4.0
160 WHEN `grade` = 'B' THEN 3.5
161 WHEN `grade` = 'C' THEN 3.0
162 WHEN `grade` = 'D' THEN 2.5
163 WHEN `grade` = 'F' THEN 1.0
164 WHEN `grade` = 'I' THEN 0.0
165 END)) / (COUNT(*)) AS GPA
166 FROM `dakare02_Grades` AS GR
167 WHERE `sid` = NEW.`sid`
168 GROUP BY GR.`sid`
169 ELSE IF (NEW.`grade` = 'D')
170 THEN SET `gpa` = SELECT (SUM(CASE
171 WHEN `grade` = 'A' THEN 4.0
172 WHEN `grade` = 'B' THEN 3.5
173 WHEN `grade` = 'C' THEN 3.0
174 WHEN `grade` = 'D' THEN 2.5
175 WHEN `grade` = 'F' THEN 1.0
176 WHEN `grade` = 'I' THEN 0.0
177 END)) / (COUNT(*)) AS GPA
178 FROM `dakare02_Grades` AS GR
179 WHERE `sid` = NEW.`sid`
180 GROUP BY GR.`sid`
181
182 ELSE IF (NEW.`grade` = 'F')
183 THEN SET `gpa` = SELECT (SUM(CASE
184 WHEN `grade` = 'A' THEN 4.0
185 WHEN `grade` = 'B' THEN 3.5
186 WHEN `grade` = 'C' THEN 3.0
187 WHEN `grade` = 'D' THEN 2.5
188 WHEN `grade` = 'F' THEN 1.0
189 WHEN `grade` = 'I' THEN 0.0
190 END)) / (COUNT(*)) AS GPA
191 FROM `dakare02_Grades` AS GR
192 WHERE `sid` = NEW.`sid`
193 GROUP BY GR.`sid`
194
195 ELSE IF (NEW.`grade` = 'I')
196 THEN SET `gpa` = SELECT (SUM(CASE
197 WHEN `grade` = 'A' THEN 4.0
198 WHEN `grade` = 'B' THEN 3.5
199 WHEN `grade` = 'C' THEN 3.0
200 WHEN `grade` = 'D' THEN 2.5
201 WHEN `grade` = 'F' THEN 1.0
202 WHEN `grade` = 'I' THEN 0.0
203 END)) / (COUNT(*)) AS GPA
204 FROM `dakare02_Grades` AS GR
205 WHERE `sid` = NEW.`sid`
206 GROUP BY GR.`sid`
207
208 END IF
209
210 WHERE `dakare02_StudentPerformance`.`sid` = NEW.`sid`
211
212
213 END
214DELIMITER ;
215
216 /*
217Inserting values into the professor table
218*/
219INSERT INTO `dakare02_Professor`(`name`, `age`, `office-building`, `office-number`, `phone-number`)
220
221VALUES
222( 'Brian Tai', 27, 'Office A', 1, '502 664 4345'),
223( 'Dylan Tai', 33, 'Office A', 2, '502 634 4445'),
224( 'Wahandra Tai', 25, 'Office A', 1, '502 452 4345'),
225( 'Louis Lawry', 68, 'Office B', 2, '124 333 3333'),
226( 'Dion Devine', 44, 'Office B', 2, '845 335 9427');
227
228
229 /*
230Inserting values into the department table
231*/
232INSERT INTO `dakare02_Department`(`dept-code`, `name`, `address`, `school`, `chair`)
233
234VALUES
235('AQFL', 'Conjuration', '1154 Toad Lane', 'Magicka', 1),
236('AZFL', 'Blood Magic', '1155 Toad Lane', 'Magicka', 2),
237('BFZN', 'Illusion', '1156 Toad Lane', 'Magicka', 3),
238('MBFL', 'Alchemy', '1212 Toad Lane', 'Witchcraft', 4),
239('TNFZ', 'VooDoo', '1213 Toad Lane', 'Witchcraft', 5);
240
241 /*
242Update the professors to have a department
243*/
244UPDATE `dakare02_Professor` SET `dept` = 'AQFL' WHERE `faculty-id` = 1;
245UPDATE `dakare02_Professor` SET `dept` = 'AZFL' WHERE `faculty-id` = 2;
246UPDATE `dakare02_Professor` SET `dept` = 'BFZN' WHERE `faculty-id` = 3;
247UPDATE `dakare02_Professor` SET `dept` = 'MBFL' WHERE `faculty-id` = 4;
248UPDATE `dakare02_Professor` SET `dept` = 'TNFZ' WHERE `faculty-id` = 5;
249
250
251 /*
252Inserting values into the Course table
253*/
254INSERT INTO `dakare02_Course`(`did`, `cnumber`, `title`, `num-credits`, `teacher`)
255
256VALUES
257('AQFL', 101, 'Intro to Conjuration', 3, 1),
258('AZFL', 101, 'Intro to Blood Magic', 3, 2),
259('BFZN', 101, 'Intro to Blood Illusion', 3, 3),
260('MBFL', 101, 'Intro to Alchemy', 3, 4),
261('TNFZ', 101, 'Intro to VooDoo', 3, 5);
262
263
264 /*
265Inserting values into the Student table
266*/
267INSERT INTO `dakare02_Students`(`name`, `address`, `date-of-birth`)
268
269VALUES
270( 'Harry Potter', '1245 Hell Lane', '09/04/1995'),
271( 'Griffin Griffindor', '542 Lightning Street', '07/11/1995'),
272( 'Rachel Serpentithe', '117 Circle Lane', '03/06/1995'),
273( 'Dread Reaper', '7645 Abyss Avenue', '02/24/1994'),
274( 'Virgil Nine-Tails', '1245 Dragon Spire Rd', '12/30/1996');
275
276
277 /*
278Inserting values into the Enrolls table
279*/
280INSERT INTO `dakare02_Enrolls`(`sid`, `did`, `cnumber`, `semester`, `year`)
281
282VALUES
283(1, 'AQFL', 101, 'Summer', '2017' ),
284(2, 'AZFL', 101, 'Spring', '2017' ),
285(3, 'BFZN', 101, 'Fall', '2017' ),
286(4, 'MBFL', 101, 'Spring', '2017' ),
287(5, 'TNFZ', 101, 'Summer', '2017' );
288
289 /*
290Inserting values into the Grades table
291*/
292INSERT INTO `dakare02_Grades`(`sid`, `did`, `cnumber`, `grade`)
293VALUES
294(1, 'AQFL', 101, 'A' ),
295(2, 'AZFL', 101, 'I' ),
296(3, 'BFZN', 101, 'B' ),
297(4, 'MBFL', 101, 'A' ),
298(5, 'TNFZ', 101, 'C' );
299
300INSERT INTO `dakare02_studentperformance`(`sid`, `gpa`)
301 SELECT `sid`, AVG(CASE
302 WHEN `grade` = 'A' THEN 4.0
303 WHEN `grade` = 'B' THEN 3.5
304 WHEN `grade` = 'C' THEN 3.0
305 WHEN `grade` = 'D' THEN 2.5
306 WHEN `grade` = 'F' THEN 1.0
307 WHEN `grade` = 'I' THEN 0.0
308 END) AS GPA
309 FROM `dakare02_Grades` AS GR
310 GROUP BY GR.`sid`;
311
312
313INSERT INTO `dakare02_Enrolls`(`sid`, `did`, `cnumber`, `semester`, `year`)
314
315VALUES
316(1, 'AZFL', 101, 'Summer', '2017' );
317
318
319INSERT INTO `dakare02_Grades`(`sid`, `did`, `cnumber`, `grade`)
320VALUES
321(1, 'AZFL', 101, 'C' );