Proposed solutions Project Assignment #1 (SQL) (a) Database creation:
create tablesailor
(sname CHAR(30), rating INTEGER) create tableboat
(bname CHAR (30), color CHAR (15), rating INTEGER) create tablereservation
(sname CHAR(30), bname CHAR (30), rday CHAR(15), starting INTEGER, ending INTEGER)
(b) Instance upload:
insert intosailor values(’Brutus’, 1); insert intosailor values(’Andy’, 8); insert intosailor values(’Horatio’, 7); insert intosailor values(’Rusty’, 8); insert intosailor values(’Bob’, 1);
insert intoboat values(’SpeedQueen’, ’white’, 9); insert intoboat values(’InterLake’, ’red’, 8); insert intoboat values(’Marine’, ’blue’, 7); insert intoboat values(’Bay’, ’red’, 3); insert intoreservation
values(’Andy’, ’Interlake’, ’Monday’, 10, 14); insert intoreservation
values(’Andy’, ’Marine’, ’Saturday’, 14, 16); insert intoreservation
values(’Andy’, ’Bay’, ’Wednesday’, 8, 12); insert intoreservation
values(’Rusty’, ’Bay’, ’Sunday’, 9, 12); insert intoreservation
values(’Rusty’, ’Interlake’, ’Wednesday’, 13, 20); insert intoreservation
values(’Rusty’, ’Interlake’, ’Monday’, 9, 11); insert intoreservation
values(’Andy’, ’Bay’, ’Wednesday’, 9, 10); insert intoreservation
(c) Instance listing: select*fromsailor; select* fromboat; select*fromreservation; (d) SQL queries:
1.
selectsname, bname fromsailor, boat
wheresailor.rating>= boat.rating
2.
(selectsname, count(bname) as number fromsailor, boat
wheresailor.rating>= boat.rating group bysname)
union
(selectsname, 0asnumber fromsailor
wheresnamenot in (selectsname fromsailor, boat
wheresailor.rating≥ boat.rating))
Note that Brutus and Bob are not qualified to sail any boat, so their count should be shown as zero.
3. (i) one using the MIN aggregate function, and selectsname
fromsailor where rating in
(selectMIN(rating) fromsailor)
(ii) another without using MIN. selects.sname
fromsailor s where not exists
(select* fromsailor
where rating<s.rating)
4.
selectsname fromreservation wheresname not in
(selectsname
fromreservation, boat
wherereservation.bname = boat.bname and boat.color<> ’red’)
Without the assumption that each boat has only one color, we would write:
selectsname fromreservation wheresname not in
(selectsnamefromreservation wherebname not in
(selectbname fromboat where color = ’red’))
5.
selects.sname fromsailor s wherenot exists
(select*
fromreservation r, boat b wherer.sname = s.sname and r.bname = b.bname and color = ’red’)
6. (i) with NOT IN tests; selectsname fromreservation where sname not in
(selectsname
fromreservation, boat b where b.color = ’red’ and sname not in
(selectsname fromreservation
wherebname = b.bname )) (ii) with NOT EXISTS tests;
selectr.sname fromreservation r where not exists
(select* fromboat b
where b.color = ’red’ and not exists
(select*
fromreservation
wheresname = r.sname
and reservation.bname = b.bname )) (iii) with COUNT aggregate functions.
selectr.sname fromreservation r where
(selectcount(*)fromboat b where b.color = ’red’
and (select count(*)fromreservation wheresname = r.sname
andbname = b.bname) = 0 ) = 0
alternatively, selectr.sname
fromreservation r, boat b where r.bname = b.bname and b.color = ’red’
group byr.sname
having count (distinct r.bname) =
(selectcount (distinct bname)fromboat wherecolor = ’red’)
7.
create viewunique as
selectdistinct sailor.sname, rating, bname fromsailor, reservation
wheresailor.sname = reservation.sname; selectbname, avgrating = avg(rating * 1.0) fromunique
group bybname
The reason for multiplying rating with 1.0 in avg(rating * 1.0) is to convert the rating to a real so that the average is not truncated to an integer. The role of the view unique is to provide the list of boats and sailors with their ratings who have reserved the boatwithout duplicates. This is to avoid counting the same sailor multiple times if the sailor has multiple reservations of the same boat.
For example, the following alternative does not avoid this prob-lem:
selectbname, avgrating = avg(rating) fromsailor, reservation
wheresailor.sname = reservation.sname group bybname
(e)
selectx.bname, x.rday,
x.sname as sname1, x.starting as start1, x.ending as end1, y.sname as sname2, y.starting as start2, y.ending as end2 fromreservation x, reservation y
wherex.bname = y.bname andx.rday = y.rday and
((x.starting = y.starting and x.ending = y.ending and x.sname<y.sname) or(x.starting = y.starting and x.ending >y.ending)
or(x.starting <y.starting and y.starting <x.ending))
Note that this lists only one tuple for every pair of conflicting reserva-tionsx, y. Indeed, suppose first the time intervals of the reservations are the same. Then the sailors of the two reservations should be dif-ferent (otherwisexand ywould be the same reservation). The double listing is avoided by requiring that x.sname<y.sname (this compares the strings). If the starting time of the two reservations are the same and the intervals are not equal then only one of them ends first, so requiring x.ending>y.ending avoids again the double listing. Finally, if the reservations have different starting times then only one starts first, so x.starting<y.starting avoids double listing.
(f) Updates in SQL.
1. updateboat
setcolor = ’xxx’ where color = ’blue’; updateboat
setcolor = ’blue’ wherecolor = ’red’; updateboat
setcolor = ’red’ wherecolor = ’xxx’
2.
delete fromsailor s wherenot exists (select* fromboat