Download Homework #4 Problems - Database Systems | CS 411 and more Assignments Deductive Database Systems in PDF only on Docsity!
Homework 4
Administrivia
Out: 11 st^ April, 2006. Due date: At the beginning of the lecture on 27 th^ April, 2006. Please submit a hard copy of your homework. Bring it down to the lecture table (the one with the PC that I use to display the slides).
The hard copy should be as clearly readable as possible. You may be subtracted points for unreadability and ugly presentation.
This homework will be counted as 1.5 homeworks. Thus, the total is 150 points.
Off-campus students: You should e-mail your solutions to Yoonkyong Lee in the pdf format or in the Word document. Send the file as attachment with your email by 2 PM UIUC time (CST). Off-campus students in other time zones should note that the deadline is according to CST.
Please note: Late homework will not be accepted, barring exceptional circumstances.
Storage
Problem 1. (20 points) Consider the Megatron 757 disk with the following properties:
- There are four platters providing eight surfaces.
- There are 2^13 = 8192, tracks per surface.
- There are (on average) 2^8 = 256 sectors per track.
- There are 2^9 = 512 bytes per sector.
- The disk rotates at 5400 RPM.
- The block size is 2^12 = 4096 bytes
- Assume 10% of each track is used as overhead.
- The time it takes the head to move n tracks is (1 + n /500) milliseconds.
Suppose that we know that the last I/O request accessed track 2000.
a. What is the expected (average) number of tracks that will be traveled due to the very next I/O request for a block on a random track to this disk? b. What is the expected (average) block access time, including average seek time, average rotational latency and transfer time, for the next I/O for a block on a random track, again given that the head is on track 2000 initially?
Indexing
Problem 2. (20 points) Suppose each block holds either 20 records, or 50 key-pointer pairs. If a data file has 100,000 records, how many blocks do we need to hold this data file and:
(a) A dense index? (b) A sparse index?
B+ Tree
Problem 3. (20 points) Consider the B+ tree index of degree 2 shown in Figure 1. For each problem below, you only need to show the final resulting B+ Tree, if the answer is a B+ Tree. (a) Name all the nodes that would be fetched to answer the following query: “Get all records with search keys greater than 17.” (b) Show the B+ tree that would result from inserting a record with search key 47 into the tree. (c) Show the B+ tree that would result from deleting the record with search key 17 from the original tree. (d) Name a search key value such that inserting it into the original tree would cause an increase in the height of the original tree.
Figure 1
(a) (10 points) Analyze the above two plans carefully in terms of their behavior regarding accesses to disk, and explain which of the plans is better. Be sure to include in your analysis which accesses to disk are sequential accesses and which ones are random accesses.
(b) (5 points) Can you think of an alternative plan that is better than the above two in terms of disk access? You do not need to submit detail computations for this question. Briefly explain your assumptions and your alternative plan and argue why your plan could be better.
Query Optimization
Problem 5. (20 points) Consider three relations R(A,B), S(B,C), T(C,D) with the following statistics:
- T(R) = 200, V(A, R) = 50, V(B, R) = 100
- T(S) = 300, V(B, S) = 50, V(C, S) = 50
- T(T) = 400, V(C, T) = 40, V(D, T) = 100
T(R) represents the number of tuples of R, and V(A, R) represents the number of distinct values of attribute A in relation R. Estimate the sizes of relations that are the results of the following expressions: (a) select{B = 20}(S) join T_ (b) select{R.A < S.C}(R join S)_
Problem 6. (20 points) Exercise 16.6.2, page 858
Transaction Management
Problem 7. (15 points) Assume that initially A = 5 and B = 10. For the following transaction T, A := B * 2; B := A * 2;
(a) Add the read- and write-actions to the computation and show the effect of the steps on memory and disk. (b) Show the undo-log records for T. (c) Show the redo-log records for T.
Problem 8. (20 points) Consider the following sequence of log records:
.
Describe the action of the recovery manager, including changes to both disk and the log, if there is a crash and the last log record to appear on disk is:
(a) (b)
