pfW9PckK

1create database egmckn01_CECSProject;
2use egmckn01_CECSProject;
3create table egmckn01_Professor
4 (faculty_id int(11) NOT NULL AUTO_INCREMENT,
5  name
6  age
7  office_building
8  office_number
9  phone_number
10  dept,
11  PRIMARY KEY (id));
12create table egmckn01_Department
13 (dept_code char(4),
14  name,
15  address,
16  school,
17  chair,
18  PRIMARY KEY (dept_code));
19create table egmckn01_Course
20 (did char(4),
21  cnumber int(14),
22  title,
23  num_credits,
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_gpi 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,