Consider the join R S where the join predicate is R.a = S.b, given the following metadata about R and S:

Relation R contains 20,000 tuples and has 10 tuples per block

Relation S contains 5,000 tuples and has 10 tuples per block

Attribute b of relation S is the primary key for S, and every tuple in S matches 3 tuples in R

There exists a unclustered (secondary) index on R.a with height 3

There exists a clustered (primary) index on S.b with height 2

The main memory buffer can hold 5 blocks (B=5)

Answer the following questions:

a. If R S is evaluated with a block nested loop join, which relation should be the outer relation? Justify your answer. What is the cost of the join in number of I/Os?

b. If R S is evaluated with an index nested loop join, what will be the cost of the join in number of I/Os? Show your cost analysis.

c. What is the cost of a plan that evaluates this query using sort-merge join. Show the details of your cost analysis.

d. Evaluate the cost of computing the R S using hash join assuming: i) The main memory buffer can hold 202 blocks, ii) The main memory buffer can hold 11 blocks