• 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'?
Correct : a
Explanation:
To retrieve the names of players who belong to a specific team, our Tuple Relational Calculus (TRC) query needs to filter, join, and project attributes across both relations correctly. Let's break down the required logic step-by-step:
1. Determine the Target Variable:
We want to return the names of the players (pname). The main tuple variable bounding this output attribute must belong to the players relation. Therefore, the query must start with:
{ p.pname | p ∈ players ∧ ... }
This immediately eliminates choices (b) and (d), which incorrectly state that p ∈ teams.
2. Establish the Cross-Relation Link (Join Condition):
To find team details corresponding to a player, there must exist a tuple t in the teams relation (∃t (t ∈ teams)) such that the team identifier matches the player's team identifier. This is the explicit join condition:
p.tid = t.tid
3. Apply the Filter Criteria:
We only care about the specific team where the team name matches 'MI':
t.tname = 'MI'
4. Evaluate the Remaining Options:
• Option (a): Correctly specifies that p is a tuple from players, asserts that there exists a tuple t in teams, links them via their shared foreign key (p.tid = t.tid), and applies the exact string filter on the team name.
• Option (c): Missing the critical join constraint p.tid = t.tid. Without this condition, it creates a Cartesian product—returning the names of all players in the database as long as a team named 'MI' exists anywhere in the teams table.
5. Conclusion:
Option (a) is the only syntactically and logically sound TRC formulation for this request.
Similar Questions
Total Unique Visitors