170 likes | 226 Views
CS4432: Database Systems II. Revision. Question 1.
E N D
CS4432: Database Systems II Revision
Question 1 (a) Build a two-level secondary index on the given file. Use the “Indirection” method to avoid repeating the same key multiple times in the 1st level. Each index block contains either 4 (key, pointer) pairs, or 5 pointers (if no key is stored in the entry). (b) Given the following query “Search for Key 40”. How many I/Os (disk blocks) will be read to answer the query (consider the index blocks + the data blocks)? 7 Blocks
13 20 24 30 2* 3* 16* 24* 27* 28* 40* 41* 45* 77* 19* 20* 22* Question 2 (a) Delete Key 14 • Borrow from one of the siblings and adjust the parent’s values
13 20 24 30 2* 3* 16* 24* 27* 28* 40* 41* 45* 77* 19* 20* 22* Question 2 (b) Insert Key 90 24 45 13 20 30 90* 2* 3* 16* 24* 27* 28* 40* 41* 45* 77* 19* 20* 22* Split a leaf node (middle key copied up) Split a non-leaf node (middle key moved up)
Question 2 (c) Delete Key 19 24 24 45 20 30 45 13 20 30 Step 1 90* 2* 3* 16* 24* 27* 28* 40* 41* 45* 77* 90* 2* 3* 20* 16* 24* 27* 28* 40* 41* 45* 77* 22* 19* 20* 22* Merge the leaf node with its left child ( you may merge with the right) Delete key from parent
Question 2 (c) Delete Key 19 24 24 30 45 45 20 20 30 Step 2 Step 1 90* 90* 2* 3* 2* 3* 16* 24* 27* 28* 40* 41* 45* 77* 16* 24* 27* 28* 40* 41* 45* 77* 20* 20* 22* 22* Merge Non-leaf nodes [20, 24, 30, 45]
Question 3 • Assume a disk block of 4k bytes = 4096 • Assume indexing date keys (10 bytes each) & each pointer is 8 bytes How many record can be indexed in a 3-level B-Tree index (root + 2 levels). Compute the minimum & maximum. • Each node has N keys & N+1 pointers • 10N + 8(N+1) = 4096 N = 227 • A 3-Level B-Tree (root + 1st and 2nd levels): Max • First level (root) 1 Node [Max=227 keys, 228 pointers] • 2nd level 228 Nodes [each one has max 228 pointers] • 3rd level 2282 Leaf nodes [each has max 227 keys] • So Max indexed records is = 2282 x 227 records
Question 3 (Cont’d) • Assume a disk block of 4k bytes = 4096 • Assume indexing date keys (10 bytes each) & each pointer is 8 bytes How many record can be indexed in a 3-level B-Tree index (root + 2 levels). Compute the minimum & maximum. • Each node has N keys & N+1 pointers • 10N + 8(N+1) = 4096 N = 227 • A 3-Level B-Tree (root + 1st and 2nd levels): Min • First level (root) 1 Node [Min=1 key, 2 pointers] • 2nd level 2 Nodes [each one has min 113 key, and 114 pointers] • 3rd level 2x 114 Leaf nodes [each has min 113 keys] • So Min indexed records is = 228 x 113 records What is the average ?
Question 4 • Starting with the following hash table, insert the following keys: • 11, 17, 36 • Each Bucket can hold at most 4 keys
Question 4 Inserting 11 [1011] 11
Question 4 Inserting 17 [10001] Bucket B 3 0 B1 0 3 X 0 1, 17 0 100 B2 3 101 5, 13, 21 110 111 11
Question 4 Inserting 36 [100100] Bucket A A1 3 X 3 32, 16 0 B1 0 3 X 0 1, 17 0 100 B2 3 101 5, 13, 21 110 111 A2 3 11 4, 12, 36
Also Revise • Disk Storage and Management • External Sorting
External Sort Phase 1 : Prepare INPUT 1 . . . INPUT 2 . . . INPUT B Disk Disk B Main memory buffers • Construct as large as possible starter lists. • Will reduce the number of needed passes
External Sort Phase 2 : Merge INPUT 1 • Merge as many sorted sublists into one long sorted list. • Keep 1 buffer for the output • Use B-1 buffers to read from B-1 lists . . . INPUT 2 . . . OUTPUT INPUT B-1 Disk Disk B Main memory buffers
General External Merge Sort • To sort a file with N pages using B buffer pages: • Pass 0: use B buffer pages. Produce sorted runs of B pages each. • Pass 1, 2, …, etc.: merge B-1 runs. INPUT 1 . . . . . . INPUT 2 . . . OUTPUT INPUT B-1 Disk Disk B Main memory buffers
Cost of External Merge Sort • Number of passes: • Cost = 2N * (# of passes) Assume we have 10 memory buffers, what is the maximum number of data blocks that we can sort using 4 passes? Assume we have 10 memory buffers, and a file consisting of 1000 blocks, how many passes are needed?