GATE2025_CS1_Q39

Consider two relations describing teams and players in a sports league:

• teams(tid, tname): tid, tname are team-id and team-name, respectively
• players(pid, pname, tid): pid, pname, and tid denote player-id, player-name and the team-id of the player, respectively

Which ONE of the following tuple relational calculus queries returns the name of the players who play for the team having tname as 'MI'?

✅ Final Answer:

The correct answer is option (A).

🔹 Short Answer:

The correct query must select the player's name (p.pname) from the players relation (p ∈ players) for which there exists a team (∃t (t ∈ teams)) that satisfies two conditions: the team's name is 'MI' (t.tname = 'MI') and the player's team ID matches the team's ID (p.tid = t.tid). Option (A) is the only one that correctly formulates all these conditions.

🔸 Long Answer:

Let's break down the required logic and analyze how each Tuple Relational Calculus (TRC) expression attempts to achieve it.

Goal of the Query

The objective is to find the names (pname) of all players who are part of the team named 'MI'. This requires three logical steps:

1. Iterate through all players in the players relation.
2. For each player, find their corresponding team in the teams relation using the foreign key tid.
3. Check if that team's name (tname) is 'MI'.
4. If all conditions are met, retrieve the player's name (pname).

Deconstructing Tuple Relational Calculus Syntax

A typical TRC query has the form { T | P(T) }, which means "Return the set of tuples T for which the predicate P(T) is true."

Analysis of the Correct Option (A)

Let's break down option (A) piece by piece:

Part of the Query	Meaning
{ p.pname | ... }	Select the pname attribute from the tuple variable p.
p ∈ players	The tuple variable p ranges over the tuples of the players relation. This is correct as we are looking for player names.
∧	AND - The following condition must also be true.
∃t ( ... )	"There exists a tuple t such that..."
t ∈ teams	The tuple variable t ranges over the tuples of the teams relation. This is also correct.
∧ p.tid = t.tid	AND the tid of the player tuple p must match the tid of the team tuple t. This is the crucial **join condition**.
∧ t.tname = 'MI'	AND the name of that team must be 'MI'. This is the **filter condition**.

When combined, the query correctly translates to: "Find the name of each player p for whom there exists a team t, where p and t are linked by their team ID, and the team's name is 'MI'". This is exactly what we need.

Analysis of Incorrect Options

• (B) { p.pname | p ∈ teams ... }: This is fundamentally flawed. It tries to get a player name (p.pname) from a tuple p that belongs to the teams relation. The teams relation has no pname attribute.
• (C) { p.pname | p ∈ players ∧ ∃t (t ∈ teams ∧ t.tname = 'MI')}: This query is missing the join condition p.tid = t.tid. It would return the names of *all* players as long as at least one team named 'MI' exists in the database. It fails to link specific players to that specific team.
• (D) { p.pname | p ∈ teams ... }: This has the same error as option (B). The tuple variable p is incorrectly defined as belonging to the teams relation.

Bibliography:
- Korth, H. F., Silberschatz, A., & Sudarshan, S. (2010). Database System Concepts. McGraw-Hill. (Chapter on Relational Calculus).
- Garcia-Molina, H., Ullman, J. D., & Widom, J. (2008). Database Systems: The Complete Book. Pearson.

Ask your doubts in comment form our GURUJEE will reply ASAP, click notify me button so that you get notified on reply.