Homework # 1 – Data Normalization

Create 3rd Normal Form Schemas for the business documents on the following pages. Your final
3NF column should show entities, attributes and primary keys for all the data appearing in
these documents.
Submission
You should present your finished results in a “Spreadsheet” format as provided in the example
below that we looked at in class. Please save your spreadsheet as a PDF and submit the PDF via
Moodle where the assignment appears in the Week Four materials.
Spreadsheet
Record your results in columnar format imitating the spreadsheet template provided.
Your spreadsheet should have four columns:
Unnormalized 1st Normal Form 2nd Normal Form 3rd Normal Form
Begin by listing, for each document, all data elements (“attributes”) on the document in
the “unnormalized” column.
List the document name in UPPER CASE and HIGHLIGHT it in yellow.
For example:
Unnormalized
CUSTOMER ORDER
Order Number
Order Date
Delivery Date
Customer Number
Customer Name
Etc.
Etc.
After listing all data elements (“attributes”) in the “unnormalized” column for all three
documents, then go through the list and put all data into First Normal Form. Replace
each document name with an entity name as needed. If an attribute appears on
multiple documents (for example, CustomerName), then you should only list it once in
your First Normal Form column.
Identify the Primary Keys by highlighting them in a different color other than yellow (as
you see in grey in the example below.)
Do the same for the second and third normal form columns.
Your final 3NF column should contain all the data in all the documents organized by
entity, listing all attributes in each entity with primary keys defined.
If an entity does not change from first to third normal form, then simply copy and paste
the data in the second normal form column to show that the data is already in second
normal form. HINT: you’ll know this if the entity has an atomic key – that is, NOT
concatenated/compound. Without a compound key, the data is already in 2NF.
Example:
The documents that follow represent some of the data used by a small Midwestern chemical
distribution company.
The reports are web pages from the company’s intranet portal used by the Sales division of the
company.
The data entry screen is a web page from the web-based Customer Maintenance application
used to add/change/delete customers in the system.
Some of the data items deserve a little explanation:
Customers are allowed flexible prices which vary based on the customer’s overall purchase
volume. There are four levels of discounted prices for each product. Each customer carries a
single discount code A, B, C or D. A Customer with an "A" discount code, for instance, will be
charged the "A" price for all products they order. When a product is ordered by a customer,
the sales person entering the order must check the customer’s discount code and then charge
the corresponding price for the product.
A Customer belongs to only one Sales Territory. Each customer is assigned to receive
shipments from only one Warehouse.
Each Customer has a unique identifying customer number.
Product Sales Report Page 1
Product No: 32010 Description: Nucleotide Emulsifier
Invoice Inv. Date Cust ID Cust Name Quantity Price
928321 01/03/2016 3621417 J. T. Harman 20 800
928375 02/03/2016 4273765 B. Baggins 10 430
928430 04/04/2016 1672349 N. Robinson 32 1280
928774 07/19/2016 3357669 Gombler & Sons 3 138
928901 09/06/2016 1473332 Thom & Hall 15 630
Customer Invoice Page 1
Invoice No: 928321 Invoice Date: 01/31/2016
Customer ID 3621417
Name & Address J. T. Harman & Company, LLC
22 Newbolt Rd.
Framingham, MN 52410
Product Product Std Disc Disc Quantity Price
Number Description Price Code Price
42161 Dye Wash Benzocaine 93.50 A 90.00 10 900.00
63214 Flax Seed Oil 10.60 A 8.00 20 160.00
17719 Cod Liver Oil 14.30 A 12.00 30 360.00
19214 Vitamin D Extract 96.50 A 92.00 10 920.00
32010 Nucleotide Emulsifier 46.00 A 40.00 20 800.00
___ ______
Invoice Total 90 3140.00
This is an image of the Customer Data Entry screen, used to add a new customer to the system
or change a customer’s information.
Customer Data Entry Screen
Customer ID
Sales Territory Warehouse
Customer Name
Address
Discount Code
Credit Limit
Delivery
Instructions