B+ tree index structure in DBMS Theory - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a B+ tree index on the column 'employee_id'.

DBMS Theory

CREATE INDEX idx_emp_id ON employees USING [1] (employee_id);

Drag options to blanks, or click blank then click option'

Abtree

Bgin

Cgist

Dhash

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'hash' instead of 'btree' will create a hash index, not a B+ tree index.

Using 'gist' or 'gin' are for other index types, not B+ tree.

The B+ tree index is created using the keyword btree in SQL.

2fill in blank

medium

Complete the code to find the leaf node in a B+ tree index where the key 50 would be located.

DBMS Theory

SELECT * FROM bplus_tree WHERE key = [1];

Drag options to blanks, or click blank then click option'

A50

B25

C75

D100

Attempts:

3 left

💡 Hint

Common Mistakes

Using a different key value will not find the correct leaf node.

Using a range or inequality instead of equality.

To find the leaf node for key 50, you query the B+ tree index with key = 50.

3fill in blank

hard

Fix the error in the B+ tree node split condition to check if the number of keys exceeds the maximum.

DBMS Theory

if (num_keys [1] max_keys) {
    split_node();
}

Drag options to blanks, or click blank then click option'

A<

B>=

C<=

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' or '<=' will cause split to happen too early or never.

Using '>=' may cause split to happen one key too late.

The node should split when the number of keys is greater than the maximum allowed, so use '>'.

4fill in blank

hard

Fill both blanks to complete the B+ tree search condition for keys between 10 and 20 inclusive.

DBMS Theory

SELECT * FROM bplus_tree WHERE key [1] 10 AND key [2] 20;

Drag options to blanks, or click blank then click option'

A>=

B<

C<=

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' or '>' excludes boundary keys.

Mixing operators incorrectly changes the range.

To include keys between 10 and 20 inclusive, use 'key >= 10' and 'key <= 20'.

5fill in blank

hard

Fill all three blanks to complete the B+ tree insertion pseudocode for updating parent after split.

DBMS Theory

parent.keys.[1](new_key)
parent.children.[2](new_child)
if len(parent.keys) [3] max_keys:
    split_node(parent)

Drag options to blanks, or click blank then click option'

Aappend

Binsert

C>

Dpop

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'append' for keys may not keep order.

Using 'pop' removes elements instead of adding.

Using '<' or '<=' in condition delays split.

Insert the new key with 'insert', add new child with 'append', and check if keys exceed max with '>'.