Read the following and Answer the questions: Consider the relational
Duration: 7 min
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 79 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.