Copyright

Third Normal Form in DBMS with Examples

Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems and has a PhD in Information Technology Management. He is an adjunct professor of computer science and computer programming.

Normalization in databases help organize tables and keeps records clean. This lesson will cover the third normal form of database normalization which, through the extension of the second normal form (2NF), further reduces dependencies within the data.

Normalization

The process of normalizing a database is not to help it fit in with the other database; it is to ensure that the tables within are organized and fits in with the other tables in the database. Each table should really store a information about a single topic: The columns/fields in a given table should really only hold data about that subject.

In other words, don't store lawnmower data in your music database. The album table shouldn't have a field for Toro carburetor part numbers; nor should it store information about a customer or user. Only data about an allbum should be stored in the album table.

The purpose of Third Normal Form (3NF) is to further reduce dependencies and clutter that may still remain from first and second normal form work.

3rd Normal Form

In second normal form, all columns in the table rely on the primary key and the table has a singular purpose. However, there could be relationships between the columns... dependencies could lurk within these columns.

This is called transitive dependence. It's a technical term that can be hard to understand, but with a few examples, it should be clear. Transitive dependence means that a value of a column/field within a table relies on a another column in that same table, but this is facilitated through another column between them.

In order for a table to be in third normal form, it must meet the following requirements:

  • It's already in 2nd Normal Form (2NF)
  • It contains only fields/columns that do not have transitive dependence

Again, transitive dependence means dependence between columns of the same table. Think of ArtistNationality, Artist, and Artwork. The values for ArtistNationality and Artist depend on the Artwork; once you figure out the Artwork, you know the Artist/ArtistNationality. But ArtistNationality depends on the value from Artist: This is a transitive dependence.

A few examples will make this concept easier to understand:

Primary Key Column 1 Column 2 Transitive?
customerID firstName lastName No: These two fields are not dependent as you don't need one to get the other and can't determine one from the either
artistID countryCode countryName Yes: The country name depends on the country code, which depends on artistID.
albumID releaseDate cover No: There is no relation between an album's release data and the cover (in this case a link to the cover picture)

In order to get to third normal form, we need to make sure all columns are only dependent upon the primary key. That means we have to get the country code out of the artist table. In the following example, we've added country name to the table to further highlight the issue with normalizing:

Table Needs to Be Normalized
Needs Normalizing

It's fine that the country code exists in the artist table, but having the country name breaks the 3rd normal form rule, since we can't get the country name without the code; we can't get the code without the artist ID. This is by nature the definition of transitive dependence.

Let's look at some examples that will use the fictional music database as a subject.

Example 1

To fix the issue, we'll create another table called countries, move the country code and country name to this table. Country code becomes the primary key in the countries table, but is retained in the artist table as a foreign key.

3NF Country Codes
3NF Country Codes

Example 2

In the album table, we have the following fields: albumTitle, artistID, dateAdded, rating, and ratingNotes (comments as to why a specific rating was given).

Needs Normalizing
Needs Normalizing

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support