• INSTRUCTIONS
(1) This lab is graded.
(2) Use the student database that was created in Lab7.
• QUESTION 1
Consider the following relations: Student(snum: integer, sname: string, major: string, level: string, age: integer, to-
tal_credits: integer)
Class(name: string, meets_at: time, room: string, fid: integer)
Enrolled(snum: integer, cname: string, varchar:grade)
Faculty (fid: integer, fname: string, deptid: integer)
The meaning of these relations is straightforward; for example, Enrolled has one record per student-class pair such that the student is enrolled in the class.
(1) Alter the table Student to create an attribute called _ . By default, its value must be 0. Alter the table class to create an attribute called credits. The value of credits for each course was given to you in the previous lab. Alter the table enrolled to create an attribute called grade, with its default value as null. The values of grades were also given to you in the previous lab.
Write a trigger that calculates and updates the field_ using the following rules: 1) If a student has an F grade or if he has not yet been graded, he gets no credit. 2) If a student gets a grade other than F for a course, he gets the credits for that course. 3) It is possible that a student’s grade changes after re-evaluation of his papers, after he writes a supplementary exam, etc. It is also possible that a grade that was entered wrongly is later corrected.
Add the values given for grades to each student in enrolled. Check whether the trigger works as expected. You can assume that entries in the student, enrolled or class tables will not get deleted. [5 Marks]
(2) Create a table called _ , with the attribute snum. If _ of a student is more than 10, add the student’s number (snum) to this table, indicating that he has sufficient credits to move to the next step. Due to grade changes, if _ reduces, the student’s number must not be in this table. If it is present, it must be removed. [5 Marks]
.
1
