Read the following data and answer the question. Consider the relational…
2022
Read the following data and answer the question.
Consider the relational schema of 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/8/98 |
22 | 104 | 10/7/98 |
31 | 102 | 11/10/98 |
31 | 103 | 11/6/98 |
31 | 104 | 11/12/98 |
64 | 101 | 9/5/98 |
64 | 102 | 9/8/98 |
74 | 103 | 9/8/98 |
Table 3: Boats B
bid | bname | color |
|---|---|---|
101 | Interlake | blue |
102 | Interlake | red |
103 | Clipper | green |
104 | Marine | red |
Which relational algebra query computes the names of sailors who have reserved all boats?
- A.
𝜌 (Tempsids, ( 𝜋 bid Reserves) / 𝜋 bid Boats) 𝜋 sname ((Tempsids) ⋈ Sailors)
- B.
𝜌 (Tempsids, ( 𝜋 sid, bid Reserves) / 𝜋 bid Boats) 𝜋 sname ((Tempsids) ⋈ Sailors)
- C.
𝜌 (Tempsids. ( 𝜋 sid Sailors) / 𝜋 bid Boats) 𝜋sname ((Tempsids) Sailors)
- D.
𝜌 (Tempsids, ( 𝜋 sid Reserves) /𝜋 bid Boats) 𝜋 sname ((Tempsids) ⋈ Boats)
Attempted by 82 students.
Show answer & explanation
Correct answer: B
Correct answer: the expression using π_{sid,bid}(Reserves) ÷ π_{bid}(Boats), followed by a join with Sailors and projection of sname.
To find sailors who reserved all boats, use the relational algebra division operator.
Project all sailor-boat reservation pairs:
π_{sid,bid}(Reserves).Project all boat ids:
π_{bid}(Boats).Divide them:
π_{sid,bid}(Reserves) ÷ π_{bid}(Boats). This returns the sids paired with every boat id in Boats.Join the resulting sids with Sailors and project names:
π_{sname}(Tempsids ⋈ Sailors).
After correcting the Reserves table, sailor 22 has reserved boats 101, 102, 103, and 104, so the sample data is consistent with the division logic.
A video solution is available for this question — log in and enroll to watch it.