DB HW/project

The purpose of this workshop is to review Codd's rules for normalizing a database, to learn how to identify various normal forms, and to gain practice in applying rules for normalization.

Preparation: Review Codd's rules for normalization.

Movie Madness has been in business for 50 years renting movies to its customers. They began to conduct business long ago when tape drives were state of the art, and have managed to remain profitable over the years despite antiquated equipment and data storage practices. In the past knowledge of which movies were available and where they were stored in the company's warehouse existed primarily in the heads of the owner, his grandson, and the grandson's nephew. A month ago, the grandson and grandson's nephew left the company to spend a few years traveling the world with their families, leaving technology and movies behind. The owner, seeing the others enjoying their freedom, recently retired to a cabin in Montana, leaving the business to his 21-year-old granddaughter, Angela, who has just graduated with her degree in computer science.

Angela would like to reorganize the company's data to provide faster response, allow users to search online for movies by various artists and in various genres, and make it easier to identify both current and historic trends in movie rentals. She needs to do this quickly, because the only records she has are on tape, organized by customer rental, and she is finding it difficult to manage customer requests in a timely manner. The structure of the records on the tape is as follows – no other records exist:

customer_name1, customer_phone1, customer_address1, customer information

rental_date1, charge1, rental date + charge for all movies rented on that date

movie1.1, actor1.1.1, actor1.1.2, ... actor1.1.n, producer1.1, all data for an individual movie rented

movie_studio1.1, movie_studio_address1.1,

movie1.2, actor1.2.1, actor1.2.2, ... actor1.2.n, producer1.2, another movie rented the same date

movie_studio1.2, movie_studio_address1.2,

..., more movies rented the same date

rental_date2, charge2

movie2.1, actor2.1.1, actor2.1.2, ... actor2.1.n, producer2.1,

movie_studio2.1, movie_studio_address2.1,

movie2.2, actor2.2.1, actor2.2.2, ... actor2.2.n, producer2.2,

movie_studio2.2, movie_studio_address2.2, .

..,

total revenue from customer1 total revenue received from this customer over all rentals

customer_name2, customer_phone2, customer_address2,

rental_date1,

movie1.1, actor1.1.1, actor1.1.2, ... actor1.1.n, producer1.1,

movie_studio1.1, movie_studio_address1.1,

movie1.2, actor1.2.1, actor1.2.2, ... actor1.2.n, producer1.2,

movie_studio1.2, movie_studio_address1.2,

...,

rental_date2,

movie2.1, actor2.1.1, actor2.1.2, ... actor2.1.n, producer2.1,

movie_studio2.1, movie_studio_address2.1,

movie2.2, actor2.2.1, actor2.2.2, ... actor2.2.n, producer2.2,

movie_studio2.2, movie_studio_address2.2,

...,

total revenue from customer2

... more customer records

Angela has discovered several inconsistencies in the data. She wants to understand where the problems lie and to restructure the data so that she can continue to run a profitable business. Follow each step below as specified to analyze the data and gradually transform it into a normalized data set. Do not skip any steps or take any shortcuts.

1. Provide (make up) some sample data for this database, following the structure above.

2. Identify potential problems with the current record structure, giving examples from answer 1.

3. In what normal form is this database? How do you know this?

4. Convert the data in this table to first normal form. Show the resulting record structure.

5. Convert the data in this table to second normal form. Show the resulting record structure.

6. Convert the data in this table to third normal form. Show the resulting record structure.

7. Draw an ERD for the record structure you developed in problem 6.