Question

INTRO TO DATABASES

Consider the boat reservation system:

Sailors (sid, sname,rating, age)

Boats(bid, bname, color)

Reserves (sid, bid, day)

Formulate the following question in relational algebra using two different sequences of relational operators:

A. Find the colors of boats reserved by Lubber.

B. Find the sids of sailors older than 20 who have not reserved a red boat.

Answer 1

Please UPVOTE if it helps.

NOTE :

• Selection (σ) operator is used to select rows or tuples from relation/table.
• Projection (π) operator is used to select colums from table.
• The difference (-) of two relations R and S is the set of tuples that are in R but not in S
• The natural join (∞) of two relations R and S is a set of pairs of tuples, one from R and one from S, that agree on whatever attributes are common to the schemas of R and S.

A. Find the colors of boats reserved by Lubber.

Solution :  π_color((σ_{sname= ‘Lubber’} Sailor)∞ Reserves ∞ Boats ))

1. 1. Find all sailors who's name (sname) is "Lubber". [ (σ_{sname= ‘Lubber’} Sailor) ]
2. 2. Then find the boats which are reserved by "Lubber". [ (σ_{sname= ‘Lubber’} Sailor)∞ Reserves ∞ Boats ) ]
3. 3. Now,find colors of boat reserved by "Lubber".

B . Find the sids of sailors older than 20 who have not reserved a red boat.

Solution : π_sid (σ_age>20Sailors) – π_sid ((σ_{color=‘red’} Boats) ∞Reserves)

1. Find all sailors (sids) with age over 20. [ π_sid (σ_age>20Sailors) ]

2. Find all sailors (sids) who have reserved a red boat. [ π_sid ((σ_{color=‘red’} Boats) ∞Reserves)

3. Now, we can find sids of sailors older than 20 who have not reserved a red boat by taking difference between those people who's age is greater than 20 and those who have reserved the red boat.