GATE DA Database Management and Warehousing Previous Year Questions

GATE DA 2025 | Question 16

MCQ · 1 mark · −0.33

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?

Selection Projection Join Set union

Explanation

Answer: (C)

A functional dependency is cheap to enforce when all its attributes sit in one relation of the decomposition: each insert or update is checked against that relation alone. In a decomposition that is not dependency-preserving, some dependency \(X \to Y\) cannot be derived from the dependencies holding within the individual relations, because its attributes are spread over several of them. To check it after every change, those relations must first be joined to bring \(X\) and \(Y\) back together, so joins are executed far more often. Selection, projection and set union cannot reunite attributes stored in different relations.

Answer: C

GATE DA 2025 | Question 17

MCQ · 1 mark · −0.33

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. (\(\bowtie\) denotes natural join)

\[\pi_{\texttt{owner}}(\texttt{Own} \bowtie (\sigma_{\texttt{color}=\text{“}\mathit{red}\text{”}}(\texttt{Car} \bowtie (\sigma_{\texttt{maker}=\text{“}\mathit{ABC}\text{”}}\texttt{Make}))))\]

Which one of the following options describes what the above expression computes?

All owners of a red car, a car made by ABC, or a red car made by ABC All owners of more than one car, where at least one car is red and made by ABC All owners of a red car made by ABC All red cars made by ABC

Explanation

Answer: (C)

Evaluate the expression from the innermost part outwards.

\(\sigma_{\texttt{maker}=\text{“}\mathit{ABC}\text{”}}\texttt{Make}\) keeps the models that ABC makes.
Joining with Car (on model) keeps only cars of those models, and \(\sigma_{\texttt{color}=\text{“}\mathit{red}\text{”}}\) then keeps the red ones. Both conditions hold for the same car.
Joining with Own (on serial) pairs each such car with its owners, and \(\pi_{\texttt{owner}}\) keeps only the owners.

The result is every owner of a red car made by ABC. Option A would need a union of separate selections, B needs a count that the expression never takes, and D describes cars although the projection outputs owners.

Answer: C

GATE DA 2025 | Question 33

NAT · 1 mark

On a relation named Loan of a bank:

Loan
loan_number	branch_name	amount
L11	Banjara Hills	90000
L14	Kondapur	50000
L15	SR Nagar	40000
L22	SR Nagar	25000
L23	Balanagar	80000
L25	Kondapur	70000
L19	SR Nagar	65000

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)

Your answer

Explanation

Answer: 3

The inner query does not use L1, so it produces a single value: the largest SR Nagar amount, \(\max(40000, 25000, 65000) = 65000\).

The outer query keeps the loans whose amount is strictly greater than 65000: L11 (90000), L23 (80000) and L25 (70000). L19 is left out because 65000 is not greater than itself, and every other loan is smaller. So the query returns 3 rows.

Answer: 3

GATE DA 2025 | Question 46

MCQ · 2 marks · −0.66

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);

1 6 9 12

Explanation

Answer: (D)

GROUP BY CUBE (D1, D2) produces one group of rows for every subset of the grouping attributes:

(D1, D2): one row per combination, \(3 \times 2 = 6\);
(D1): one subtotal per value of D1, 3 rows;
(D2): one subtotal per value of D2, 2 rows;
(): the grand total, 1 row.

That gives \(6 + 3 + 2 + 1 = 12\) tuples. Equivalently, each dimension is either one of its values or ALL, so \((3 + 1)(2 + 1) = 12\). The value 6 is a plain GROUP BY D1, D2, which leaves out the subtotal and total rows.

Answer: D

GATE DA 2025 | Question 56

MSQ · 2 marks

Consider the following two relations, named Customer and Person, in a database:

Person (
    aadhaar CHAR(12) PRIMARY KEY,
    name VARCHAR(32));
Customer (
    name VARCHAR(32),
    email VARCHAR(32) PRIMARY KEY,
    phone CHAR(10),
    aadhaar CHAR(12),
      FOREIGN KEY (aadhaar) REFERENCES Person(aadhaar));

Which of the following statements is/are correct?

aadhaar is a candidate key in the Customer relation phone can be NULL in the Customer relation aadhaar is a candidate key in the Person relation aadhaar can be NULL in the Person relation

Explanation

Answer: (B), (C)

Only the declared constraints restrict the columns.

A fails: the primary key of Customer is email, and aadhaar there is only a foreign key with no UNIQUE constraint. Several customer rows (say, one person with two email addresses) can share the same aadhaar, so it is not a candidate key.
B holds: phone has no NOT NULL or key constraint, so it may be NULL.
C holds: aadhaar is the primary key of Person, and a primary key is always a candidate key.
D fails: a primary key column can never be NULL.

Answer: B, C

GATE DA 2025 | Question 57

MSQ · 2 marks

Consider a database relation R with attributes ABCDEFG, and having the following functional dependencies:

\[ \mathrm{A} \rightarrow \mathrm{BCEF} \qquad \mathrm{E} \rightarrow \mathrm{DG} \qquad \mathrm{BC} \rightarrow \mathrm{A} \]

Which of the following statements is/are correct?

A is the only candidate key of R A, BC are the candidate keys of R A, BC, E are the candidate keys of R Relation R is not in Boyce-Codd Normal Form (BCNF)

Explanation

Answer: (B), (D)

Find the keys with attribute closures.

\(\mathrm{A}^{+}\): A \(\rightarrow\) BCEF, then E \(\rightarrow\) DG, so \(\mathrm{A}^{+} = \mathrm{ABCDEFG}\) and A is a key.
\(\mathrm{BC}^{+}\): BC \(\rightarrow\) A, and everything follows as above. \(\mathrm{B}^{+} = \mathrm{B}\) and \(\mathrm{C}^{+} = \mathrm{C}\), so BC is a candidate key.
\(\mathrm{E}^{+} = \mathrm{DEG}\), so E is not a key.

A appears on a right side only in BC \(\rightarrow\) A, and B and C are determined only by A, so every key contains A or both B and C. The candidate keys are exactly A and BC: B holds, while A and C fail.

In E \(\rightarrow\) DG the left side E is not a superkey, so R is not in BCNF and D holds.

Answer: B, D

GATE DA 2025 | Question 62

NAT · 2 marks

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

Loan
loan_num	branch_name	amount
L11	Banjara Hills	90000
L14	Kondapur	50000
L15	SR Nagar	40000
L22	SR Nagar	25000
L23	Balanagar	80000
L25	Kondapur	70000
L19	SR Nagar	65000

Borrower
customer_name	loan_num
Anand	L11
Karteek	L11
Karteek	L14
Ankita	L15
Gopal	L19
Karteek	L22
Karteek	L23
Sunil	L23
Sunil	L25

Query: \(\pi_{\textbf{branch\_name},\ \textbf{customer\_name}}(\textbf{Loan} \bowtie \textbf{Borrower}) \div \pi_{\textbf{branch\_name}}(\textbf{Loan})\)
where \(\bowtie\) denotes natural join.
The number of tuples returned by the above relational algebra query is ________
(Answer in integer)

Your answer

Explanation

Answer: 1

Division keeps the customers who are paired with every branch in \(\pi_{\textbf{branch\_name}}(\textbf{Loan})\), that is, with all four of Banjara Hills, Kondapur, SR Nagar and Balanagar.

The natural join matches on loan_num, which gives each customer's branches:

Anand: Banjara Hills (L11)
Karteek: Banjara Hills (L11), Kondapur (L14), SR Nagar (L22), Balanagar (L23)
Ankita: SR Nagar (L15); Gopal: SR Nagar (L19)
Sunil: Balanagar (L23), Kondapur (L25)

Only Karteek appears with all four branches, so the query returns the single tuple (Karteek).

Answer: 1

GATE DA 2024 | Question 26

MCQ · 1 mark · −0.33

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 \(\phi\) denotes the empty set?

\(\Pi_{\mathit{name}}(\text{Team}) \setminus \big(\Pi_{\mathit{name}}(\text{Defender}) \cap \Pi_{\mathit{name}}(\text{Forward})\big) = \phi\) \((\Pi_{\mathit{name}}(\text{Defender}) \cap \Pi_{\mathit{name}}(\text{Forward})) \setminus \Pi_{\mathit{name}}(\text{Team}) = \phi\) \(\Pi_{\mathit{name}}(\text{Team}) \setminus \big(\Pi_{\mathit{name}}(\text{Defender}) \cup \Pi_{\mathit{name}}(\text{Forward})\big) = \phi\) \(\big(\Pi_{\mathit{name}}(\text{Defender}) \cup \Pi_{\mathit{name}}(\text{Forward})\big) \setminus \Pi_{\mathit{name}}(\text{Team}) = \phi\)

Explanation

Answer: (C)

The condition says that the set of Team names is contained in the set of names that occur in Defender or Forward:

\[\Pi_{\mathit{name}}(\text{Team}) \subseteq \Pi_{\mathit{name}}(\text{Defender}) \cup \Pi_{\mathit{name}}(\text{Forward}).\]

A set \(A\) is a subset of \(B\) exactly when \(A \setminus B = \phi\): nothing in \(A\) is left over after removing the members of \(B\). That is option C.

A uses \(\cap\), so it would demand that every Team name occur in both Defender and Forward.
B and D test the opposite inclusion, that the players' names all occur in Team.

Answer: C

GATE DA 2024 | Question 27

MCQ · 1 mark · −0.33

Let the minimum, maximum, mean and standard deviation values for the attribute income of data scientists be ₹46000, ₹170000, ₹96000, and ₹21000, respectively. The z-score normalized income value of ₹106000 is closest to which ONE of the following options?

0.217 0.476 0.623 2.304

Explanation

Answer: (B)

z-score normalization subtracts the mean and divides by the standard deviation:

\[z = \frac{x - \mu}{\sigma} = \frac{106000 - 96000}{21000} = \frac{10000}{21000} \approx 0.476.\]

The minimum and maximum play no part here. They are what min-max normalization uses, and that would give \(\frac{106000 - 46000}{170000 - 46000} \approx 0.484\), which is not an option either. Option A divides the difference from the mean by the minimum instead of the standard deviation, and D divides the income itself by the minimum.

Answer: B

GATE DA 2024 | Question 31

NAT · 1 mark

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	RaidPoints
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	BidPoints
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 ______.

Your answer

Explanation

Answer: 3

The condition Raider.ID=Team.ID turns the cross product into a join that pairs each Team row with the raider of the same ID. Of these pairs, keep only the Jaipur rows whose raider has more than 200 raid points:

Jaipur, ID 2: Ankush with 219 points, kept.
Jaipur, ID 1: Arjun with 250 points, kept.
Jaipur, ID 6: Gopal with 215 points, kept.

The Patna and Hyderabad rows fail the city test. Note that the comparison is strict, so a raider with exactly 200 points (Sunil) would not count, but he is in Patna anyway. The query returns 3 rows.

Answer: 3

Topics