Construct a database normalized to 3NF for a range of real-world scenarios

• To normalize a database to 3NF:

  1. Start with the unnormalized data
  2. identify the primary key
  3. remove repeating groups to reach 1NF
  4. remove partial dependencies to reach 2NF
  5. remove transitive dependencies to reach 3NF

• Real-world example:

Unnormalized table:

OrderID	CustomerName	CustomerPhone	ProductID	ProductName	Quantity	SupplierName
1001	Alice	9999	P1	Keyboard	2	TechCo
1001	Alice	9999	P2	Mouse	1	TechCo

• Problems:
  • repeated customer data
  • repeated product data
  • possible update anomalies
• Step 1: 1NF
• Ensure each field is atomic and each row stores one product per order.
• The table shown is already close to 1NF because each cell contains one value only.

OrderLines

OrderID	CustomerName	CustomerPhone	ProductID	ProductName	Quantity	SupplierName
1001	Alice	9999	P1	Keyboard	2	TechCo
1001	Alice	9999	P2	Mouse	1	TechCo

• Candidate composite key:
  • (OrderID, ProductID)
• Step 2: 2NF
• Remove attributes that depend on only part of the composite key:
  • CustomerName, CustomerPhone depend on OrderID
  • ProductName, SupplierName depend on ProductID

Orders

OrderID	CustomerName	CustomerPhone
1001	Alice	9999

Products

ProductID	ProductName	SupplierName
P1	Keyboard	TechCo
P2	Mouse	TechCo

OrderItems

OrderID	ProductID	Quantity
1001	P1	2
1001	P2	1

• Step 3: 3NF
• Remove transitive dependencies:
  • customer details should not be stored directly in Orders if they depend on CustomerID
  • if supplier details were expanded further, they might also need a separate Suppliers table

Customers

CustomerID	CustomerName	CustomerPhone
C1	Alice	9999

Orders

OrderID	CustomerID
1001	C1

Products

ProductID	ProductName	SupplierName
P1	Keyboard	TechCo
P2	Mouse	TechCo

OrderItems

OrderID	ProductID	Quantity
1001	P1	2
1001	P2	1

• Why this is better:
  • customer data stored once
  • product data stored once
  • order-product relationship handled separately
  • easier updates and better integrity

How to write this in an exam

1. State the original key or candidate key.
2. Identify repeating groups or non-atomic values.
3. Show the 1NF table.
4. State which attributes have partial dependency and move them out to reach 2NF.
5. State which attributes have transitive dependency and move them out to reach 3NF.
6. List the final relations clearly with primary keys and foreign keys.

Final 3NF relations for the example

• Customers(CustomerID, CustomerName, CustomerPhone)
• Orders(OrderID, CustomerID)
• Products(ProductID, ProductName, SupplierName)
• OrderItems(OrderID, ProductID, Quantity)