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:
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
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.