Homework # 1 Data Normalization Solution

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 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/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.




Page 1

Homework # 1 – Data Normalization













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 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” schema. List the document name in UPPER CASE and/or HIGHLIGHT it. Identify the candidate keys with underscore.
















Page 2

Homework # 1 – Data Normalization










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)







Page 3

Homework # 1 – Data Normalization



















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)

























































































































Page 4

Homework # 1 – Data Normalization













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.























































Page 5

Homework # 1 – Data Normalization















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



























Page 6

Homework # 1 – Data Normalization



















SALES TERRITORY REPORT







SALES TERRITORY 812







CUSTOMER NO.
ORDERS
ACCOUNT
ORDERS


YTD
BALANCE
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

































































Page 7

Homework # 1 – Data Normalization



















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




















































Page 8

Homework # 1 – Data Normalization





















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














































































































Page 9

Homework # 1 – Data Normalization













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














Weight
















Delete








































































Pack Unit




Description




























Product Class




































Inventory Code
























Discount Prices
A






























B


Standard Price


































































C






















































D




























































































































Page 10