- 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
Answer:
-
For the data, we will need
n --- blocks (n records, 3 records in 1 block) 3For dense index we need one key-pointer pair for each block. So we will need:
n --- blocks (n key-pointer indices, 10 indices in 1 block) 10to store the index.
So the total number of blocks is:
n n 13n --- + --- = ---- blocks 10 3 30
- Suppose blocks hold either three records, or ten key-pointer
pairs.
- 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
Answer:
-
For the data, we will need
n --- blocks (n records, 3 records in 1 block) 3For 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 30to store the index.
So the total number of blocks is:
n n 33n 11n --- + --- = ---- = ---- blocks 30 3 90 30
- Suppose blocks hold either three records, or ten key-pointer
pairs.
- Exercise 14.2.5: Execute the following operations on Fig. 14.13:
Questions:
- Show the pointers followed by the operation to
lookup the record with key 41: (don't forget to include the
data record pointers
-- i.e., the bottom arrows)
Explanation: Start at the root. 41 ≥ 13 so follow the second pointer. 31 ≤ 41 < 43 so follow the third pointer. We find one of the keys is 41 so we follow the third pointer to the data block.
- Show the pointers followed by the operation to
lookup all records in the range 20 to 30:
(don't forget to include the
data record pointers
-- i.e., the bottom arrows)
Explanation: Start at the root. 20 ≥ 13 so follow the second pointer. 20 < 23 so follow the first pointer. No keys in the range are found so follow the next leaf pointer. 23 is in the range, so follow the first pointer to the data block. Key 29 is in the range so follow second pointer to the data block. No more keys on the leaf so follow next leaf pointer. Key 31 is not in the range so we are done.
- Initial B-tree:
Show the B-tree after we insert a record with key 2500:
Explanation:
- Inserting 2500 causes the node to split into 2 nodes: (1000, 1500) and (2000, 2500). The key 2000 and the link to right child node (2000, 2500) is inserted into the parent node.
- Inserting 2000 causes the parent node to split into 2 nodes: (2000, 3000) and (7000). The key 5000 and the link to right child node (7000) is inserted into the parent node.
- Because there is no parent node, we create a root node with (5000)
- Show the pointers followed by the operation to
lookup the record with key 41: (don't forget to include the
data record pointers
-- i.e., the bottom arrows)
- Initial B-tree:
Show the B-tree after we delete the record with key 7000:
Explanation:
- Deleting 7000 will cause underflow; node only has a
right sibling with minimum number of keys ---
we must merge the empty node with (8000) into a
new left child node: (8000)
The merge-operation will delete the key 8000 and the right child link from the parent node.
- The key 8000 and the right child
link can be readily deleted from the parent node;
the new parent node is: (9000)
The deletion operation stops (the root node will remain (7000) !!!)
- Deleting 7000 will cause underflow; node only has a
right sibling with minimum number of keys ---
we must merge the empty node with (8000) into a
new left child node: (8000)
- Initial B-tree:
Show the B-tree after we delete the record with key 5500:
Explanation:
- Deleting 7000 will cause underflow; in this case: the node has a
right sibling that can donate a key ---
we must use transfer to resolve underflow
We transfer the key 6000 from the sibling node into the underflow node.
We also update the search key in the parent node (to 6500)
The algorithm stops --- the algorithm will not update the key in the root node !!!
- Deleting 7000 will cause underflow; in this case: the node has a
right sibling that can donate a key ---
we must use transfer to resolve underflow
- Initial B-tree:
Show the B-tree after we delete the record with key 6000:
Explanation:
- Deleting 6000 will cause underflow; the node has
no sibling nodes that can donate a key ---
we must use merge to resolve underflow
We merge the empty node and its left sibling node into the left sibling node obtaining the new left node: (1000)
We must delete the search key (6000) and its right subtree link in the parent node
- Deleting the key 6000 in the parent node
will cause underflow; this node has
no sibling nodes that can donate a key ---
we must again use merge to resolve underflow
We the empty (parent) node and its right sibling node (9000) and the key 8000 in its parent node into itself obtaining the new left node: (8000, 9000)
We must delete the search key 8000 and its right subtree link in the parent node
- Deleting the key 8000 in the parent node
will cause underflow;
since this node is the root node and it is now empty,
we delete it
The algorithm stops
- Deleting 6000 will cause underflow; the node has
no sibling nodes that can donate a key ---
we must use merge to resolve underflow
|
|
/* ========================================================
Delete( x, rx ) from a node N in B+-tree
======================================================== */
Delete( x, rx, N )
{
Delete x, rx from node N;
/* ==================================================
ANSWER:
We add code here to 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 !!
}
}
|
- 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;Answer:
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?
Answer:
The buckets searched are:
- [80-100] [150][200]
- [100-120] [150][200]
- [120-140]
[150][200]
- [80-100] [200][250]
- [120-140] [200][250]