Consider a database that includes the following relations: Defender(name,…
2024
Consider a database that includes the following relations:
Defender(name, rating, side, goals)
Forward(name, rating, assists, goals)
Team(name, club, price)
Which ONE of the following relational algebra expressions checks that every name occurring in Team appears in either Defender or Forward, where 𝜙 denotes the empty set?
- A.
Π𝑛𝑎𝑚𝑒 (Team) \ (Π𝑛𝑎𝑚𝑒 (Defender)∩ Π𝑛𝑎𝑚𝑒 (Forward)) = 𝜙
- B.
(Π𝑛𝑎𝑚𝑒 (Defender)∩ Π𝑛𝑎𝑚𝑒 (Forward)) \ Π𝑛𝑎𝑚𝑒 (Team) = 𝜙
- C.
Π𝑛𝑎𝑚𝑒 (Team) \ (Π𝑛𝑎𝑚𝑒 (Defender)∪ Π𝑛𝑎𝑚𝑒 (Forward)) = 𝜙
- D.
(Π𝑛𝑎𝑚𝑒 (Defender)∪ Π𝑛𝑎𝑚𝑒 (Forward)) \ Π𝑛𝑎𝑚𝑒 (Team) = 𝜙
Attempted by 106 students.
Show answer & explanation
Correct answer: C
Answer: Π name (Team) ∖ (Π name (Defender) ∪ Π name (Forward)) = 𝜙
Reason: The requirement "every name occurring in Team appears in either Defender or Forward" means the set of Team names is a subset of the union of Defender and Forward names. A set inclusion A ⊆ B is equivalent to A ∖ B = ∅, so the expression above correctly tests that Π name (Team) ⊆ Π name (Defender) ∪ Π name (Forward).
Why the other expressions are incorrect:
An expression that subtracts the intersection of Defender and Forward from Team names fails because a Team name present in exactly one of the player relations will not be removed and so the difference can be nonempty even when every Team name is in at least one player relation.
An expression that subtracts Team names from the intersection of Defender and Forward ensures that anyone appearing in both player relations is in Team; this checks the wrong subset relationship and says nothing about every Team name appearing in the player relations.
An expression that subtracts Team from the union of Defender and Forward enforces that every player in Defender or Forward appears in Team (i.e., player relations ⊆ Team), which is the reverse of the desired requirement.