Spec & Goals 3 min
AQA Spec 3.7.1 — Relational databases: tables, records, fields
By the end of this lesson you can:
- Define a relational database.
- Use the terms table, record and field correctly.
- Show how splitting data into tables reduces redundancy.
Warm-Up 5 min
Last lesson, one big flat file caused repeated data. A relational database solves this by splitting data into separate, linked tables.
Quick starter
A library has "members" and "loans". Why might it make sense to keep these as two tables rather than one?
Reveal the idea
A member's details are stored once in the members table; each loan just refers to that member. No repeating a full address on every loan.
Key Concept — tables, records, fields 14 min
A relational database stores data in two or more tables that are linked together by common fields (the next lesson covers the keys that link them).
The three core terms
| Term | Meaning | In a table… |
|---|---|---|
| Table (entity) | Stores data about one type of thing (e.g. all Students). | The whole grid. |
| Record | All the data about one item (one student). | A single row. |
| Field | One piece of data stored for every record. | A single column. |
Our sample table: Student
We'll use this table throughout the SQL lessons.
| StudentID | Name | Form | Town | Mark |
|---|---|---|---|---|
| 101 | Aisyah | 10A | Ipoh | 78 |
| 102 | Wei Jie | 10B | Penang | 65 |
| 103 | Priya | 10A | Ipoh | 91 |
| 104 | Arjun | 11C | Johor Bahru | 54 |
| 105 | Mei Ling | 11C | Penang | 88 |
| 106 | Hafiz | 10B | Kuala Lumpur | 72 |
- The whole grid is the Student table (the entity "Student").
- Each row (e.g. 103 · Priya · 10A · Ipoh · 91) is a record.
- Each column (e.g.
Town) is a field.
Worked Example — splitting into tables 12 min
Problem: A library's flat file repeats member details on every loan. Redesign it as two linked tables.
Member table — each member stored once:
| MemberID | Name | Town |
|---|---|---|
| 101 | Aisyah | Ipoh |
| 102 | Wei Jie | Penang |
Loan table — each loan refers to a member by ID:
| LoanID | MemberID | Book |
|---|---|---|
| 5001 | 101 | Sapiens |
| 5002 | 101 | Dune |
| 5003 | 102 | 1984 |
Aisyah has two loans, but her name and town are stored once (MemberID 101). The Loan table just stores 101. Redundancy gone; update her town in one place.
Try It Yourself 12 min
Goal: In the Student table, state how many records and how many fields there are.
Hint: rows vs columns.
Goal: Define record and field, giving an example of each from the Student table.
Goal: A shop stores customers and their orders in one table. Sketch two tables that would store the data with less redundancy.
📝 Exam Practice 10 min
Define the term relational database.
Mark scheme
- A database that stores data in two or more linked tables (1).
In a database table, state what is meant by a record and a field.
Mark scheme
- Record — a row / all the data about one item (1).
- Field — a column / one item of data stored for every record (1).
Explain how using two linked tables instead of one reduces data redundancy.
Mark scheme
- Each item's details are stored only once in its own table (1).
- Other tables refer to it by an ID instead of repeating the data (1).
Recap & Key Terms 3 min
A relational database stores data in multiple linked tables. A record is a row (one item); a field is a column (one fact). Splitting data by entity stores each fact once, cutting redundancy. Tables are linked by keys — the next lesson.
- Relational database
- A database storing data in multiple tables linked by common fields.
- Table (entity)
- A grid holding data about one type of thing.
- Record
- A row in a table — all the fields about one item.
- Field
- A column in a table — one item of data held for every record.
Homework 1 min
Task (≤ 15 min): Design a two-table relational database for a music streaming app — one table for artists and one for songs. List the fields in each, and circle which field links them.
Model answer (example)
Artist(ArtistID, Name, Country). Song(SongID, Title, ArtistID, Length). The linking field is ArtistID — stored once in Artist, referenced by each Song. So an artist's details are not repeated on every song.
Award marks for: two sensible tables (1); appropriate fields (1); correct linking field identified (1).