Quiz 2

Question 1

each page in table R will be read in once, this results in 8 page reads.
each page in table S will be read in once for every page in table R, this results in 5 x 8 = 40 page reads
the total number of page reads is 8 + 40 = 48

you have 6 buffers total, but 1 is locked for output, leaving 5 buffers available for reading pages
Table S is exactly 3 pages long. To do a nested loop join, you need 1 buffer to hold the current page of R, and you need to load the pages of S. Because you have 5 buffers available, all 3 pages of S can fit into the buffer pool simultaneously alongside the current page of R
On the first pass of the outer loop, the system reads page 1 of R (1 read) and pages 1, 2, and 3 of S (3 reads)
For the rest of the query, Table S never leaves the buffer pool! The MRU strategy (replacing the most recently unpinned page) will constantly swap out the old page of R for the new page of R, leaving the pages of S safely pinned in memory.
The system reads Table R exactly once (8 reads) and Table S exactly once (3 reads). 8+3=11.

Let S equal the number of slots, and let N equal the number of 32-bit words used for the presence vector.
A tuple takes exactly 8 bytes. A 32-bit word takes 4 bytes. The total size of the tuples plus the presence vector cannot exceed the 1024-byte page size. \(8S + 4N \leq 1024\)
The presence vector must have at least enough bits to cover the number of slots. \(S \leq 32N\)
If N = 3, then S = 32x3 = 96. 8x96+4x3 = 780
If N = 4, then S = 32x4 = 128. 8x128+4x4 = 1040. (1024-4x4)/8 = 126.