The GATE Grind

GATE 2018 CS – Question 51

Databases · Relational Model: Relational Algebra, Tuple Calculus, SQL · 2 marks · Multiple choice

Consider 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\bowtie(\sigma_{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$?

  1. $\sigma_{B<5}(r\bowtie s)$
  2. $\sigma_{B<5}(r\ LOJ\ s)$
  3. $r\ LOJ\ (\sigma_{B<5}(s))$
  4. $\sigma_{B<5}(r)\ LOJ\ s$

Practise this question in The GATE Grind →

Show answer and explanation

Correct answer: (C) $r\ LOJ\ (\sigma_{B<5}(s))$

Explanation

Because $r.B$ is a foreign key, every tuple of $r$ has a match in $s$. $Q$ keeps only tuples of $r$ whose $B<5$. Options A, B and D keep the same set. But in C the left outer join keeps tuples of $r$ with $B\ge5$ as well (padded with nulls), so C is not equivalent.