DBMS (2nd Ed.) By RaghuramKrishnan #page no 104 and 105 #Q-5 and Q7

Question

Find the names of sailors who have reserved a red or a green boat.???

&

 Find the names of sailors who have reserved at least two boats?

Answer 1

Relational algebra query for question 1 :

π_sname (S3 natural join (R2  natural join σ_{color = "Red" V color = "Green"}(B1)).

Here first we extract those bid's whose color is red or green then do natural join with R2 to find those sids which have the desired bids found in the earlier step then joining with S3 to get the corresponding names of the extracted sids and finally displaying the names of sailors using projection..

Relational algebra query for question 2 :

For this query to perform we need 2 instances of the table "reserves" so as to find an sid instance which has two different bids..Hence here we need rename operation as well..Then doing natural join with sailors to get the names..Hence the query can be written as : 

π_sname(S3 natural join   π_sid(R2  conditional join(condition is sid = S ^ bid != B)   ρ_S,B,D(R2))) 

Comments

@Habibkhan sir Can u please explain Question 2

---

@Rishi yadav first we have created another instance of table R2 using rename operator which renames sid to S , bid to B and day to D..Having done that we apply conditional join between original R2 and the renamed R2 and look for the sid which is mapped to at least 2 bids..This can be ensured by the condition which is mentioned in the answer..

Likewise for at least 3 scenario we need 3 instances of the same table and C(3,2) = 3 conditions for writing in relational algebra..

Having done that we have found the desired sids so have projected them and then performed natural join with S3 (sailors) table which have sid attribute in common and finally projected sname to get the desired names of the sailors corresponding to the sid which we have found..

Answer 2

since there are two different tables one for sailors and another one for boat. Both tables are linked via Reserves table. so for the question:: Find the names of sailors who have reserved a red or a green boat.???  

we need to join the table Reserves and Boats and Sailors so the query to get the result should be

SELECT SNAME FROM SAILORS  S , BOATS  B , RESERVES  R   WHERE   R.SID = S.SID AND R.BID = B.BID AND

B.COLOR = ' red ' OR B.COLOR = ' green ' ;

p.s. comment below if you have still any doubt
and for the second question we solve this with the help of group by and subquery

Question.))  Find the names of sailors who have reserved at least two boats?

SELECT SNAME FROM SAILORS WHERE SID IN((SELECT SID FROM RESERVES GROUP BY SID HAVING COUNT(*)>=2));