The GATE Grind

GATE 2024 DA – Question 55

Database Management and Warehousing · File organization and indexing · 2 marks · Multiple select

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. B+ tree on all the attributes.
  2. Hash index on Genre.Name and B+ tree on the remaining attributes.
  3. Hash index on Movie.CustomerRating and B+ tree on the remaining attributes.
  4. Hash index on all the attributes.

Practise this question in The GATE Grind →

Show answer and explanation

Correct answer: (A) B+ tree on all the attributes.; (B) Hash index on Genre.Name and B+ tree on the remaining attributes.

Explanation

The query has a range condition on CustomerRating, which only a B+ tree can use (a hash index supports only equality). It has an equality condition on Genre.Name, for which both a hash index and a B+ tree work, and join conditions on IDs, for which B+ trees or hash indexes both help. So a B+ tree on all attributes (A) speeds it up, and so does a hash index on Genre.Name with B+ trees elsewhere (B). In C the rating, which needs a range search, would be on a hash index, which cannot be used for it, and D has no range support at all.