Normalisation and SQL

A-Level Computer Science · Databases

Relational databases

A relational database stores data in tables (relations). Each table has records (rows) and fields (columns/attributes). Tables are linked using keys, which reduces duplication and keeps data consistent.

Keys

Primary key — a field (or combination) that uniquely identifies each record in a table.
Foreign key — a field that refers to the primary key of another table, creating the link (relationship) between tables.
Composite key — a primary key made of more than one field.

Normalisation

Normalisation is the process of organising tables to remove redundancy (repeated data) and avoid update, insertion and deletion anomalies.

First Normal Form (1NF): no repeating groups; each field holds a single (atomic) value; there's a primary key.
Second Normal Form (2NF): in 1NF and every non-key field depends on the whole primary key (removes partial dependencies — relevant when there's a composite key).
Third Normal Form (3NF): in 2NF and no non-key field depends on another non-key field (removes transitive dependencies).

Normalised databases avoid storing the same data twice, so updates only happen in one place → fewer errors.

SQL — the query language

SQL (Structured Query Language) is used to manage and query relational databases.

SELECT name, grade
FROM Students
WHERE grade > 60
ORDER BY name ASC;

Common keywords:

SELECT … FROM … WHERE — retrieve data matching a condition.
ORDER BY — sort results (ASC/DESC).
INSERT INTO … VALUES — add a record.
UPDATE … SET … WHERE — change existing records.
DELETE FROM … WHERE — remove records.
JOIN — combine rows from two tables on a matching key.

Referential integrity & transactions

Referential integrity ensures a foreign key always refers to an existing primary-key record (no "orphan" links).
ACID properties (Atomicity, Consistency, Isolation, Durability) keep transactions reliable, even with many users.

Worked example

Why split customer and order data into two tables linked by a key rather than one big table?

Storing each customer's details once (in a Customers table) and linking orders via a foreign key avoids redundancy and update anomalies — you change a customer's address in one place, not on every order row. ✓

Common mistakes

Confusing primary (unique ID in this table) and foreign (reference to another table's primary key) keys.
Mixing up the normal forms — 2NF removes partial, 3NF removes transitive dependencies.
Forgetting the WHERE clause in UPDATE/DELETE (would affect every row).

Exam tips

Learn 1NF/2NF/3NF definitions and be able to normalise a simple table.
Write and read basic SQL (SELECT/WHERE/ORDER BY, INSERT, UPDATE, DELETE, JOIN).
Explain primary/foreign keys and why normalisation reduces redundancy.

Key facts to remember

Relational DBs use tables linked by primary/foreign keys; normalisation removes redundancy and anomalies.
1NF (atomic, no repeating groups), 2NF (no partial dependency), 3NF (no transitive dependency).
SQL manages data: SELECT/FROM/WHERE, ORDER BY, INSERT, UPDATE, DELETE, JOIN; referential integrity protects foreign-key links.