Database normalisation is the process of structuring a relational database to reduce redundancy and improve data integrity. GCSE Computer Science requires you to understand 1NF, 2NF, and 3NF and apply them to convert a flat, repetitive table into a clean set of related tables.
Why is a poorly designed database a problem?
Imagine a spreadsheet that records every book a library lends out. If you store the borrower's address alongside every single book they borrow, the address appears dozens of times. Now the borrower moves house — you must update every row, and if you miss one, the database contains contradictory information. Turing would describe this as a system that has encoded the same fact in more than one place: a structural flaw waiting to become a logical error.
Normalisation removes these anomalies systematically:
| Anomaly type | What happens without normalisation |
|---|---|
| Update anomaly | Changing one fact (e.g., a borrower's address) requires updating multiple rows |
| Insert anomaly | You cannot record a fact (e.g., a new subject) without inventing a related record |
| Delete anomaly | Deleting the last row about a student accidentally deletes information about their teacher |
What is First Normal Form (1NF)?
A table is in First Normal Form when:
- Every cell contains a single, atomic value — no lists or groups.
- Every row is unique (there is a primary key).
- Each column holds values of the same data type.
Example — unnormalised:
| OrderID | CustomerName | Items |
|---|---|---|
| 1 | Alice Brown | Pen, Notebook |
| 2 | Bob Lee | Ruler |
The Items column violates 1NF because it holds multiple values in one cell. To reach 1NF, split repeating groups into separate rows:
| OrderID | CustomerName | Item |
|---|---|---|
| 1 | Alice Brown | Pen |
| 1 | Alice Brown | Notebook |
| 2 | Bob Lee | Ruler |
Now every cell is atomic and a composite primary key (OrderID + Item) uniquely identifies each row.
What is Second Normal Form (2NF)?
A table is in Second Normal Form when it is already in 1NF and every non-key attribute is fully functionally dependent on the whole primary key — not just part of it. This only matters when the primary key is composite (made of two or more columns).
In the 1NF example above, CustomerName depends only on OrderID, not on Item. It is therefore only partially dependent on the composite key (OrderID, Item) — a 2NF violation.
Fix: split into two tables.
Orders table:
| OrderID | CustomerName |
|---|---|
| 1 | Alice Brown |
| 2 | Bob Lee |
OrderItems table:
| OrderID | Item |
|---|---|
| 1 | Pen |
| 1 | Notebook |
| 2 | Ruler |
Now each non-key attribute depends on the full primary key of its table.
What is Third Normal Form (3NF)?
A table is in Third Normal Form when it is already in 2NF and no non-key attribute is transitively dependent on the primary key through another non-key attribute. In plain language: non-key columns must depend only on the key, not on other non-key columns.
Example — 2NF but not 3NF:
| StudentID | StudentName | TeacherID | TeacherName |
|---|---|---|---|
| 101 | Priya | T5 | Mr Hassan |
| 102 | Sam | T5 | Mr Hassan |
TeacherName depends on TeacherID, not on StudentID. This is a transitive dependency: StudentID → TeacherID → TeacherName.
Fix: split into two tables.
Students table:
| StudentID | StudentName | TeacherID |
|---|---|---|
| 101 | Priya | T5 |
| 102 | Sam | T5 |
Teachers table:
| TeacherID | TeacherName |
|---|---|
| T5 | Mr Hassan |
TeacherName now lives with its key. The students table holds only a foreign key (TeacherID) linking to the teachers table.
How do you recognise which normal form is violated?
Work through this checklist in order:
- Check 1NF first: are there any multi-valued cells or repeated column groups? If yes, fix those before anything else.
- Check for partial dependencies (2NF): is the primary key composite? If so, does every non-key column depend on ALL of it?
- Check for transitive dependencies (3NF): can you reach a non-key column by hopping through another non-key column?
The mnemonic "The key, the whole key, and nothing but the key" captures all three forms: every non-key fact must depend on the key (1NF), on the whole key (2NF), and on nothing but the key (3NF).
What are the benefits of a normalised database?
| Benefit | Explanation |
|---|---|
| Less storage | Repeated data is stored once, not many times |
| Easier updates | Changing a fact requires editing one row in one table |
| Fewer anomalies | Insert and delete operations cannot accidentally corrupt unrelated data |
| Clearer structure | The schema reflects real-world relationships between entities |
The trade-off is that queries become more complex — retrieving related data requires JOIN operations across tables. For read-heavy applications this can be a concern, but for a transactional school database the integrity benefits far outweigh the query overhead.
How does normalisation relate to entity-relationship diagrams?
An entity-relationship (ER) diagram is the design tool; normalisation is the validation step. You might sketch an ER diagram with entities for Student, Teacher, and Subject, then check whether the resulting tables satisfy 1NF, 2NF, and 3NF. If a table fails 2NF, it usually means you missed a relationship in the ER diagram. The two tools are complementary: ER modelling captures structure; normalisation audits it for hidden redundancy.
Frequently asked questions
Do I need to know 4NF or BCNF for GCSE?
No. AQA and OCR GCSE Computer Science specifications require you to understand and apply 1NF, 2NF, and 3NF only. Boyce-Codd Normal Form (BCNF) and Fourth Normal Form (4NF) are covered at A-level and degree level. Focus on being able to identify violations of the three GCSE forms and explain how splitting tables fixes them.
What is a functional dependency?
A functional dependency means that knowing the value of one column (the determinant) allows you to determine the value of another. Writing A → B means "A determines B". In 2NF, every non-key column must be functionally dependent on the whole primary key. In 3NF, non-key columns must not determine other non-key columns (which would create a transitive dependency).
Can a table be in 3NF without being in 1NF first?
No. The normal forms are hierarchical: 3NF requires 2NF, which requires 1NF. If you attempt to check for transitive dependencies in a table that still has multi-valued cells, you are working on an ill-formed structure where the rules do not apply cleanly. Always achieve 1NF, then 2NF, then 3NF — in that order.
How do exam questions usually test normalisation?
Examiners typically give you an unnormalised flat table — often a school timetable, an order form, or a library record — and ask you to convert it to a given normal form. You are expected to identify which columns create the dependency violation, state what type of anomaly exists, and draw the resulting normalised tables with appropriate primary and foreign keys shown. Practise by working backwards: spot what fact is repeated, ask "what determines it?", and separate it into its own table.
Get step-by-step guidance on database design and normalisation from Professor Turing at aitutors.me.