Normalization
Normalization is the process of organizing the data and the attributes of a database. It reduces redundancy and ensures that data is stored logically.
1 First Normal Form (1NF)
Section titled “1 First Normal Form (1NF)”Definition
Section titled “Definition”A relation is in 1NF if it contains only atomic (indivisible) values, and each column contains values of a single type.
- Conditions:
- No repeating groups or arrays.
- Each cell in the table must contain a single value.
- The order in which data is stored does not matter.
Example
Section titled “Example”Before 1NF
Section titled “Before 1NF”| Student_ID | Name | Subjects |
|---|---|---|
| 1 | Alice | Math, Science |
| 2 | Bob | English |
After 1NF
Section titled “After 1NF”| Student_ID | Name | Subject |
|---|---|---|
| 1 | Alice | Math |
| 1 | Alice | Science |
| 2 | Bob | English |
2 Second Normal Form (2NF)
Section titled “2 Second Normal Form (2NF)”Definition
Section titled “Definition”A relation is in 2NF if it is in 1NF and all non-prime attributes (attributes that are not part of any candidate key) are fully functionally dependent on the entire candidate key.
- Conditions:
- It must be in 1NF.
- No partial dependency (i.e., non-prime attributes must depend on the whole primary key, not just part of it).
Example
Section titled “Example”Before 2NF (Partial Dependency)
Section titled “Before 2NF (Partial Dependency)”| Student_ID | Course | Instructor | Instructor_Phone |
|---|---|---|---|
| 1 | Math | Dr. Smith | 1234 |
| 1 | Science | Dr. Taylor | 5678 |
| 2 | Math | Dr. Smith | 1234 |
After 2NF
Section titled “After 2NF”Student Table:
| Student_ID | Course |
|---|---|
| 1 | Math |
| 1 | Science |
| 2 | Math |
Instructor Table:
| Course | Instructor | Instructor_Phone |
|---|---|---|
| Math | Dr. Smith | 1234 |
| Science | Dr. Taylor | 5678 |
3 Third Normal Form (3NF)
Section titled “3 Third Normal Form (3NF)”Definition
Section titled “Definition”A relation is in 3NF if it is in 2NF and no transitive dependency exists (i.e., non-prime attributes should not depend on other non-prime attributes).
- Conditions:
- It must be in 2NF.
- There is no transitive dependency (no non-prime attribute depends on another non-prime attribute).
Example
Section titled “Example”Before 3NF (Transitive Dependency)
Section titled “Before 3NF (Transitive Dependency)”| Student_ID | Course | Instructor | Instructor_Email |
|---|---|---|---|
| 1 | Math | Dr. Smith | smith@email.com |
| 2 | Math | Dr. Smith | smith@email.com |
| 3 | Science | Dr. Taylor | taylor@email.com |
After 3NF
Section titled “After 3NF”Student Table:
| Student_ID | Course |
|---|---|
| 1 | Math |
| 2 | Math |
| 3 | Science |
Instructor Table:
| Instructor | Instructor_Email |
|---|---|
| Dr. Smith | smith@email.com |
| Dr. Taylor | taylor@email.com |
4 Boyce-Codd Normal Form (BCNF)
Section titled “4 Boyce-Codd Normal Form (BCNF)”Definition
Section titled “Definition”A relation is in BCNF if it is in 3NF and for every functional dependency, the left-hand side (determinant) is a superkey.
- Conditions:
- It must be in 3NF.
- Every determinant is a superkey.
Example
Section titled “Example”Before BCNF (Non-superkey dependency)
Section titled “Before BCNF (Non-superkey dependency)”| Student_ID | Course | Instructor |
|---|---|---|
| 1 | Math | Dr. Smith |
| 2 | Math | Dr. Smith |
| 3 | Science | Dr. Taylor |
After BCNF
Section titled “After BCNF”Student Table:
| Student_ID | Course |
|---|---|
| 1 | Math |
| 2 | Math |
| 3 | Science |
Instructor Table:
| Course | Instructor |
|---|---|
| Math | Dr. Smith |
| Science | Dr. Taylor |
5 Fourth Normal Form (4NF)
Section titled “5 Fourth Normal Form (4NF)”Definition
Section titled “Definition”A relation is in 4NF if it is in BCNF and has no multi-valued dependencies.
- Conditions:
- It must be in BCNF.
- No multi-valued dependencies exist, where one attribute determines multiple independent values.
Example
Section titled “Example”Before 4NF (Multi-valued Dependency)
Section titled “Before 4NF (Multi-valued Dependency)”| Student_ID | Hobby | Language |
|---|---|---|
| 1 | Reading | English |
| 1 | Painting | French |
After 4NF
Section titled “After 4NF”Student Table:
| Student_ID | Hobby |
|---|---|
| 1 | Reading |
| 1 | Painting |
Language Table:
| Student_ID | Language |
|---|---|
| 1 | English |
| 1 | French |
6 Fifth Normal Form (5NF)
Section titled “6 Fifth Normal Form (5NF)”Definition
Section titled “Definition”A relation is in 5NF if it is in 4NF and cannot be decomposed into smaller relations without loss of information (i.e., no join dependency exists).
- Conditions:
- It must be in 4NF.
- The relation should not have any join dependencies that cause loss of information when decomposed.
Example
Section titled “Example”Before 5NF (Join Dependency)
Section titled “Before 5NF (Join Dependency)”| Student_ID | Course | Instructor |
|---|---|---|
| 1 | Math | Dr. Smith |
| 1 | Science | Dr. Taylor |
| 2 | Math | Dr. Smith |
After 5NF
Section titled “After 5NF”Student Table:
| Student_ID | Course |
|---|---|
| 1 | Math |
| 1 | Science |
| 2 | Math |
Instructor Table:
| Course | Instructor |
|---|---|
| Math | Dr. Smith |
| Science | Dr. Taylor |
| Normal Form | Conditions |
|---|---|
| 1NF | Atomic values, no repeating groups |
| 2NF | 1NF + No partial dependency |
| 3NF | 2NF + No transitive dependency |
| BCNF | 3NF + Every determinant is a superkey |
| 4NF | BCNF + No multi-valued dependencies |
| 5NF | 4NF + No join dependencies (lossless decomposition) |