Database Systems Lab 6 Part II

Triggers

The Islamic University of Gaza
Engineering Faculty
Department of Computer Engineering

Authors: Usama R. Al Zayan & Rasha E. Kahil2023/04/04

---

Expecteed outcomes

• To be introduced to Database Triggers commands and concepts.
• To learn how to create and delete Triggers in PostgreSQL.
• To learn how to use Triggers in PostgreSQL.

Lab 5: Time and Plan

Tasks	Timing
Quiz 7	10 min
Task 1	40 min
Task 2	40 min

Lab Tasks

Task 1 (5 marks)

1. View data in mat_all_sections view after inserting some data in both section and teaches tables, you can see that the new records are not present in the view’s response. Do you remember why? Refresh the view manually, now note that the new records are included now in the view.
2. Use triggers to automatically perform refreshes, and check if everything works properly.

Task 2 (10 marks)

3. Show how to enforce the constraint “an instructor cannot teach two different sections in a semester in the same time slot.” using a trigger (remember that the constraint can be violated by changes to the teaches relation as well as to the section relation).

(More Questions)

1. Write a function teatching_load() that takes an instructor id as an argument and finds the sum of all course hours(credits in our case) that instructor taught. Then write another function that uses the first one, to update all instructors’ salary using this equation: new salary = old salary+ [old salary * sum of his course hours *0.001].

2. Create a new trigger, that inserts the old budget into the department_budget_audits table including dept_name, old budget and the time of change when the budget of a department changes. (Create the table and any other necessary objects. Also provide some test pictures.)

3. Show how to enforce the constraint “a student cannot take two different sections in a semester in the same time slot.” using a trigger (remember that the constraint can be violated by changes to the takes relation as well as to the section relation).

4. 1. Create a new materialized view named “available_classrooms” that shows all available classrooms in all semesters along with their sections and time slots. An available classroom is a classroom with a section having enrollments that didn't exceed its classroom capacity.
   2. Create the necessary triggers to refresh that view automatically.
   3. Using a trigger enforce this rule on your university system:
      “When a classroom capacity is reached; and a new enrollment comes, this new enrollment is inserted into a section for the same course; that is in an available classroom for the same course, and semester (use the view to find the section). If there are no available sections, open one in the same semester for the same course and instructor, in a different time slot. Give the section [the next available ID*] and give it an available classroom* (note you need to insert in many tables to do that)”
      • The next available ID is 1 + max (sec_id) for all sections offered for that particular course.
      • To get an available classroom follow this rule: If x is the max (time_slot_id) for all course sections. Then choose the id equal to x+1 (that is; for A choose B, and for C choose D and so on).

tags: Database Systems IUG Computer Engineering

End Of Lab 6 Part II