She moved, but only on one row
A phone repair shop keeps one big spreadsheet. Every repair repeats the customer's name and town.
Isla moves from Glasgow to Edinburgh. Somebody changes one row and misses the other four.
Now the shop has two towns for one person, and nobody knows which is true.
By the end of this lesson
Note
I can remove repeated data from a table design.
Key words
| Word | What it means |
|---|---|
| Redundancy | the same fact stored more than once |
| Anomaly | a mistake that repeated data lets in |
| Normalisation | splitting tables so each fact is stored once |
| 1NF | one value in every cell, and a key for every record |
| 2NF | 1NF, and every field depends on the whole key |
| 3NF | 2NF, and no field depends on another field that is not the key |
The cells students fill in are left blank in this preview.
A joined view repeats facts
In the shop, a customer's city is stored once, in customers. Each order holds only the customer's number.
A join can still show the city beside every order. That view repeats the city, but the table underneath does not.
Sign in and look at Isla's orders.
Let the terminal trust the server
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make a short name for the shop
alias sql='mariadb -h db -u student -pstudent shop -t -e'Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Isla's orders with her city
sql "SELECT orders.id AS order_no, customers.first_name, customers.city FROM orders JOIN customers ON customers.id = orders.customer_id WHERE customers.id = 5 ORDER BY orders.id"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make a flat copy and break it
Now copy that view into a flat table of your own, where the city really is typed on every order. Then move Isla to Edinburgh, but change only one order, as a busy person might.
This is an update anomaly. Run these in order.
Make your own database
sql "CREATE DATABASE IF NOT EXISTS student_flat"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make a short name for it
alias flat='mariadb -h db -u student -pstudent student_flat -t -e'Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Start clean
flat "DROP TABLE IF EXISTS sheet"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Copy the view into a flat table
flat "CREATE TABLE sheet AS SELECT orders.id AS order_no, customers.first_name, customers.city FROM shop.orders JOIN shop.customers ON customers.id = orders.customer_id"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Move Isla on one row only
flat "UPDATE sheet SET city = 'Edinburgh' WHERE order_no = 49"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Where does Isla live now?
flat "SELECT first_name, city, COUNT(*) AS orders FROM sheet WHERE first_name = 'Isla' GROUP BY first_name, city ORDER BY city"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A common mistake
Warning
Many people think repeated data is harmless because storage is cheap. The cost is not space. Every copy must be changed together, and one missed copy makes the data disagree with itself.
First normal form: one value a cell
A table is in 1NF when every cell holds one value and every record has a key.
A cell such as "Kart Rush, Block Town" breaks it. So do columns called game1 and game2. Give each value a record of its own.
Worked example: into 1NF
| Before (not 1NF) | After (1NF) |
|---|---|
| NovaFox | Kart Rush, Block Town | Two records: NovaFox | Kart Rush and NovaFox | Block Town |
| Zed | Goal Line | Zed | Goal Line, already one value |
| Zed | game1 Goal Line | game2 Kart Rush | Two records: Zed | Goal Line and Zed | Kart Rush |
The cells students fill in are left blank in this preview.
2NF and 3NF: each fact about its key
Look at a repair shop's order lines. The key is the order number and the item together.
The price depends only on the item, so it breaks 2NF. The town depends on the customer, not on the order, so it breaks 3NF.
The fix is to move each fact to the table it is about.
Worked example: splitting to 3NF
| Table | Key | Other fields | Each fact stored |
|---|---|---|---|
| customers | customer | town | Once per customer |
| items | item | price | Once per item |
| orders | order_no | customer | Once per order |
| order_lines | order_no and item | none | Once per line |
The cells students fill in are left blank in this preview.
Going further
A college network logs every sign-in: device name, owner, the owner's tutor group and the time.
On paper, list the fields that repeat. Split the log into tables so that each fact is stored once, and name the key of each table.