Consider the relations \(R(A, B)\) and \(S(B, C)\) and the following four…
2016
Consider the relations \(R(A, B)\) and \(S(B, C)\) and the following four relational algebra queries over R and S :
I. \(\pi_{A, B} (R \bowtie S)\)
II. \(R \bowtie \pi_B(S)\)
III. \(R \cap (\pi_A(R) \times \pi_B (S))\)
IV. \(\Pi_{A. R. B} (R \times S)\) where \(R⋅B\) refers to the column B in table R.
One can determine that :
- A.
I, III and IV are the same query.
- B.
II, III and IV are the same query.
- C.
I, II and IV are the same query.
- D.
I, II and III are the same query.
Attempted by 68 students.
Show answer & explanation
Correct answer: D
Answer: I, II and III are equivalent; IV is different.
Query I (π_{A,B}(R ⋈ S)): returns all (A,B) pairs from R for which there exists a matching B in S. In other words, {(a,b) in R | ∃c: (b,c) in S}.
Query II (R ⋈ π_B(S)): π_B(S) is just the set of B values present in S, so joining R with that filters R to rows whose B occurs in S. This yields the same set as Query I.
Query III (R ∩ (π_A(R) × π_B(S))): π_A(R) × π_B(S) generates all combinations of A values from R with B values from S; intersecting with R keeps exactly those (A,B) pairs that are in R and whose B is in S. This is the same result as Queries I and II.
Query IV (projection of A and R's B from R×S): because R×S pairs every R tuple with every S tuple, projecting A and the B attribute from R produces every (A,B) present in R whenever S is non-empty (duplicates are removed by projection). Thus Query IV equals R when S is non-empty and is empty when S is empty; it does not require the B values to match between R and S and so is not equivalent to I/II/III in general.
Counterexample illustrating the difference:
Let R = {(a1,b1), (a2,b2)} and S = {(b1,c1)}.
Then I, II and III produce {(a1,b1)} because only b1 appears in S.
Query IV produces {(a1,b1), (a2,b2)} because pairing each R tuple with the single S tuple and projecting R's attributes yields every (A,B) from R.
A video solution is available for this question — log in and enroll to watch it.