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 red boat ?
Q1: π sname ((δ color = red Boats) ⋈ Reserves ⋈ Sailors)
Q2: π sname (π sid ((π bid δ color = red Boats) ⋈ Reserves) ⋈ Sailors)
Q3: π sname ((δ color = red Reserves) Boats ⋈ Sailors)
- A.
Both Q1 and Q2
- B.
Both Q2 and Q3
- C.
Only Q1
- D.
Only Q2
Attempted by 83 students.
Show answer & explanation
Correct answer: A
Answer: Only the query that filters Boats by color = red and then joins with Reserves and Sailors computes the correct sailor names.
Why the correct query works: Filter Boats for color = red, join the resulting boats with Reserves on bid, then join with Sailors on sid; finally project sname. This preserves the necessary join attributes (bid and sid) so you can obtain sailor names who reserved red boats.
Why the query that projects only bid before joining fails: Projecting only bid values removes sid, so there is no attribute left to join to Sailors to get sname. Losing the sid breaks the link needed to retrieve sailor names.
Why applying color selection to Reserves is wrong: Reserves has no color attribute; a selection on color cannot be performed on Reserves and is therefore invalid.
Result on the given data (sailors who reserved red boats):
Dustin (sid 22)
Lubber (sid 31)
Horatio (sid 64)
A video solution is available for this question — log in and enroll to watch it.