### CS554, Homework 4

• Question 1

• Exercise 14.1.1 (a):

 Suppose blocks hold either three records, or ten key-pointer pairs. As a function of n (= the number of records), how many blocks do we need to store a data file using a dense index

• For the data, we will need

 ``` n --- blocks (n records, 3 records in 1 block) 3 ```

For dense index we need one key-pointer pair for each record. So we will need:

 ``` n --- blocks (n key-pointer indices, 10 indices in 1 block) 10 ```

to store the index.

So the total number of blocks is:

 ``` n n 13n --- + --- = ---- blocks 10 3 30 ```

• Exercise 14.1.1 (b):

 Suppose blocks hold either three records, or ten key-pointer pairs. As a function of n (= the number of records), how many blocks do we need to store a data file using a sparce index

• For the data, we will need

 ``` n --- blocks (n records, 3 records in 1 block) 3 ```

For dense index we need one key-pointer pair for each block. So we will need:

 ``` n/3 n ------ = --- blocks (n/3 indices, 10 indices in 1 block) 10 30 ```

to store the index.

So the total number of blocks is:

 ``` n n 33n 11n --- + --- = ---- = ---- blocks 30 3 90 30 ```

• Question 2

• Exercise 14.2.5: Execute the following operations on Fig. 14.13:

Questions:

• Question 3

 ``` /* ======================================================== Delete( x, rx ) from a node N in B+-tree ======================================================== */ Delete( x, rx, N ) { Delete x, rx from node N; /* ================================================== Take care of the special case: N = root ================================================== */ if ( N == root ) { /* ================================================================= The root node does not need be have ≥ ⌊(n+1)/2⌋ pointers !!! ================================================================= */ if ( N has ≥ 2 pointers ) { return; } else { // Root node is empty ===> make left most child of root the new root root = root.leftMostPointer; return; } } // ALternative solution (shorter - but less clear) // // if ( N == root ) // { // if ( N is empty (or has 0 keys) ) // { // root = root.leftMostPointer; // make leftMost child the new root // // return; // } // } // Continue here to handle non-root internal node deletion..... /* ==================================== Check for underflow condition... ==================================== */ if ( N has ≥ ⌊(n+1)/2⌋ pointers /* At least half full*/ ) { return; // Done } else { /* --------------------------------------------------------- N underflowed: fix the size of N with transfer or merge --------------------------------------------------------- */ /* ======================================================== Always try transfer first !!! (Merge is only possible if 2 nodes are half filled) ======================================================== */ if ( leftSibling(N) has ≥ ⌊(n+1)/2⌋ + 1 pointers ) { 1. transfer last key from leftSibling(N) through parent into N as the first key; 2. transfer right subtree link into N as the first link } else if ( rightSibling(N) has ≥ ⌊(n+1)/2⌋ + 1 pointers ) { 1. transfer first key from rightSibling(N) through parent into N as the last key; 2. transfer left subtree link into N as the last link } /* ========================================================== Here: can't solve underflow with a transfer Because: BOTH sibling nodes have minimum # keys/pointers (= half filled !!!) Solution: merge the 2 half filled nodes into 1 node ========================================================== */ else if ( leftSibling(N) exists ) { /* =============================================== merge N with left sibling node =============================================== */ 1. Merge (1) leftSibling(N) + (2) key in parent node + (3) N into the leftSibling(N) node; 2. Delete ( transfered key, right subtree ptr, parent(N) ); // Recurse !! } else // Node N must have a right sibling node !!! { /* =============================================== merge N with right sibling node =============================================== */ 1. Merge (1) N + (2) key in parent node + (3) rightSibling(N) into the node N; 2. Delete ( transfered key, right subtree ptr, parent(N) ); // Recurse !! } } ```

• Question 4

• Exercise 14.5.5:

• Suppose we store a relation R (x,y) in a grid file.
• Both attributes have a range of values from 0 to 1000.
• The partitions of this grid file happen to be uniformly spaced:

 for x there are partitions every 20 units, at 20, 40, 60, and so on, for y the partitions are every 50 units, at 50, 100, 150, and so on.

Questions:

• How many buckets do we have to examine to answer the range query

 ``` SELECT * FROM R WHERE 310 < x AND x < 400 AND 520 < y AND y < 730; ```

 ``` x: [300-320] [320-340] [340-360] [360-380] [380=400] y: [500-550] [550-600] [600-650] [650-700] [700-750] # byckets: 5 × 5 = 25 ```

• We wish to perform a nearest-neighbor query for the point (110,205) (i.e., find the closest element to the point (110,205))

We begin by searching the bucket with lower-left corner at (100,200) and upper-right corner at (120,250).

We indicate this bucket as:

 ``` [100-120][200-250] ```

We find that the closest point in this bucket is (115,220).

What other buckets must be searched to verify that this point is the closest?