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

Practice - 5 Tasks

1fill in blank

easy

Complete the code to declare a B-tree index on the column 'name' in SQL.

DBMS Theory

CREATE INDEX idx_name ON employees ([1]);

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

Aname

Bage

Cdepartment

Dsalary

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a column unrelated to the query, like 'age' or 'salary'.

Forgetting to specify the column name.

The B-tree index is created on the 'name' column to speed up searches based on employee names.

2fill in blank

medium

Complete the SQL query to find all employees with names starting with 'A' using the B-tree index.

DBMS Theory

SELECT * FROM employees WHERE name [1] 'A%';

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

A=

BLIKE

CIN

DBETWEEN

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' which looks for exact matches only.

Using 'IN' which is for lists of values.

The LIKE operator is used with '%' as a wildcard to find names starting with 'A'.

3fill in blank

hard

Fix the error in the SQL statement to create a B-tree index explicitly.

DBMS Theory

CREATE INDEX idx_salary ON employees (salary) USING [1];

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

Agin

Bhash

Cgist

Dbtree

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'hash' which is a different index type.

Using 'gist' or 'gin' which are for other data types.

The B-tree index type is specified by 'btree' to create a balanced tree index.

4fill in blank

hard

Fill both blanks to complete the B-tree node split condition in pseudocode.

DBMS Theory

if (node.[1] > [2]) {
    splitNode(node);
}

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

AkeysCount

BmaxKeys

CminKeys

DchildrenCount

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'minKeys' which is the minimum, not the maximum.

Using 'childrenCount' which is not the key count.

The node splits when the number of keys exceeds the maximum allowed keys in a B-tree node.

5fill in blank

hard

Fill all three blanks to complete the B-tree search pseudocode for a key.

DBMS Theory

function search(node, key) {
    let i = 0;
    while (i < node.[1] && key > node.keys[[2]]) {
        i = i + 1;
    }
    if (i < node.[3] && key == node.keys[i]) {
        return node.values[i];
    }
    if (node.isLeaf) {
        return null;
    } else {
        return search(node.children[i], key);
    }
}

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

AkeysCount

Bi

DchildrenCount

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'childrenCount' instead of 'keysCount' for key comparisons.

Using wrong index variables in conditions.

The search loops while 'i' is less than the number of keys and the key is greater than the current key. Then it checks if the key matches at position 'i'.