Lesson 28 — Data Integrity Rules

Normalisation

Normalisation is the process of structuring tables to reduce redundancy and prevent update anomalies. Understanding 1NF, 2NF, and 3NF is fundamental to professional database design.

Why Normalise?

Consider this table where each row stores multiple values in one column and repeats data:

SQL — Unnormalised problems 
-- UNNORMALISED table (problem data):
-- learner_id | name    | courses           | city          | province
-- 1          | Thandi  | Java, Python, SQL | Johannesburg  | Gauteng
-- 2          | Sipho   | Java              | Cape Town     | Western Cape
-- 3          | Thandi  | C#                | Johannesburg  | Gauteng

-- Problems:
-- 1. Querying 'who studies Java' requires parsing comma-separated values
-- 2. If Thandi moves, update is needed in every row she appears
-- 3. Gauteng is repeated — typo in one row corrupts the province
-- 4. Deleting Sipho also destroys the fact that Cape Town is in Western Cape

First Normal Form (1NF)

Rule: Every column holds atomic (single, indivisible) values. No repeating groups. Each row is unique.

SQL — 1NF 
-- Violates 1NF — courses column has multiple values:
-- learner_id | name    | courses
-- 1          | Thandi  | Java, Python, SQL

-- 1NF — separate rows for each course (later improved by 2NF):
-- learner_id | name    | course
-- 1          | Thandi  | Java
-- 1          | Thandi  | Python
-- 1          | Thandi  | SQL
-- 2          | Sipho   | Java

-- City/province redundancy now obvious — a 2NF issue to fix next

Second Normal Form (2NF)

Rule: In 1NF AND every non-key column depends on the entire primary key (no partial dependency). Applies to tables with composite primary keys.

SQL — 2NF 
-- Table: (learner_id, course) composite PK
-- learner_name depends only on learner_id — partial dependency!
-- course_description depends only on course — partial dependency!

-- VIOLATES 2NF:
-- PK: (learner_id, course)
-- learner_name — depends on learner_id alone
-- course_description — depends on course alone
-- enrol_date — depends on (learner_id, course) — correct

-- FIX — split into three tables:
CREATE TABLE learners (
    learner_id   INT PRIMARY KEY,
    learner_name VARCHAR(100)
);
CREATE TABLE courses (
    course       VARCHAR(50) PRIMARY KEY,
    description  TEXT
);
CREATE TABLE enrolments (
    learner_id INT NOT NULL,
    course     VARCHAR(50) NOT NULL,
    enrol_date DATE NOT NULL,
    PRIMARY KEY (learner_id, course),
    FOREIGN KEY (learner_id) REFERENCES learners(learner_id),
    FOREIGN KEY (course)     REFERENCES courses(course)
);

Third Normal Form (3NF)

Rule: In 2NF AND no non-key column depends on another non-key column (no transitive dependency).

SQL — 3NF 
-- VIOLATES 3NF:
-- learner_id (PK) | full_name | city          | province
-- 1               | Thandi    | Johannesburg  | Gauteng
-- 2               | Sipho     | Cape Town     | Western Cape
-- province depends on city, not on learner_id — transitive dependency!

-- FIX — extract city-province into its own table:
CREATE TABLE cities (
    city     VARCHAR(50) PRIMARY KEY,
    province VARCHAR(50) NOT NULL
);
CREATE TABLE learners (
    learner_id INT PRIMARY KEY,
    full_name  VARCHAR(100),
    city       VARCHAR(50),
    FOREIGN KEY (city) REFERENCES cities(city)
);
-- Now province is stored exactly once, in cities.
-- Update Johannesburg's province: one row to update.
-- No possibility of Johannesburg appearing as both 'Gauteng' and 'Gauteng Province' in different rows.
SQL — Summary 
-- Summary of normal forms:
-- 1NF: Atomic values, no repeating groups, unique rows
-- 2NF: 1NF + no partial dependency on composite PK
-- 3NF: 2NF + no transitive dependency (non-key depends only on PK)

-- 3NF is the standard target for most production databases
-- BCNF, 4NF, 5NF exist but are rarely needed in practice

-- When to DENORMALISE (break normal form deliberately):
-- - Reporting tables / data warehouses (optimised for read, not write)
-- - When JOIN cost is proven to be too high
-- - Caching calculated values that are expensive to recompute
-- Always document why you denormalised

Practice Task

Your Turn

Take this single unnormalised table: (learner_id, learner_name, learner_phone, course_title, course_description, facilitator_name, facilitator_email, mark, enrol_date). Identify all 1NF, 2NF, and 3NF violations. Write the normalised schema with at least 4 tables. Verify by writing a JOIN query that reconstructs the original flat view.

Common Mistakes

  • Stopping at 2NF — transitive dependencies cause update anomalies too.
  • Over-normalising — splitting a table that is naturally one entity creates unnecessary JOINs.
  • Assuming normalisation means better performance — heavily normalised schemas can be slow for reads. Data warehouses often denormalise deliberately.
  • Forgetting to add indexes after normalisation — every new FK column needs one.

Professional Tip

Aim for 3NF for transactional (OLTP) databases. Data warehouses and reporting databases often intentionally denormalise (store redundant data) to avoid expensive JOINs in analytical queries. Know when each approach is appropriate.

Mini Quiz

What does Third Normal Form prevent?