Third Normal Form (3NF)

2NF, 3NF, and BCNF are rules for evaluating functional depencies, or FD for short. If you are not familiar with the concept of functional dependency, please see my post on 2NF where I describe it in detail.

If you’re ready to tackle 3NF, then let’s begin!

Remember the definitions of our key terms:

SUPERKEY: A field or combination of fields that uniquely identifies a row.

CANDIDATE KEY: A superkey that is irreducible, sometimes called a ‘minimal superkey’.

SUBKEY: A subset of a candidate key. Remember that any set is a subset of itself.

Let’s analyze a table that tracks the inventory of a car dealership.

Figure 1 – dbo.Inventory

figure 1 - 3NF

First we will identify the superkeys. InventoryId appears to be an identity or auto-number column, so any combination of fields that include it will be a superkey:

{InventoryId,Make,Model,ModelYear,Odometer}
{InventoryId,Make,Model,ModelYear}
{InventoryId,Make,Model,Odometer}
{InventoryId,Make,ModelYear,Odometer}
{InventoryId,Model,ModelYear,Odometer}
{InventoryId,Make,Model}
{InventoryId,Make,ModelYear}
{InventoryId,Make,Odometer}
{InventoryId,Model,ModelYear}
{InventoryId,Model,Odometer}
{InventoryId,ModelYear,Odometer}
{InventoryId}

There are no other sets of fields that are a superkey.

Identify the candidate keys:

{InventoryId}

Identify any subkeys:

{InventoryId}

Now for the non-trivial functional dependencies. Because {InventoryId} is a candidate key, any field or combination of fields are functionally dependent on it.

{InventoryId}→{Make,Model,ModelYear,Odometer}
{InventoryId}→{Make,Model,ModelYear}
{InventoryId}→{Make,Model,Odometer}
{InventoryId}→{Make,ModelYear,Odometer}
{InventoryId}→{Model,ModelYear,Odometer}
{InventoryId}→{Make,Model}
{InventoryId}→{Make,ModelYear}
{InventoryId}→{Make,Odometer}
{InventoryId}→{Model,ModelYear}
{InventoryId}→{Model,Odometer}
{InventoryId}→{ModelYear,Odometer}
{InventoryId}→{Make}
{InventoryId}→{Model}
{InventoryId}→{ModelYear}
{InventoryId}→{Odometer}

Also, any combination of fields that includes {InventoryId} will functionally determine any field or combination of fields. For example, {Inventory,Make}→{Model} and {InventoryId,Model}→{Make}. I did not list all of these possible combinations.

You will probably notice one more FD in this table. Because we know that only Honda makes Civics and only Chevy makes Camaros, and so forth:

{Model}→{Make}

3NF:

“Relvar R is in third normal form (3NF) if and only if, for every nontrivial FD XY that holds in R, either (a) X is a superkey or (b) Y is a subkey.”
Date, C.J. Database Design & Relational Theory. Sebastopol: O,Reilly Media, Inc., 2012. Print. (Emphasis theirs)

{InventoryId} is a superkey, so any FD where X is {InventoryId} will satisfy condition (a). The only other non-trivial FD is FD {Model}–>{Make}. {Model} is not a superkey and {Make} is not a subkey, so this table is not in 3NF.

You may notice that 3NF is the same as 2NF with one possible condition removed. In 2NF, one of the following must be true: (a) X is a superkey; (b) Y is a subkey; or (c) X is not a subkey.

In figure 1, condition (c) is true. {Model} is not a subkey. So this table is in 2NF.

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s