Question

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

teams(tid, tname): tid and 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'?

• A. \( \{ p.pname \mid p \in {players} \land \exists t \in {teams} \land p.tid = t.tid \land t.tname = {'MI'} \} \)
• B. \( \{ p.pname \mid p \in {teams} \land \exists t \in {players} \land p.tid = t.tid \land t.tname = {'MI'} \} \)
• C. \( \{ p.pname \mid p \in {players} \land \exists t \in {teams} \land t.tname = {'MI'} \} \)
• D. \( \{ p.pname \mid p \in {teams} \land \exists t \in {players} \land t.tname = {'MI'} \} \)

Hint:

When using tuple relational calculus, ensure that you select the right relation first (in this case, tt{players}), and then perform the appropriate join and filtering to get the desired result.

Answer 1

The Correct Option is A

We are looking for the names of players who play for the team with the name 'MI'. The relational calculus query must:

- Access the players relation to get the player names.
- Join the players relation with the teams relation using the common attribute tid (team id).
- Filter the result where the team name (tname) is 'MI'.

Option (A): This query correctly returns the player names by finding players whose tid matches the tid of the team named 'MI'. This is the correct query.

Option (B): This query first accesses the teams relation and then tries to find the corresponding player names. However, the players relation should be accessed first to retrieve player names, and then the team should be matched using the tid. Hence, this option is incorrect.

Option (C): This query accesses the players relation and finds players where the tid matches the team with name 'MI'. While it looks close, it misses the join condition between players and teams by not explicitly linking the tid of players and teams. Therefore, it is not precise enough.

Option (D): This query incorrectly begins with teams and tries to find the players. The player names should be accessed from the players relation first, and then the team details should be joined. This makes option (D) incorrect.

Thus, the correct answer is \( \boxed{A} \).