Database Normalization
Normalization is the process of minimizing redundancy from a relation or set of relations by
decomposing the relation into multiple relations and link them using foreign keys. Redundancy in
relation may cause insertion, deletion and updation anomalies.
Redundant data wastes disk space and creates database maintenance problems. If data that exists
in more than one place must be changed, the data must be changed in exactly the same way in all
locations. A customer address change is much easier to implement if that data is stored only in the
Customers table and nowhere else in the database.
Example 1):
In the following relation, student can take many courses:
Student_Grade_Report (StudentNo, StudentName, Major, (CourseNo, CourseName,
InstructorNo, InstructorName, InstructorLocation, Grade))
Course data is a multivalued attributes, because students can register many courses, so the
relation is not in 1NF.
To make this relation in the 1NF, we can make course data as single attributes, and make
PK composed of studentNo, and CourseNo.
Student_Grade_Report (StudentNo, StudentName, Major, CourseNo, CourseName,
InstructorNo, InstructorName, InstructorLocation, Grade)
Second Normal Form (2NF)
For the second normal form, the relation must first be in 1NF. The relation is automatically
in 2NF if, and only if, the PK comprises a single attribute.
If the relation has a composite PK, then each non-key attribute must be fully dependent on
the entire PK and not on a subset of the PK (i.e., there must be no partial dependency or
augmentation).
For the second normal form, the relation must first be in 1NF. The relation is automatically
in 2NF if, and only if, the PK comprises a single attribute.
If the relation has a composite PK, then each non-key attribute must be fully dependent on
the entire PK and not on a subset of the PK (i.e., there must be no partial dependency or
augmentation).
Process for 2NF
The relation is not in the 2NF because the attributes studentName, Major depend partially
on the PK, they are determined by studentNo only.
Also, course name, and instructor data depends on courseNo only. So, we decompose this
relation into the following
Student (StudentNo, StudentName, Major)
CourseGrade (StudentNo, CourseNo, Grade)
CourseInstructor (CourseNo, CourseName, InstructorNo, InstructorName,
InstructorLocation)
Third Normal Form (3NF)
To be in third normal form, the relation must be in second normal form. Also all transitive
dependencies must be removed; a non-key attribute may not be functionally dependent on
another non-key attribute.
Process for 3NF
Eliminate all dependent attributes in transitive relationship(s) from each of the tables that
have a transitive relationship.
Student (StudentNo, StudentName, Major)
CourseGrade (StudentNo, CourseNo, Grade)
Course (CourseNo, CourseName, InstructorNo)
Instructor (InstructorNo, InstructorName, InstructorLocation)
Example 2)
AUTHORS
Author_ID Author Book Author_Nationality
Auth_001 Orson Scott Card Ender's Game United States
Auth_001 Orson Scott Card Children of the MindUnited States
Auth_002 Margaret AtwoodThe Handmaid's TaleCanada
This relation’ primary key is author_id, single attribute so the relation is in 2NF.
In the AUTHORS example above:
Book → Author: Here, the Book attribute determines the Author attribute. If you
know the book name, you can learn the author's name. However, Author does not
determine Book, because an author can write multiple books. For example, just
because we know the author's name Orson Scott Card, we still don't know the book
name.
Author → Author_Nationality: Likewise, the Author attribute determines
the Author_Nationality, but not the other way around; just because we know the
nationality does not mean we can determine the author.
But this table introduces a transitive dependency:
Book →Author_Nationality: If we know the book name, we can determine
the nationality via the Author column.
Avoiding Transitive Dependencies
To ensure Third Normal Form, let's remove the transitive dependency.
We can start by removing the Book column from the Authors table and creating a
separate Books table:
BOOKS
Book_ID Book Author_ID
Book_001 Ender's Game Auth_001
Book_001 Children of the Mind Auth_001
Book_002 The Handmaid's Tale Auth_002
AUTHORS
Author_ID Author Author_Nationality
Auth_001 Orson Scott Card United States
Auth_002 Margaret Atwood Canada
Did this fix it? Let's examine our dependencies now:
BOOKS table:
Book_ID → Book: The Book depends on the Book_ID.
No other dependencies in this table exist, so we are okay. Note that the
foreign key Author_ID links this table to the AUTHORS table through its
primary key Author_ID. We have created a relationship to avoid a transitive
dependency, a key design of relational databases.
AUTHORS table:
Author_ID → Author: The Author depends on the Author_ID.
Author → Author_Nationality: The nationality can be determined by the
author.
Author_ID → Author_Nationality: The nationality can be determined from
the Author_ID through the Author attribute. We still have a transitive
dependency.
We need to add a third table to normalize this data:
COUNTRIES
Country_ID Country
Coun_001 United States
Coun_002 Canada
AUTHORS
Author_ID Author Country_ID
Auth_001 Orson Scott Card Coun_001
Auth_002 Margaret Atwood Coun_002
Now we have three tables, making use of foreign keys to link between the tables:
The BOOK table's foreign key Author_ID links a book to an author in the
AUTHORS table.
The AUTHORS table's foreign key Country_ID links an author to a country in
the COUNTRIES table.
The COUNTRIES table has no foreign key because it has no need to link to
another table in this design.