Project ForgeConsider the relations r(A, B) and s(B, C), where s.B is a primary key and r.B is a foreign key referencing s.B. Consider the query Q: r ⋈ (σ_{B<5}(s)). Let LOJ denote the natural left outer-join operation. Assume that r and s contain no null values. Which one of the following queries is NOT equivalent to Q?
- Aσ_{B<5}(r ⋈ s)
- Bσ_{B<5}(r LOJ s)
- r LOJ (σ_{B<5}(s))
- Dσ_{B<5}(r) LOJ s
Explanation
Q = r ⋈ (σ_{B<5}(s)) is an INNER join, so it only keeps r-rows whose B value is < 5 (matching the filtered s). Option C, r LOJ (σ_{B<5}(s)), is a LEFT OUTER join instead — it keeps ALL rows of r regardless of whether their B value is < 5, filling in NULLs for s.C when there's no match in the filtered s. This retains extra rows (with NULLs) that Q would have dropped entirely, so option C is NOT equivalent to Q. The other three options can all be shown equivalent to Q using the foreign-key constraint (every r.B value is guaranteed to exist in s.B, so no NULLs are ever introduced in options A, B, D).
Source: GATE CS 2018 (2018)
This question is part of a 15-day revision cycle — sign in and Project Forge will bring it back the day before you'd forget it.