Change one name, fix it everywhere
A library sheet lists the author "Ana Lee" on 200 rows. Ana gets married and changes her name. Someone must change 200 rows.
Miss one and the sheet disagrees with itself. If the author were stored once, one change would fix everything. There is a set way to arrange tables to do that.
Key words
| Word | What it means | Example |
|---|---|---|
| Normalisation | Arranging tables so each fact is stored once. | Author name in one place |
| Relational database | A database that keeps data in linked tables. | Members, books and loans |
| Primary key | A field that identifies each row in a table. | member_id |
| Foreign key | A field that holds the primary key of another table, to link them. | member_id in loans |
| Unnormalised form | A table with repeated groups and mixed facts. Short name: UNF. | A loan sheet |
| Atomic | Holding only one value in a cell. | One book per cell |
| Dependency | When one field is decided by another field. | A title is decided by its book id |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can take a table from unnormalised form to third normal form and say what each step fixes.
Starter: what you already know
Have a go
Starter: what you already know
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Unnormalised form: the loan sheet
| member | member_name | books | authors |
|---|---|---|---|
| 1 | Sam | Maps, Rivers | Ana Lee, Max Cho |
| 2 | Jo | Maps | Ana Lee |
The cells students fill in are left blank in this preview.
The three steps
First normal form (1NF): every cell holds one value, and there are no repeating groups. Split "Maps, Rivers" into separate rows.
Second normal form (2NF): every other field depends on the whole key, not part of it. Book facts move to a books table.
Third normal form (3NF): fields depend on the key and nothing else. Member facts move to a members table.
Try it: join the tables back
select m.member_name, b.title, l.loan_date
from loans l
join members m on m.member_id = l.member_id
join books b on b.book_id = l.book_id
order by l.loan_date;
-- Try it: add b.author to the list.