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:
$ {T.branch_name} ( ({branch_name, assets}(T(branch))) {T.assets > S.assets} ({assets} ({branch_city = ‘Brooklyn’}(_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.