4.8

As discussed in Section 4.4.8, we expect the constraint “an instructor cannot teach sections in two different classrooms in a semester in the same time slot” to hold.
a. Write an SQL query that returns all (instructor, section) combinations that violate this constraint.
b. Write an SQL assertion to enforce this constraint (as discussed in Section 4.4.8, current generation database systems do not support assertions, although they are part of the SQL standard).

---

1. Query:

SELECT id,name,semester,year,time_slot_id,COUNT(DISTINCT (building, room_number))
FROM instructor NATURAL JOIN teaches NATURAL JOIN section
GROUP BY (id,name,semester,year,time_slot_id)
HAVING COUNT(DISTINCT (building, room_number)) > 1; 

Note that we are assuming that the time slots are mutually exclusive (i.e. The time slots don’t intersect).

Note that the distinct keyword is required above. This is to allow two different sections to run concurrently in the same time slot and are taught by the same instructor without being reported as a constraint violation.

2. Query:

CREATE ASSERTION CHECK NOT EXISTS (
    -- the folowing is identical to the previous query.
    SELECT id,name,semester,year,time_slot_id,COUNT(DISTINCT (building, room_number))
    FROM instructor NATURAL JOIN teaches NATURAL JOIN section
    GROUP BY (id,name,semester,year,time_slot_id)
    HAVING COUNT(DISTINCT (building, room_number)) > 1
);