CECS 323 HOMEWORK: NORMALIZATION
OBJECTIVE: Give you some firsthand experience converting an existing data table into a normalized design.
INTRODUCTION: You land a job with a small movie rental store. They have been managing their rental records by means of a small
spreadsheet, but they realize that they have occasionally entered some conflicting information into their database. Just
last week they had one record that claimed that the Movie Logan was a comedy and the rest of the records for that
movie portrayed it as an action film. You ask them for some sample data, and they provide you with this:
Customer
Customer Rental Release Invoice
Physical Movies Rented Salutation Category Studio/Location
Name Date Year Cost
Address
Pirates of the Caribbean: Walt Disney
st The Curse of the Black Action, 2003, Pictures/California,
Janet Jones 1 St. Plot #4 5/15/2019 Ms.
Pearl| Clash of the Action 2010 MGM/Florida
Titans
Universal Pictures,
Forgetting Sarah
Romance, 2012, Lionsgate/Michigan,
Marshal| Daddy’s Little
Robert Phil 3rd St. 34 6/22/2019 Mr. Romance, 2007, Paramount $5.83
Girls| The Hunt for Red
Action 1990 Pictures/California
October
Robert Phil 28 5th Ave 8/12/2019 Clash of the Titans Mr. Action 2010 MGM/Florida $3.50
Upon questioning the owner, you find:
As small as this video store is, they still have customers like Robert Phil who are two different people, but just
happen to have the same name.
The video store computes the invoice cost when the customer turns in the videos. That cost depends on how
long the customer has had the video. If you see no invoice cost, that means that the customer has not returned
one or more of the videos. The store would like to be able to capture the date on which the customer returns
each video.
The Movies Rented and the Category columns parallel each other. For each movie in the Movies Rented
column, the corresponding value in the Category column represents the type of movie. For instance, since The
528110451.docx 11/28/2020 11:20:00 AM 1
CECS 323 HOMEWORK: NORMALIZATION
Hunt for Red October is the third entry in that value for Movies Rented, that means that it is an Action film, since
it is the third value in the corresponding Category entry.
In the Movies column: the | separates the movies.
In the Category column: the , separates the category for the movies
In the Studio/Location column: the , separates the studio for the movies and the / separates the Studio from its
location.
PROCEDURE: Using the process that we talked about in class, normalize the above table structure one step at a time until you get it
into 3rd normal form.
WHAT TO TURN IN:
A UML model of what this data looks like in first normal form.
A UML model of what this data looks like in 2 nd normal form.
A UML model of what this data looks like in 3 rd normal form.
The relation scheme of your 3rd normal form model.
Description of the classes in 3rd normal form.
528110451.docx 11/28/2020 11:20:00 AM 2
