Different Normal Forms
What is Database Normalisation?
Database normalisation is the process of organising data in a relational database to reduce redundancy and improve integrity.
It involves structuring data across multiple tables using normal forms (1NF, 2NF, 3NF) to eliminate anomalies and maintain consistency.
Why Normalise a Database?
- Minimises Data Redundancy: Ensures that data is not duplicated unnecessarily.
- Prevents Data Anomalies: Reduces inconsistencies when updating or deleting data.
- Improves Data Integrity: Maintains logical relationships between tables.
- Enhances Query Performance: Optimises storage and retrieval efficiency.
Normal Forms
For this course, you need to be able to normalise a database to 3rd Normal Form.
To illustrate these processes, we shall use the example of Student Enrolment in Different Courses.
1NF (First Normal Form)
Definition: A table is in 1NF if there is a Primary Key and there are no repeating attributes, or groups of repeating attributes.
How to:
- Identify non-atomic values and repeating groups/columns
- Separate values into new rows
Identify non-atomic values and repeating groups/columns
A group of repeating attributes
| StudentID | StudentName | Courses |
|---|---|---|
| 1001 | Isla Green | Maths, Physics |
| 1002 | Leo Patel | Biology |
or Repeating attributes
| StudentID | StudentName | Course1 | Course2 |
|---|---|---|---|
| 1001 | Isla Green | Maths | Physics |
| 1002 | Leo Patel | Biology | - |
Separate values into new rows
In 1NF:
| StudentID | StudentName | Course |
|---|---|---|
| 1001 | Isla Green | Maths |
| 1001 | Isla Green | Physics |
| 1002 | Leo Patel | Biology |
2NF (Second Normal Form)
Definition: A table is in 2NF if it is already in 1NF; and all fields are dependent on the whole (composite) key.
How to:
- Ensure the table is in 1NF
- Move attributes that depend only on part of a composite key into a separate table
The table is in 1NF. However, it is not in 2NF – StudentName only depends on StudentID
| StudentID | CourseID | StudentName |
|---|---|---|
| 1001 | M101 | Isla Green |
| 1001 | P102 | Isla Green |
| 1002 | B103 | Leo Patel |
Move attributes that depend only on StudentID to a separate table
In 2NF:
| StudentID | StudentName |
|---|---|
| 1001 | Isla Green |
| 1002 | Leo Patel |
| StudentID | CourseID |
|---|---|
| 1001 | M101 |
| 1001 | P102 |
| 1002 | B103 |
3NF (Third Normal Form)
Definition: A table is in 3NF if it is already in 2NF; and all attributes are only dependent on the primary key, and not on any non-key attributes (no transitive dependencies).
How to:
- Ensure the table is in 2NF
- Move attributes that depend on non-key attributes into a new related table
Not in 3NF – InstructorName depends on InstructorID, which is not a primary key
| StudentID | CourseID | InstructorID | InstructorName |
|---|---|---|---|
| 1001 | M101 | I001 | Ms Roberts |
| 1001 | P102 | I002 | Dr Clark |
| 1002 | B103 | I002 | Dr Clark |
Move instructor details to a separate table
In 3NF:
| InstructorID | InstructorName |
|---|---|
| I001 | Ms Roberts |
| I002 | Dr Clark |
| StudentID | CourseID | InstructorID |
|---|---|---|
| 1001 | M101 | I001 |
| 1001 | P102 | I002 |
| 1002 | B103 | I002 |
Comparison of Normal Forms
| Normal Form | Condition | Key Concept |
|---|---|---|
| 1NF | All columns contain atomic values | No repeating groups |
| 2NF | 1NF + No partial-key dependencies | All attributes depend on the whole primary key |
| 3NF | 2NF + No transitive dependencies | Non-key attributes do not depend on other non-key attributes |
Important Normalisation Concepts
- Atomicity: Each field contains only one value (1NF).
- Unique Identification: Every record must be uniquely identifiable using a key.
- Functional Dependencies: A relationship where one attribute uniquely determines another.
- Partial-Key Dependencies: When an attribute depends on only part of a composite key (violates 2NF).
- Transitive Dependencies: When a non-key attribute depends on another non-key attribute (violates 3NF).
Key Takeaways
- Database normalisation structures data efficiently by reducing redundancy.
- 1NF: Ensures atomic values and unique rows.
- 2NF: Eliminates partial dependencies on composite keys.
- 3NF: Eliminates transitive dependencies for better consistency.
- 1NF, 2NF, 3NF summarised as: The key, the whole key, and nothing but the key (so help me, Codd).
- Applying normalisation ensures efficient storage, better query performance, and data integrity.