Normalization Example

A simple example that explains the normalisation process.
Assumption: A customer can have multiple orders but an order can be for only 1 product. CustName and OrderNo preassigned as keys.
0NF
CUSTOMER ORDER (CustName, OrderNo, ProdNo, ProdDesc, Qty, CustAddress, DateOrdered)
1NF – remove multivalued dependencies
CUSTOMER (CustName, CustAddress)
ORDER (CustName, OrderNo, ProdNo, ProdDesc, Qty, DateOrdered)
2NF – remove partial dependencies
CUSTOMER (CustName, CustAddress)
CUSTOMER ORDER (CustName, OrderNo)
ORDER (OrderNo, ProdNo, ProdDesc, Qty, DateOrdered)
3NF – remove transitive dependencies
CUSTOMER (CustName, CustAddress)
CUSTOMER ORDER (CustName, OrderNo)
ORDER (OrderNo, ProdNo, Qty, DateOrdered)
PRODUCT (ProdNo, ProdDesc)

A simple example that explains the normalization process.

Assumption: A customer can have multiple orders but an order can be for only 1 product. CustName and OrderNo preassigned as keys.

0NF

  • CUSTOMER ORDER (Customer Name, Order No, Product No, Product Description, Qty, Customer Address, Date Ordered)

1NF – Remove Multivalued dependencies

From the above order details Customer and Order can be separated and stored in different tables.

  • CUSTOMER (Customer Name, Customer Address)
  • ORDER (Customer Name, Order No, Product No, Product Description , Qty, Date Ordered)

2NF – Remove Partial dependencies

  • CUSTOMER (CustName, CustAddress)
  • CUSTOMER ORDER (CustName, OrderNo)
  • ORDER (OrderNo, ProdNo, ProdDesc, Qty, DateOrdered)

3NF – Remove Transitive dependencies

  • CUSTOMER (CustName, CustAddress)
  • CUSTOMER ORDER (CustName, OrderNo)
  • ORDER (OrderNo, ProdNo, Qty, DateOrdered)
  • PRODUCT (ProdNo, ProdDesc)
320 views

Return to top