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?

  1. Option A: B+ tree on all the attributes. (correct)
  2. Option B: Hash index on Genre.Name and B+ tree on the remaining attributes. (correct)
  3. Option C: Hash index on Movie.CustomerRating and B+ tree on the remaining attributes.
  4. Option D: Hash index on all the attributes.

Answer

(A), (B)

Explanation

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