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
Answer
(B)
Explanation
With no grouping or counting, “at least 3 persons” has to be found as three tuples with the same city and pairwise different pids. Two cross products bring three renamed copies together:
\[\pi_{P_1.\mathit{city}}\big(\sigma_{C}(\rho_{P_1}(\mathit{Person}) \times \rho_{P_2}(\mathit{Person}) \times \rho_{P_3}(\mathit{Person}))\big)\]
where \(C\) requires equal cities and \(P_1.\mathit{pid} \ne P_2.\mathit{pid}\), \(P_1.\mathit{pid} \ne P_3.\mathit{pid}\), \(P_2.\mathit{pid} \ne P_3.\mathit{pid}\).
One cross product is not enough. An expression with at most one cross product builds each answer from at most two Person tuples. If it reported a city with three residents, it would also report that city for a relation holding only the two tuples it used, where that city has at most two residents.
Answer: B