Download Database Systems Homework 3 - Fall 2004 and more Assignments Deductive Database Systems in PDF only on Docsity!
CS411 Database Systems
Fall 2004
HW#
Due: 11:30am CST, 11/12/
Note: Print your name and NetID in the upper right corner of every page of your submission.
Problem 1 Merge-Sort
Suppose we have a relation with 16665 tuples and each tuple requires 15 bytes. We have a machine whose main memory and disk-block size (100 bytes) are sufficient to sort the 16665 tuples using TPMMS (Two-Phase, Multiway Merge-Sort). What is the minimum size of the main memory?
Problem 2 Pointer Swizzling
Exercise 12.3.11 (page 589)
Problem 3 Data Representation
Exercise 12.4.3 (page 596)
Problem 4 Indexes on Sequential Files
(a) Exercise 13.1.2 (page 620)
(b) Exercise 13.1.3 (page 620)
Problem 5 B+tree
Execute the following operations on Fig. 13.23 (page 635). Show the detailed steps, using Example 13.23 and Example 13.24 as examples.
(a) Lookup the record with key 19.
(b) Lookup the record with key 18.
(c) Lookup all records in the range 35 to 45.
(d) Lookup all records with keys less than 35.
(e) Lookup all records with keys greater than 35.
Problem 6 B+tree
Consider constructing and updating a B+tree of order 3 (i.e., n = 3).
(a) The following is an incomplete B+tree for indexing {10, 20, 30, 40, 50, 60}. Complete the missing keys and pointers.
10 20 30 40 50 60
(b) Insert 25 then 18 to the tree. Show the resulting tree.
(c) Insert 28 then 70 to the tree. Show the resulting tree.
(d) Our tree now has indexed key values {10, 20, 30, 40, 50, 60, 25, 18, 28, 70}. The following tree, called tree Tall, also index the same data values. Is tree Tall a legal B+tree of order 3 (i.e., so that it does not violate any B+tree utilization rules)?
- If yes: show a sequence of insertions (starting from an empty tree) of these values so that it will result in this tree.
- If no: explain why not.
10 18 20 25 28 30 40 50
20 28 60
40
60 70
Another tree. Let’s call it Tree Tall
(e) Let’s pretend that Tree T all is legal, regardless of what your answer is for (d). Now, delete 40 from Tree T all. Show the resulting tree.
Problem 7 Extensible Hash Table
Consider indexing the following key values using an extensible hash table. Suppose that we insert the keys in the order of
44 , 36 , 30 , 56 , 34 , 62 , 50 , 22.
The hash function h(n) for key n is h(n) = n mod 16; i.e., the hash function is the remainder after the key value is divided by 16. Thus, the hash value is a 4-bit value. Assume that each bucket can hold 2 data items.
(a) Draw the hash index (both the directory and the buckets), after the first four keys are inserted. Show the keys themselves in the buckets, as well as the hash values. Be sure to indicate the number of bits in the hash value that are used (in the directory as well as each bucket).
(b) Draw the hash index after all the keys are inserted.
