Trust Coding : Programming: Database Normalization Example: Online Order System 3NF

Database Normalization: Online Item Order System up to 3NF

📌 Question

Consider the following unnormalized table for an Online Item Order System:

Order_ID	Customer_Name	Customer_Phone	Product_ID	Product_Name	Product_Price	Quantity	Total_Amount	Customer_Address

Tasks:

Identify the anomalies present in this table that violate database normalization rules.
Transform the table step by step to achieve Third Normal Form (3NF).
For each step (1NF, 2NF, 3NF), provide a valid justification for the changes made.
Draw the final ER diagram representing the 3NF schema.

✅ Answer

Step 1: Identify Anomalies

Insertion Anomaly: Cannot add a new customer or product unless an order is placed.

Update Anomaly: Updating product price or customer details requires multiple row changes.

Deletion Anomaly: Deleting the last order removes customer or product information permanently.

Step 2: First Normal Form (1NF)

Rule: Eliminate repeating groups and ensure atomic values.

Relation in 1NF:

Orders(Order_ID, Customer_Name, Customer_Phone, Customer_Address, 
       Product_ID, Product_Name, Product_Price, Quantity, Total_Amount)

Justification: Data is atomic, but redundancy still exists.

Step 3: Second Normal Form (2NF)

Rule: Remove partial dependencies on a composite key.

Relations in 2NF:

Orders(Order_ID, Customer_ID, Order_Date, Total_Amount)
Customers(Customer_ID, Customer_Name, Customer_Phone, Customer_Address)
Products(Product_ID, Product_Name, Product_Price)
OrderDetails(Order_ID, Product_ID, Quantity)

Justification: Customer details depend only on Order_ID, and product details depend only on Product_ID. Both separated.

Step 4: Third Normal Form (3NF)

Rule: Remove transitive dependencies.

Final Relations in 3NF:

Orders(Order_ID, Customer_ID, Order_Date)
Customers(Customer_ID, Customer_Name, Customer_Phone, Customer_Address)
Products(Product_ID, Product_Name, Product_Price)
OrderDetails(Order_ID, Product_ID, Quantity)

Justification: Total_Amount is derived from Quantity × Product_Price, so it is removed.

Step 5: Final ER Diagram - Text Version

Customers (Customer_ID, Name, Phone, Address)
        |
        | 1 --- ∞
Orders (Order_ID, Customer_ID, Order_Date)
        |
        | 1 --- ∞
OrderDetails (Order_ID, Product_ID, Quantity)
        |
        | ∞ --- 1
Products (Product_ID, Product_Name, Product_Price)

📌 Final Summary

Unnormalized table caused insertion, update, deletion anomalies.
Step-by-step transformation: 1NF → 2NF → 3NF.
Final schema consists of Customers, Orders, Products, OrderDetails.
ER Diagram shows clear relationships for an optimized database design.

Trust Coding : Programming

Database Normalization Example: Online Order System 3NF