· 8 years ago · Dec 04, 2017, 06:34 AM
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 ('Art', 'Business', 'Engineering','Law', 'Psychology', 'Nursing', 'Medicine', 'Dentistry', 'Excercise Science'))
26
27);
28
29
30 CREATE TABLE `dakare02_Course`
31(
32 `did` VARCHAR(4),
33 `cnumber` INT(4) UNSIGNED,
34 `title` VARCHAR(100),
35 `num-credits` INT UNSIGNED,
36 `teacher` INT UNSIGNED NOT NULL,
37 FOREIGN KEY `fk_did`(`did`) REFERENCES `dakare02_Department`(`dept-code`),
38 FOREIGN KEY `fk_teacher`(`teacher`) REFERENCES `dakare02_Professor`(`faculty-id`)
39
40);
41
42 CREATE TABLE `dakare02_Students`
43 (
44 `sid` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
45 `name` VARCHAR(100),
46 `address` VARCHAR(100),
47 `date-of-birth` VARCHAR(100)
48);
49
50 CREATE TABLE `dakare02_Enrolls`
51(
52 `sid` INT UNSIGNED NOT NULL,
53 `did` VARCHAR(4),
54 `cnumber` INT UNSIGNED,
55 `semester` VARCHAR(10),
56 `year` VARCHAR(25),
57
58 FOREIGN KEY `fk_sid`(`sid`) REFERENCES `dakare02_Students`(`sid`),
59 FOREIGN KEY `fk_course`(`did`,`cnumber`) REFERENCES `dakare02_Course`(`did`, `cnumber`),
60
61 CONSTRAINT `chk_sem` CHECK(`semester` IN ('Fall', 'Spring', 'Summer'))
62);
63
64 CREATE TABLE `dakare02_Grades`
65(
66 `sid` INT UNSIGNED NOT NULL,
67 `did` VARCHAR(4),
68 `cnumber` INT UNSIGNED,
69 `grade` VARCHAR(1),
70
71 FOREIGN KEY `fk_sid`(`sid`) REFERENCES `dakare02_Students`(`sid`),
72 FOREIGN KEY `fk_course`(`did`,`cnumber`) REFERENCES `dakare02_Course`(`did`, `cnumber`),
73 CONSTRAINT `chk_grade` CHECK(`grade` IN ('A', 'B', 'C', 'D', 'F', 'I'))
74
75);
76
77 CREATE TABLE `dakare02_StudentPerformance`
78(
79 `sid` INT UNSIGNED NOT NULL PRIMARY KEY,
80 `gpa` DEC(5, 5),
81
82
83 FOREIGN KEY `fk_sid`(`sid`) REFERENCES `dakare02_Students`(`sid`)
84);
85
86
87/*
88This 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
89*/
90DELIMITER
91CREATE TRIGGER `dakare02_chk_GradeTuples` BEFORE INSERT ON `dakare02_Grades`
92 FOR EACH ROW
93 BEGIN
94 IF (
95 EXISTS(
96 SELECT *
97 FROM `dakare02_Enrolls`
98 WHERE NEW.`sid` = `sid`
99 AND NEW.`did` = `did`
100 AND NEW.`cnumber` = `cnumber`
101 AND (NEW.`grade` = 'A' OR NEW.`grade` = 'B' OR NEW.`grade` = 'C' OR NEW.`grade` = 'D' OR NEW.`grade` = 'F' OR NEW.`grade` = 'I')
102 )
103 )
104 THEN INSERT INTO `dakare02_Grades`
105 VALUES
106 (NEW.`sid`, NEW.`did`, NEW.`cnumber`, NEW.`grade`);
107
108
109 END IF;
110 END
111DELIMITER ;
112
113
114 /*
115Inserting values into the professor table
116*/
117INSERT INTO `dakare02_Professor`(`faculty-id`, `name`, `age`, `office-building`, `office-number`, `phone-number`, `dept`)
118
119VALUES
120(1101, 'Brian Tai', 27, 'Office A', 1, '502 664 4345', 'AQFL' ),
121(1102, 'Dylan Tai', 33, 'Office A', 2, '502 634 4445', 'AZFL' ),
122(1103, 'Wahandra Tai', 25, 'Office A', 1, '502 452 4345', 'BFZN' ),
123(1104, 'Louis Lawry', 68, 'Office B', 2, '124 333 3333', 'MBFL'),
124(1105, 'Dion Devine', 44, 'Office B', 2, '845 335 9427', 'TNFZ' );
125
126 /*
127Inserting values into the department table
128*/
129INSERT INTO `dakare02_Department`(`dept-code`, `name`, `address`, `school`, `chair`)
130
131VALUES
132('AQFL', 'Conjuration', '1154 Toad Lane', 'Magicka', 1101),
133('AZFL', 'Blood Magic', '1155 Toad Lane', 'Magicka', 1102),
134('BFZN', 'Illusion', '1156 Toad Lane', 'Magicka', 1103),
135('MBFL', 'Alchemy', '1212 Toad Lane', 'Witchcraft', 1104),
136('TNFZ', 'VooDoo', '1213 Toad Lane', 'Witchcraft', 1105);
137
138
139
140 /*
141Inserting values into the Course table
142*/
143INSERT INTO `dakare02_Course`(`did`, `cnumber`, `title`, `num-credits`, `teacher`)
144
145VALUES
146('AQFL', 101, 'Intro to Conjuration', 3, 1101),
147('AZFL', 101, 'Intro to Blood Magic', 3, 1102),
148('BFZN', 101, 'Intro to Blood Illusion', 3, 1103),
149('MBFL', 101, 'Intro to Alchemy', 3, 1104),
150('TNFZ', 101, 'Intro to VooDoo', 3, 1105);
151
152
153
154
155 /*
156Inserting values into the Student table
157*/
158INSERT INTO `dakare02_Students`(`sid`, `name`, `address`, `date-of-birth`)
159
160VALUES
161(1201, 'Harry Potter', '1245 Hell Lane', '09/04/1995'),
162(1202, 'Griffin Griffindor', '542 Lightning Street', '07/11/1995'),
163(1203, 'Rachel Serpentithe', '117 Circle Lane', '03/06/1995'),
164(1204, 'Dread Reaper', '7645 Abyss Avenue', '02/24/1994'),
165(1205, 'Virgil Nine-Tails', '1245 Dragon Spire Rd', '12/30/1996');
166
167
168
169
170 /*
171Inserting values into the Enrolls table
172*/
173INSERT INTO `dakare02_Enrolls`(`sid`, `did`, `cnumber`, `semester`, `year`)
174
175VALUES
176(1201, 'AQFL', 101, 'Summer', '2017' ),
177(1202, 'AZFL', 101, 'Spring', '2017' ),
178(1203, 'BFZN', 101, 'Fall', '2017' ),
179(1204, 'MBFL', 101, 'Spring', '2017' ),
180(1205, 'TNFZ', 101, 'Summer', '2017' );
181
182
183
184 /*
185Inserting values into the Grades table
186*/
187INSERT INTO `dakare02_Grades`(`sid`, `did`, `cnumber`, `grade`)
188VALUES
189(1201, 'AQFL', 101, 'A' ),
190(1202, 'AZFL', 101, 'I' ),
191(1203, 'BFZN', 101, 'B' ),
192(1204, 'MBFL', 101, 'A' ),
193(1205, 'TNFZ', 101, 'C' );