Definition
Normalization
A process used to organize data in a database to reduce redundancy and improve data integrity.
- The normalisation process involves dividing large tables into smaller, related tables.
- This ensures that the data dependencies are logical.
- There are three normalisation forms:
- 1NF
- 2NF
- 3NF
1NF: Eliminate Repeating Groups
- 1NF requires that each table has a primary key and that each attribute contains atomic values.
- This means that there are no repeating groups or arrays in any column.
Example
- Consider a table with the following data:
| StudentID | Name | Courses |
|---|---|---|
| 1 | Alice | Math, Science |
| 2 | Bob | English |
- This table is not in 1NF because the Courses column contains multiple values.
- To convert this table to 1NF, we create separate rows for each course:
| StudentID | Name | Course |
|---|---|---|
| 1 | Alice | Math |
| 1 | Alice | Science |
| 2 | Bob | English |
Tip
- To achieve 1NF, ensure that each attribute contains only a single value.
2NF: Eliminate Partial Dependencies
- 2NF builds on 1NF by eliminating partial dependencies.
- A partial dependency occurs when a non-prime attribute is dependent on only part of a composite primary key.
Note
- Non-prime attributes are attributes that are not part of any candidate key.
Example
- Consider a table with the following data:
| StudentID | CourseID | Grade | StudentName |
|---|---|---|---|
| 1 | 101 | A | Alice |
| 1 | 102 | B | Alice |
| 2 | 101 | B | Bob |
- The primary key is (StudentID, CourseID).
- The Grade attribute is fully dependent on the primary key, but StudentName is only dependent on StudentID.
- To convert this table to 2NF, we create separate tables:
| StudentID | StudentName |
|---|---|
| 1 | Alice |
| 2 | Bob |
| StudentID | CourseID | Grade |
|---|---|---|
| 1 | 101 | A |
| 1 | 102 | B |
| 2 | 101 | B |
Tip
- To achieve 2NF, ensure that all non-prime attributes are fully dependent on the primary key.
3NF: Eliminate Transitive Dependencies
- 3NF builds on 2NF by eliminating transitive dependencies.
- A transitive dependency occurs when a non-prime attribute is dependent on another non-prime attribute.
Example
- Consider a table with the following data:
| StudentID | CourseID | Instructor | InstructorEmail |
|---|---|---|---|
| 1 | 101 | Dr. Smith | smith@example.com |
| 2 | 102 | Dr. Jones | jones@example.com |
- The primary key is (StudentID, CourseID). The InstructorEmail attribute is dependent on Instructor, which is a non-prime attribute.
- To convert this table to 3NF, we create separate tables:
| CourseID | Instructor | InstructorEmail |
|---|---|---|
| 101 | Dr. Smith | smith@example.com |
| 102 | Dr. Jones | jones@example.com |
| StudentID | CourseID |
|---|---|
| 1 | 101 |
| 2 | 102 |
Tip
- To achieve 3NF, ensure that all attributes are only dependent on the primary key.
Differences Between 1NF, 2NF, and 3NF
- 1NF focuses on eliminating repeating groups and ensuring atomic values.
- 2NF focuses on eliminating partial dependencies and ensuring full functional dependency.
- 3NF focuses on eliminating transitive dependencies and ensuring non-transitive dependency.
Note
- Normalization helps reduce data redundancy and improve data integrity, but it can also increase the complexity of queries.
