nnEJvsMD

1create database egmckn01_CECSProject;
2use egmckn01_CECSProject;
3create table egmckn01_Professor
4 (faculty_id int(11) NOT NULL AUTO_INCREMENT,
5  name varchar(30), 
6  age int(7), 
7  office_building varchar(30), 
8  office_number int(11), 
9  phone_number char(10),
10  dept char(4),
11  PRIMARY KEY (id));
12create table egmckn01_Department
13 (dept_code char(4),
14  name varchar(30),
15  address varchar(30),
16  school varchar(30),
17  chair int(11),
18  PRIMARY KEY (dept_code));
19create table egmckn01_Course
20 (did char(4),
21  cnumber int(14),
22  title varchar(30),
23  num_credits int(4),
24  teacher int(11),
25  PRIMARY KEY (did,cnumber));
26create table egmckn01_Students
27 (sid int(11) NOT NULL AUTO_INCREMENT,
28  name,
29  address,
30  date_of_birth,
31  PRIMARY KEY (sid));
32create table egmckn01_Enrolls
33 (sid int(11),
34  did char(4),
35  cnumber int(14),
36  semester,
37  year int(12),
38  PRIMARY KEY (sid,did,cnumber));
39create table egmckn01_Grades
40 (sid int(11),
41  did char(4),
42  cnumber int(14),
43  grade char(1),
44  PRIMARY KEY (sid,did,cnumber));
45create trigger check_grades before insert on egmckn01_Grades
46for each row
47begin
48if not exists (select * from egmckn01_Enrolls where egmckn01_Enrolls.sid = new.sid and egmckn01_Enrolls.cid = new.cid and egmckn01_Enrolls.cnumber = new.cnumber) then
49signal sqlstate '45000';
50end if;
51if new.grade not in ('A','B','C','D','F','I') then
52signal sqlstate '45000';
53end if;
54end;
55
56create table egmckn01_StudentPerformance
57 (sid int(11),
58  gpa numeric(3,2),
59  PRIMARY KEY (sid));
60
61create trigger update_gpa after insert on egmckn01_Grades
62for each row
63begin
64if not exists (select * from egmckn01_StudentPerformance where new.sid = egmckn01_StudentPerformance.sid) then
65insert into egmckn01_StudentPerformance (sid, gpa) values (new.sid, CASE new.grade WHEN 'A' THEN 4
66                                                                                   WHEN 'B' THEN 3.5
67                                                                                   WHEN 'C' THEN 3
68                                                                                   WHEN 'D' THEN 2.5
69                                                                                   WHEN 'F' THEN 1
70                                                                                   ELSE 0 END);
71ELSE update egmckn01_StudentPerformance set gpa = (select AVG(CASE grade WHEN 'A' THEN 4
72                                                                                   WHEN 'B' THEN 3.5
73                                                                                   WHEN 'C' THEN 3
74                                                                                   WHEN 'D' THEN 2.5
75                                                                                   WHEN 'F' THEN 1
76                                                                                   ELSE 0 END) FROM egmckn01_Grades where sid = new.sid) where egmckn01_StudentPerformance.sid = new.sid;
77end if;
78end;