When designing a database, poor structure leads to data redundancy (duplicate data), update anomalies, and wasted storage. Normalisation is the process of organising tables to minimise these problems.
Definition
Normalisation is the process of organising a database to reduce redundancy and improve data integrity by following a series of rules called normal forms.
First Normal Form (1NF)
Definition
A table is in First Normal Form (1NF) if:
• Each cell contains only atomic (single, indivisible) values
• There are no repeating groups or arrays
• Each record is unique (has a primary key)
• Each cell contains only atomic (single, indivisible) values
• There are no repeating groups or arrays
• Each record is unique (has a primary key)
Worked Example
Worked Example: Achieving 1NF
NOT in 1NF (repeating groups):
Problem: Courses column contains multiple values
IN 1NF (atomic values):
Now each cell contains ONE value only
NOT in 1NF (repeating groups):
| StudentID | Name | Courses |
|---|---|---|
| S001 | Alice | Maths, Physics, Chemistry |
| S002 | Bob | Maths, Biology |
IN 1NF (atomic values):
| StudentID | Name | Course |
|---|---|---|
| S001 | Alice | Maths |
| S001 | Alice | Physics |
| S001 | Alice | Chemistry |
| S002 | Bob | Maths |
| S002 | Bob | Biology |
Second Normal Form (2NF)
Definition
A table is in Second Normal Form (2NF) if:
• It is already in 1NF
• All non-key attributes are fully dependent on the ENTIRE primary key (no partial dependencies)
• It is already in 1NF
• All non-key attributes are fully dependent on the ENTIRE primary key (no partial dependencies)
Worked Example
Worked Example: Achieving 2NF
IN 1NF but NOT 2NF:
Primary key: (StudentID, CourseID) - composite key
Problem: StudentName depends only on StudentID, not CourseID. CourseName depends only on CourseID, not StudentID. These are PARTIAL dependencies.
IN 2NF (split into separate tables):
Student Table:
Course Table:
Enrolment Table:
Now all non-key attributes depend on the full primary key
IN 1NF but NOT 2NF:
| StudentID | CourseID | StudentName | CourseName | Grade |
|---|---|---|---|---|
| S001 | C001 | Alice | Maths | A |
| S001 | C002 | Alice | Physics | B |
| S002 | C001 | Bob | Maths | B |
Problem: StudentName depends only on StudentID, not CourseID. CourseName depends only on CourseID, not StudentID. These are PARTIAL dependencies.
IN 2NF (split into separate tables):
Student Table:
| StudentID | StudentName |
|---|---|
| S001 | Alice |
| S002 | Bob |
| CourseID | CourseName |
|---|---|
| C001 | Maths |
| C002 | Physics |
| StudentID | CourseID | Grade |
|---|---|---|
| S001 | C001 | A |
| S001 | C002 | B |
| S002 | C001 | B |
Third Normal Form (3NF)
Definition
A table is in Third Normal Form (3NF) if:
• It is already in 2NF
• No non-key attribute depends on another non-key attribute (no transitive dependencies)
• It is already in 2NF
• No non-key attribute depends on another non-key attribute (no transitive dependencies)
Worked Example
Worked Example: Achieving 3NF
IN 2NF but NOT 3NF:
Problem: TutorName and TutorOffice depend on TutorID, not StudentID. StudentID → TutorID → TutorName (transitive dependency).
IN 3NF (remove transitive dependency):
Student Table:
Tutor Table:
Now no non-key attribute depends on another non-key attribute
IN 2NF but NOT 3NF:
| StudentID | Name | TutorID | TutorName | TutorOffice |
|---|---|---|---|---|
| S001 | Alice | T01 | Mr Brown | Room 12 |
| S002 | Bob | T01 | Mr Brown | Room 12 |
| S003 | Charlie | T02 | Ms Green | Room 15 |
IN 3NF (remove transitive dependency):
Student Table:
| StudentID | Name | TutorID |
|---|---|---|
| S001 | Alice | T01 |
| S002 | Bob | T01 |
| S003 | Charlie | T02 |
| TutorID | TutorName | TutorOffice |
|---|---|---|
| T01 | Mr Brown | Room 12 |
| T02 | Ms Green | Room 15 |
Exam Tip
Benefits of Normalisation:
• Reduces data redundancy (no duplicate data)
• Prevents update anomalies (change once, applies everywhere)
• Saves storage space
• Improves data integrity
• Makes database easier to maintain
• Reduces data redundancy (no duplicate data)
• Prevents update anomalies (change once, applies everywhere)
• Saves storage space
• Improves data integrity
• Makes database easier to maintain