GATE DS&AI 2024 | Question: 45

Question

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

\[
\begin{array}{l}
\text { Movie (ID, CustomerRating) } \\
\text { Genre (ID, Name) } \\
\text { Movie_Genre (MovieID, GenreID) }
\end{array}
\]

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. $I D$;

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

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

Answer 1

B+ tree can be used on all attributes while hashed index can be used only on equality attributes.

So option A and B correct.