What is 3NF and what is an example of it?

 

The purpose of going to 3NF is that on this normal form, we will eliminate functional dependencies on non-key fields. At this stage, all non-key fields are dependent only on the key.

Now for an example, imagine that we have the following used car parts store table where the Unit price varies depending on the state of the piece and where Order Number is our primary key:

 

Order Number	Customer Number	Unit Price	Quantity	Total
1	241	$10	2	$20
2	842	$9	20	$180
3	919	$19	1	$19
4	919	$12	10	$120

 

First we have to check if it satisfies the rules for 1NF:

Are there any duplicates? No there aren’t any

Do we have a primary key? Yes, Order Number is our primary key

So as a result, we satisfy 1NF.

 

Now we check to see if we satisfy the rules for 2NF:

Are there any subsets of data that apply to multiple rows?  No there aren’t any.

As a result, we also satisfy 2NF.

 

Now our main concern is to get to 3NF; the rules to go from 2NF to 3NF are the following:

Are all of the columns fully dependent upon the primary key?

Well, by checking the table we notice that the Total is not fully dependent on the primary key since the Total can be derived from multiplying the Unit Price times the Quantity.

 

Therefore, the Total field can be eliminated from our table to comply with 3NF.

 

You might ask yourself, but does Unit Price is fully dependent on the Primary Key, yes it is, remember, the price varies depending on the state of the used car part that the customer is buying, therefore it is fully dependent on the Primary key.

 

So our resulting table in 3NF would look like:

Order Number	Customer Number	Unit Price	Quantity
1	241	$10	2
2	842	$9	20
3	919	$19	1
4	919	$12	10

 

Multiple Choice questions:

1. What is a valid rule for 1NF?

a)     All data must be on the table

b)     All data on the table must be dependant on the primary key

c)      We need to have a primary key

Answer: c

2. What is the correct rule to go from 2NF to 3NF?

a)     We need to have all the primary keys defined

b)     All the columns need to be fully dependent on the primary key

c)      All data redundancy needs to be eliminated

Answer: b

Short questions:

What is the purpose of going to 3NF?

A: Eliminating functional dependencies on non-key fields.

What is it that we have to check for to make sure we comply with 3NF?

A: As a rule, we have to check first that we comply with 1NF, after complying with the rules for 1NF we have to check that now we comply with 2NF, after making sure we comply with 2NF, we just apply the rule for 3NF and make sure our table complies with it.