Download CS411 Database Systems Homework 3 and more Assignments Deductive Database Systems in PDF only on Docsity!
CS411 Database Systems
Fall 2007
HW#
Due: 3:00pm CST, 11/14/
Note: Print your name and NetID in the upper right corner of every page of your submission. Handin your stapled homework to Donna Coleman in 2120 SC. In case Donna is not in office, slide your homework under the door.
To grade homeworks faster, the homework is partitioned into two parts. Please, submit each part separately. For each part, make sure to write down your name and NetID.
This homework is partitioned into two parts as follows:
- Part 1: Problem 1 - Problem 4
- Part 2: Problem 5 - Problem 9
There is a bonus credit of 2% if your homework is formatted correctly: Each part must be separable so that it can be independently graded. If you submit a paper copy then each part should i) be separately stapled and ii) include your netid.
Handwritten submissions will be graded but they will take longer to grade. For clarity, machine formatted text is preferable: Expect to lose points if your handwritten answer is unclear or misread by the grader.
Part 1
Problem 1 Buffer Management (12 points; 4 points for each)
Suppose a relation consists of 50 records and are stored in 5 blocks in the disk. Records 1- are stored in the 1st block, records 11-20 are in the 2nd block, records 21-30 are in the 3rd block, records 31-40 are in the 4th block, and 41-50 are in the 5th block. Now we have a job that needs to compare every record with all the other records (for example, a theta join). Assume the job is programmed as the following nested loop:
for i = 1 to 50 do; access record i; for j = 1 to 50 do; access record j; compare records i and j; end end
Suppose the system has a main-memory buffer with size 3 blocks. Assume at the beginning, the buffer is clean (no block has data). Whenever a record is accessed, if its block is not in the buffer, the system will perform a disk read to load the block into the buffer. Now please answer the following questions about different buffering strategies:
(a) If using LRU (Least-Recently Used), how many disk reads are required?
(b) If using MRU (Most-Recently Used), how many disk reads are required? Note that when choosing a block to replace, LRU will replace the least-recently used block, and MRU will replace the most-recently used block.
(c) If using FIFO (First-In-First-Out), how many disk reads are required?
Problem 3 Data Representation(8 points; 4 points for each) , suggested reading:
Chappter 12.
We are designing a database system for a medical application. Each patient record has 10 fields that always occur (e.g., name, patient number) and 40 fields that may or may not occur (e.g., number of children, cholesterol level). Assume that each of the optional fields occurs with probability p. The values for all fields are a fixed size of 10 bytes.
Now, considering two options: (i) A fixed format record; (ii) A variable format record where all fields are tagged. Assume that the tag for each field is totally 1 byte.
(a) What is the expected size of a record for each option? Note that your answer may be a function of p.
(b) For what range of p values is the fixed format option better?
Problem 4 Indexes on Sequential Files(16 points; 4 points for each) , suggested
reading: Chapter 13.1 and 13.
Consider an indexed sequential file consisting of 1,000,000 blocks. Each block contains 10 fixed size records.
Let’s assume that a block is 4KB, a block pointer is 4 bytes, and a key is 5 bytes.
(a) What is the minimum number of blocks a sparse one-level primary sequential index needs? Note that keys cannot be not spanned across blocks.
(b) Now consider to construct a sparse seconnd-level sequential index on the index of (a), how many blocks does it require? Again, use minimum amount of space, and keys are not spanned across blocks.
(c) Next, let’s construct a dense one-level secondary index. Assume a secondary key is 6 bytes long, and a record pointer takes 5 bytes. Then, how many blocks does it require? (no buckets are used; multiple values of a key are replicated in the index; and the [key, pointer] pairs are not spanned across blocks)
(d) Finally, suppose we want to construct a sparse second-level sequential index for the index of (c), how many blocks would it be? (using minimum space and no spanning)
Problem 6 B+ tree (12 points; 3 points for each)
Consider the B+ tree of order d =2 shown in Figure 1, execute the following operations on the tree and show the detailed steps, i.e., how you traverse the tree.
50
8 18 32 40 73 85
1 2 5 6 8 10 18 27 32 39 41 45 52 58 73 80 91 99
Figure 1: B+ tree for Problem 2
a) Look up the record with key 27.
b) Find out if record with key 60 exists.
c) Look up all records in the range from 15 to 60.
d) Look up all records with keys less than 40.
Problem 7 B+ tree (12 points; 3 points for each)
Consider the B+ tree in problem 6, assume that all the blocks are in the disk:
a) Show the tree that would result from inserting a data entry with key 9 into this tree.
b) Show the B+ tree that would result from inserting a data entry with key 3 into the original tree.
c) Show the B+ tree that would result from deleting the data entry with key 8 from the original tree.
d) Show the B+ tree that would result from starting with the original tree, inserting a data entry with key 46 and then deleting the data entry with key 52.
Problem 9 IR(8 points; 4 points for each)
(a) [IDF] Consider three English words ”Iraq war”, ”the” and ”is”. In a collection of docu- ments about news article, which of these three words you expect to have the highest IDF (Inverse Document Frequency)?
(b) [Precision-Recall] Suppose we have a collection of 100 documents with a total of 20 relevant documents. A system has retrieved 50 documents, out of those, 10 are relevant. Compute the precision and recall of the system?
