beXBQ88J

1-- create_schema.txt
2-- CSC 370 - Spring 2018
3--
4--
5-- Faesal Murad - 07/04/2018
6-- V00852832
7
8-- If the tables/functions already exist, delete them --
9drop table if exists grades;
10drop table if exists prerequisites;
11drop table if exists enrollments;
12drop table if exists students;
13drop table if exists course_offerings;
14drop table if exists courses;
15
16drop function if exists enrollment_capacity();
17drop function if exists students_ignore_duplicates();
18drop function if exists course_ignore_duplicates();
19drop function if exists prerequisite_check();
20drop function if exists reject_drops();
21
22-- create students table --
23create table students( 
24	student_id varchar(9) primary key,
25	name varchar(255)
26	);
27
28-- create courses table --
29create table courses( 
30	course_code varchar(10) primary key
31	);
32
33-- create course_offerings table --
34create table course_offerings( 
35	course_code varchar(10),
36	course_name varchar(128) not null,
37	term_code integer,
38	maximum_capacity integer,
39	check (maximum_capacity >= 0),
40	instructor_name varchar(128) not null,
41	primary key (course_code, term_code)
42	);
43
44-- create enrollments table --
45create table enrollments( 
46	student_id varchar(9),
47	course_code varchar(10), 
48	term_code integer,
49	foreign key (student_id) references students(student_id)
50	on update cascade,
51	foreign key (course_code, term_code) references course_offerings(course_code, term_code)
52	on update cascade,
53	primary key (student_id, course_code, term_code)
54	);
55
56-- create grades table --
57create table grades( 
58	student_id varchar(9),
59	course_code varchar(10), 
60	term_code integer,
61	final_grade integer,
62	check (final_grade <= 100 and final_grade >= 0),
63	foreign key (student_id, course_code, term_code) references enrollments(student_id, course_code, term_code)
64	on update cascade,
65	primary key (student_id, course_code, term_code)
66	);
67
68-- create prerequisites table --
69create table prerequisites( 
70	course_code varchar(10), 
71	term_code integer,
72	prerequisite varchar(10), 
73	foreign key (prerequisite) references courses(course_code)
74	on update cascade,
75	foreign key (course_code, term_code) references course_offerings(course_code,term_code)
76	on update cascade,
77	primary key(course_code,term_code,prerequisite)
78	);
79
80
81-- check classes are not full before enrolling a student
82create function enrollment_capacity()
83returns trigger as
84$BODY$
85begin
86if (select count(*) from enrollments where course_code = new.course_code and term_code = new.term_code) > (select maximum_capacity from course_offerings
87    where course_code = new.course_code and term_code = new.term_code)
88then 
89	raise exception 'Cannot enroll student. Class is full';
90end if;
91return new;
92end
93$BODY$
94language plpgsql;
95
96-- Add the size constraint trigger to enrollments --
97create trigger enrollments_size_trigger
98	after insert or update on enrollments 
99	for each row 
100	execute procedure enrollment_capacity();
101-- --
102
103-- check that student has all prerequisites for a course
104create function prerequisite_check()
105returns trigger as
106$BODY$
107begin
108
109-- if course has no prerequisites
110if (select count(*) from prerequisites where course_code = new.course_code and term_code =  new.term_code) = 0
111then
112	return new;
113end if;
114
115-- check what courses student has done in previous terms ( with final_grade >= 50 or unmarked)
116if (
117with pending_courses as (select course_code from enrollments natural left outer join grades where
118							student_id = new.student_id and term_code <  new.term_code and final_grade is NULL),
119completed_courses as (select course_code from enrollments natural left outer join grades where
120							student_id = new.student_id and term_code <  new.term_code and final_grade >= 50),
121all_valid_courses as (select * from pending_courses union select * from completed_courses),
122prereqs as (select prerequisite from prerequisites where course_code =  new.course_code and term_code =  new.term_code),
123missing_courses as (select * from prereqs except select * from all_valid_courses)
124select count(*) from missing_courses
125) = 0 
126then 
127	return new;
128end if;
129raise exception 'Cannot enroll student. Prerequisite error ';
130end
131$BODY$
132language plpgsql;
133
134-- Add the prerequisite constraint trigger to enrollments --
135create trigger prerequisite_check_trigger
136	after  insert on enrollments 
137	for each row 
138	execute procedure prerequisite_check();
139-- --
140
141-- Define students_ignore_duplicates function --
142create function students_ignore_duplicates()
143returns trigger as
144$BODY$
145begin
146if (select count(*) 
147	from students 
148	where student_id = new.student_id and name = new.name) > 0
149then 
150	return NULL;
151end if;
152return new;
153end
154$BODY$
155language plpgsql;
156
157-- Add the ignore duplicates trigger to the students table --
158create trigger students_ignore_duplicates_trigger
159	before insert on students 
160	for each row 
161	execute procedure students_ignore_duplicates();
162
163-- Define course_ignore_duplicates function --
164create function course_ignore_duplicates()
165returns trigger as
166$BODY$
167begin
168if (select count(*) 
169	from courses 
170	where course_code = new.course_code) > 0
171then 
172	return NULL;
173end if;
174return new;
175end
176$BODY$
177language plpgsql;
178
179-- Add the ignore duplicates trigger to the courses table --
180create trigger course_ignore_duplicates_trigger
181	before insert on courses 
182	for each row 
183	execute procedure course_ignore_duplicates();
184-- --
185
186-- Define reject_drops function --
187create function reject_drops()
188returns trigger as
189$BODY$
190begin
191if (select final_grade from enrollments natural left outer join grades
192	where student_id = old.student_id and course_code = old.course_code and term_code = old.term_code) is null
193then 
194	return old;
195end if;
196raise exception 'Cannot drop student. Grade has already been assigned';
197end
198$BODY$
199language plpgsql;
200
201-- Add the reject_drops trigger to the enrollments table --
202create trigger reject_drops_trigger
203	before delete on enrollments 
204	for each row 
205	execute procedure reject_drops();
206-- --