Armando Cavazos
Erick Rivers
Database Design and Development
September 30, 2013
Normalization
The tables shows here is an un normalized data. This includes the customer details, product information, order details, invoice and payment details
The information given here for each entities in the logical model
ID,Name, Address, city,state, phone no for Customer
OrderDate,OrderID,Customer_ID and ItemID, Item_Total_Price,Order_total_price for Order
ID, price,quantity, description for Product
And Invoice and Payment entities.
First convert this un normalized data into first normal form
First Normal Form
Redesign the table not having repeating groups.
This can be achieved by adding the appropriate entry in at least the primary key column.
Making all attributes depending on this primary key
Taking the customer details in a Customer table ,Order details in a Order table , product details in Product table and so on , the primary keys are solid underlined, Remove the repeating information
Customer(ID,Name, Address, city,state, phone no, PaymentId)
Order(OrderID ,OrderDate,Customer_ID)
Create another table OrderLine
OrderLine(OrderID,ItemID, Item_Total_Price,Order_total_price)
Invoice [ InvNo,CustomerID, CustName, CustAddress, OrderID]
Invoice -Sub [InvNo, ItemID,ItemDesc, Qty, Price]
Product(ID, price,quantity, description)
Hereunderlined solid attributes are the primary keys of each table
Second Normal Form
Convert first normal form into the 2NF format by
Writing each key component on a separate line, and then writing the original key on the last line. Writing the dependent attributes after each new key.
So the tables eliminated by creating new relation consisting of attributes which are partial dependencies with the primary key of the new relation being the part of the primary key which determines the value of these attributes
By making foreign key constraint to relate the tables each other
Customer(ID,Name, Address, city,state, phone no, PaymentId)
Order(OrderID ,OrderDate,Customer_ID)
OrderLine(OrderID,ItemID, Qty,price)
Product(ID, price,quantity, description)
Invoice [ InvNo,CustomerID, OrderID]
Invoice -Sub [InvNo, ItemID,, Qty, Price]
Here the Customer_ID in Order table,Order_ID in Invoice table are the foreign keys which are the primary keys of Customer and Order tables
Thus relation has no partial dependencies and attributes whose values can be determined by knowing only part of the key
Third Normal Form
Create a separate table with attributes in a transitive functional dependence relationship. That the tables in 3 NF if it has notransitive dependencies. when the primary key and nothing but the primary key can be used to determine the value of each non-key , that is attributes whose values can be determined by knowing something other than the key.
The tables are converted as
Customer(ID,Name, Address, city,state, phone no, PaymentId)
Payment [PaymentId,PayTerms]
Order(OrderID ,OrderDate,Customer_ID)
OrderLine(OrderID,ItemID, Qty)
Product(ID, price,quantity, description)
Invoice [ InvNo,CustomerID, OrderID]
Invoice -Sub [InvNo, ItemID,, Qty, Price]