COMP9315 Final Exam The University of New South Wales
COMP9315 DBMS Implementation
Final Exam
DBMS Implementation
[Instructions] [Notes] [PostgreSQL] [C]
[Q1] [Q2] [Q3] [Q4] [Q5] [Q6] [Q7] [Q8]

Question 5 (5 marks)

Consider two tables R(x,y,z) and S(a,x,b) with bR = 100 and bS = 20, and a natural join on these two tables (via attribute x). If we have a buffer pool with B = 20 buffers, then calculate how many pages would be read/written when performing the join operation using the methods below. Do not include the cost of writing the final result.

  1. Block nested loop join, with R as outer table and S as inner.

  2. Sort merge join, using intermediate files for sorting.
    Do not assume any sort order on the original files.

  3. Grace hash join, with R as outer table and S as inner.
    Use buffers as appropriate for input, output and in-memory hash tables.
    You can assume that all hash functions distribute tuples uniformly
    and that all partitions of R will fit in the in-memory hash table.

Show all working.

Instructions:

End of Question