Relational Algebra & CalculusExercises1) Find names of sailors who’ve reserved boat #1032) Find names of sailors who’ve reserved a red boat3) Find sailors who’ve reserved a red or a green boat4) Find sailors who’ve reserved a red and a green boatSlide 75) Find the names of sailors who’ve reserved all boats1Relational Algebra & CalculusChapter 4, Part A (Relational Algebra)Query Formulation Exercise2ExercisesGiven relational schema:Sailors (sid, sname, rating, age)Reservation (sid, bid, date)Boats (bid, bname, color)1) Find names of sailors who’ve reserved boat #1032) Find names of sailors who’ve reserved a red boat3) Find sailors who’ve reserved a red or a green boat4) Find sailors who’ve reserved a red and a green boat5) Find the names of sailors who’ve reserved all boats31) Find names of sailors who’ve reserved boat #103Solution 1:  snamebidserves Sailors(( Re ) )103 Solution 2: ( , Re )Temp servesbid1103( , )Temp Temp Sailors2 1 snameTemp( )2 Solution 3: snamebidserves Sailors( (Re ))10342) Find names of sailors who’ve reserved a red boatBoats (bid, bname, color)Information about boat color only available in Boats; so need an extra join: snamecolor redBoats serves Sailors((' ') Re )  A more efficient solution -- why more efficient?   snamesid bid color redBoats s Sailors( ((' ') Re ) ) A query optimizer can find this, given the first solution!53) Find sailors who’ve reserved a red or a green boatCan identify all red or green boats, then find sailors who’ve reserved one of these boats: ( , (' ' ' '))Tempboatscolor red color greenBoats  snameT empboats ser ves Sailors( Re )  Can also define Tempboats using union! (How?) What happens if is replaced by in this query?64) Find sailors who’ve reserved a red and a green boatPrevious approach won’t work! Why? Must identify sailors who’ve reserved red boats, sailors who’ve reserved green boats, then find the intersection (note that sid is a key for Sailors):74) Find sailors who’ve reserved a red and a green boatPrevious approach won’t work! Why? Must identify sailors who’ve reserved red boats, sailors who’ve reserved green boats, then find the intersection (note that sid is a key for Sailors):  ( , ((' ') Re ))Tempredsid color redBoats serves ))(( SailorsTempgreenTempredsna me   ( , ((' ') Re ))Tempgreensid color greenBoats serves85) Find the names of sailors who’ve reserved all boatsUses division; schemas of the input relations to division (/) must be carefully chosen:  ( , (,Re ) / ( ))Tempsidssid bi dservesbidB oatssnameTempsids Sailors( ) To find sailors who’ve reserved all ‘Interlake’ boats:/ (' ') bid bname Int erla