ITPEC FE Morning April 2020 Question 29

Relational Division with NOT EXISTS + EXCEPT — find employees who work on all projects.

The query uses a classic relational division pattern:

• •(SELECT PID FROM PROJECT) — the full set of all projects
• •(SELECT PID FROM WORK_PROJ WHERE EID = EMPLOYEE.EID) — projects for a specific employee
• •EXCEPT — returns projects the employee does not participate in
• •NOT EXISTS — true only when the EXCEPT result is empty (employee covers all projects)

Step-by-step evaluation:

• •Rahbar (EID=1): works on PID 1, 2 → {1,2} EXCEPT {1,2} = ∅ → included
• •Karthik (EID=2): works on PID 1 → {1,2} EXCEPT {1} = {2} → excluded
• •Abir (EID=3): works on PID 1, 2 → {1,2} EXCEPT {1,2} = ∅ → included

Result: Rahbar, Abir → (d)

Why not others:
- (a) NULL — two employees satisfy the condition
- (b) Rahbar, Karthik — Karthik only works on PID 1, missing PID 2
- (c) Karthik, Abir — same issue with Karthik

Key pattern: NOT EXISTS (A EXCEPT B) means "B contains all of A" — the standard SQL way to express universal quantification (∀).

AI-generated — may contain errors