FINAL
CS122, Spring 2004
Please print out a hard copy of your solutions and turn it in
at
the end of the exam. Do not forget to put your name on the hard copy.
Before turning in your queries, try to run them in MS Access and make
sure they work correctly. If
your query contains syntax errors, you receive an automatic 50% credit
deduction for that problem, no matter how "close" the query seem to be
correct.
[Normalization] Suppose a student registration database has a table Grades for student grades:
Grades: <studentID, lastName, firstName, courseId, courseTitle, sectionNumber, semester, year, units, meetingTime, meetingRoom, grade>
Assumptions:
- courseId is a unique identifier for a course, e.g. CS122
is the id for the course "Using Relational Databases and SQL", and no
other course has the same id.
- sectionNumer is not a unique identifier for a
section. It is only used to distinguish multiple sections of the same
course offered in the same semester. For example, we may have two CS122
sections with sectionNumber 1 and 2, respectively.
- units for a course does not change. For example, CS122 is a 4-unit course, no matter when it is offered.
- You may add addtional assumptions about the data, as long as your assumptions are clearly stated and reasonable.
1. (15pt) What are the key(s) of the table? If there are more than one key in
the table, choose one of them as your primary key, and list all partial
dependencies and transitive dependencies.
2. (10pt) Normalize the table to 2NF. The answers to this question and the next question should be in the form:
table_name: <field_name, ...>, primary key (field_name,...)
...
3. (10pt) Normalize the table to 3NF.
4. (15pt) Write CREATE TABLE statements for the 3NF tables with proper primary key and foreign key constraints.
Use Lyric2K.mdb for the following questions:
[Queries]
5. (5pt) Use a correlated subquery to list all genres that do not have recorded titles.
6. (5pt) Produces a single list of studio names with their web addresses and
artist names with their web addresses. Do not include any studio or
artist who does not have a web address. For example:
Name
|
Web Address |
| 21 West Elm |
www.21westelm.com |
| Jose MacArthur |
www.josemacarthur.com |
| Lone Star Recording |
www.lsrecords.com |
| MakeTrax |
www.maketrax.com |
| Pacific Rim |
www.pacrim.org |
| Sonata |
www.classical.com/sonata |
| The Neurotics |
www.theneurotics.com |
| Today |
www.today.com |
7. (10pt) List each genre from the genre table and the total length
in minutes of all tracks recorded for that genre. If a genre does not
have a recorded title, or the in the case of pop, the
information about the track lengths is not available in the database,
list the recorded length for that genre as 0. Use proper column
headings. For example:
| Genre |
Recorded Length in Minutes |
| alternative |
108.95 |
| classical |
44.3666666666667 |
| hip-hop |
0 |
| jazz |
41.2666666666667 |
| metal |
35.4833333333333 |
| pop |
0 |
| R&B |
0 |
| rap |
0 |
8. (5pt) List the names of all salespersons whose supervisor is supervised by no one.
9. (5p) Each member is given his or her salesperson as a primary
contact name and also the name of that salesperon's supervisor as a
secondary contact name. Produce a list of member names and the primary
and secondary contacts for each member.
[Updates]
10. (5pt) Add a new artist to the Artists table with the following information:
| ArtistID |
ArtistName
|
City
|
Region
|
Country
|
WebAddress
|
EntryDate
|
LeadSource
|
12
|
November
|
New Orleans
|
LA
|
USA
|
|
(today)
|
Directmail
|
Use a database function to get today's date.
11. (5pt) Lyric Music has decided to set up a web page for every artist who
does not have a web site. The web address will be www.lyricmusic.com/
followed by the artist ID. Update the Artists table accordingly.
12. (10pt) Due to some contract disputes, Lyric Music must remove
all MP3 and Real Audio downloads for the tracks from the title Time Flies. Update the Tracks table accordingly.