12.2
Consider the bank database of Figure 12.13, 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.
Branch(branch_name, branch_city, assets)
Customer(customer_name, customer_street, customer_city)
Loan(loan_number, branch_name, amount)
Borrower(customer_name, loan_number)
Account(account_number, branch_name, balance)
Depositor(customer_name, account_number)
Figure 12.13 Banking Database
|
12.3 Let relationsr1(A, B,C) andr2(C, D, E) have the following properties:r1 has20,000tuples,r2 has45,000tuples,25 tuples of r1 t on one block,and 30 tuples of r2 t on one block.Estimate the number of block transfers and seeks required,using each of the following join strategies for r1 join r2:
a. Nested-loop join.
b. Block nested-loop join.
c. Merge join.
d. Hash join.
12.12 Whyisitnotdesirabletoforceuserstomakeanexplicitchoiceofaqueryprocessing strategy? Are there cases in which it is desirable for users to be aware of the costs of competing query-processing strategies? Explain your answer.
13.15 Suppose that a B+-tree index on (dept_name, building) is available on relation department. What would be the best way to handle the following section
θ (building
13.16 Show how to derive the following equivalences by a sequence of transformations
using the equivalence rules in Section 13.2.1.
a. 1∧2∧3 (E) = 1 (2 (3 (E)))
b. 1∧2 (E1 3 E2) = 1 (E1 3 (2 (E2))), where 2 involves only
attributes from E2