8/2/2019 b Tree Example

1/18

Rasmus Ejlers Mgelberg

Insertions and deletions

in B+ treesIntroduction to Database Design 2011, Lecture 11Supplement to lecture slides

8/2/2019 b Tree Example

2/18

Rasmus Ejlers Mgelberg

Initial setup

We consider the B+ tree below

2

rickjudy

tompetemikejanebob

eddiebob joejane karenjudy philpetenanmike solrick tomalabe

8/2/2019 b Tree Example

3/18

Rasmus Ejlers Mgelberg

Observations

Observe that the tree has fan out 3

Invariants to be preserved- Leafs must contain between 1 and 2 values- Internal nodes must contain between 2 and 3 pointers

- Root must have between 2 and 3 pointers- Tree must be balanced, i.e., all paths from root to a leaf

must be of same length

3

8/2/2019 b Tree Example

4/18

Rasmus Ejlers Mgelberg

Inserting Vince

4

rickjudy

tompetemikejanebob

eddiebob joejane karenjudy philpetenanmike solrick vincetomalabe

8/2/2019 b Tree Example

5/18

Rasmus Ejlers Mgelberg

Inserting Vera

Leaf consisting of tom and vince is split and extrapointer is inserted in parent

5

rickjudy

vincetompetemikejanebob

eddiebob joejane karenjudy philpetenanmike solrick veratomalabe vince

8/2/2019 b Tree Example

6/18

Rasmus Ejlers Mgelberg

Inserting rob

Inserting rob is more difficult. We first create anew leaf node and insert it as below

The node above is temporarily extended tocontain 4 pointers

6

rickjudy

tomsolpetemikejanebob

eddiebob joejane karenjudy philpetenanmike robrick veratomalabe vince

vince

sol

8/2/2019 b Tree Example

7/18

Rasmus Ejlers Mgelberg

Inserting rob

The overfull internal node is then split in 2

The new pointer is inserted into the root nodewhich then becomes overfull

7

rickjudy

solpetemikejanebob

eddiebob joejane karenjudy philpetenanmike robrick veratomalabe vincesol

vince

tom

8/2/2019 b Tree Example

8/18

Rasmus Ejlers Mgelberg

Inserting rob

Finally the overfull root is split in 2

At this point the tree satisfies the requirements ofslide 3 and so the insertion procedure ends

8

judy

solpetemikejanebob

eddiebob joejane karenjudy philpetenanmike robrick veratomalabe vincesol

vince

rick

tom

8/2/2019 b Tree Example

9/18

Rasmus Ejlers Mgelberg

Deleting jane

Is straight forward

Note that the node above the leaf where jane wasdeleted must also be updated

9

judy

solpetemikejoebob

eddiebob joe karenjudy philpetenanmike robrick veratomalabe vincesol

vince

rick

tom

8/2/2019 b Tree Example

10/18

Rasmus Ejlers Mgelberg

Deleting bob

10

judy

solpetemikejoeeddie

eddie joe karenjudy philpetenanmike robrick veratomalabe vincesol

vince

rick

tom

8/2/2019 b Tree Example

11/18

Rasmus Ejlers Mgelberg

Deleting joe

Leads to a leaf being deleted and the parent beingupdated

11

judy

solpetemikeeddie

eddie karenjudy philpetenanmike robrick veratomalabe vincesol

vince

rick

tom

8/2/2019 b Tree Example

12/18

Rasmus Ejlers Mgelberg

Deleting eddie

Leads to deletion of a leaf

At this point the parent becomes underfull

12

judy

solpetemike

karenjudy philpetenanmike robrick veratomalabe vincesol

vince

rick

tom

8/2/2019 b Tree Example

13/18

Rasmus Ejlers Mgelberg

Deleting eddie

When a node becomes underful the algorithm willtry to redistribute some pointers from aneighbouring sibling to it.

Since this is possible in this case we do it

13

mike

solpetejudy

karenjudy philpetenanmike robrick veratomalabe vincesol

vince

rick

tom

8/2/2019 b Tree Example

14/18

Rasmus Ejlers Mgelberg

Deleting abe and al

Leads to deletion of a leaf

This makes the parent underfull. We cannot redistribute pointers again since this

will make the neighbour underfull

14

mike

solpete

karenjudy philpetenanmike robrick veratom vincesol

vince

rick

tom

8/2/2019 b Tree Example

15/18

Rasmus Ejlers Mgelberg

Deleting abe and al

Instead we must merge with the neighbouring

sibling

But this makes the parent underfull

15

solpetemike

karenjudy philpetenanmike robrick veratom vincesol

vince

rick

tom

8/2/2019 b Tree Example

16/18

Rasmus Ejlers Mgelberg

Deleting abe and al

Since we can not solve this problem by

redistributing pointers we must merge siblingsagain

16

tomrick

solpetemike

karenjudy philpetenanmike robrick veratom vincesol

vince

8/2/2019 b Tree Example

17/18

Rasmus Ejlers Mgelberg

Deleting abe and al

Since the root is underfull it can be deleted

The resulting tree satisfies the requirements andso the deletion algorithm ends

17

tomrick

solpetemike

karenjudy philpetenanmike robrick veratom vincesol

vince

8/2/2019 b Tree Example

18/18

Rasmus Ejlers Mgelberg

General remarks

When a node becomes underfull the algorithm will

try to redistribute pointers from the neighbouringsibling either on the left or the right

If this is not possible, it should merge with one ofthem

The value held in an internal node or the rootshould always be the smallest value appearing in aleaf of the subtree pointed to by the pointer afterthe value

18