Sign up for a club
A school has chess, football and drama clubs. A student can join many clubs. A club has many students.
What must the system remember? The name? The year? Which club? What if someone types year 20? In UK schools, the years go from 7 to 13.
Before you build tables, you write down every fact and every rule. This is the design step.
Key words
| Word | What it means | Example |
|---|---|---|
| Database | An organised store of data that programs can search and change. | |
| Data dictionary | A list of every field in the database, with its type, size and rules. | |
| Field | One fact in a table, such as a name. It is one column. | |
| Data type | The kind of value a field holds, such as text, whole number or date. | |
| Validation | A check that data is sensible before it is stored. | year must be 7 to 13 |
| Primary key | A field that is different for every row. It finds one row. | |
| Foreign key | A field that holds the primary key of a row in another table. | |
| Entity | A thing the database stores facts about, such as a member or a club. Each entity is a table. | |
| ERD | Entity relationship diagram. A picture of the tables and how they link. | |
| One-to-many | One row in table A links to many rows in table B. | |
| Many-to-many | Many rows in A link to many rows in B. It needs a link table between them. |
The cells students fill in are left blank in this preview.
Warm up: what to remember
Have a go
Warm up: what to remember
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
The data dictionary
A data dictionary has one row for each field. Everybody on the team reads the same list. Nobody guesses.
For each field it says the name, the type, the size, if it is required, and the validation rule. Read the one below for the member table. Then answer the quick check.
A data dictionary for member
| Field | Type | Size | Required | Validation | Key |
|---|---|---|---|---|---|
| member_id | Whole number | Up to 6 digits | Yes | Different for every member | Primary key |
| name | Text | Up to 40 characters | Yes | Not empty | |
| year | Whole number | 2 digits | Yes | From 7 to 13 | |
| Text | Up to 80 characters | No | Must contain @ |
The cells students fill in are left blank in this preview.
Quick check: reading the dictionary
Have a go
Quick check: reading the dictionary
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.