Indexing and B+ Trees Previous Year Questions (PYQs) for GATE DA: 3+ Solved Questions with Step-by-Step Solutions

Solve 3+ Indexing and B+ Trees previous year questions for GATE DA with answers and detailed solutions. Free sample questions below.

Chapter Roadmap: Indexing and B+ Trees

Chapter Journey

1. B+ Tree Structure, Insertion and Fanout Calculation

Current Topic • High Importance • Foundation for all indexing and disk I/O questions

2. Index Selection for SQL Query Processing

Upcoming • Moderate Importance • Applies B+ tree mechanics to query execution plans

What you will master: Deriving node capacity limits from block sizes, executing step-by-step B+ tree insertions, and avoiding classic split traps.

Why B+ Trees? The Core Intuition

The Disk I/O Problem

Database systems minimize slow disk accesses by reading data in fixed-size blocks (or pages). A good index must minimize the number of blocks read to find a record.

Why Not Binary Search Trees?

A binary tree has a maximum of two children per node. For a million records, the height is around 20, meaning up to 20 disk reads. This is inefficient.

The B+ Tree Solution

High Fanout: Each node can hold hundreds of keys, drastically reducing the tree height (typically to 3 or 4 levels for millions of records).
Balanced: All leaf nodes are at the exact same depth, guaranteeing predictable, worst-case $O (lo g_{p} N)$ search time.
Data at Leaves Only: Internal nodes act purely as a "map" or routing guide. Only leaf nodes contain Data Record Pointers.
Linked Leaves: Leaf nodes are connected via sequential pointers, making range queries highly efficient without traversing back up the tree.

Indexing and B+ Trees: Solved Questions with Step-by-Step Explanations (3 Problems)

Question 1 · Database Management and Warehousing MSQ

An OTT company is maintaining a large disk-based relational database of different
movies with the following schema:

Movie(ID, CustomerRating)
Genre(ID, Name)
Movie_Genre(MovieID, GenreID)

Consider the following SQL query on the relation database above:

SELECT *
FROM Movie, Genre, Movie_Genre
WHERE
Movie.CustomerRating > 3.4 AND
Genre.Name = “Comedy” AND
Movie_Genre.MovieID = Movie.ID AND
Movie_Genre.GenreID = Genre.ID;

This SQL query can be sped up using which of the following indexing options?

A.
$B^{+}$ tree on all the attributes.
B.
Hash index on Genre.Name and $B^{+}$ tree on the remaining attributes.
C.
Hash index on Movie.CustomerRating and $B^{+}$ tree on the remaining attributes.
D.
Hash index on all the attributes.

Question 2 · Database Management and Warehousing MCQ

Consider a B+ Tree where the maximum number of key values in each leaf node is 2 and the maximum number of pointers in each non-leaf node is 3. Let the content of the B+ Tree be as shown in the figure.

Which of the following options denotes the key value(s) stored in the root node after inserting a key value 3 in the given B+ Tree?

A.
5
B.
8
C.
3 and 5
D.
3, 5 and 8

Question 3 · Database Management and Warehousing NAT

In a relational database, a B+ Tree Index is to be constructed for a relation on a key field. In a B+ Tree, a Node Pointer points to a sub-tree and a Data Record Pointer points to a block of database records.

Let, Node size = 4096 bytes, Node Pointer size = 10 bytes, Search Key Field size = 11 bytes and Data Record Pointer size = 12 bytes.

The maximum number of Node Pointers that can be present in a non-leaf node of the B+ Tree is ________ . (Answer in integer)

A question from this chapter

Question 1

An OTT company is maintaining a large disk-based relational database of different movies with the following schema: Movie(ID, CustomerRating) Genre(ID, Name) Movie_Genre(MovieID, GenreID) Consider the following SQL query on the relation database above: SELECT * FROM Movie, Genre, Movie_Genre WHERE Movie.CustomerRating > 3.4 AND Genre.Name = “Comedy” AND Movie_Genre.MovieID = Movie.ID AND Movie_Genre.GenreID = Genre.ID; This SQL query can be sped up using which of the following indexing options?

Question 2

Consider a B+ Tree where the maximum number of key values in each leaf node is 2 and the maximum number of pointers in each non-leaf node is 3. Let the content of the B+ Tree be as shown in the figure. 581578912 NULL Which of the following options denotes the key value(s) stored in the root node after inserting a key value 3 in the given B+ Tree?

Question 3

In a relational database, a B+ Tree Index is to be constructed for a relation on a key field. In a B+ Tree, a Node Pointer points to a sub-tree and a Data Record Pointer points to a block of database records. Let, Node size = 4096 bytes, Node Pointer size = 10 bytes, Search Key Field size = 11 bytes and Data Record Pointer size = 12 bytes. The maximum number of Node Pointers that can be present in a non-leaf node of the B+ Tree is ________ . (Answer in integer)

Free preview ends here

Login to view the complete previous-year questions and solutions

Creating an account is free. You get the rest of this chapter, step-by-step solutions, and a study plan built around the topics you are actually weak at.

Why MastersUp

Personalised first. High quality throughout.

Most platforms hand everyone the same content. Here the content moves with your performance, topic by topic.

Built around you, not around a syllabus PDF

Every answer you give moves your topic-level intelligence rate. The next question, the next revision card and tomorrow's plan all change with it.

Revision that hits your weak spots

We only revise topics you have actually attempted and are still below the safe bar on — never the same chapter on repeat.

Questions calibrated to the real exam

Each question carries a measured toughness. You are served a rung above your current level, so practice keeps stretching you.

Notes written for recall, not for volume

Full lesson cards for first study, curated short-note cards for the last mile — with derivations, traps and exam patterns marked.

One place for everything

Notes, chapter practice, previous-year questions, test series and full-length papers — all feeding one picture of your preparation.

Honest progress

No vanity streaks. Progress here means chapters mastered and accuracy that held up on harder questions.

Unlock the whole course

Full notes and short notes, the complete question bank with worked solutions, mock tests, full-length papers, and an adaptive plan that rebuilds itself as you improve.

View all plans Start free