The relation schema, Person(pid,city), describes the city of residence for…
2024
The relation schema, Person(pid,city), describes the city of residence for every person uniquely identified by pid. The following relational algebra operators are available: selection, projection, cross product, and rename.
To find the list of cities where at least 3 persons reside, using the above operators, the minimum number of cross product operations that must be used is
- A.
1
- B.
2
- C.
3
- D.
4
Attempted by 185 students.
Show answer & explanation
Correct answer: B
Answer: 2 cross products.
Reasoning: To test that a city has at least three distinct persons, we need three copies of the Person relation so we can compare three tuples. Build three renamed copies (P1, P2, P3) and combine them with two cross products: (P1 × P2) × P3.
Rename Person to P1, P2, P3.
Use two cross products to get all three copies together: (P1 × P2) × P3.
Select tuples with P1.city = P2.city AND P2.city = P3.city, and require the pids to be pairwise distinct (P1.pid ≠ P2.pid, P1.pid ≠ P3.pid, P2.pid ≠ P3.pid).
Project the city attribute to get the list of cities with at least three residents.
Minimality: One cross product yields only two copies, which is insufficient to check three persons; hence at least two cross products are necessary and sufficient.
A video solution is available for this question — log in and enroll to watch it.