15.2

Consider the bank database of Figure 15.14, where the primary keys are underlined, and the following SQL query:

SELECT T.branch_name
FROM branch T, branch S
WHERE T.assets > S.assets AND S.branch_city = "Brooklyn"

Write an efficient relational-algebra expression that is equivalent to this query. Justify your choice.

---

Query:

${\Pi }_{T.branch\_name}(({\Pi }_{branch\_name,assets}({\rho }_T(branch))){\bowtie }_{T.assets>S.assets}({\Pi }_{assets}({\sigma }_{branch\_city{=}^{\prime }Brookly{n}^{\prime }}({\rho }_S(branch)))))$

This expression performs the theta join on the smallest amount of data possible. It does this by restricting the right-hand side operand of the join to only those branches in Brooklyn and also eliminating the unneeded attributes from both the operands.