COMP 2673
Homework 4 (Databases)
solutions

Relational Algebra questions

1. π_Boats.bnameσ_{Boats.bname='blue'}(Boats)
2. π_{Sailors.lname ^ Sailors.sid}σ_{Sailors.age<25 ∧ Sailors.state = 'CO'}(Sailors)
3. π_{Boats.bname ^ Boats.capacity}σ_{Boats.bid=Reserve_r.bid ∧ Reserve.rdate='15-Dec-97'}(Boats × Reserve)
4. π_{Sailors.lname ^ S.rating}σ_{Reserve.bid=113 ∧ Sailors.sid = Resere.sid}(Sailors × Reserve)
5. π_{Sailors.lname ^ Sailors.rating}σ_{Boats.color='blue' ∧ Boats.bid = Reserve.bid ∧ Sailors.sid = Reserve.sid}(Sailors × Boats × Reserve)

SQL questions

1. select B.bname
   from leut.boats B
   where B.color = 'Blue' ;
   
   output:
   BNAME
   -------------------------
   Mackerel-1
   Mackerel-37
   Monkfish-11
   Haddock-13
   Lungfish-32
   Sole-7
   Pirahna-13
   Mackerel-29
   Shrimp-5
   
   9 rows selected.
   

2. select S.lname, S.sid
   from leut.sailors S
   where S.age < 25 and S.state = 'CO' ;
   
   output:
   LNAME                            SID
   ------------------------- ----------
   Mytton                          1155
   

3. select B.bname, B.capacity
   from leut.boats B, leut.reserve_r R
   where B.bid = R.bid and R.rdate = '15-Dec-97' ;
   
   output:
   BNAME                       CAPACITY
   ------------------------- ----------
   Lobster-40                         3
   Goldfish-4                         3
   

4. select S.lname, S.rating
   from leut.sailors S, leut.reserve_r R
   where R.bid = 113 and S.sid = R.sid ;
   
   output:
   LNAME                         RATING
   ------------------------- ----------
   Barr                              83
   

5. select S.lname, S.rating
   from leut.sailors S, leut.reserve_r R, leut.boats B
   where B.color = 'Blue' and B.bid = R.bid and S.sid = R.sid ;
   
   output:
   LNAME                         RATING
   ------------------------- ----------
   Mills                             37
   Ball                              48
   Glenister                         28
   McCartney                         42
   Strecker                          58
   Corbett                           48
   Barnett                           87
   Coincoin                          40
   Loughlin                          86
   Yang                              79
   Noffsinger                        68
   
   LNAME                         RATING
   ------------------------- ----------
   Schnitzer                         44
   Amos                              92
   Freeds                            70
   
   14 rows selected.
   

E/R Modeling