GATE DA 2024 — Question 55

Database Management and Warehousingfile organization, indexingMSQ2 marksMedium

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:
sql
1SELECT *
2FROM Movie, Genre, Movie_Genre
3WHERE
4  Movie.CustomerRating > 3.4 AND
5  Genre.Name = "Comedy" AND
6  Movie_Genre.MovieID = Movie.ID AND
7  Movie_Genre.GenreID = Genre.ID;
8
This SQL query can be sped up using which of the following indexing options?
  1. A
    B+ tree on all the attributes.
  2. B
    Hash index on Genre.Name and B+ tree on the remaining attributes.
  3. C
    Hash index on Movie.CustomerRating and B+ tree on the remaining attributes.
  4. D
    Hash index on all the attributes.
Practise the full 2024 paper
Challenge a friend: WhatsApp Telegram
From RENYXERAPractise this subject in the exam simulatorTimed, GATE-style tests with analytics that find your weak topics.
Practise more on file organization, indexingTimed, in the real exam interface · free to start

A GATE question every morning

One real past-paper question every morning for your branch, answer hidden until you tap. Free on Telegram.

Join
Stuck? Ask in the student group