Database Systems
Instructor: Hao-Hua Chu Fall Semester, 2005 Assignment 5: Hash-based Indexing & Query Evaluation Overview
Deadline: At the end of the class, Dec 19 (Monday), 2005
This is an individual assignment, that is, no group submissions are allowed. Cheating Policy: If you are caught cheating, your grade is 0.
Late Policy: You may hand in your late assignment at TA’s office hour on Tue (12/20/2005) for 80% of original grade, or at TA’s office hour on Wed (12/21/2005) for 70%. We will not accept any assignment submissions after Wed.
Questions
1. Suppose that we are building an extendible hashing index. At the beginning, directory is array of size 2 as shown below. A bucket can hold 3 records.
a. Show the extendible hash structure after inserting the following search-key values:
2, 3, 5, 7, 11, 17, 19, 23, 29, 31
b. Show how the extendible hash structure of (a.) changes after the following steps:
Delete 31, Delete 7, Insert 12, Delete 23
2. What are the advantages and disadvantages of hash index relative to B+-tree index? How might the type of index available influence the choice of a query processing strategy?
3. Suppose that a B+-tree index on branch-city is available on relation branch, and that no other index is available. List different ways to handle the following selections that involve negation:
a. σ¬ (branch-city<“Brooklyn”)(branch) b. σ¬ (branch-city=“Brooklyn”)(branch)
c. σ¬ (branch-city<“Brooklyn” OR assets<5000)(branch)
4. Let relations r1(A,B,C) and r2(C,D,E) have the following properties: r1 has 20,000 tuples, r2 has 45,000 tuples, 25 tuples of r1 fit on one page, and 30 tuples of r2 fit on one page. The buffer size is 101 pages.
Estimate the LEAST number of page accesses required and explain in detail how you obtain it, using each of the following join strategies:
a. Index Nested Loop
(We have both unclustered hash index <C> in r1 and <C> in r2. NO overflow occurs.) b. Sort-Merge
(r1 and r2 are NOT initially sorted on the join key.)
Submission
Hand in PAPER PRINTOUT that contains your answers to the three questions. Please include your name and ID.
