Question

# Specify this query in Relational Algebra using the given database schema: Find the sid's of sailors...

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((OCOUNT(sid)>=3Reserves))⋈Sailors)

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 )

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