Homework #4

A database query execution engine has an arsenal of algorithms for processing each individual relational algebra operation. The algorithm selected to process an operation depends on the information available at the time of execution, on the particular instance of the database, and also on the amount of memory available. The database does cost estimations to determine which of the algorithms to use to process the operation.

In this assignment, you will evaluate the cost involved with processing a query using specified algorithms.

Problems

For the problems below, assume you have a 27-page memory buffer and that you are given the following information about two tables, r and s.
Table r occupies 800 pages, 20 rows per page, one of its attributes is A
Table s occupies 200 pages, 10 rows per page, one of its attributes is B

  1. Compute the minimum cost (measured as the number of I/O operations) of a block-nested loops join algorithm to process r JOINA=B s. Specify how the buffer pages are used and explain your reasoning carefully.
  2. Compute the cost of using the Sort-Merge Join algorithm to process the same join as in part a.
  3. Assume there is a clustered, 3-level, B+ tree index on r with search key A and that on average each row of s joins with 5 rows of r. Compute the cost of an index-nested loops join to process r JOINA=B s.
  4. Repeat part (c.) assuming that the index is unclustered.

Notes

Be sure to show all your work. Explain your reasoning carefully. An answer that is just a number gets no credit.

Deliverables

Submit your solutions showing all of your work.

Database Resources