Consider the following SQL query Select distinct a1, a2, …, an from r1, r2, …,…
Consider the following SQL query
Select distinct a1, a2, …, an
from r1, r2, …, rm
where P
For an arbitrary predicate P, this query is equivalent to which of the following relational algebra expressions?
Answer: A. Πa1,a2,…,an σP (r1 × r2 × ⋯× rm) — Key idea: translate the SQL clauses into relational-algebra operators: FROM → Cartesian product, WHERE → selection, SELECT DISTINCT → projection that removes…
- A.
Πa1,a2,…,an σP (r1 × r2 × ⋯× rm)
- B.
Πa1,a2,…,an σP (r1⋈r2⋈⋯⋈rm)
- C.
Πa1,a2,…,an σP (r1∪r2∪⋯∪rm)
- D.
Πa1,a2,…,an σP (r1∩r2∩⋯∩rm)
Attempted by 677 students.
Show answer & explanation
Correct answer: A
Key idea: translate the SQL clauses into relational-algebra operators: FROM → Cartesian product, WHERE → selection, SELECT DISTINCT → projection that removes duplicates.
Canonical equivalent relational-algebra expression:
π_{a1,a2,…,an}(σ_P(r1 × r2 × … × rm))
FROM r1, r2, …, rm corresponds to the Cartesian product r1 × r2 × … × rm.
WHERE P is the selection σ_P applied to that Cartesian product.
SELECT DISTINCT a1,a2,…,an is the projection π_{a1,a2,…,an}, which removes duplicate tuples in relational algebra.
Note: Although you can sometimes rewrite the selection and cross product into join operators for optimization (when P contains join-equality conditions), the direct and canonical translation of the given SQL is the selection over the Cartesian product followed by projection as shown above.