Consider the query A,B,C,D (R A=CS). Suppose that the projection routine is based on sorting and is

Question:

Consider the query πA,B,C,D (R ⪻⪼A=CS). Suppose that the projection routine is based on sorting and is smart enough to eliminate all but the desired attributes during the initial pass of the sort and also to toss out duplicate tuples on the fly while sorting, thus eliminating two potential extra passes. Finally, assume that you know the following:
R is 10 pages long, and R tuples are 300 bytes long.
S is 100 pages long, and S tuples are 500 bytes long.
C is a key for S, and A is a key for R.
The page size is 1024 bytes.
Each S tuple joins with exactly one R tuple.
The combined size of attributes A, B, C, and D is 450 bytes.
A and B are in R and have a combined size of 200 bytes; C and D are in S.
1. What is the cost of writing out the final result? (As usual, you should ignore this cost in answering subsequent questions.)
2. Suppose that three buffer pages are available, and the only join method that is implemented is simple (page-oriented) nested loops.
(a) Compute the cost of doing the projection followed by the join.
(b) Compute the cost of doing the join followed by the projection.
(c) Compute the cost of doing the join first and then the projection on-the-fly.
(d) Would your answers change if 11 buffer pages were available?