Consider the following 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.
Online Item Order System – Database Normalization (3NF)
Given Table:
| Order_ID |
Customer_Name |
Customer_Phone |
Product_ID |
Product_Name |
Product_Price |
Quantity |
Total_Amount |
Customer_Address |
| … | … | … | … | … | … | … | … | … |
1. Anomalies in the Table
- Update Anomaly: Changing product price or customer info requires multiple row updates.
- Insert Anomaly: Cannot insert a new product or customer without placing an order.
- Delete Anomaly: Deleting the last order deletes customer or product information.
2. Step-by-Step Normalization
Step 1: First Normal Form (1NF)
Rule: Each attribute must be atomic, no repeating groups, and each record unique.
- Problem: Multiple products per order may create repeating groups.
- Solution: Use composite key (Order_ID, Product_ID) and atomic columns.
1NF Table:
| Order_ID | Product_ID | Customer_Name | Customer_Phone | Customer_Address | Product_Name | Product_Price | Quantity | Total_Amount |
Step 2: Second Normal Form (2NF)
Rule: Must be in 1NF and no partial dependency (non-key attributes must depend on full key).
- Problem: Customer_Name depends only on Order_ID; Product_Name and Product_Price depend only on Product_ID (partial dependency).
- Solution: Split into multiple tables:
Orders Table
| Order_ID (PK) | Customer_ID (FK) | Total_Amount |
|---|
Order_Details Table
| Order_ID (FK) | Product_ID (FK) | Quantity |
|---|
Customers Table
| Customer_ID (PK) | Customer_Name | Customer_Phone | Customer_Address |
|---|
Products Table
| Product_ID (PK) | Product_Name | Product_Price |
|---|
Step 3: Third Normal Form (3NF)
Rule: Must be in 2NF and no transitive dependency. All non-key attributes depend only on primary key.
- Problem: Total_Amount = Product_Price × Quantity → derived attribute (transitive dependency).
- Solution: Remove derived attribute from Orders table.
Final 3NF Tables
Customers
| Customer_ID (PK) | Customer_Name | Customer_Phone | Customer_Address |
|---|
Products
| Product_ID (PK) | Product_Name | Product_Price |
|---|
Orders
| Order_ID (PK) | Customer_ID (FK) | Order_Date |
|---|
Order_Details
| Order_ID (FK) | Product_ID (FK) | Quantity |
|---|
Relationships (ER Diagram)
- One Customer → Many Orders (1:M)
- One Order → Many Order_Details (1:M)
- One Product → Many Order_Details (1:M)
- Orders and Products have Many-to-Many relationship resolved by Order_Details
Visual Representation:
Customer (1) --------< Orders (1) --------< Order_Details >-------- (1) Products
Summary of Normalization
| Normal Form | Problem | Solution |
|---|
| 1NF | Repeating groups / Non-atomic data | Use atomic values and composite key |
| 2NF | Partial dependency on composite key | Split into Customers, Products, Orders, Order_Details |
| 3NF | Transitive / Derived attribute (Total_Amount) | Remove derived attributes; each non-key depends on PK |
কোন মন্তব্য নেই:
একটি মন্তব্য পোস্ট করুন