Download Homework 4 with Solutions - Database Systems | CS 411 and more Assignments Deductive Database Systems in PDF only on Docsity!
CS 311: Spring 2004
Homework 4
Solutions (Total 150 points)
Problem 1 (Storage, 30 points)
Consider a disk with a sector size of 512 bytes, 2000 tracks per surface, 50 sectors per track, 5 double-sided platters, average seek time of 10 msecs.
- What is the capacity of a track in bytes? What is the capacity of the disk?
- Assuming that one track of data can be transferred per revolution, and if the disk platter rotates 5,400 rpm (revolution per minute), what is the transfer rate?
Suppose that a block size of 1024 is chosen. Suppose that a file containing 100, records of 100 bytes is to be stored on such a disk and that no record is allowed to span two blocks.
- How many records fit onto a block?
- How many blocks are required to store the entire file?
- What is the time required to read a file containing 100,000 records of 100 bytes each sequentially? Again, how would your answer change if the disk was capable of reading/writing from all heads in parallel (and the data was arranged optimally)?
- What is the time required to read a file containing 100,000 records of 100 bytes each in some random order? Note that in order to read a record, the block containing the record has to be fetched from disk. Assume that each block request incurs the average seek time and rotational delay.
Answer:
- bytes/track = bytes/sector × sectors/track = 512 × 50 = 25K bytes/surface = bytes/track × tracks/surface = 25K × 2000 = 50,000K bytes/disk = bytes/surface × surface/disk = 50,000K × 10 = 500,000K
- The time required for a rotation, which is the maximum rotational delay, is: 1/5400 × 60 = 0.011 seconds. The capacity of a track is 25K bytes. Since one track of data can be transferred per revolution, the data transfer rate is 25K/0. = 2,250Kbytes per sec.
- 1024 / 100 = 10. We can have at most 10 records in a block.
- There are 100,000 records all together, and each block holds 10 records. Thus, we need 10,000 blocks to store the file.
- A file containing 100,000 records of 100 bytes needs 40 cylinders or 400 tracks in this disk. The transfer time of one track of data is 0.011 seconds. Then it takes 400 × 0.011 = 4.4 seconds to transfer 400 tracks. This access seeks the track 40 times. The seek time is 40 × 0.01 = 0.4 seconds. Therefore, total access time is 4.
- 0.4 = 4.8 seconds. If the disk were capable of reading/writing from all heads in parallel, the disk can read 10 tracks at a time. The transfer time is 10 times less, which is 0.44 seconds. Thus total access time is 0.44 + 0.4 = 0.84 seconds. 6. For any block of data, averageaccesstime = seektime + rotationaldelay+transfertime. Seektime=100msec, rotationaldelay=6msec. transfertime= 1K/2,250K/sec = 0.44 msecs. The average access time for a block of data would be 16.44 msecs. For a file containing 100,000 records of 100 bytes, the total access time would be 164.4 seconds.
a) Problem 3 (B+ Tree, 20 points 4 points for each sub part)
Fig. 1
Fig. 1 denotes a B+ tree which stores actual data items in the leaf nodes and has only one pointer per page. Each intermediate node can hold up to five pointers and four key values. Each leaf can hold up to four records. For this tree answer the following questions
a) Name all the nodes that would be fetched to answer the following query: “Get all records with search keys greater than 38.” b) Show the B+ tree that would result from inserting a record with search key 109 into the tree. c) Show the B+ tree that would result from deleting the record with search key 81 from the original tree. d) Name a search key value such that inserting it into the original tree would cause its height to increase. e) Note that sub-trees A, B, C are not fully specified. Nonetheless, what can you infer about the contents and the shape of this tree.
Answer:
a) I1, I2, [L2 … L8].
b)
Fig. 2 c)
d) Any search key within the range [50 … 79] would suffice. The entry into the leaves would cause the leaf to split and would increase the number of leaf nodes. To add extra pointers the internal node I2 would need to split. To accommodate this split the root would have to split increasing the height of the tree. e) The various things that can be inferred are: a. They all must have height 1, because their siblings have height 1. b. Sub-tree A holds keys less than 10, sub-tree B has keys ≥ 10 and < 20, and sub-tree C holds keys ≥ 20 and < 30. c. Each intermediate node must have at least 2 key values and 3 pointers.
Output of the bucket B’ occurs we have saved some I/O in comparison to the standard 2 pass algorithm.
Problem 5 (Query Optimization, 20 points) a. Exercise: 16.4.1 part (h) and (i), page 834. b. Exercise: 16.6.5, page 859.
Answer:
a.
(h) T( σ a = 1 ANDb > 2 ( W )) =
VW a
T W
= 1.67 (5 points)
(i) T( X (^) X. c ∞< Y (^). cY J) = 3
T ( X )* T ( Y )
= 20,000 (5 points)
b. (a) Total number of trees = 7!(T(1)T(6) + T(2)T(5) + T(3)T(4) + T(4)T(3) + T(5)T(2) + T(6)T(1)) = 5040(142 + 114 + 25 + 52 + 141 + 421) = 5040 = 665, No of left-deep trees = No of right-deep trees = 7! = 5040 No of bushy trees = 5040 * 132 – 5040 * 2 = 655,200 (5 points)
(b) Total number of trees = 8!(T(1)T(7) + T(2)T(6) + T(3)T(5) + T(4)T(4) + T(5)T(3) + T(6)T(2) + T(7)T(1)) = 40320(1132 + 142 + 214 + 44 + 142 + 421 + 1321) = 40320*(132 + 42 + 28 + 16 + 28 + 42 + 132) = 40320 * 420 = 16934400 No of left-deep trees = No of right-deep trees = 8! = 40320 No of bushy trees = 40320 * 420 – 40320 * 2 = 16853760 (5 points)
Problem 4 (Transaction Management, 25 points)
(22 points) Consider the following sequence of log records: ; <S,A,60,61>; ; ; <T,A,61,62>; ; <U,B,20,21>; <START CKPT (T,U)>; <T,C,30,31>; ; <U,D,40,41>; <V,F,70,71>; ; ; <T,E,50,51>; ; <V,B,21,22>; .
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) <T,E,50,51> b)
Answer:
a) We find the record on the log. Transaction S is prior to the checkpoint, so S is correctly assume to have both completed and had its changes written do disk. Transaction U is committed, so we have to redo it. However, we do not need to look prior to the <START CKPT (T, U)> record, because we know that U's changes prior to the start of the checkpoint were flushed to disk during the checkpoint. Thus, D is set to 41. Neither transaction T nor transaction V is committed. Therefore, we have to undo them. Thus, B is set to 21, E is set to 50, F is set to 70, C is set to 30, and A is set to 61. Then, we write T and V records on the log.
b) The only difference is that we know that T is committed. Thus, we have to redo it. However, we do not need to look prior to the <START CKPT (T, U)> record, because we know that T's changes prior to the start of the checkpoint were flushed to disk during the checkpoint. We set C to 31, E to 51.
We will consider Redo only solution for partial credit for this problem.
