COMP9315 21T1 Final Exam

The University of New South Wales COMP9315 DBMS Implementation 21T1 Final Exam

DBMS Implementation

Question 6 (7 marks)

Consider the following scenario:

• R is a relation with bR = 40 data pages and no overflow pages
• S is a relation with bS = 20 data pages and no overflow pages
• a buffer pool with N = 25 buffers

Ignoring the cost of writing the final result tuples, calculate minimum cost estimates for the (R ⋈ S) operation for each of following join strategies:

1. block nested loop join, where you can choose either table as the outer relation

2. sort-merge join, where neither table is sorted initially

3. sort-merge join, where R is initially sorted, but S is not

4. sort-merge join, where S is initially sorted, but R is not

5. sort-merge join, where both tables are initially sorted

For each cost, show the minimum number of buffers required to acheive this cost.

Show all working.

Instructions:

• Type your answer to this question into the file called q6.txt
• Submit via:   give cs9315 exam_q6 q6.txt
  or via: Webcms3 > exams > Final Exam > Q6 submission > Make Submission