Homework 1
CS422,
Summer 2006
Due: Thursday, July 6
Please upload your solutions using CSNS.
Note that file
uploading will be disabled automatically after 11:59PM
of the due date, so please turn in your work on time.
[Reading]
- Textbook Chapter 2 and 3.1-3.5.
[Notown Records Database]
Notown Records has decided to store information about musicians who
perform on its albums, as well as other company data, in a database.
- Each musician that records at Notown has an SSN, a name, an
address, and a phone number. Poorly paid musicians often share the same
address, and no address has more than one phone.
- Each instrument used in songs recorded at Notown has a name (e.g.
guitar, synthesizer, flute) and a musical key (e.g. C, B-flat, E-flat).
- Each album recorded on the Notown label has a title, a copyright
date, a format (e.g. CD or MC), and an album identifier.
- Each song recorded at Notown has a title and an author.
- Each musician may play several instruments, and a given
instrument may be played by several musicians.
- Each album has a number of songs on it, but no song may appear on
more than one album.
- Each song is performed by one or more musicians, and a
musician may perform a number of songs.
- Each album has exactly one musician who acts as its producer. A
musician may produce several albums.
1. (35pt) Draw the ER diagram for the database design.
2. (15pt) Convert the ER diagram to relations.
[Surveys Database]
Design a database to store surveys and the answers to the surveys. A
survey consists of a title, a description, and a number of questions.
Each question is of one of the following types:
- Single-Choice Questions.
A single-choice question consists of a question statement and a number
of choices, and a user may choose only one of the choices as the answer.
- Multiple-Choice Questions.
A multiple-choice question is similar to a single-choice question,
except that a user may choose more than one choice.
- Single-Answer Questions.
A single-answer question requires a user to write a short answer, which
may be a sentence or a short paragraph. Note that unlike for
single-choice questions, the user must write up the answer instead of
choosing it from a set of given choices.
- Multiple-Answer Questions.
A multiple-answer question is similar to a single-answer question,
except that a user may give multiple answers.
- Rating Questions. A
rating question lets a user to choose an integer number between a
minimal rating and a maximal rating, both of which are also integers.
Note that although rating questions seem to be similar to
single-choice questions, you may have to handle them differently,
because the answers to rating questions need to be treated as numerical
values in order to allow operations such as calculating average ratings.
A user may take more than one survey. For each user, the database
stores the name, age, ethnicity, and annual income of the user, and of
course, the user's answers to all the survey questions.
3. (35pt) Draw the ER diagram for the database design.
4. (15pt) Convert the ER diagram to relations.
[Functional Dependency]
5. (20pt) Given relation R(A, B, C,
D, E) with FD's AB -> C, BC
-> D, D -> A,
and E -> B.
(a) Compute {A,E}+
(b) Find all keys of R
(c) List a minimal basis for the FD's which hold for R'(A,B,D)