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.