Maya moves house
Maya Ford has bought three things. Her town is stored on every row. She moves, and the shop must change it in three places.
If it misses one, Maya lives in two towns at once. Splitting the table stops that. Each fact is stored once.
Key words
| Word | What it means | Example |
|---|---|---|
| Attribute | one fact about an entity, such as a name | |
| Normalisation | splitting tables so that each fact is stored once | |
| Redundancy | the same fact stored in more than one place | |
| Primary key | an attribute whose value is different for every row | |
| Composite key | a primary key made of two or more attributes together | |
| Foreign key | an attribute that holds the primary key of another table | |
| Dependency | when one attribute is decided by another | a name is decided by its id |
| Database | an organised collection of data, kept in linked tables | |
| SQL | a language for asking questions of data kept in tables |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can take a flat table to third normal form, choose the keys, and say what each step removes.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
This is practice
Important
The real task comes from the exam board. This lesson uses an invented organisation and its own made-up data. Your teacher tells you how the real tasks are set and what you may bring.
Worked example: the flat table
| sale | item | customer | town | item name | price | qty |
|---|---|---|---|---|---|---|
| 1 | B01 | 1 Maya Ford | Hillside | Bread | 120 | 2 |
| 1 | M02 | 1 Maya Ford | Hillside | Milk | 90 | 1 |
| 2 | B01 | 2 Ben Lowe | Marsh Row | Bread | 120 | 1 |
| 3 | M02 | 1 Maya Ford | Hillside | Milk | 90 | 3 |
| 3 | T05 | 1 Maya Ford | Hillside | Tea | 250 | 1 |
The cells students fill in are left blank in this preview.
Spot the repeats
Have a go
Spot the repeats
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.