Normalisation

Contents

What is Normalisation?

UNF to 1NF — Removing Repeating Groups

Hardware order example: one value per cell, one fact per row

The repeating item columns (orange) move to their own table. Each item now has its own row, and the two tables are linked by Order Number.

1NF to 2NF — Removing Partial Dependencies

Hardware order example: Description and Unit Cost depend only on Item Number

Description and Unit Cost move to ITEM because they depend only on Item Number, not on the full composite key.

2NF to 3NF — Removing Transitive Dependencies

Hardware order example: Name, Address and Contact depend on Customer Number, not Order Number

The complete 3NF result: ORDER, CUSTOMER, ITEM, and ORDER_ITEM — four tables with no redundancy, no partial dependencies, and no transitive dependencies.

Hardware Order — Table Relationships

How the four 3NF tables connect through foreign keys

Hospital Visit System — Step by Step

Applying the three-step checklist to healthcare data

The hospital example produces five tables in 3NF. Each fact is stored once — updating a doctor's department or a treatment's cost requires changing only one row.

Hospital Visit System — Table Relationships

How the five 3NF tables connect through foreign keys

How to Identify Where to Split Tables

A three-step decision checklist for each normal form

Apply these three checks in order to any table. Each step builds on the previous one — you must achieve 1NF before checking for 2NF, and 2NF before checking for 3NF.

Worked Example — Lecturer Course System

Normalising a college timetable from UNF to 3NF

The lecturer example uses the same three-step checklist. Course Name had a partial dependency on Course Code, but no transitive dependency was found — 3NF was reached in step 2.

Lecturer Course System — Table Relationships

How the three 3NF tables connect through foreign keys

SQL — Step 1: UNF to 1NF

Split the single ORDER table into ORDER + ORDER_ITEM

-- STEP 1: UNF → 1NF
-- Remove repeating groups (item details) into a new table

CREATE TABLE "Order" (
    order_number     INT           PRIMARY KEY,
    order_date       DATE          NOT NULL,
    customer_number  VARCHAR(4)    NOT NULL,
    name             VARCHAR(50)   NOT NULL,
    address          VARCHAR(100)  NOT NULL,
    contact_number   VARCHAR(20)   NOT NULL
);

-- New table for the repeating item group
-- Composite key: order_number + item_number
CREATE TABLE Order_Item (
    order_number  INT          REFERENCES "Order",
    item_number   VARCHAR(10)  NOT NULL,
    description   VARCHAR(100) NOT NULL,
    unit_cost     DECIMAL(10,2) NOT NULL,
    quantity      INT          NOT NULL,
    PRIMARY KEY (order_number, item_number)
);

SQL — Step 2: 1NF to 2NF

Extract ITEM table to remove partial dependency

-- STEP 2: 1NF → 2NF
-- Description and unit_cost depend only on item_number
-- (partial dependency on the composite key)
-- Move them to a new ITEM table

CREATE TABLE Item (
    item_number  VARCHAR(10)   PRIMARY KEY,
    description  VARCHAR(100)  NOT NULL,
    unit_cost    DECIMAL(10,2) NOT NULL
);

-- Order_Item now only keeps quantity
-- (depends on the FULL composite key)
CREATE TABLE Order_Item (
    order_number  INT          REFERENCES "Order",
    item_number   VARCHAR(10)  REFERENCES Item,
    quantity      INT          NOT NULL,
    PRIMARY KEY (order_number, item_number)
);

SQL — Step 3: 2NF to 3NF

Extract CUSTOMER table to remove transitive dependency

-- STEP 3: 2NF → 3NF
-- Name, address and contact depend on customer_number
-- (transitive dependency — not on the PK)
-- Move them to a new CUSTOMER table

CREATE TABLE Customer (
    customer_number  VARCHAR(4)    PRIMARY KEY,
    name             VARCHAR(50)   NOT NULL,
    address          VARCHAR(100)  NOT NULL,
    contact_number   VARCHAR(20)   NOT NULL
);

-- Order now only keeps customer_number as FK
CREATE TABLE "Order" (
    order_number     INT           PRIMARY KEY,
    customer_number  VARCHAR(4)    REFERENCES Customer,
    order_date       DATE          NOT NULL
);

-- Final schema: 4 tables in 3NF
-- Customer, Order, Item, Order_Item
-- Each fact stored exactly once ✓

Quick Reference

Practice Exercises

Test your understanding of normalisation

Answers

References

Sources used to build this material