ESP Document 1
This is a sample solution of normalizing the Customer Details View and the Customer Orders View in ESP Document 1. The styling conforms to the associated [Legend.md](normalization legend).
The markdown for this file can be found in the source repository.
Customer Details View
The Customer Details View and its resulting tables are based on the following form (taken from the original documentation).
This form was analyzed according to the rules of 0NF (Zero-Normal Form), 1NF (First-Normal Form), 2NF (Second-Normal Form), and 3NF (Third-Normal Form).
0NF
After performing Zero-Normal Form, a single table was generated: Customer.
Customer: (CustomerNumber, FirstName, LastName, Address, City, Province, PostalCode, HomePhone)
1NF – 3NF
No changes to the table in 1NF to 3NF.
ERD
The following ERD represents the tables/entities from the Customer Details View.
Customer Orders View
The Customer Orders View and its resulting tables are based on the following form (taken from the original documentation).
This form was analyzed according to the rules of 0NF (Zero-Normal Form), 1NF (First-Normal Form), 2NF (Second-Normal Form), and 3NF (Third-Normal Form).
0NF
After performing Zero-Normal Form, a single table was generated: Order.
Order: (CustomerNumber, FirstName, LastName, Address, City, Province, PostalCode, Phone, Date, OrderNumber, ItemNumber, Description, Quantity, CurrentPrice, SellingPrice, Amount, Subtotal, GST, Total)
1NF
After performing First-Normal Form, a new table was generated: OrderDetail.
Order: (CustomerNumber, FirstName, LastName, Address, City, Province, PostalCode, Phone, Date, OrderNumber, Subtotal, GST, Total)
OrderDetail: (OrderNumber, ItemNumber, Description, Quantity, CurrentPrice, SellingPrice, Amount)
2NF
After performing Second-Normal Form, another new table was generated: Item.
OrderDetail (OrderNumber, ItemNumber, Quantity, SellingPrice, Amount)
Item (ItemNumber, Description, CurrentPrice)
3NF
After performing Third-Normal Form, another new table was generated: Customer.
Order (OrderNumber, CustomerNumber, Date, Subtotal, GST, Total)
Customer (CustomerNumber, FirstName, LastName, Address, City, Province, PostalCode, Phone)
Tables after 3rd Normal Form
These are the tables/entities after normalizing the Customer Details View.
Order (OrderNumber, CustomerNumber, Date, Subtotal, GST, Total)
OrderDetail (OrderNumber, ItemNumber, Quantity, SellingPrice, Amount)
Item (ItemNumber, Description, CurrentPrice)
Customer (CustomerNumber, FirstName, LastName, Address, City, Province, PostalCode, Phone)
Customer Orders View ERD
The following ERD for the Customer Orders View is based on the final 3rd normal form.
