Given two relations R1 (A, B) and R2 (C, D), the result of following query…
2017
Given two relations R1 (A, B) and R2 (C, D), the result of following query
Select distinct A, B
from R1 , R2
is guaranteed to be same as R1 provided one of the following condition is satisfied.
- A.
R1 has no duplicates and R2 is empty.
- B.
R1 has no duplicates and R2 is non - empty.
- C.
Both R1 and R2 have no duplicates.
- D.
R2 has no duplicates and R1 is non - empty.
Attempted by 760 students.
Show answer & explanation
Correct answer: B
Answer: R1 has no duplicates and R2 is non - empty guarantees the result (and in fact the weaker condition that R2 is non - empty alone is sufficient for the DISTINCT projection to contain all (A,B) pairs from R1).
If the second relation is non - empty, then for every row (A,B) in the first relation there is at least one row in the cross product R1 × R2 that projects to that (A,B).
The projection SELECT A, B takes A and B values only from rows of R1, so it cannot introduce any (A,B) pair that does not already appear in R1.
SELECT DISTINCT removes duplicates produced by the cross product. If R1 has no duplicates then the set of distinct (A,B) pairs produced equals R1. Even if R1 had duplicates, the DISTINCT result equals the set of unique (A,B) pairs from R1.
Why other listed conditions fail:
If the second relation is empty, the cross product is empty and the SELECT DISTINCT result is empty; that equals the first relation only if the first relation is also empty, which is not guaranteed by saying the first relation has no duplicates.
Stating that the second relation has no duplicates does not ensure it is non - empty; an empty relation trivially has no duplicates but would make the result empty.
Summary: The essential requirement is that the second relation be non - empty. The option that adds 'first relation has no duplicates and second relation is non - empty' is therefore a correct sufficient condition; the no-duplicates part is stronger than necessary.