Imagine a school spreadsheet storing student details, subject names, and teacher names all in one table — repeating the teacher's name on every row. Change one name and you must update dozens of rows, risking errors. A relational database solves this by splitting data into linked tables, storing each fact exactly once.
What is a relational database?
A relational database is a collection of tables (also called relations) where data is organised into rows and columns, and the tables are linked to each other through key fields. The relational model was proposed by Edgar F. Codd in 1970 and underpins most modern database systems, including MySQL, PostgreSQL, SQLite, and Microsoft SQL Server.
Each table stores data about one specific entity — for example, Students, Teachers, or Subjects — avoiding the repetition that plagues a single flat-file spreadsheet.
What are tables, records, and fields?
| Term | Meaning | Example |
|---|---|---|
| Table | A grid of rows and columns for one entity | Students table |
| Record (row) | One complete set of data for a single item | One student's details |
| Field (column) | A single category of data within the table | last_name, year_group |
| Primary key | A field whose value uniquely identifies each record | student_id (no two students share the same ID) |
| Foreign key | A field in one table that matches a primary key in another | teacher_id in the Subjects table linking to the Teachers table |
How do tables link together?
Consider a school database with three tables:
Students
| student_id | first_name | last_name | year_group |
|---|---|---|---|
| S001 | Priya | Sharma | 10 |
| S002 | Marcus | Chen | 11 |
Subjects
| subject_id | subject_name | teacher_id |
|---|---|---|
| C001 | Computing | T004 |
| M001 | Mathematics | T007 |
Teachers
| teacher_id | teacher_name | department |
|---|---|---|
| T004 | Ms Ahmed | Computing |
| T007 | Mr Patel | Mathematics |
The teacher_id in Subjects is a foreign key pointing to the teacher_id in Teachers. This link means: "to find out who teaches Computing, look up T004 in the Teachers table." Ms Ahmed's name is stored once — if she changes her name, you update one record and it is correct everywhere.
What is a flat-file database and why is a relational database better?
A flat-file database stores all data in a single table — like one very large spreadsheet. The problems:
| Problem | Flat-file | Relational database |
|---|---|---|
| Redundancy | Teacher name repeated on every subject row | Stored once in Teachers table |
| Update anomaly | Change teacher name → must update every row | Change one record |
| Insertion anomaly | Can't add a teacher who teaches no subjects | Add teacher independently |
| Deletion anomaly | Deleting the last subject removes teacher's details too | Tables are separate |
These problems are collectively called data anomalies. A well-designed relational database, using the process of normalisation, eliminates them.
How do you query across linked tables?
SQL (Structured Query Language) uses a JOIN to combine data from two tables in a single query:
SELECT Students.first_name, Students.last_name, Subjects.subject_name
FROM Students
JOIN Enrolments ON Students.student_id = Enrolments.student_id
JOIN Subjects ON Enrolments.subject_id = Subjects.subject_id
WHERE Students.year_group = 10;
This query finds all Year 10 students and the subjects they study, pulling data from three tables in one operation. You do not need to store the student's name next to the subject — the JOIN assembles the answer at query time.
What is normalisation?
Normalisation is the process of structuring a database to reduce redundancy and improve data integrity. It involves a set of rules called Normal Forms (1NF, 2NF, 3NF). At GCSE level, the key idea is:
- First Normal Form (1NF): Each column holds one value per row; no repeating groups.
- Second Normal Form (2NF): Every non-key field depends on the whole primary key (relevant when the key is composite).
- Third Normal Form (3NF): No non-key field depends on another non-key field (eliminate transitive dependencies).
In plain English: each piece of data should be stored in exactly the right place, dependent on the primary key, and nowhere else.
Frequently asked questions
What is the difference between a primary key and a foreign key?
A primary key uniquely identifies each record within its own table — no two rows can have the same value. A foreign key is a field in one table that refers to the primary key of another table, establishing a link between the two.
Does every table need a primary key?
Yes. Without a primary key it is impossible to guarantee that records are unique, and joining tables together reliably becomes impossible. Primary keys are fundamental to the relational model.
Why is a relational database better than storing data in a spreadsheet?
Spreadsheets are excellent for small, standalone datasets. They become problematic when the same data is needed in multiple places (causing redundancy), when multiple users need simultaneous access (causing conflicts), or when complex queries across multiple entities are required (causing manual effort). Relational databases handle all three scenarios correctly.
Is SQL the only language for querying relational databases?
SQL (Structured Query Language) is the universal standard for relational databases and is the language you will learn at GCSE. Different database systems (MySQL, SQLite, PostgreSQL) each have minor dialect differences, but the core commands — SELECT, FROM, WHERE, JOIN, INSERT, UPDATE, DELETE — are the same across all of them.
Need help understanding tables, keys, and SQL queries for your GCSE exam? Visit aitutors.me — Professor Turing will walk you through database design with real examples.