Mini-Lesson

Relational databases & SQL

This mini-lesson covers AQA 3.7 — Relational databases and SQL: flat file vs relational databases, the vocabulary of tables, records and fields, primary and foreign keys, and writing SQL (SELECT, FROM, WHERE, ORDER BY, INSERT INTO, UPDATE, DELETE).

Work through each screen, answer the questions as you go (some are wordy, some are SQL calculations) and collect ⭐ stars. Press Start when you're ready.

Flat file vs relational

Two ways to store data

A database is an organised store of data. AQA compares two designs:

Flat file database — one single table holds everything. If the same customer buys three times, their name, address and phone number are repeated on every row. This duplication (redundancy) wastes space and is hard to maintain — change one detail and you must edit it everywhere, or the data becomes inconsistent.
Relational database — data is split across multiple linked tables. Each fact is stored once and tables are joined using keys. This reduces redundancy, avoids inconsistency and makes updating easy.

Definition to memorise: a relational database stores data in two or more tables that are linked together. It reduces data duplication and keeps data consistent.

Quick check

Why relational?

?Why is a relational database usually better than a single flat file?

Database vocabulary

Tables, records & fields

You must use the correct words for the parts of a table:

Table — stores data about one entity (a thing you want to record, e.g. Student).
Record — a single row in a table (all the data about one student).
Field — a single column / attribute in a table (e.g. Surname).

Table = the whole grid · a record is one row · a field is one column.

Keys & links

Primary & foreign keys

Keys are how relational databases link tables together:

Primary key — a field that uniquely identifies each record in a table. No two records share the same value (e.g. StudentID). Every table has one.
Foreign key — a field in one table that is the primary key of another table. It is what creates the link (relationship) between the two tables.

StudentID is the primary key of Student; the same field in Detention is a foreign key.

Remember: the foreign key in one table matches the primary key of another — that match is the relationship.

Quick check

Name that key

?A field that uniquely identifies each record in a table is called the…?

Calculate

Your turn — counting keys

1A relational database links tables using keys. A foreign key in one table matches the primary key of another table. If Table A already has 1 primary key field and you add 1 foreign key to link it to Table B, how many KEY fields are now in Table A? (primary + foreign)

key fields

Hint: 1 primary key + 1 foreign key = ?

SQL · reading data

SELECT, FROM, WHERE & ORDER BY

SQL (Structured Query Language) is used to search and change data. To retrieve data you use SELECT:

SELECT lists the fields you want; FROM names the table.
WHERE filters rows using a condition.
ORDER BY … ASC / DESC sorts the results (ascending or descending).
SELECT * returns all fields.

-- Year 11 students, A→Z by surname SELECT FirstName, Surname FROM Student WHERE YearGroup = 11 ORDER BY Surname ASC

Exam skill: read the request, pick the right fields, the correct table, a sensible WHERE condition, and add ORDER BY … ASC/DESC only if a sort is asked for.

Quick check

Which keyword?

?Which SQL keyword filters rows by a condition (so only matching records are returned)?

Run the query

Your turn — count the results

2Look at the Student table below, then work out how many records the query returns.
ID | Name | Year
1  | Amy  | 11
2  | Ben  | 10
3  | Cara | 11
4  | Dan  | 9
5  | Eve  | 11

How many records does this return?
SELECT * FROM Student WHERE Year = 11

records

Hint: find every row where Year = 11 (Amy, Cara, Eve).

SQL · changing data

INSERT, UPDATE & DELETE

SQL can also change the data in a table:

INSERT INTO — adds a new record, using the VALUES keyword.
UPDATE … SET … WHERE — changes existing data in matching records.
DELETE FROM … WHERE — removes matching records.

-- add a new student record INSERT INTO Student (ID, Name, Year) VALUES (6, 'Finn', 11)

-- move Ben up to Year 11 UPDATE Student SET Year = 11 WHERE Name = 'Ben'

-- remove Dan's record DELETE FROM Student WHERE Name = 'Dan'

Warning: always add a WHERE to UPDATE and DELETE — without it you would change or delete every record in the table!

Quick check

Which statement adds a record?

?Which SQL statement ADDS a new record to a table?

Sort it

Which SQL statement?

Tap a description, then tap the SQL statement it belongs to.

🔎 SELECT (read)

➕ INSERT (add)

🗑️ DELETE (remove)

Match it

Match term to meaning

Tap a description on the left, then its matching term on the right.

Description

Term

Recap

The big ideas to know

Flat file vs relational: flat file = one table, data duplicated & hard to maintain · relational = linked tables, less redundancy, consistent

Vocabulary: table = an entity · record = a row · field = a column / attribute

Keys: primary key uniquely identifies each record · foreign key = a primary key from another table that links them

Reading data: SELECT fields FROM table · WHERE filters · ORDER BY … ASC/DESC sorts · SELECT * = all fields

Changing data: INSERT INTO … VALUES (add) · UPDATE … SET … WHERE (change) · DELETE FROM … WHERE (remove)

You've covered all of AQA 3.7 — flat file vs relational databases, keys and SQL. Press Finish to see your score.

🏆

Mini-lesson complete!

⭐⭐⭐

You've worked through Relational databases and SQL for AQA GCSE Computer Science. 🎉

Your stars: 0 / 0

Next: test yourself in the Evaluate stage Confidence Quiz, then lock it in with Verify.

📣 Smashed it? Share your score

Challenge a mate to beat your stars, or show a parent how you got on.

→ Back to all subjects