Database Design

A music database stores details of a personal music library, and could be used to manage your MP3, CD, or vinyl collection. Draw an ER diagram from the list of requirements for this database given below.

• The collection consists of albums

• An album is made by exactly one artist

• An artist makes one or more albums

• An album contains one or more tracks

• Artists, albums, and tracks each have a name and a unique identifier

• Each track is on exactly one album

• Each track has a time length, measured in seconds

For more practice questions, check the exercise section of the chapter titled "Data Modeling Using the Entity–Relationship (ER) Model" from the book.

An organization depends on a number of different types of persons for its successful operation. The organization is interested in the following for all of these persons: SSN, Name, Address, and Telephone. A person may have multiple telephone numbers. Three types of persons are of greatest interest: employees, volunteers, and donors. Employees only have a date hired attribute, and volunteers only have a skill attribute. Donors only have a relationship (named Donates) with an item that has a number and name. A donor must have donated one or more items, and an item may have no donors, or one or many donors.

There are persons other than employees, volunteers, and donors who are of interest to the organization, so that a person need not belong to any of these three groups. On the other hand, at a given time a person may belong to two or more of these groups (for example, employees and donors).

Draw an EER diagram based on the requirements above. Write down the assumptions (if any) you make in your answer.

Consider the following relation for published books:

BOOK (Book_title, Author_name, Book_type, List_price, Author_affil, Publisher)

Book_title and Author_name make up the primary key. Author_affil refers to the affiliation of author. Suppose the following dependencies exist:

Book_title → Publisher, Book_type
Book_type → List_price
Author_name → Author_affil

1. What normal form is the relation in? Explain your answer.

2. Apply normalization until you cannot decompose the relations further. State the reasons behind each decomposition.

Question: Assignment on SQL