Homework # 1 – Data Normalization

Homework # 1
Data Normalization
This homework is worth 10 points (10/100, or 10%) toward your final grade.
Create 3rd Normal Forms for each of the six documents on the following pages. Then combine
into a single set of 3rd Normal Form Relations depicting all known data items for this company.
You have a choice of how to format/present your finished results. “Spreadsheet” or “Schema”.
Spreadsheet.
Record your results in columnar format imitating the spreadsheet template provided.
Create one tab/worksheet in the spreadsheet for each of the six documents. Then
create a final tab/worksheet for the combined solution. Each tab/worksheet should
have four columns:
Unnormalized 1
st Normal Form 2
nd Normal Form 3
rd 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/or HIGHLIGHT
it. Identify the candidate keys.
For example:
Unnormalized
PRODUCT SALES REPORT
Product No
Description
Invoice
Inv Date
Cust No
Name
Qty
Price
After listing all documents (“entities”) and data elements (“attributes”) in the
“unnormalized” column, then go through the list and put all data into First Normal
Form. Replace document names with entity names where possible. Then do the same
for second and third normal form.
Homework # 1 – Data Normalization
Page 2
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.
Example:
Schema.
Record your results in schema format imitating the example provided. Create one
section in your submission for each of the six documents. Then create a final section for
the combined solution. Each section should have four schemas:
Unnormalized 1
st Normal Form 2
nd Normal Form 3
rd Normal Form
Begin by listing, for each document, all data elements (“attributes”) on the document in
the “unnormalized” schema. List the document name in UPPER CASE and/or HIGHLIGHT
it. Identify the candidate keys with underscore.
Homework # 1 – Data Normalization
Page 3
For example:
After listing all documents (“entities”) and data elements (“attributes”) in the
“unnormalized” section, then go through the list and put all data into First Normal Form.
Replace document names with entity names where possible. Then do the same for
second and third normal form.
If an entity does not change from first to third normal form, then simply copy and paste
the data in the second normal form section to show that the data is already in second
normal form.
Example:
Unnormalized Section
CustomerOrder(OrderNumber, OrderDate, DeliveryDate, CustomerDiscount,
DiscountAmount, Customer Discount, Invoiced amount, CustomerNumber,
CustomerName, BillToAddress, BillToCity, BillToState, BillToZip, ShipToAddress,
ShipToCity, ShipToState, ShipToZip, ProductNumber, Description, QuantityOrdered,
UnitPrice, OrderTotal)
FirstNormalForm Section
CustomerOrder(OrderNumber, OrderDate, DeliveryDate, CustomerDiscount,
DiscountAmount, Customer Discount, Invoiced amount, CustomerNumber,
CustomerName, BillToAddress, BillToCity, BillToState, BillToZip, ShipToAddress,
ShipToCity, ShipToState, ShipToZip, Order total)
OrderProduct(Order number, Product Number,ProductDescription, Quantity,
UnitPrice)
SecondNormalForm Section
CustomerOrder(OrderNumber, OrderDate, DeliveryDate, CustomerDiscount,
DiscountAmount, Customer Discount, Invoiced amount, CustomerNumber,
CustomerName, BillToAddress, BillToCity, BillToState, BillToZip, ShipToAddress,
ShipToCity, ShipToState, ShipToZip, Order total)
OrderProduct(Order number, Product Number, Quantity, Total)
Homework # 1 – Data Normalization
Page 4
Product(Product Number, Description, UnitPrice)
ThirdNormalForm Section
CustomerOrder(OrderNumber, OrderDate, DeliveryDate, CustomerDiscount,
DiscountAmount, Customer Discount, Invoiced amount, Order total)
Customer(CustomerNumber, , CustomerName, BillToAddress, BillToCity,
BillToState, BillToZip, ShipToAddress, ShipToCity, ShipToState, ShipToZip,)
OrderProduct(Order number, Product Number, Quantity, Total)
Product(Product Number, Description, UnitPrice)
Homework # 1 – Data Normalization
Page 5
These documents represent some of the data used by a small midwestern chemical distribution
company. Some of the forms are computer-generated reports. Some are computer data-entry
screens.
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 he orders. 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.
Location Code is a grid reference within a depot (“warehouse”) identifying a physical palette
spot or bin on the depot floor. Location Codes are only unique within Depot Code.
A customer is always served out of only one depot.
A Customer belongs to only one Sales Territory.
Each Customer has a unique identifying customer number.
There are 16 Product Classes, each product class belongs to one of four Inventory Codes.
The “Screen ID” field and the “Add/Change/Delete” fields on the online screen images are
operating features of the software that displays and processes the online screens. These
attributes do NOT need to be stored in a database and they can be left out of your normalized
data.
Homework # 1 – Data Normalization
Page 6
Product Sales Report Page 1
Product No: 32010 Description: Nucleotide Emulsifier
Invoice Inv. Date Cust No. 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 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
Homework # 1 – Data Normalization
Page 7
SALES TERRITORY REPORT
SALES TERRITORY 812
CUSTOMER NO. ORDERS
YTD
ACCOUNT
BALANCE
ORDERS
VALUE
6214312 6 254.50 1,000.00
7121416 10 0.00 500.00
9161417 20 0.00 400.00
3241718 40 400.60 500.00
6141846 50 900.00 600.00
7219612 100 25.25 700.00
6142361 204 30.60 100.00
7194871 30 32.70 200.00
8141714 60 100.00 1,000.00
520 5,000.00
Homework # 1 – Data Normalization
Page 8
This is an image of the Customer Entry screen, used to add a new customer to the system or
change a customer’s information.
Customer Entry
Screen ID: C01 Add
Customer Number Change

Sales Territory Depot
Customer Name
Address
Trade Class Discount Code
Substitute Credit Limit
Delivery Instructions
Homework # 1 – Data Normalization
Page 9
Product Warehouse Stock Report Page 1
Product No: 42161
Depot Stock Location YTD Orders
Code Quantity Code
01 1,000 B 61 22,341
02 0 A 42 20,341
03 2,142 A 42 1,000
04 6,100 F 99 60,000
05 7,120 H 24 1,342
06 2,000 J 16 6,214
07 600 B 12 7,418
08 304 D 14 8,213
09 0 C 32 9,141
10 260 D 22 8,762
_____ ______
Totals 19,526 144,772
Homework # 1 – Data Normalization
Page 10
This is an image of the Product Entry screen used to add a new product to the system, and/or
change/delete an existing product from the system. Deleting a product merely marks it
“inactive”. Its history is NOT actually deleted from the database.
Product Entry
Screen ID: P01 Action Add
Product Number Change
Delete
Weight
Pack Unit
Description
Product Class
Inventory Code
Discount Prices A
Standard Price
B
C
D