GATE DA Indexing Previous Year Questions

GATE DA 2024 | Question 55

MSQ · 2 marks

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?

B⁺ tree on all the attributes. Hash index on Genre.Name and B⁺ tree on the remaining attributes. Hash index on Movie.CustomerRating and B⁺ tree on the remaining attributes. Hash index on all the attributes.

Explanation

Answer: (A), (B)

The query has one range condition, Movie.CustomerRating > 3.4, and only equality conditions otherwise: Genre.Name = "Comedy" and the two join conditions on the IDs.

A B⁺ tree keeps its keys in sorted order, so it serves range searches as well as equality lookups.
A hash index places keys by their hash values, so it can only find exact matches; it cannot list all ratings above 3.4.

A (B⁺ trees everywhere) and B (a hash index only on the equality attribute Genre.Name, B⁺ trees elsewhere) give every condition an index it can use. C and D put a hash index on CustomerRating, which cannot help the range condition, so the selection on Movie would still need a full scan.

Answer: A, B