How do I normalise a database to 3NF and show it in my assignment?
- Expert answer
- Undergraduate
- Asked
The question
I have one big orders table with customer name, customer address, product name, product price and quantity. The assignment asks me to normalise it to third normal form and show my working.
Short answer
Move to 1NF by making every field atomic with a primary key, to 2NF by removing attributes that depend on only part of a composite key, and to 3NF by removing attributes that depend on other non-key attributes. Your table splits into Customers, Products, Orders and OrderLines.
Full expert answer
Database systems tutor
MSc Data Science, Oracle certified
The marks for normalisation come from showing each stage and naming the dependency you removed, not just from the final schema. Work through it one form at a time.
1NF: atomic values and a key
Each row should hold one product per order line, with no repeating groups or comma-separated lists. A composite key of (OrderID, ProductID) identifies each row.
2NF: remove partial dependencies
Product name and price depend only on ProductID, not on the whole composite key. Customer details depend only on OrderID. Move them out, leaving OrderLines (OrderID, ProductID, Quantity), Products (ProductID, Name, Price) and Orders (OrderID, CustomerName, CustomerAddress).
3NF: remove transitive dependencies
In Orders, the customer's address depends on the customer, not on the order. That is a transitive dependency through CustomerID. Create Customers (CustomerID, Name, Address) and keep only CustomerID as a foreign key in Orders.
This answer explains a method for you to apply to your own work. Copying it into a submission would count as plagiarism, and it is indexed by similarity checkers.
All questions