1 / 17

CS4432: Database Systems II

CS4432: Database Systems II. Revision. Question 1.

Download Presentation

CS4432: Database Systems II

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. CS4432: Database Systems II Revision

  2. 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

  3. 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

  4. 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)

  5. 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

  6. 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]

  7. 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

  8. 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 ?

  9. Question 4 • Starting with the following hash table, insert the following keys: • 11, 17, 36 • Each Bucket can hold at most 4 keys

  10. Question 4 Inserting 11  [1011] 11

  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

  12. 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

  13. Also Revise • Disk Storage and Management • External Sorting

  14. 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

  15. 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

  16. 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

  17. 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?

More Related