Normalisation (1NF, 2NF, 3NF)

Normalisation is the process of organising a database to reduce redundancy (unnecessary duplication of data) and improve data integrity. It involves applying a series of rules called normal forms to decompose tables into a better structure.

---

Why Normalise?

Without normalisation, databases can suffer from:

Problem	Description
Insertion anomaly	Cannot add data without unrelated data already existing (e.g. cannot add a new course without a student enrolled on it)
Deletion anomaly	Deleting one piece of data accidentally removes other valuable data
Update anomaly	Changing data in one place but not everywhere it appears, leading to inconsistency
Data redundancy	The same data is stored multiple times, wasting space and risking inconsistency

---

Unnormalised Form (UNF)

Data in its raw, unprocessed state — often containing repeating groups, multi-valued fields, or nested data.

Example — Unnormalised Student-Course Data:

StudentID	Name	Courses
101	Alice	Maths, Physics, CS
102	Bob	English, CS
103	Carol	Maths, English, History, CS

The Courses field contains multiple values — this is a repeating group and violates 1NF.

---

First Normal Form (1NF)

A table is in 1NF if:

1. All values are atomic (indivisible — each cell contains a single value).
2. There are no repeating groups or arrays of data within a single field.
3. Each record is uniquely identifiable (a primary key exists).

Applying 1NF to the example above:

StudentID	Name	Course
101	Alice	Maths
101	Alice	Physics
101	Alice	CS
102	Bob	English
102	Bob	CS
103	Carol	Maths
103	Carol	English
103	Carol	History
103	Carol	CS

Now each cell contains a single value. The composite primary key is (StudentID, Course).

However, notice that student names are repeated many times — this is data redundancy that we will resolve in 2NF.

---

Second Normal Form (2NF)

A table is in 2NF if:

1. It is already in 1NF.
2. Every non-key field is fully functionally dependent on the whole primary key (not just part of it).

This rule only applies to tables with a composite primary key. If the primary key is a single field, a 1NF table is automatically in 2NF.

Functional Dependency

A field B is functionally dependent on field A if the value of A determines the value of B. Written as A → B.

In our 1NF table with composite key (StudentID, Course):

• StudentID → Name (Name depends only on StudentID, not on Course).
• This is a partial dependency — Name depends on only part of the composite key.

Applying 2NF: Remove partial dependencies by decomposing into separate tables.

Student Table:

StudentID (PK)	Name
101	Alice
102	Bob
103	Carol

StudentCourse Table:

StudentID (FK)	CourseID (FK)
101	Maths
101	Physics
101	CS
102	English
102	CS
103	Maths
103	English
103	History
103	CS