Read the following and Answer the questions: Consider the relational schema of…
2022
Read the following and Answer the questions:
Consider the relational schema of sailors S, Reserves B and Boats B.
Table 1: Sailors S
\(\begin{array}{|l|l|l|} \hline Sid & Sname & Ratting & Age \\ \hline 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 \\ \hline \end{array}\)
Table 2: Reserves R
\(\begin{array}{|l|l|l|} \hline sid & Bid & day \\ \hline 22 & 101 & 10 / 1098 \\ 22 & 108 & 101098 \\ 22 & 108 & 101898 \\ 22 & 104 & 10 / 798 \\ 31 & 108 & 11 / 1098 \\ 31 & 103 & 11 / 699 \\ 31 & 104 & 11 / 298 \\ 64 & 101 & 9558 \\ 64 & 102 & 28858 \\ 74 & 103 & 98958 \\ \hline \end{array}\)
Table 3: Boats B
\(\begin{array}{|l|l|l|} \hline Bid & Bname & Color \\ \hline 101 & Interlake & blue \\ 102 & Interlake & red \\ 103 & Clipper & green \\ 104 & Marine & red \\ \hline \end{array}\)
Which of the following relational algebra query computes the names of sailors who have reserved a red and a green boat ?
- A.
\(𝜌 (Tempred, 𝜋 _{sid} ((𝛿_{color} = \ _{‘𝑟𝑒𝑑′} Boats ) ⋈ Reserves)), 𝜌 (Tempgreen. 𝜋 _{sid} ((𝛿_{color} = \ _{‘𝑔𝑟𝑒𝑒𝑛′} \\ Boats ) ⋈ Reserves)), 𝜋 _{sname} (Tempred ∩ Tempgreen) ⋈ Sailors \) - B.
\(𝜌( Tempboats \ 2, (𝛿_ {color} = \ _{‘𝑟𝑒𝑑′} Boats )∩(𝛿 color = \ _{‘𝑔𝑟𝑒𝑒𝑛′} Boats ))𝜋 _{sname} (Tempboats 2 ⋈ Reserves) \\ \) - C.
\(𝜋 sname ((𝛿_{color} = \ _{ ′𝑟𝑒𝑑′} Boats )∩(𝛿_{color} = \ _{ ′𝑔𝑟𝑒𝑒𝑛′} Boats ) (Tempboats ⋈ Sailors ⋈ Reserves)) \) - D.
\(𝜌( Tempboats \ 2, (𝛿 _{𝑐𝑜𝑙𝑜𝑟} = \ _{𝑟𝑒𝑑} Boats 𝛿 _{𝑐𝑜𝑙𝑜𝑟} =\ _{ ‘𝑔𝑟𝑒𝑒𝑛′} Boats )𝜋 _{sname} (Tempboats \ 2 ⋈ sailors)\)
Attempted by 80 students.
Show answer & explanation
Correct answer: A
Correct approach:
Find sailor IDs who reserved a red boat: π_sid(Reserves ⋈ σ_color='red'(Boats))
Find sailor IDs who reserved a green boat: π_sid(Reserves ⋈ σ_color='green'(Boats))
Intersect these two sets of sailor IDs to get sailors who reserved both colors: π_sid(Reserves ⋈ σ_color='red'(Boats)) ∩ π_sid(Reserves ⋈ σ_color='green'(Boats))
Join the resulting sailor IDs with Sailors and project the names: π_sname( Sailors ⋈ (π_sid(Reserves ⋈ σ_color='red'(Boats)) ∩ π_sid(Reserves ⋈ σ_color='green'(Boats))) )
Final relational-algebra expression:
π_sname( Sailors ⋈ (π_sid(Reserves ⋈ σ_color='red'(Boats)) ∩ π_sid(Reserves ⋈ σ_color='green'(Boats))) )
Applied to the given data, this returns the single sailor name: Lubber.
A video solution is available for this question — log in and enroll to watch it.