A3.2.6 · Database design
Normalising to 3NF
By the end you can
Take a messy real-world data set and design a database in third normal form, showing the keys.
Real data does not arrive in tidy tables. It arrives as an order form, a spreadsheet or a register, and the designer's job is to turn it into a design. The method is the same every time. Work through these questions in order.
- What is one row? Decide what a single record stands for (one item on an order, one student on one course) and pick a key that identifies it. Make every cell hold one value (1NF).
- For each attribute, what decides it? If it depends on only part of the key, it moves out to a table keyed by that part (2NF).
- Does anything depend on something that is not the key? If so, it moves out to a table keyed by that attribute (3NF).
- Check it. Name each table's primary key, mark each foreign key, and make sure joining the tables gives the original back.
Worked example: a craft marketplace#
An online marketplace sells handmade goods. Its order book is one big table with a row for each item on each order. Each order has a customer, each item has a maker, and an order can hold many items. The key is OrderID + ItemID. Step through the stages and read what the data says at each one. Notice the two kinds of problem: facts about the order or the item alone (partial), and facts about the customer or the maker (transitive).
First normal form
Every cell is a single value and each row is identified by a key: here OrderID and ItemID together (one row per item on an order). Everything is still in one table, so customers, items and makers are repeated on every row.
ORDER_LINE
| OrderIDPK | OrderDate | CustomerID | CustomerName | CustomerCity | ItemIDPK | ItemName | Price | MakerID | MakerName | Qty |
|---|---|---|---|---|---|---|---|---|---|---|
| O1 | 2025-06-01 | C1 | Ana Lopez | Madrid | I1 | Clay Mug | 12 | M1 | Studio Nine | 2 |
| O1 | 2025-06-01 | C1 | Ana Lopez | Madrid | I2 | Wall Print | 25 | M2 | Kiln and Co | 1 |
| O2 | 2025-06-03 | C2 | Ben Wright | Leeds | I1 | Clay Mug | 12 | M1 | Studio Nine | 1 |
| O3 | 2025-06-03 | C1 | Ana Lopez | Madrid | I3 | Linen Scarf | 25 | M3 | Hearth Works | 1 |
| O3 | 2025-06-03 | C1 | Ana Lopez | Madrid | I1 | Clay Mug | 12 | M1 | Studio Nine | 3 |
| O4 | 2025-06-09 | C3 | Chloe Dubois | Paris | I2 | Wall Print | 25 | M2 | Kiln and Co | 2 |
| O5 | 2025-06-10 | C2 | Ben Wright | Leeds | I3 | Linen Scarf | 25 | M3 | Hearth Works | 1 |
| O5 | 2025-06-10 | C2 | Ben Wright | Leeds | I4 | Tea Set | 40 | M3 | Hearth Works | 1 |
Tables
1
Cells stored
88
Join back = original?
yes
What the data says (dependencies found in the rows)
- OrderID determines OrderDate partial (breaks 2NF)
- OrderID determines CustomerID partial (breaks 2NF)
- OrderID determines CustomerName partial (breaks 2NF)
- CustomerID determines CustomerName transitive (breaks 3NF)
- OrderID determines CustomerCity partial (breaks 2NF)
- CustomerID determines CustomerCity transitive (breaks 3NF)
- CustomerName determines CustomerCity transitive (breaks 3NF)
- ItemID determines ItemName partial (breaks 2NF)
- ItemID determines Price partial (breaks 2NF)
- ItemID determines MakerID partial (breaks 2NF)
- ItemID determines MakerName partial (breaks 2NF)
- MakerID determines MakerName transitive (breaks 3NF)
- OrderID + ItemID together determine the rest of the row (for example Qty) full: fine
Why the single table is a problem
- Update: Hearth Works changes its name, so 3 rows must all be changed. Miss one and the data disagrees with itself.
- Insert: a new item cannot be listed until somebody orders it, because OrderID is half of the key.
- Delete: when the only order line for the Tea Set is deleted, the database also loses the Tea Set itself, its price and its maker.
In 3NF each fact is stored once, in one row of one table.
Your turn: a library#
A library group records loans in one table: LibraryName, LibraryCity, BorrowerID, BorrowerName, BookNumber, BookTitle, DateBorrowed, DateReturned. A borrower belongs to one library, and a person can borrow the same book on different dates. Try it on paper before you read on.
- 1NF: one row per loan; key
BorrowerID + BookNumber + DateBorrowed. - 2NF:
BookTitledepends onBookNumberalone, andBorrowerNameand the library depend onBorrowerIDalone, so they move out.DateReturnedneeds the whole key. - 3NF:
LibraryCitydepends onLibraryName, not on the borrower, so Library becomes its own table. - Result: LOAN(BorrowerID*, BookNumber*, DateBorrowed, DateReturned), BORROWER(BorrowerID, BorrowerName, LibraryName*), BOOK(BookNumber, BookTitle), LIBRARY(LibraryName, LibraryCity). The star marks foreign keys.
Check yourself
Normalising to 3NF
Quick questions, made fresh every time and marked here. Do a few until they feel easy.
Exam practice
Paper 1-style questions
4 questions · 14 marks
Write your answer as you would in the exam, then show the mark points and tick each one your answer made. These are our own questions in the style of the exam, not past-paper questions.
1.A school trip table holds: TripName, Destination, MaxPlaces, LeaderName, LeaderPhone, StudentID, StudentName, Grade. A trip has one leader and many students; a student can join several trips. Construct a design in third normal form.
[5]Construct: Draw or write it out (a table, a diagram, a program).
2.Outline the steps to take a table from unnormalised form to third normal form.
[4]Outline: A brief account: the main points, without much detail.
3.A table Order(OrderID, CustomerID, CustomerName, CustomerCity) has the primary key OrderID. Explain why it is not in third normal form and how to fix it.
[3]Explain: Give reasons: say what happens AND why, or how one thing causes another.
4.Explain how you can check that a normalised design has not lost any information.
[2]Explain: Give reasons: say what happens AND why, or how one thing causes another.
In a nutshell
- To normalise a real data set: list the attributes, find the key, then work through 1NF, 2NF and 3NF, splitting tables as dependencies demand.
- Look for the questions: what decides what? Anything that depends on part of the key, or on a non-key attribute, belongs in a new table.
- Each split leaves a foreign key behind so the tables can be joined back together. Joining all the tables should reproduce the original data exactly.
- Many-to-many relationships end up as linking tables with a composite primary key made of two foreign keys.
- In the end each fact appears once, in one row of one table.