Welcome to Data Normalisation!
Welcome to one of the most important chapters in your CCEA A2 1: Systems Approaches and Database Concepts unit! In your exam, you will be given a Pre-release Case Study containing business documents such as invoices, quotation forms, or job cards. Your task will be to turn messy, real-world data into a clean, efficient relational database.
Don't worry if normalisation seems tricky or abstract at first. Think of it like organising an overstuffed wardrobe: instead of throwing all your clothes, shoes, and coats into one chaotic drawer, you sort them into specific sections where everything has its own designated place and nothing gets duplicated. Let's break it down step-by-step!
---1. What is Data Normalisation and Why Do We Need It?
Data Normalisation is a formal, systematic database design technique used to organise data fields in a relational database. Its primary goals are to minimise data redundancy (duplicate data) and avoid data anomalies (errors when adding, editing, or removing data), thereby preserving data integrity.
The Three Database Anomalies:
When a database is un-normalised and stored in one giant flat table, three major problems occur:
• Insertion Anomaly: You cannot record information about an entity without recording data for another entity first (e.g. you cannot add a new product until a customer actually buys it).
• Update / Modification Anomaly: If an item's price or a customer's address changes, you have to update dozens or hundreds of individual rows. If you miss one, your database contains conflicting data.
• Deletion Anomaly: Deleting a record removes more information than intended (e.g. deleting a cancelled order accidentally erases the only record of that customer's contact details).
The Core Solution: Decomposition
Decomposition is the process of breaking down a large, un-normalised data structure or flat table into smaller, well-structured relational tables linked together using Primary Key and Foreign Key relationships.
Key Takeaway: Normalisation takes a messy flat structure and decomposes it into tidy, linked tables to protect data integrity and stop duplicate information from causing errors.
---2. Pre-Normalisation Steps: Document Analysis
Before jumping straight into the mathematical stages of normalisation in your CCEA examination, you must inspect the raw form or case study document carefully. Follow these essential preparatory steps:
Step 1: Identify and Remove Derived Fields
A derived field (or calculated field) is any value that can be calculated dynamically from other existing data. You must exclude derived fields from the normalisation process because storing calculated values wastes space and creates data redundancy.
Common derived fields to remove:
• Total Cost / Subtotal / Line Total (calculated by multiplying Quantity \(\times\) Unit Price)
• VAT (calculated from the net price and VAT percentage)
• Count / Number of Items (calculated by counting rows)
Step 2: Ensure Data is Atomic
Atomic data means that each field holds only one indivisible piece of information. Non-atomic (composite) fields must be broken down before starting:
• Customer Name \(\rightarrow\) FirstName, LastName
• Address \(\rightarrow\) AddressLine1, Town, Postcode
Step 3: Identify Repeating / Nested Groups
A repeating group consists of fields that can occur multiple times for a single primary entity. For example, on a single sales invoice (one invoice number, one date, one customer), there can be multiple item lines (different product codes, descriptions, and quantities).
Key Takeaway: Before starting normalisation: Strip out derived fields, split composite fields into atomic parts, and spot the repeating groups.
---3. Standard CCEA Relational Schema Notation
When writing your answers for normalisation in Unit A2 1, CCEA requires a specific relational schema notation:
TABLE_NAME(PrimaryKeyField, Attribute1, Attribute2, ForeignKeyField*)
• Primary Key: Always clearly underlined (e.g. CustomerID).
• Foreign Key: Marked with an asterisk (e.g. CustomerID*) to indicate a relational link to another table.
• Composite Primary Key: When two or more fields together form the primary key, underline all of them together (e.g. OrderID, ProductCode).
---4. The Three Normal Forms Explained
Un-normalised Form (UNF)
UNF is a flat list representing all raw fields from the source document (with calculated/derived fields removed, composite attributes split into atomic fields, and repeating groups clearly identified).
First Normal Form (1NF)
The Rule: A relation is in 1NF if:
1. All attributes are atomic (no composite fields).
2. There are no repeating groups.
3. A unique Primary Key (frequently a composite key) is identified to uniquely identify every row.
How to transform UNF to 1NF:
Separate the repeating items into their own group. To link this repeating group back to the main document, combine the primary key of the main entity with the primary key of the repeating item. This forms a composite primary key.
1NF Memory Hook: "Each cell has one single value, and there are no repeating groups!"
Second Normal Form (2NF)
The Rule: A relation is in 2NF if:
1. It is already in 1NF.
2. It contains NO partial dependencies (every non-key attribute must be fully functionally dependent on the entire primary key).
What is a Partial Dependency?
A partial dependency only happens when a table has a composite primary key (e.g. OrderID, ProductCode). If an attribute depends on only one part of that key (for example, ProductDescription depends only on ProductCode, not on OrderID), it is a partial dependency!
How to transform 1NF to 2NF:
Remove the partially dependent attributes and place them into a new table where the partial key becomes the standalone primary key. Leave the composite key table intact with only the attributes that truly depend on both keys (e.g. QuantityOrdered).
2NF Memory Hook: "No partial keys! Every non-key field must depend on the whole key."
Third Normal Form (3NF)
The Rule: A relation is in 3NF if:
1. It is already in 2NF.
2. It contains NO transitive dependencies (no non-key attribute depends on another non-key attribute, expressed as \(X \rightarrow Y \rightarrow Z\)).
What is a Transitive Dependency?
This happens when a non-key attribute depends on another non-key attribute instead of directly on the primary key. For example: In an ORDER table, CustomerID determines CustomerLastName and CustomerTown. Because CustomerID is not the primary key of the ORDER table (the primary key is OrderID), this is a transitive dependency (\(\text{OrderID} \rightarrow \text{CustomerID} \rightarrow \text{CustomerLastName}\)).
How to transform 2NF to 3NF:
Extract the transitively dependent non-key attributes into a new relation with their determinant as the primary key. Always leave that determinant behind in the original table as a Foreign Key (marked with an asterisk *) so the tables remain linked!
3NF Memory Hook: "Every non-key field must depend on the key, the whole key, and nothing but the key!"
---5. Step-by-Step Worked Example
Let us walk through a typical CCEA exam scenario: a customer sales invoice.
Raw Invoice Document Fields:
InvoiceNo, InvoiceDate, CustomerName, CustomerAddress, ProductCode, ProductDescription, UnitPrice, Quantity, LineTotal, InvoiceTotal
Step A: Pre-Normalisation Analysis
• Remove derived fields: Exclude LineTotal and InvoiceTotal.
• Make fields atomic: Split CustomerName into CustomerFirstName, CustomerLastName. Split CustomerAddress into AddressLine1, Town, Postcode.
• Identify repeating group: A single invoice contains multiple items (ProductCode, ProductDescription, UnitPrice, Quantity).
Step B: UNF (Un-normalised Form)
INVOICE_DATA(InvoiceNo, InvoiceDate, CustomerID, CustomerFirstName, CustomerLastName, AddressLine1, Town, Postcode, (ProductCode, ProductDescription, UnitPrice, Quantity))
Step C: 1NF (Remove Repeating Groups)
We split the data into the main invoice entity and the repeating invoice line items entity (with a composite primary key):
INVOICE(InvoiceNo, InvoiceDate, CustomerID, CustomerFirstName, CustomerLastName, AddressLine1, Town, Postcode)
INVOICE_LINE(InvoiceNo, ProductCode, ProductDescription, UnitPrice, Quantity)
Step D: 2NF (Remove Partial Dependencies)
Look at INVOICE_LINE. The primary key is composite (InvoiceNo, ProductCode). ProductDescription and UnitPrice depend only on ProductCode, not on InvoiceNo. Remove them to a new table:
INVOICE(InvoiceNo, InvoiceDate, CustomerID, CustomerFirstName, CustomerLastName, AddressLine1, Town, Postcode)
INVOICE_LINE(InvoiceNo*, ProductCode*, Quantity)
PRODUCT(ProductCode, ProductDescription, UnitPrice)
Step E: 3NF (Remove Transitive Dependencies)
Look at INVOICE. CustomerFirstName, CustomerLastName, AddressLine1, Town, and Postcode depend on CustomerID (a non-key attribute in this table). Extract them to a new CUSTOMER table, leaving CustomerID* behind as a foreign key:
INVOICE(InvoiceNo, InvoiceDate, CustomerID*)
CUSTOMER(CustomerID, CustomerFirstName, CustomerLastName, AddressLine1, Town, Postcode)
INVOICE_LINE(InvoiceNo*, ProductCode*, Quantity)
PRODUCT(ProductCode, ProductDescription, UnitPrice)
---6. Pitfalls and Common Examiner Mistakes
Make sure you avoid these common traps highlighted in CCEA examiner reports:
• Retaining Derived Attributes: Leaving calculated fields like VAT, Subtotal, or TotalCost in your relations will cost you direct marks. Always delete them before 1NF!
• Forgetting to Split Composite Fields: Leaving full names or full addresses combined prevents fields from being atomic.
• Confusing 2NF and 3NF: Remember, 2NF ONLY applies to tables with composite primary keys. If a table has a single primary key, it is already automatically in 2NF!
• Losing Foreign Keys: When you break a table apart in 2NF or 3NF, always leave the linking foreign key attribute (marked with an asterisk *) in the referencing table.
• Inconsistent Schema Notation: Always underline primary keys (and all parts of composite keys) and use clear, meaningful table names as required by the case study.
---Quick Summary Checklist
• UNF: Derived fields deleted, composite attributes split, repeating groups identified.
• 1NF: Atomic attributes, no repeating groups, primary/composite key defined.
• 2NF: In 1NF + no partial dependencies (check tables with composite keys).
• 3NF: In 2NF + no transitive dependencies (no non-key field depends on another non-key field; foreign keys * retained).