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

Q. 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:   𝑟 ⋈ (𝜎𝐵<5(𝑠))

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) 𝜎𝐵<5(𝑟 ⋈ 𝑠)                                       (B) 𝜎𝐵<5(𝑟 𝐿𝑂𝐽 𝑠)

(C) 𝑟 𝐿𝑂𝐽 (𝜎𝐵<5(𝑠))                          (D) 𝜎𝐵<5(𝑟) 𝐿𝑂𝐽 𝑠

Ans: 𝑟 𝐿𝑂𝐽 (𝜎𝐵<5(𝑠))  

Sol:

Since, we are joining/LOJ using attribute B which is primary key of table s and foreign key of table r.

So, we need to apply condition σ_B<5 on left table of join always, i.e., table r because left outer join (LOJ) returns all the values from an inner join plus all values in the left table that do not match to the right table.