GATE CS 2025 Set 1 | Question 39

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 \(\text{‘MI’}\)?

Option A: \(\{\, p.pname \mid p \in players \land \exists t\, (t \in teams \land p.tid = t.tid \land t.tname = \text{‘MI’}) \,\}\) (correct)
Option B: \(\{\, p.pname \mid p \in teams \land \exists t\, (t \in players \land p.tid = t.tid \land t.tname = \text{‘MI’}) \,\}\)
Option C: \(\{\, p.pname \mid p \in players \land \exists t\, (t \in teams \land t.tname = \text{‘MI’}) \,\}\)
Option D: \(\{\, p.pname \mid p \in teams \land \exists t\, (t \in players \land t.tname = \text{‘MI’}) \,\}\)

Answer

(A)

Explanation

The result must list players, so \(p\) has to range over \(players\) (the relation that has \(pname\)), and there must exist a team \(t\) with the same \(tid\) whose \(tname\) is ‘MI’.

(A) does exactly this: \(p \in players\), \(t \in teams\), joined by \(p.tid = t.tid\) and filtered by \(t.tname = \text{‘MI’}\). Correct.
(B) and (D) take \(p\) from \(teams\) and \(t\) from \(players\), but \(teams\) has no \(pname\) and \(players\) has no \(tname\), so they are wrong.
(C) has no join condition \(p.tid = t.tid\): as long as some team is called ‘MI’, it returns every player in the league.

Answer: A