SYLLABUS
ER-model, relational model: relational algebra, tuple calculus, SQL, integrity constraints, normal form, file organization, indexing, data types, data transformation such as normalization, discretization, sampling, compression; data warehouse modelling: schema for multidimensional data models, concept hierarchies, measures: categorization and computations.
Q1 – Consider a database that includes the following relations:
Defender(name, rating, side, goals)
Forward(name, rating, assists, goals)
Team(name, club, price)
Which ONE of the following relational algebra expressions checks that every name occurring in Team appears in either Defender or Forward, where 𝜙 denotes the empty set?
(A) Π𝑛𝑎𝑚𝑒(Team)∖(Π𝑛𝑎𝑚𝑒(Defender)∩Π𝑛𝑎𝑚𝑒(Forward)) = 𝜙
(B) (Π𝑛𝑎𝑚𝑒(Defender)∩Π𝑛𝑎𝑚𝑒(Forward))∖Π𝑛𝑎𝑚𝑒(Team) = 𝜙
(C) Π𝑛𝑎𝑚𝑒(Team)∖(Π𝑛𝑎𝑚𝑒(Defender)∪Π𝑛𝑎𝑚𝑒(Forward)) = 𝜙
(D) (Π𝑛𝑎𝑚𝑒(Defender)∪Π𝑛𝑎𝑚𝑒(Forward))∖Π𝑛𝑎𝑚𝑒(Team) = 𝜙
(GATE DS&AI 2024)
Ans – (C)
Explanation – Every name occurring in Team appears in either Defender or Forward, it means Π𝑛𝑎𝑚𝑒(Team)∖(Π𝑛𝑎𝑚𝑒(Defender) ∪ Π𝑛𝑎𝑚𝑒(Forward)) = 𝜙
Q2 –
Consider the following two tables named Raider and Team in a relational database maintained by a Kabaddi league. The attribute ID in table Team references the primary key of the Raider table, ID.
Raider | |||
ID | Name | Raids | Raid Points |
1 | Arjun | 200 | 250 |
2 | Ankush | 190 | 219 |
3 | Sunil | 150 | 200 |
4 | Reza | 150 | 190 |
5 | Pratham | 175 | 220 |
6 | Gopal | 193 | 215 |
Team | ||
City | ID | Bid Points |
Jaipur | 2 | 200 |
Patna | 3 | 195 |
Hyderabad | 5 | 175 |
Jaipur | 1 | 250 |
Patna | 4 | 200 |
Jaipur | 6 | 200 |
The SQL query described below is executed on this database:
SELECT *
FROM Raider, Team
WHERE Raider.ID=Team.ID AND City=“Jaipur” AND RaidPoints > 200;
The number of rows returned by this query is ______.
(GATE DS&AI 2024)
Ans – (3)
Explanation – The number of rows returned by this query is
ID | Name | Raid | Raid Point | City | Bid Point |
1 | Arjun | 200 | 250 | Jaipur | 250 |
2 | Ankush | 190 | 219 | Jaipur | 200 |
6 | Gopal | 193 | 215 | Jaipur | 200 |
Q3 – Given the relational schema 𝑅=(𝑈,𝑉,𝑊,𝑋,𝑌,𝑍) and the set of functional dependencies:
{𝑈→𝑉, 𝑈→𝑊, 𝑊𝑋→𝑌, 𝑊𝑋→𝑍, 𝑉→𝑋}
Which of the following functional dependencies can be derived from the above set?
(A) 𝑉𝑊→𝑌𝑍
(B) 𝑊𝑋→𝑌𝑍
(C) 𝑉𝑊→𝑈
(D) 𝑉𝑊→𝑌
(GATE DS&AI 2024)
Ans – (A, B, D)
Explanation –
Option A – 𝑉𝑊→𝑌𝑍
Functional dependencies are 𝑉→𝑋 and 𝑊𝑋→𝑌, 𝑊𝑋→𝑍.
In 𝑊𝑋→𝑌, you can replace X from V, because X is dependent on V. So, 𝑊V→𝑌
Also, in 𝑊𝑋→𝑍, you can replace X from V, So, it becomes 𝑊V→𝑍.
Since 𝑊V→𝑌 and 𝑊V→𝑍, option (A) 𝑉𝑊→𝑌𝑍 is correct.
Option B – 𝑊𝑋→𝑌𝑍
Since 𝑊𝑋→𝑌 and 𝑊𝑋→𝑍, it becomes 𝑊𝑋→𝑌Z. So, option (B) is correct.
Option C – 𝑉𝑊→𝑈
In functional dependencies, there isn’t a dependency in which U comes on the right-hand side. So, option (C) is incorrect.
Option D – 𝑉𝑊→𝑌
In proving option (A), we have proved 𝑊V→𝑌, So, option (D) is correct.
Q4 – 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?
(A) B+ tree on all the attributes.
(B) Hash index on Genre.Name and B+ tree on the remaining attributes.
(C) Hash index on Movie.CustomerRating and B+ tree on the remaining attributes.
(D) Hash index on all the attributes.
(GATE DS&AI 2024)
Ans – (A, B)
Explanation – Option A – B+ Trees are sorted and it is useful for both selection and join operations. Hence this option speeds up the process.
Option B – The time complexity of Hash index for finding the Genre.Name = “Comedy” is very low. Also, B+ Trees for remaining attributes is good. So, this option speeds up the process.
Option C and D – Hash index can’t support range queries such as (>3.4). So, this option does not speed up the process.