**Question 3:**

Consider we have three tables A, B, and C, stored on a magnetic hard drive. The following information pertains to the tables:

- Each page is 2000 bytes.
- No tuples can be split between pages.
- Size of each table:
- **A**: 20,000 tuples, 100 bytes per tuple
- **B**: 30,000 tuples, 200 bytes per tuple
- **C**: 60,000 tuples, 300 bytes per tuple

- Table A is stored on one track of the disk.
- Table B is evenly split between two tracks (different from the tracks that store A or C).
- Table C is evenly split between two tracks (different from the tracks that store A or B).
- Data on each track is stored as contiguous pages.
- Assume each seek + rotation takes 50 ms, and each page read/write takes 0.5 ms.
- For every query in this question, we assume the disk head is at neither of the tracks that store a table at the beginning.

**Question 4:**

Using the same setup as Question 3 (for this question, ignore seek time/rotational delay in your calculations). Consider a query that joins 3 tables, A, B, and C with the join condition A.x = B.x and B.x = C.x. Assume the following:

- A.x, B.x, C.x are all integer attributes.
- None of the attributes is the primary key of each table.
- Every value that appears in B.x appears in A.x (but not necessarily vice versa).
- A.x has values between 1-1000 (inclusive), evenly distributed.
- C.x has values between 201-2200 (inclusive), evenly distributed.
- No extra information about the distribution of B.x is available.

Consider the following:

a. Suppose we want to join tables A and C first. What should be the join condition?

b. Assume we use a nested loop join to join tables A and C. Which table should be in the outer loop? Why?

c. Now assume we have 200 buffers available. Further, assume we evenly distribute the buffers between the two tables. Calculate the time needed to execute this query. Show your work.

d. How many tuples will the result have?

e. To simplify the calculation, assume the join result of A and C contains every attribute (including duplicating the join attributes). Now assume we store the result of the join onto the disk. How many pages are needed?

f. Now suppose we have an output buffer of 200 pages (i.e., the join stores the results in the buffers, and writing happens only when the output buffer is full, and all pages will be written to the same track). There will be a dedicated track (different from the tracks that store the tables for the result). Calculate the time needed to write the output.

g. Now suppose we join B with the result from part (e) that is written to the disk, using a nested loop. Once again, we split the 200 buffers evenly between the two tables. Calculate the time for this join.

h. Calculate the overall time for the query. Remember to factor in the time you write the result of the first join to the disk. (But you can assume you are not writing the final result to the disk).