Online College Courses for Credit

+
Higher Normalization

Higher Normalization

Rating:
(0)
Author: Sophia Tutorial
Description:

Contrast the differences between third normal form and higher levels of normalization including Boyce-Codd normal form, 4NF and 5NF.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores the higher levels of normalization with Boyce-Codd normal form, fourth normal form, and fifth normal form.

With transactional databases, the third normal form is generally the furthest that we would go with normalization. It does not make sense to go beyond it for most situations. There are certain cases where it could potentially be useful.

Boyce-Codd normal form or also known as BCNF is viewed as 3.5 NF. It is one that is already in third normal form but has every determinant in a table as a candidate key. A candidate key has the same characteristics as a primary key but for some reason, it may not have been chosen as the primary key. If a table only contains one candidate key, then 3NF and BCNF are equivalent to one another. It is only when a table contains more than one candidate key that we would need to address it.

With the fourth normal form, the database design must be in 3NF (but does not have to be in BCNF) and should not have any multivalued dependencies. This is when a single primary key can determine multiple values of two other attributes and those attributes are independent of one another. To eliminate this problem that’s created by multivalued dependencies, we have to create new tables for the components of the multivalued dependencies. By doing so, now row should contain two or more multivalued facts about an entity.

With the fifth normal form, this is not implemented at all but conceptually, a relation between tables is in 5NF is it is in fourth normal form and does not contain any join dependencies. The joining should be completely lossless. It will break down tables into as many tables as possible to avoid redundancy. It is also known as the project-join normal form. In our database in PostgreSQL, it would be similar to taking the track table and splitting up each of the combined items:

File:11538-5250-1.png

We would have a separate table for track_id and genre_id, track_id and album_id, track_id and media_type_id, genre_id and media_type_id, genre_id and album_id, and lastly media_type_id and album_id. This way, every combination would be set up as its own table to avoid redundancy. However, you can easily see why this would be impractical in a real-world setting.


summary
Higher levels of normalization like Boyce-Codd normal form, fourth normal form, and fifth normal form are generally not used in real-world databases.