CIS 365 Business Database Concepts Assignment 2 -

Normalization / Logical Design (54 points)

The following assignment is to be drawn using any software drawing package you have access to (PowerPoint, Gliffy, etc.). Identify and label all functional dependencies and primary keys in each FD diagram. Make sure all work is clear and easy to read. Professional quality is expected. Use the methods shown in class for drawing both the dependency diagrams and the logical database schema.

1. (18) Normalize the following relation to 3rd normal form. Identify & draw the complete FDs for all normal forms through to 3NF.

Application Summary: clients rent properties from their owners for a determined amount of time. This is the entity taken from the ERD, which shows the client who rented the property (along with other info about the rental that must be recorded). Primary keys for the entity are identified on the diagram below. (Do not add or delete any attributes).

PAddress propertys address OName owners name OPhone owners phone

a) Identify all full (above the diagram), partial, and transitive dependencies (below the diagram) and label them.

b) Improve the relations to 3NF. Show and label all FDs and all dependency arrows. Identify all primary keys for each FD.

c) Draw the relational schema for the final database table structure. Name each relation and identify all primary and foreign keys.

2. (15) Normalize the following relation to 3rd normal form. Identify & draw the complete FDs for all normal forms through to 3NF.

Application Summary: this data shows scheduled surgeries for patients. The surgery requires one surgeon who has a nurse to assist. Nurses will assist on many types of surgeries. Surgeons and nurses may perform more than one surgery per day, but a patient can have only one surgery per day. The table below shows sample data. (Do not add or delete any attributes)

a) Identify all full (above the diagram), partial, and transitive dependencies (below the diagram) and label them.

b) Improve the relations to 3NF. Show and label all FDs and all dependency arrows. Identify all primary keys for each FD.

c) Draw the relational schema for the final database table structure. Name each relation and identify all primary and foreign keys.

3. (21) Normalize the following relation to 3rd normal form. Identify & draw the complete FDs for all normal forms through to 3NF.

Application Summary: customers rent classic one of a kind movies from an online video rental service. CustCC customer credit card

Studio where the movie was made

Director of the movie

a) Identify all full (above the diagram), partial, and transitive dependencies (below the diagram) and label them.

b) Improve the relations to 3NF. Show and label all FDs and all dependency arrows. Identify all primary keys for each FD.

c) Draw the relational schema for the final database table structure. Name each relation and identify all primary and foreign keys.

Client# | Property# | Owner# | CName | PAddress | RentStarDatel RentEndDate | RentAmt! OName | OPhone Client# | Property# | Owner# | CName | PAddress | RentStarDatel RentEndDate | RentAmt! OName | OPhone