Introduction to Relational Databases

AQA 4.10.1 — Relational databases

This is genuinely new content. Year 12 does not cover databases or SQL at any point — nothing here is "advanced" relative to prior teaching; it's the first lesson of an entirely new subject area, and is taught from first principles accordingly.

Key vocabulary

• Database — an organised, persistent collection of related data.
• DBMS (Database Management System) — the software that stores, retrieves, and manages a database, and enforces the rules (structure, integrity, access) defined for it.
• Table — a named collection of data organised into rows and columns, each table representing one type of "thing" (customers, products, orders).
• Row (record) — one specific entry in a table.
• Column (field) — one specific piece of data every row in a table has.
• Primary key (PK) — a column (or set of columns) that uniquely identifies each row in a table; no two rows may share one, and it cannot be empty.
• Foreign key (FK) — a column in one table that references the primary key of another table, creating a relationship between them.
• Schema — the structural definition of a database: which tables exist, their columns, and their relationships.

Understand — why relational databases exist

A spreadsheet or flat file struggles as data grows: the same customer's details might be repeated across hundreds of order rows, wasting space and risking inconsistency (what if their city is spelled two different ways in two different rows?). A relational database solves this by splitting data into separate, focused tables — one row per customer, stored once — and using foreign keys to link related data together only when needed, rather than duplicating it everywhere.

See it — this course's sample schema

customers                      products
──────────────────────         ─────────────────────────
id  (PK)                       id  (PK)
name                           name
city                           category
loyalty_points                 price
                                stock

orders                          order_items
──────────────────────         ─────────────────────────
id  (PK)                       id  (PK)
customer_id (FK -> customers)  order_id (FK -> orders)
product_id  (FK -> products)   product_id (FK -> products)
quantity                       quantity
order_date

orders.customer_id is a foreign key: every value that appears there must match an actual id already present in customers — this is precisely what stops an order from ever referencing a customer who doesn't exist. order_items links orders to products in a genuinely richer way, covered fully in a later lesson.

Reason about why each key is exactly what it is

products.id is the primary key because it uniquely identifies one specific product, and no two products may share an id. orders.customer_id is a foreign key, not a primary key, because many orders can legitimately belong to the same customer — it doesn't uniquely identify an order row on its own (that's orders.id's job); it identifies which customer the order belongs to.

Exam-style worked example 1

Question: State the primary key of the order_items table, and explain why order_items.product_id is described as a foreign key rather than a primary key. (3 marks)

Model answer: The primary key of order_items is id — it uniquely identifies each individual row (each specific line item) in the table. product_id is a foreign key because it references products.id to identify which product a line item refers to, but the same product can legitimately appear in many different order_items rows (across many orders), so it cannot uniquely identify a row on its own — that is exactly what distinguishes a foreign key from a primary key.

Exam-style worked example 2

Question: A school database has tables students (id, name, form_group) and attendance_records (id, student_id, lesson_date, present). Identify the foreign key in attendance_records, and explain what relationship it represents. (3 marks)

Model answer: The foreign key is student_id, referencing students.id. It represents a one-to-many relationship: one student can have many attendance records (one per lesson they were expected to attend), but each individual attendance record belongs to exactly one student.

Common mistake

Assuming a foreign key must be unique within its own table, the way a primary key is. A foreign key is expected to repeat — orders.customer_id correctly contains the same value multiple times whenever one customer places several orders; that repetition is exactly what makes the relationship "one customer, many orders" work.

Real-life applications

• A hospital's patient records system — one patients table, linked via foreign keys to appointments, prescriptions, and test_results tables, avoiding a patient's details being re-typed on every single record.
• A bank's account system — customers, accounts, and transactions tables, where every transaction's foreign keys guarantee it can always be traced back to a real account and a real customer.

Check your understanding

A library database has tables books (id, title, author) and loans (id, book_id, borrower_name, due_date). Identify the primary key of loans, identify its foreign key, and explain why a book's title is not duplicated inside the loans table itself. (4 marks)

(Primary key of loans: id. Foreign key: book_id, referencing books.id. The book's title is not duplicated inside loans because the foreign key alone is enough to look the title up in the books table whenever it's needed - storing it a second time in loans would risk the two copies becoming inconsistent if the book's title were ever corrected, and wastes space across every loan of the same book.)

Challenge

Design a simple schema (as a list of tables, columns, primary keys and foreign keys) for a school's after-school clubs system: students can join multiple clubs, and each club has multiple students. State which table(s) you'd need beyond just students and clubs themselves, and why.

Looking ahead: the next lesson starts writing genuine SQL queries against this exact schema — reading, filtering, and ordering real data.