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.ID;

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.

Comments

@deepakpunia sir plz explain the concept of hashed index onto the queries

Answer 1

$A)$  As We know $B^+$ trees are good in Range queries if we make  $B^+$ tree on all attributes then it will defintely Speed up query.

$B)$  Hash based  indexing  is good in  equality Search.   so if we make hash index on $Genre.name$ it will Give only $Comedy$ genre names and for remaining attributes we can make $ B^+$ trees .

$C)$ if We make hash index on Customer rating then it will not efficiently  give output and will not speed up the query .

$D)$ same Reason as Previous .  that's Why $A$ and $B$ are suitable answers here.

here is refernce :Wisc university slides   ,  stack overflow

similar question : Databases: GATE CSE 2011 | Question: 39 (gateoverflow.in) 

screen shot of navathe : chap-$ 14$ , page $659$

Comments

so if there are 100 comedy movies so how will it store it in hash table will it not create linear probing

---

It will store 100 comedy movies through chaining. @sru12

Answer 2

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

So option A and B correct.

Comments

Straight to the point.

---

What you mean by equality attributes? @liontig37

---

@Sri28

Answer 3

• A. B+ tree on all the attributes.

  • This is a robust solution. B+ trees handle the range query on CustomerRating well, and also efficiently handle the equality queries on Genre.Name and all join conditions.

• B. Hash index on Genre.Name and B+ tree on the remaining attributes.

  • This specifically optimizes the Genre.Name = "Comedy" equality lookup with a Hash index (potentially faster than B+ tree for exact match).

  • It ensures a B+ tree is used for Movie.CustomerRating (essential for the range query) and for all the ID columns involved in joins. This is a very strong candidate.

• C. Hash index on Movie.CustomerRating and B+ tree on the remaining attributes.

  • This is incorrect. Placing a Hash index on Movie.CustomerRating would severely hamper the performance of the range query (> 3.4), as hash indexes are not suitable for ranges.

• D. Hash index on all the attributes.

  • This is incorrect. Similar to option C, using a Hash index on Movie.CustomerRating would make the range query inefficient.

so A, B are correct

Answer 4

HASH INDEX IS BEST FOR THE PERTICULAR VALUE SEARCH BUT RANGE QUERY ARE BEST IN B + TREE

BUT ALSO WE CAN SEARCH THE PARTICULAR VALUE IN B+ TREE

IN OPTION A THEY SEARCH ALL THE ATTRIBUTE IN B + TREE THATS COMPLETELY FINE

IN OPTION B THEY SEAR THE PARTUCULAT COMEDY MOVIE USING HASH AND OTHER ARE B+ TREE

BUT IN OPTION C THEY PERFORM THE RAGE QUERIES IN HASH INDEX WHICH IS INRORRECT

SIMILARLY OPTION D ALSO INCORRECT

 

Answer 5

Option B : Correct 

Remember this Simple Concept.