COMP3311 20T3 The University of New South Wales Database Systems
COMP3311 Database Systems
20T3
[Instructions] [Notes] [Database] [Course Website] [Cheat Sheets]
[Q1] [Q2] [Q3] [Q4] [Q5] [Q6] [Q7] [Q8] [Q9] [Q10] [Q11] [Q12] Question 9 (8 marks)
Note: This question is asking you to write some PLpgSQL functions. While you have a PostgreSQL server and could test these out, by building a small database of your own, we do not expect you to do this. Simply write the code in the file q9.sql as accurately as you can. Your work will not be tested via PostgreSQL, but will be assessed based on how close your code is to a correct solution.
In the MyMyUNSW database from Assignment 2, courses had evaluations (or ratings) assoaciated with them, which roughly correspond to the average score for the final question on the MyExperience survey. Some students might be interested in selecting/avoiding courses based on their rating. Doing this would require using the data in the following two tables:
Note that we don’t want to consider all courses here since e.g. a course with one student who rates the course 6 would get an unfair rating advantage. To ensure that we have a reasonable statistical basis for ratings, we use the following strategy:
the stueval field gives a rating on a 1..6 scale (1=poor .. 6=excellent)
nS … the total number of students enrolled in the course
nE … the number of students who gave an evaluation
avgEval … the mean of the non-null evaluations (the overall course evaluation) we consider only courses satisfying (nS > 10 && (3*nE) > nS)
Writing a query to provide an answer to this results in an expensive computation each time a student wants to find course ratings. It is simpler and more efficient if each Courses tuple already contains values for nS, nE, avgEval. This can be achieved by adding extra fields to the Courses table and using triggers to maintain these fields to be consistent with the records in the CourseEnrolments table. That is, we can modify the Courses table to:
Courses(id,subject,term,homepage,nS,nE,avgEval)
Newly-inserted Courses tuples have nS and nE set to 0, and avgEval set to NULL. The triggers are defined as follows:
Courses(id,subject,term,homepage) CourseEnrolments(student,course,mark,grade,stueval)
create trigger AddCourseEnrolmentTrigger
after insert on CourseEnrolments
execute procedure fixCoursesOnAddCourseEnrolment();
create trigger DropCourseEnrolmentTrigger
after delete on CourseEnrolments
execute procedure fixCoursesOnDropCourseEnrolment();
create trigger ModCourseEnrolmentTrigger
after update on CourseEnrolments
execute procedure fixCoursesOnModCourseEnrolment();
Exercises:
a. Write the trigger function fixCoursesOnAddCourseEnrolment().
b. Write the trigger function fixCoursesOnDropCourseEnrolment().
c. Write the trigger function fixCoursesOnModCourseEnrolment().
Note that most of the time, an update to a course enrolment will change only the mark or grade, and leave the evaluation unchanged. However, it is also possible that an update might add a new evaluation or change an existing evaluation. You may assume that evaluations will never be erased once set.
Instructions:
Type your answers to this question into a file called q9.txt Submit via: submit q9.txt
End of Question