Consider the following database schema:
STAFF(StaffNo, fName, lName, Position, sex, dob, branchNo)
BRANCH(BranchNo, Street, City)
Assume the following for the above database:
- 10000 tuples in Staff; 500 tuples in Branch;
- 500 Managers ( one manager for each branch)
- 10 Paris branches;
- no indexes or sort keys;
- results of any intermediate operations stored on disk;
- cost of the final write is ignored;
- tuples are accessed one at a time.
- Main memory is large enough to process entire relations for each relational algebra operation.
Provide the following:
a) Express the following query in SQL:
" Find all Managers who work at a Paris branch".
b) Choose two possible methods to evaluate the query, and use the relational algebra to formulate clearly all the steps required in the
evaluation.
c) For each method, produce a query tree depicting all the steps.
d) Evaluate which of these two methods is more optimised based on disk accesses. Show your workings.