The plan before the building
Nobody builds a house without a plan. A school wants to store pupils, forms and clubs. If it starts typing before it plans, the same facts end up in five places.
The plan for a store of data is a picture, and the facts it needs are the keys that link the parts.
Key words
| Word | What it means |
|---|---|
| Entity relationship diagram | a picture of the tables in a database and how they link, called an ERD |
| Database | an organised store of tables that you can ask questions |
| Schema | the plan of a database: its tables, fields and links |
| Entity | a thing we keep facts about, such as a pupil; it becomes a table |
| Record | one row: all the facts about one entity |
| Attribute | one fact about an entity; it becomes a field, or column |
| Primary key | the field that gives every record its own unique id |
| Foreign key | a field that holds the primary key of a record in another table |
| Composite key | a primary key made of two or more fields together |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can say what a schema and an ERD are for, and name tables, records, fields and the three kinds of key.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Same idea, different words
| In a diagram | In the database | Example |
|---|---|---|
| Entity | Table | pupil |
| One instance of an entity | Record, or row | Amy, form 1 |
| Attribute | Field, or column | name |
| Primary key | The field that is unique | pupil_id |
| Foreign key | A field that points to another table | form_id in pupil |
| Composite key | Two fields that are unique together | pupil_id with club_id |
The cells students fill in are left blank in this preview.
Try it: read a real schema
-- The tables this schema made.
SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name;
-- A record from pupil. form_id is the foreign key.
SELECT * FROM pupil;
-- membership has a composite key: pupil_id and club_id together.
SELECT * FROM membership;
Why three kinds of key
A primary key must be unique, so each record can be found. A foreign key links a record to one in another table, so a fact is stored once. In the pupil table, form_id points at the form table.
A composite key uses two fields together. In membership, Amy can join Chess and Choir, and Chess can have Amy and Bo. Only the pair is unique.
How the exam draws keys
Important
On paper, underline each primary key and put a star after each foreign key, such as form_id*. The picture above uses the tags PK and FK because that is how the drawing tool shows them.