GATE DA Relational Algebra and Tuple Calculus Previous Year Questions

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