GATE DA Database Management and Warehousing Previous Year Questions

GATE DA 2024 | Question 46

MSQ · 2 marks

Given the relational schema \(R = (U, V, W, X, Y, Z)\) and the set of functional dependencies:

\[\{U \to V, U \to W, WX \to Y, WX \to Z, V \to X\}\]

Which of the following functional dependencies can be derived from the above set?

\(VW \to YZ\) \(WX \to YZ\) \(VW \to U\) \(VW \to Y\)

Explanation

Answer: (A), (B), (D)

Compute attribute closures.

\(\{V, W\}^{+}\): \(V \to X\) adds \(X\), and then \(WX \to Y\) and \(WX \to Z\) add \(Y\) and \(Z\). So \(\{V, W\}^{+} = \{V, W, X, Y, Z\}\).
\(\{W, X\}^{+} = \{W, X, Y, Z\}\), straight from the two given dependencies.

Hence \(VW \to YZ\) (A) and \(VW \to Y\) (D) hold, and \(WX \to YZ\) (B) is the union rule applied to \(WX \to Y\) and \(WX \to Z\). But \(U\) is not in \(\{V, W\}^{+}\): no dependency has \(U\) on its right-hand side, so only sets containing \(U\) determine it, and C cannot be derived.

Answer: A, B, D

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?

B⁺ tree on all the attributes. Hash index on Genre.Name and B⁺ tree on the remaining attributes. Hash index on Movie.CustomerRating and B⁺ tree on the remaining attributes. Hash index on all the attributes.

Explanation

Answer: (A), (B)

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