CS336 Principles of Database Management Systems
Problem Set 3

Due: April 18, 2002

1.

The following notation describes a node of a B+ tree:

Each node appears between angle-brackets: <node-num:type [key0,key1,...] [ptr0,ptr1,...]>.
node-num is an arbitrary, unique number that identifies each node.
type is R for a root node, I for an interior node, and L for a leaf node.
The pointers in root and interior nodes point to other nodes (that is the value of the point is the node-num of another node).
The pointers in the leaf nodes point to disk blocks, except for the last pointer in the list, which points to the next leaf node in the chain. A pointer of -1 is a null pointer.

For the following B+ tree of degree=4 (reminder - last pointer in leaf nodes is the chaining pointer):

<000:R[10,20][1,2,3]>
<001:L[5][305,2]>
<002:L[15,16,19][315,316,319,3]>
<003:L[25,30][325,330,-1]>

Perform each of the following insert (key,value) operations, and write out the resultant tree after each. Please assign new node ID numbers sequentially so that I can see the order of creation in the final result:

(a): Insert (18,318)
(b): Insert (40,340)

2.

For the following B+ tree of degree=4:

<000:R[10][1,2]>
<001:I[6][3,4]>
<002:I[15,30][5,6,7]>
<003:L[3][303,4]>
<004:L[8][308,5]>
<005:L[12][312,6]>
<006:L[20][320,7]>
<007:L[40,42][340,342,-1]>

Perform each of the following delete operations, and write out the resultant tree after each:

(a): Delete 20
(b): Delete 8

3.

Which of the following are valid B+ tree indices of order n=4 (as defined in the text and in lecture)? For any tree that is not valid, explain the error(s). (You will not receive full credit without an explanation!)

(a)

          <0:R [99] [1,2]>
          <1:L [97,98] [197,198,2]>
          <2:L [111,115,119] [61,65,69,-1]>

(b)

          <0:R [12,31,39] [1,2,3,4]>
          <1:L [1,2,3] [101,102,2]>
          <2:L [13,17,15] [113,115,121,3]>
          <3:L [31,34,35] [131,134,135,4]>
          <4:L [40] [140,-1]>

(c)

          <0:R [42,97,121] [1,2,3,4]>
          <1:I [12,31,39] [5,6,7,8]>
          <2:I [47,58] [9,10,11]>
          <3:I [99] [12,13]>
          <4:I [130,190] [14,15,16]>
          <5:L [1,2,3] [101,102,103,6]>
          <6:L [13,15,21] [113,115,121,7]>
          <7:L [31,34,35] [131,134,135,8]>
          <8:L [40] [140,9]>
          <9:L [43,45,46] [143,145,146,10]>
          <10:L [47,57] [147,157,11]>
          <11:L [60,70,71] [160,170,171,12]>
          <12:L [97,98] [197,198,13]>
          <13:L [111,115,119] [61,65,69,14]>
          <14:L [124] [74,15]>
          <15:L [142,144,160] [92,344,360,16]>
          <16:L [275] [344,-1]>

4.

For the set of functional dependencies on R(ABCD):

$F:\{A \rightarrow B, AC \rightarrow D\}$

Which of the following are in the closure of F, F⁺?

(a): $A \rightarrow D$
(b): $BC \rightarrow B$
(c): $BC \rightarrow D$
(d): $AD \rightarrow AC$
(e): $AC \rightarrow BDA$
(f): $A \rightarrow AD$
(g): $ABCD \rightarrow BC$
(h): $B \rightarrow A$

5.

For the set of functional dependencies on R(ABCD):

$F:\{A \rightarrow B, B \rightarrow C, AC \rightarrow D, A \rightarrow C\}$

Give a set of dependencies that constitutes a canonical (minimal) cover, F_c.

6.

For the set of functional dependencies on R(ABCD):

$F:\{A \rightarrow B, BC \rightarrow D, D \rightarrow A, C \rightarrow B\}$

List all of the candidate keys of this relation.

7.

For each of the following relations and dependency sets, state whether the relation conforms to second normal form (2NF) and/or third normal form (3NF) and why it does or does not. You may assume that all relations already conform to first normal form (1NF). The primary key of the relation is underlined.

(a)