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’}\)?
Explanation
Answer: (A)
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