COMP3311 Final Exam 20T3 The University of New South Wales
COMP3311 Database Systems
Final Exam 20T3 		Database Systems
[Front Page] [Notes] [Database] [Course Website] [Cheat Sheets]
[Q1] [Q2] [Q3] [Q4] [Q5] [Q6] [Q7] [Q8] [Q9] [Q10] [Q11] [Q12]

Question 9 (9 marks)

Note: This question is asking you to write some PLpgSQL functions. While you have a PostgreSQL server and could test these out, we do not expect you to do this. Simply write the code in the file q9.txt 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.

For this question, assume that the MemberOf table has two extra fields: joined (date the performer joined the group), departed (date the performer left the group).

Exercises:

Write triggers to ensure that the Groups data is correctly maintained when the following changes occur:

  1. When the last member of a group leaves (i.e. all members are noted as departed)

    • the group should be recorded as disbanded
    • use the value of member's departure data as the date of disbanding

  2. When a group changes its name

    • the existing Groups tuple should not have its name changed; it should instead be marked as disbanded (using the value of current_date)
    • a new Groups entry should be made, using the value of current_date as the formation date
    • you can assume that there is some method available to generate new group id's
      (write an assumption to this effect; any reasonable assumption is acceptable)
    • all musicians who were members of the old group should be entered as members of the new group
    • all musicians where were members of the old group should be marked as having left the old group

For each case, you must write a suitable PostgreSQL CREATE TRIGGER statement and define a PLpgSQL function (or collection of functions) to carry out the appropriate actions. You must specify whether the trigger is executed before or after after the modification event. Your triggers should be defined so as to avoid trigger-cycles; to do this, you are allowed to assume that PostgreSQL allows triggers to be restricted to updates of specified columns.

Instructions:

End of Question