Question 51

Given $s.B$ is primary key and $r.B$ is foreign key referencing $s.B$, every value of $B$ in $r$ is guaranteed to exist in $s$.
Query $Q = r \bowtie (\sigma_{B < 5}(s))$ retains only tuples where $r.B = s.B$ and $B < 5$.

Evaluate the choices:

• (A) $\sigma_{B < 5}(r \bowtie s)$: Because every tuple of $r$ matches some tuple of $s$, $r \bowtie s$ preserves all $r$ tuples. Filtering by $B < 5$ produces identical results to $Q$.
• (B) $\sigma_{B < 5}(r \text{ LOJ } s)$: Since every tuple of $r$ finds a match in $s$, $r \text{ LOJ } s$ yields the exact same tuples as $r \bowtie s$. Selection $\sigma_{B < 5}$ produces identical results to $Q$.
• (C) $r \text{ LOJ } (\sigma_{B < 5}(s))$: Here, the right relation is pre-filtered. Tuples of $r$ with $B \ge 5$ find no match in $\sigma_{B < 5}(s)$. Because this is a LEFT OUTER JOIN, these tuples are retained in the output with NULL values for attribute $C$. In contrast, query $Q$ discards tuples with $B \ge 5$. Hence, query C is NOT equivalent to $Q$.
• (D) $\sigma_{B < 5}(r) \text{ LOJ } s$: Pre-filtering $r$ keeps only tuples with $B < 5$. Since each has a match in $s$, the left outer join is identical to inner join, matching $Q$.