This lesson's interactive features are locked, please to keep using them

Third Normal Form (3NF)

A table in third normal form (3NF) follows all the rules of second normal form, and one additional rule:

All columns that aren't part of the primary key are dependent solely on the primary key.

Notice that this is only slightly different from second normal form. In second normal form we can't have a column completely dependent on only part of the primary key, and in third normal form we can't have a column that is entirely dependent on anything that isn't the primary key.

Example of 2NF but Not 3NF

In this table, the primary key is simply the id column.

id	name	first_initial	email
1	Lane	l	`[email protected]`
2	Breanna	b	`[email protected]`
3	Lane	l	`[email protected]`

This table is in second normal form because first_initial is not dependent on a part of the primary key. However, because it is dependent on the name column, it doesn't adhere to third normal form.

Example of 3NF

The way to convert the table above to 3NF is to add a new table that maps a name directly to its first_initial. Notice how similar this solution is to 2NF.

id	name	email
1	Lane	`[email protected]`
2	Breanna	`[email protected]`
3	Lane	`[email protected]`

name	first_initial
Lane	l
Breanna	b

3NF Is Usually a Good Idea

The same exact rule of thumb applies to the second and third normal forms.

Optimize for data integrity and data de-duplication first by adhering to 3NF. If you have speed issues, de-normalize accordingly.

Assignment

This rollout of business accounts is really causing some headaches for our development team. The companies table has been a disaster. Our database architect pointed out that the idea behind the size field is redundant.

If a company has more than 100 employees, we consider it "large," otherwise it's "small." That's something we can calculate from the num_employees field, rather than storing it separately.

Remove the size column from the companies table by modifying the CREATE TABLE statement.
Change the SELECT statement to calculate a size field in the result set that works the same way. Return the other fields as normal.

Tip

Remember the IIF function and the AS clause.