s5.07.03 - normal forms
Master database normalisation to 3NF, understanding 1NF, 2NF, and 3NF to design efficient, reliable relational schemas.
Imagine a world where every piece of information about you - your name, your address, every single purchase you've ever made - was duplicated endlessly across hundreds of different lists. Sounds messy, right? In computer science, we have a powerful tool to prevent this digital chaos: normalisation. This lesson will transform you into a data sculptor, teaching you how to refine raw, messy data into sleek, efficient, and reliable database structures, a skill essential for any aspiring Database Administrator or Software Developer.
Learning Outcomes
Factual (Knowing that)
Conceptual (Knowing why)
Procedural (Knowing how)
Recall the definitions and goals of normalisation.
Describe the characteristics of data in First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF).
State the purpose and key components of a data dictionary.
Conceptual (Knowing why)
Analyse the problems associated with unnormalised data, such as data redundancy and update anomalies.
Explain how normalisation improves data integrity, reduces storage requirements, and enhances query efficiency.
Distinguish between partial key dependencies and non-key dependencies as violations of 2NF and 3NF respectively.
Procedural (Knowing how)
Apply the rules of normalisation to convert unnormalised data into First Normal Form (1NF).
Apply the rules of normalisation to convert data from 1NF to Second Normal Form (2NF).
Apply the rules of normalisation to convert data from 2NF to Third Normal Form (3NF).
Create a simple data dictionary for a normalised database schema.
The Deep Dive
Normal Forms: Sculpting Reliable Data
The Problem with Unorganised Data
Imagine a spreadsheet recording student enrolments in courses. Each row might contain student details (ID, name, address) and course details (ID, name, tutor, room) for every single enrolment. This creates huge amounts of data redundancy, meaning the same information is stored multiple times. Why is this a problem?
Update Anomalies: If a student's address changes, you have to update it in many rows. Miss one, and your data becomes inconsistent.
Deletion Anomalies: If the last student for a particular course drops out, deleting their record might accidentally delete all information about that course!
Insertion Anomalies: You can't record information about a new course until a student enrols in it.
Storage Inefficiency: Duplicated data wastes valuable storage space.
These issues are a nightmare for any Database Administrator or Software Engineer trying to build reliable systems. This is where normalisation comes in. Normalisation is a systematic process of organising the fields and tables of a relational database to minimise data redundancy and improve data integrity. It's about breaking down large tables into smaller, related tables and defining relationships between them.
The Journey to Third Normal Form (3NF)
Normalisation is typically performed in steps, called 'Normal Forms'. While there are higher forms (like BCNF, 4NF, 5NF), A-Level Computer Science focuses up to Third Normal Form (3NF). Each normal form has specific rules that must be met.
First Normal Form (1NF)
The first step to tidying up your data! To be in 1NF, a table must satisfy two primary conditions:
1
Eliminate Repeating Groups: Each cell must contain only a single value. No lists or multiple entries in one cell.
2
Atomic Values: Each attribute (column) in a table must contain only one value for each record. For example, a "PhoneNumber" column shouldn't contain "07700 900123, 01234 567890" in a single cell.
How to achieve 1NF:
If you have repeating groups, you generally create new rows for each repeated item, or create a separate table for the repeating data and link it back with a foreign key. The easiest approach for a simple unnormalised list is to ensure each row-column intersection (cell) has a single, atomic value.
Second Normal Form (2NF)
2NF builds on 1NF. A table is in 2NF if it is in 1NF AND all non-key attributes are fully functionally dependent on the primary key. This means there are no partial key dependencies.
Partial Key Dependency: This occurs when an attribute depends on only part of a composite primary key, rather than the entire primary key. This can only happen if you have a composite primary key (a primary key made up of two or more columns).
How to achieve 2NF:
If you identify an attribute that depends on only part of a composite primary key, move that attribute and the part of the composite key it depends on into a new table. The part of the composite key becomes the primary key of the new table, and also remains a foreign key in the original table.
Third Normal Form (3NF)
3NF builds on 2NF. A table is in 3NF if it is in 2NF AND there are no transitive dependencies (also known as non-key dependencies).
Transitive Dependency / Non-Key Dependency: This occurs when a non-key attribute depends on another non-key attribute. In other words, an attribute is indirectly dependent on the primary key through another non-key attribute.
How to achieve 3NF:
If you find a non-key attribute that determines another non-key attribute, move both attributes into a new table. The determining attribute becomes the primary key of the new table, and a foreign key in the original table.
The Data Dictionary: Your Database Blueprint
Once your database is normalised, it's crucial to document its structure. A data dictionary is a file or a set of files that contains a database's metadata - its definitions and characteristics. Think of it as the blueprint for your database.
Key elements typically included for each table and attribute:
Table Name: The name of the table (e.g.,
StudentAttribute Name: The name of each column (e.g.,
StudentIDFirstNameData Type: The type of data stored. Choose from (amongst others):
TEXTINTEGERFLOATBOOLEANDATEDATETIMELength/Size: Maximum length for strings or precision for numbers.
Key Type: Is it a Primary Key (PK), Foreign Key (FK), or a Secondary Key (SK)?
Nullability: Can the field be left empty (
NULLNOT NULLDescription: A clear explanation of what the attribute represents.
Data dictionaries are vital for maintaining, understanding, and extending complex databases. They help Database Designers and Developers ensure consistency and facilitate collaboration.
Construct a strict hierarchical concept map. Place the broadest overarching theory at the top and link downwards to specific factual examples. You must label the relationship on every connecting arrow.
Comprehension Questions
Answer these questions on your lesson sheet.1
Explain three problems that can arise from having unnormalised data in a database.
2
Describe the primary rule that a table must satisfy to be in First Normal Form (1NF).
3
Differentiate between a partial key dependency and a non-key dependency, providing an example of each.
4
Outline the steps required to convert a table from 1NF to Second Normal Form (2NF).
5
State four pieces of information typically included in a data dictionary for an attribute.
Task Database Architect Challenge: Normalising the 'Retail Order' Data
Welcome, aspiring Database Architects! Your challenge is to take a messy, unnormalised dataset from a hypothetical retail company and transform it into a robust, efficient database schema using normalisation principles. This is the kind of task a Data Engineer or Database Developer faces every day to ensure data integrity and system performance.
0
Get Organised!
Look carefully at the unnormalised table called 'RetailOrders'.
Use the reverse side of your lesson sheet for the following tasks.
1
Analyse the Unnormalised Data
Carefully examine the 'RetailOrders' table provided on your lesson sheet.
Identify any repeating groups, partial key dependencies, or non-key dependencies. Make notes on your sheet.
2
Achieve First Normal Form (1NF)
Convert the 'RetailOrders' table to 1NF. This might involve creating new rows or identifying the need for separate tables.
On your lesson sheet, draw out the new table(s) and clearly state the primary key for each.
3
Achieve Second Normal Form (2NF)
Examine your 1NF table(s). If any table has a composite primary key, check for partial key dependencies.
If you find any, split the table(s) to remove these dependencies, creating new tables as needed.
On your lesson sheet, draw out the 2NF table(s) and clearly identify all primary and foreign keys.
4
Achieve Third Normal Form (3NF)
Examine your 2NF table(s). Look for any non-key attributes that are dependent on other non-key attributes (transitive dependencies).
If found, split the table(s) further to achieve 3NF, creating new tables as required.
On your lesson sheet, draw out your final 3NF table(s) and identify all primary and foreign keys.
5
Create a Data Dictionary
Using your Word or Google Docs document, create a data dictionary for your final 3NF database schema.
For each table you've created, list every attribute. For each attribute, include:
Attribute Name
Data Type
Length/Size (if applicable)
Key Type (PK, FK, SK, None)
Nullability (YES/NO)
Description (a brief explanation)
Save this document as RetailDatabase_DataDictionary.docx (or .pdf).
Hungry for more?
Search the web: Boyce-Codd Normal Form advantages disadvantages
Ask an AI: Click on the carefully written prompt below to learn more...
Explain database normalisation as an expert Software Engineer to an A-Level Computer Science student. Focus on the practical benefits and challenges of achieving 3NF in real-world systems. Limit to 150 words. NO intro, NO outro, NO deviation from the topic, NO follow-up questions.
Advanced Normalisation: Research 4NF and 5NF and briefly explain a scenario where each would be necessary.
Outcome: You will have transformed an unnormalised dataset into a fully normalised 3NF schema and documented it with a comprehensive data dictionary.
Sticky Note Challenge
On a sticky note, write down the most challenging aspect of normalisation you encountered in this lesson and one strategy you will use to overcome it in future.
Last modified: November 5th, 2026
