GATE DA Database Management and Warehousing Quiz

Consider the function computeS(X) whose pseudocode is given below:

computeS(X)
𝑆[1] ← 1
for 𝑖 ←2 to length(X)
𝑆[𝑖] ← 1
if 𝑋[𝑖 − 1] ≤ 𝑋[𝑖]
𝑆[𝑖] ← 𝑆[𝑖] + 𝑆[𝑖 − 1]
end if
end for
return S

which ONE of the following values is returned by the function computeS(x)

for X=[6,3,5,4,10]?

On a relation named Loan of a bank:

the following SQL query is executed.

SELECT L1.loan_number

FROM Loan L1

WHERE L1.amount > (SELECT MAX (L2.amount)

FROM Loan L2

WHERE L2.branch_name = ’SR Nagar’);

The number of rows returned by the query is ___________ (Answer in integer)

Consider the following tables, Loan and Borrower, of a bank.

Query: π_{branch name, customer name}(Loan ⋈ Borrower) ÷ π_{branch name}(Loan)

where ⋈ denotes natural join.

The number of tuples returned by the above relational algebra query is _________

(Answer in integer)

Given the relational schema 𝑅 = (𝑈,𝑉, 𝑊, 𝑋, 𝑌, 𝑍) and the set of functional dependencies:

{𝑈 → 𝑉, 𝑈 → 𝑊, 𝑊𝑋 → 𝑌, 𝑊𝑋 → 𝑍, 𝑉 → 𝑋} 

Which of the following functional dependencies can be derived from the above set? [GATE || DA || MSQ || 2024]

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? [GATE DA || MSQ || 2024]

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?

[GATE DA || MCQ || 2024]

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

Team

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 DA || MCQ || 2024]

If a relational decomposition is not dependency-preserving, which one of the following relational operators will be executed more frequently in order to maintain the dependencies?
[GATE || DA || MCQ || 2025]

Consider the following three relations:

Car (model, year, serial, color)
Make (maker, model)
Own (owner, serial)

A tuple in Car represents a specific car of a given model, made in a given year, with a serial number and a color. A tuple in Make specifies that a maker company makes cars of a certain model. A tuple in Own specifies that an owner owns the car with a given serial number. Keys are underlined; (owner, serial) together form key for Own. (⋈ denotes natural join)

π_owner(Own ⋈ (σ_{color=“red”} (Car ⋈ (σ_{maker=“ABC”} Make))))

Which one of the following options describes what the above expression computes? [GATE DA || MCQ || 2025]

Consider a fact table in an OLAP application: Facts(D1, D2, val), where D1 and D2 are its dimension attributes and val is a dependent attribute. Suppose attribute D1 takes 3 values and D2 takes 2 values, and all combinations of these values are present in the table Facts. How many tuples are there in the result of the following query?

SELECT D1, D2, sum(val)
FROM Facts
GROUP BY CUBE (D1, D2); [GATE DA || MSQ || 2025]