Database Design...

Database Design

We present the main entity types of this database. For each entity type, we provide some of the corresponding attributes. Use this information in order to: (a) Build an Enhanced E-R diagram; (b) Transform the Enhanced E-R diagram to a relational database. identify the primary key(s) and the foreign key(s) for each relation. Draw the relational integrality constraints; (c) For each of the. relations created, indicate its normal form. lf the relation is not in the 3NF, decompose it into 3NF relations.

1. Album. The main attributes are identification number, name, songs, members, release date, warehouse location, quantity available, category, etc.

2. Customer. The main attributes are identification number, name, address, telephone number, e-mail, credit card information, music preferences, password, balance, etc.

3. Order. The main attributes are identification number, order date, description, expected delivery date, actual delivery date, payment type, payment amount, payment date, etc.

4. Supplier. The main attributes are identification number, name, address, telephone number, current balance, etc.

Note the following: (a) Music preferences is a multi-valued attribute of the entity type Customer, (b) Whenever an album is purchased from a supplier, the following is recorded: purchase date, purchase price, guantity purchased. (c) when an album is ordered by a customer, the price is recorded. The price of an album changes with time. (d) The attribute Songs of the entity type Album is a. multi-valued attribute. (e) Members is a multi-valued attribute of the entity type Album.

Entity Relationship Diagrams

The ERD already in 3NF, because there is no partial and transitive dependencies.

Entity Relationship Diagrams - Mapping