Question

Specify this query in Relational Algebra using the given database schema: Find the sid's of sailors who made at least 3 reservations.

Saliors(sid, sname, rating, age)

Boats (bid, name,color)

Reserves(sid, bid, date)

Is this correct:

∏sname((O_{COUNT(sid)>=3}Reserves))⋈Sailors)

Answer 1

Answer :

_sid (_{count(sid)>=3} (Sailors Reserves) ) is the specification for the given query.

Explanation :

• We have to all the sids. Therefore we have used projection. ()
• sids should be selected from sailors.So, we have specified Sailors first in the Join.
• sids should be from Sailors such that sid should have at least 3 Reserves i.e. the count of sids in Reserves should be greater than or equal to 3. (_{count(sid)>=3} )
• But sids should be common in both Sailors and Reserves. Therefore we write join as " Sailors Reserves ".

We can also write it as _sid ((_{count(sid)>=3} (Reserves)) Sailors )

• We have to all the sids. Therefore we have used projection. ()
• sids should have at least 3 Reserves i.e. the count of sids in Reserves should be greater than or equal to 3. (_{count(sid)>=3}  (Reserves) )
• Then we join with Sailors because we want sids that are common in the above select operation so performed and Sailors. ((_{count(sid)>=3} (Reserves)) Sailors )

Explanation for why your answer is wrong :

∏sname((O_{COUNT(sid)>=3}Reserves))⋈Sailors) is wrong because it prints all the snames rather than sids.