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/queries computes/compute the name of sailors who have reserved boat 103 ?
\(Q1 𝜋 _{sname} (( 𝛿_{𝑏𝑖𝑑} = 103 Boats) ⋈ Sailors) \\ Q2 𝜋 _{sname} = ( 𝛿_{𝑏𝑖𝑑} = 103(Reserves ⋈ Sailors)) \\ Q3 𝜋 _{sname} = (( 𝛿_{𝑏𝑖𝑑} = 103 Reserves) ⋈ Sailors)\)
- A.
Both Q1 and Q3
- B.
Both Q2 and Q3
- C.
Only Q3
- D.
Only Q2
Attempted by 89 students.
Show answer & explanation
Correct answer: B
Answer: The queries that compute the names of sailors who reserved boat 103 are Q2 and Q3.
Why Q2 and Q3 are correct:
Q2 joins Reserves with Sailors first (matching sid) to attach sailor names to reservations, then selects those tuples with bid = 103, and finally projects the sailor name. This returns exactly the sailors who reserved boat 103.
Q3 selects reservations with bid = 103 from Reserves, then joins with Sailors (matching sid) and projects the sailor name. This also returns exactly the sailors who reserved boat 103.
Why Q1 is incorrect:
Q1 selects the boat with bid = 103 from Boats and then joins directly with Sailors. Boats and Sailors have no common attribute to join on, so that join becomes a Cartesian product between the selected boat row and all sailors. Projecting the sailor name then yields all sailors, not just those who reserved boat 103. The Reserves relation is required to connect boats to sailors.
Resulting sailor names:
Lubber (sid 31)
Horatio (sid 74)
A video solution is available for this question — log in and enroll to watch it.