Brief Description Of The Normalization Process

Introduction

The ERD of the database, created using top-down approach, is shown below.
The functional dependencies between the attributes in the database are:

• orderID,productID → oDate, shipAddress, oTotal, customerID, cName, cAddress, cPhone, cEmail, qty, salePrice, productID, unitPrice, qtyInStock, reordLevel
• orderID → oDate, ship Address, o Total, customer ID, cName, cAddress, cPhone, cEmail
• customerID → cName, cAddress, cPhone, cEmail
• productID→ pName, unitPrice, qty In Stock, reord Level

UNF
UNNORMALISED table

1. orderID is the PK of the UNNORMALISED table.
2. qty, salePrice, productID, pName, unitPrice, qtyInStock, reordLevel is the repeating group

1NF

1. The first normalisation rule was applied – “No repeating groups…”
2. qty, salePrice, productID, pName, unitPrice, qtyInStock, reordLevel + orderID is the repeating group which was removed and put in the table ORDERPRODUCT_PRODUCT. The remaining part of the UNNORMALISED table was renamed to ORDER_CUSTOMER.
3. orderID is the PK of ORDER_CUSTOMER and its copy was taken along with the removed attributes, since ORDERPRODUCT_PRODUCT is  a child of ORDER_CUSTOMER.
4. orderID, productID is the PK of the table ORDERPRODUCT_PRODUCT based on the functional dependency (1).

2NF

1. The second normalization rule was applied – “No partial dependencies on the primary key…”.
2. p Name, unit Price, qty In Stock, record Level are the attributes which are partially dependent on the PK of the table ORDER PRODUCT_PRODUCT, since based on the functional dependency (4) they depend only on product ID. The attributes and productID were removed from the table ORDER PRODUCT_PRODUCT and placed in the table PRODUCT. The remaining part of the table ORDER PRODUCT_PRODUCT was renamed to ORDER PRODUCT.
3. product ID is the PK of PRODUCT and its copy was left behind, since PRODUCT is a parent of ORDER PRODUCT.

3NF

1. The third normalisation rule was applied – “No transitive dependencies on the primary key…”.
2. c Name, c Address , c Phone, c Email are the attributes which are transitively dependent on the PK of the table ORDER_CUSTOMER, since based on dependency (3) they are dependent on customer ID. The attributes and customer ID were removed from the table ORDER_CUSTOMER and placed in the table ORDER. The remaining part of the table ORDER_CUSTOMER was renamed to ORDER.
3. customer ID is the PK of the table CUSTOMER which was left behind in the table ORDER to, since CUSTOMER is a parent of ORDER.Order Now