A map of the data
A town map shows streets and how they join. An entity relationship diagram, or ERD, does the same for data. It shows the tables and how they join.
Anybody who reads it knows the shape of the data before they see a single row.
Key words
| Word | What it means |
|---|---|
| ERD | an entity relationship diagram: boxes for tables and lines for the links |
| Entity | a thing the system keeps facts about |
| Database | an organised collection of data, kept in linked tables |
| Attribute | one fact about an entity, such as a name |
| Primary key | an attribute whose value is different for every row |
| Foreign key | an attribute that holds the primary key of another table |
| Cardinality | how many of one entity link to the other: one or many |
| Link table | a table that joins two entities that are many-to-many |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can draw an ERD with entities, keys and relationships, using the right symbols and consistent names.
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: reading the symbols
| Symbol | Says | Example |
|---|---|---|
| `||` | Exactly one | A purchase has exactly one customer |
| `o{` | Zero or many | A customer has zero or many purchases |
| `|{` | One or many | A purchase has one or many lines |
| `PK` | Primary key | customer_id |
| `FK` | Foreign key | customer_id inside PURCHASE |
The cells students fill in are left blank in this preview.
Many to many needs a link table
A purchase can have many items. An item can be on many purchases. That is many to many, and two tables can not store it on their own.
Add a link table between them. Here it is PURCHASE_LINE. It holds both keys, and one row for each item on each purchase.