Chapter 5, Problem 5PE

Explanation of Solution

Trigger on an insert of record into section table:

Create trigger check_instructor_insert_in_section after insertion section referencing new table as inserted for each statement when inserted.course_id, inserted.sec_id, inserted.semester, inserted.year, inserted.item_slot_id in (select course_id, sec_id, semester, year, time_slot_id, count (distinct building, room_no)

from instructor natural join teaches natural join section group by (ID, name, sec_id, semester, year, time_slot_id) having count (building, room_no) > 1)

begin

    rollback

end;

Explanation:

• The prefix inserted is added to the set of tables with newly inserted records.
• Initially, define the trigger, which comes into action when an insertion into section table takes place.
• Call the “tigger check_instructor_insert_in_section”.
• Use “after insert” on clause to specify the trigger comes into effect after an insert takes place into section table.
• Use the new name for account table as inserted and put it as such in referencing new table as clause.
• Use the select query in for each statement insert into when clause. This query will get all those instructors, which violate the given constraint.
  • First, perform a natural join of instructor, teaches and section table.
  • Count the number of distinct buildings and room numbers from the join using a group by clause on “ID”, “name”, “sec_id”, “semester”, “year”, and “time_slot_id” attributes.
• Select those instructor, section details from t query for which the above-calculated count is more than 1.
• Finally, use the “begin” and “end” block to specify that any transaction, which violates this constraint, will be rolled back...