Computer Science 455
Exercise Set #5
Due: As noted
1. (20 pts.) Consider the following form:
|
|
|
|
|
|
|
|
|
|
|
Riverside Video
Rentals |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Customer Name |
Hercule Poirot |
|
Clerk Number |
A123 |
|
Customer Number |
C125 |
|
|
Clerk Name |
A. Christie |
|
Customer Address |
812 Jefferson St |
|
|
|
|
|
|
|
Moscow |
Idaho |
|
Date |
1-Apr-02 |
|
|
|
|
(208) 882-1234 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
DVD # |
DVD Name |
|
|
Rating |
Daily Charge |
|
Due |
|
|
|
|
|
|
|
|
|
|
D324 |
Attack of the Killer Tomatoes |
|
|
PG |
$1.00 |
|
5-Apr-02 |
|
D792 |
Bye-bye Braverman |
|
|
G |
$1.00 |
|
12-Apr-02 |
|
D492 |
Red Lanterns |
|
|
R |
$1.50 |
|
12-Apr-02 |
|
|
|
|
|
|
|
|
|
- Write the fields in this form as an unnormalized table. Identify
functional dependencies (assume that RATING and DAILY CHARGE are
functions of DVD NAME and that DVD NAME is a function of DVD #, noting
that there may be several DVD #'s for a given DVD NAME) and the key for the
full relation. Clearly identify repeating fields.
- Place the form into first normal form by breaking out repeating
fields. Clearly list the relational schema thus created.
- Note violations of second normal form (clearly indicating them).
Resolve violations of 2NF by decomposing the tables created in step 2.
No not take care of 3NF violations at this stage.
- Note violations of third normal form in the tables created in step
3(clearly indicating them). Resolve these violations by decomposing
the tables created in step 3.
- Construct a final relational schema for this form, clearly indicating
primary and foreign keys.
- List all determinants and check that the relations constructed in step 5
are in BCNF.
Due: Tuesday, April 9, in class
2. More SQL (20 pts.)
On pages 185 - 186 work the following problems as
SQL exercises (they are expressed as questions in the relational
algebra - ignore this for now):
Problem 16 (i.e., 6.16), 22, 24 - 27, 29, 30, 33 - 36, 39 - 46 (20
problems in all)
Place the results in a file called SQL02.SQL in your handins folder on
Plato. Please begin each file with SET ECHO ON, and please do not
use the exit command.
Due: Friday, April 12
Any questions? Please let me know. Many thanks!