COMP 2673
Homework 4 (Databases)
Spring, 2003
Due Friday, May 16, 2003
For information on using Oracle see:
Oracle Introductory Notes
For the Relational Algebra and SQL queries assume the following
relational schema:
Sailors(sid, fname, mid, lname, wjoin, age, rating, address, street, city, state, zip)
Boats(bid, bname, color, capacity)
Reserve_R(sid, bid, Rdate)
The meaning of most attributes should be self evident.
The following attribute meanings may not be as obvious:
- Sailors.wjoin: Date when the sailor joined the club
- Sailors.rating: Rating of the sailor skills, higher is better
- Boats.capactiy: Number of people a boat can hold
- Reserve_R.Rdate: Date of the reservation
Remember, when querying a relation you must preface the relation
name with leut to specify the relation belongs to user leut.
For example:
Select B.bname, B.bid
From leut.boats B
where B.bid < 10 ;
Note, for queries with dates, wjoin and Rdate, use the single quoted
date form such as:
select *
from reserve_r
where rdate = '09-oct-86'.
Problems
There are three groups of problems: Relational Algebra, SQL, and E/R modeling:
Relational Algebra
Turn in clearly written answers to the five following queries.
- Find the name of all blue boats.
- Find the last name and sailor id of all sailors under the
age of 25 that live in CO.
- Find the bname and capacity of all boats reserved on 15-Dec-97.
- Find the last name and rating of all sailors that have
reserved boat #113.
- Find the last name and rating of all sailors that have
reserved blue boats.
SQL
NOTE: none of the correct answers have more than 20 tuples in
the result, so don't be handing in pages and pages of output data!
Please hand in a listing of your five queries with the output
produced by each one.
- Find the name of all blue boats.
- Find the last name and sailor id of all sailors under the
age of 25 that live in CO.
- Find the bname and capacity of all boats reserved on 15-Dec-97.
- Find the last name and rating of all sailors that have
reserved boat #113.
- Find the last name and rating of all sailors that have
reserved blue boats.
E/R Modeling
Draw an E/R diagram for a database capturing the following information.
The American Association of Pirated Software wants to maintain
a database allowing easier location of pirated software.
Each song has associated with it the artist's name, song title, and
number of minutes. Songs are stored on many web sites each of which
has a site name (for example cs.du.edu), average load at the site,
and "safety probability", which is the probabiliity that
downloading music from the site will result in you being busted for pirating
software. Your database needs to keep track of which
songs are located at which sites and for how long they have been on that site.