GATE DA 2024 Set 1 — Question 55
Go beyond PYQs with Success TrackerAI-powered personalised practice and doubt support. Unlimited practice on eligible plans; AI usage limits apply.MSQ+2 / -0MediumB+ TreesFile Organization & IndexingDatabase Management & Warehousing
Database Management & Warehousing → File Organization & Indexing → B+ Trees
Last updated
Question
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:This SQL query can be sped up using which of the following indexing options?
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;
Correct answer
(A) B^+ tree on all the attributes.; (B) Hash index on Genre.Name and B^+ tree on the remaining attributes.
Solution
To determine which indexing options will speed up the query, we analyze the conditions in the
WHERE clause:1.Range Condition:
Movie.CustomerRating > 3.4. trees are efficient for range queries, while Hash indexes are not.2.Equality Condition:
Genre.Name = “Comedy”. Both trees and Hash indexes are efficient for equality searches.3.Join Conditions (Equality):
Evaluating the options:Movie_Genre.MovieID = Movie.ID and Movie_Genre.GenreID = Genre.ID. Both trees and Hash indexes are efficient for these equality-based joins.- (A): trees on all attributes will speed up the range query on
CustomerRatingas well as all equality checks. This is a valid option. - (B): A Hash index on
Genre.Namespeeds up the equality check for "Comedy". trees on the remaining attributes speed up the range query onCustomerRatingand the join conditions. This is a valid option. - (C): A Hash index on
Movie.CustomerRatingis inefficient for the range condition> 3.4. Thus, this option will not speed up the query as effectively as a tree. - (D): Hash indexes on all attributes would be inefficient for the range condition on
Movie.CustomerRating.
Continue learning with Success Tracker
A step still unclear? Work through it with support
Use Success Tracker to ask about the reasoning, then try another GATE DA question to check your understanding.
AI-powered practice· Unlimited practice on eligible plans
- PYQs with solutions
- Attempt available previous-year questions, then compare your reasoning with the worked solution. Coverage varies by stream.
- Practice that adapts
- Choose a topic, work on weaker areas and bookmark questions to revisit. Your attempts feed your progress tracking.
- AI doubt support
- Ask follow-up questions about a step or concept while practising, instead of stopping at the final answer.
Unlimited practice is available on eligible plans. Free practice and AI usage have limits; check the current plan allowances before choosing.
This page stays readable without an account. AI responses can be wrong; check them against the solution and source material.
More questions on File Organization & Indexing
2026 Set 1 Q17 be a relational schema with functional dependency set…2026 Set 1 Q18Consider that the visualization of a 3-dimensional data cube is showing Sales Quantity for each…2026 Set 1 Q26Consider two relations r and s defined on the relational schemas R(A,B) and S(E,C), respectively. A…2026 Set 1 Q32In a relational database, a B+ Tree Index is to be constructed for a relation on a key field. In a…2026 Set 1 Q41Consider a B+ Tree where the maximum number of key values in each leaf node is 2 and the maximum…