1
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.5 |
| 64 | Horatio | 7 | 35.5 |
| 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 of the following relational algebra query computes the Sid's of sailors with age over $20$ who have not reserved a red boat?
Solution
We need sailors whose age is over $20$ and who have not reserved a red boat.
First find sailors with age over $20$.
From Sailors table:
$\pi_{sid}(\sigma_{age>20}(Sailors))=\{22,29,31,32,58,64,74,85,95\}$
Now find red boats.
From Boats table, red boats are:
$Bid=102$ and $Bid=104$
Now find sailors who reserved red boats.
From Reserves table:
$Sid=22$ reserved $Bid=102$ and $Bid=104$
$Sid=31$ reserved $Bid=102$ and $Bid=104$
$Sid=64$ reserved $Bid=102$
So,
$\pi_{sid}((\sigma_{color='red'}(Boats))\bowtie Reserves\bowtie Sailors)=\{22,31,64\}$
Now subtract sailors who reserved red boats from sailors whose age is over $20$.
$\{22,29,31,32,58,64,74,85,95\}-\{22,31,64\}$
$=\{29,32,58,74,85,95\}$
So, the correct relational algebra expression must be:
$\pi_{sid}(\sigma_{age>20}(Sailors))-\pi_{sid}((\sigma_{color='red'}(Boats))\bowtie Reserves\bowtie Sailors)$