Question

An OTT company is maintaining a large disk-based relational database of different movies with the following schema :
Movie (ID , Customer Rating)
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.Genre ID = 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.

Answer 1

Answer: (A), (B)

B+ tree on all the attributes.

Answer 2

The correct option is (A) : B+ tree on all the attributes and (B) : Hash index on Genre.Name and B+ tree on the remaining attributes.