Read the following and answer the question. Consider the relational schema…
2022
Read the following and answer the question.
Consider the relational schema Sailors S, Reserves R and Boats B.
Table 1: Sailors S
Sid | Sname | Rating | Age
22 | Dustin | 7 | 45.0
29 | Brutus | 1 | 33.0
31 | Lubber | 8 | 55.5
32 | Andy | 8 | 25.5
58 | Rusty | 10 | 35.0
64 | Horatio | 7 | 35.0
71 | Zorba | 10 | 16.0
74 | Horatio | 9 | 35.0
85 | Art | 3 | 25.5
95 | Bob | 3 | 63.5
Table 2: Reserves R
Sid | Bid | Day
22 | 101 | 10/10/98
22 | 102 | 10/10/98
22 | 103 | 10/08/98
22 | 104 | 10/07/98
31 | 102 | 11/10/98
31 | 103 | 11/06/98
31 | 104 | 11/12/98
64 | 101 | 09/05/98
64 | 102 | 09/08/98
74 | 103 | 09/08/98
Table 3: Boats B
Bid | Bname | Color
101 | Interlake | blue
102 | Interlake | red
103 | Clipper | green
104 | Marine | red
Which of the following relational algebra query computes the sid values of sailors with age > 20 who have not reserved a red boat?
- A.
π_sid(σ_age > 20(Sailors)) − π_sid((σ_color = 'red'(Boats)) ⋈ Reserves ⋈ Sailors)
- B.
π_sid(σ_color ≠ 'red' ∧ age > 20(Boats ⋈ Sailors ⋈ Reserves))
- C.
π_sid(σ_age < 20(Sailors)) − π_sid((σ_color = 'red'(Boats)) ⋈ Reserves ⋈ Sailors)
- D.
π_sid(σ_age > 20(Sailors)) ∩ π_sid((σ_color = 'red'(Boats)) ⋈ Reserves ⋈ Sailors)
Attempted by 80 students.
Show answer & explanation
Correct answer: A
Correct answer: Option A
Required result:
Sailors with age > 20 who have not reserved a red boat.
Step 1: Find all sailors with age > 20.
π_sid(σ_age > 20(Sailors))
Step 2: Find sailors who have reserved a red boat.
π_sid((σ_color = 'red'(Boats)) ⋈ Reserves ⋈ Sailors)
Step 3: Subtract the red-boat reservers from the age > 20 sailors.
π_sid(σ_age > 20(Sailors)) − π_sid((σ_color = 'red'(Boats)) ⋈ Reserves ⋈ Sailors)
Why Option B is wrong:
color ≠ 'red' after the join only finds sailors with at least one non-red reservation. A sailor who reserved both red and non-red boats could still appear, so it does not enforce “not reserved a red boat.”
Why Option C is wrong:
It uses age < 20 instead of age > 20.
Why Option D is wrong:
Intersection gives sailors older than 20 who have reserved a red boat, not those who have not reserved one.
Using the given data:
Sailors with age > 20 = {22, 29, 31, 32, 58, 64, 74, 85, 95}
Sailors who reserved red boats (Bid 102 or 104) = {22, 31, 64}
Final result = {29, 32, 58, 74, 85, 95}
A video solution is available for this question — log in and enroll to watch it.