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)