Buffering:

Perform a nested loops join with 10 pages as the size of memory buffer
available.  Suppose that one relation is 9 pages big and the other is 100
pages big.  Compute the number of pages of read I/O required using an LRU
buffer replacement policy and compare with the cost of read I/O using an
optimal buffer replacement policy.  (Ignore all output costs, and in
particular do not assign any buffers to output).

Suppose you have 20 pages available in the buffer pool to perform a nested
loops join of two relations, one 24 pages big and the other 140 pages big.
The output relation is expected to be about 280 pages.
How would you allocate buffers for this purpose?  Decide based on a
comparison of at least the following alternatives. (Ignore double buffering,
but do take output buffers into account).
(You will load the smaller relation in memory in two parts, and for each part
make one full pass over the larger relation).
(a) Balanced (Load 12 pages of smaller relation in each pass) versus Greedy
(Load 18 pages in first pass and 6 in second pass).
(b) When there are extra buffers, allocate them equally to the input and
output versus allocate them all to input or all to output.  (Use blocked I/O,
and assume that a sequential page access costs one third of a random access.
Assume also that you have exactly one disk arm.)

What is a clustered sequential reference pattern, as defined by Chou and
DeWitt?  What is an access method where it may be observed?
How about a looping hierarchical reference pattern?