Description
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
