Solution to Exercise 2
In this lesson we will discuss the solution to exercise 2.
We'll cover the following
Solution
A table is in third normal form when the following conditions are met:
-
It is in the second normal form.
-
All non-primary fields are dependent on the primary key.
Customer table
Cust_Id | Cust_Name | DOB | Area | City | State | Zip |
---|---|---|---|---|---|---|
1 | Jack | 1996-01-13 | 777 Brockton Avenue | Abington | MA | 2351 |
2 | Bruce | 1995-09-22 | 3018 East Ave | Central Square | NY | 13036 |
3 | Amy | 1999-11-17 | 80 Town Line Rd | Rocky Hill | CT | 6067 |
4 | James | 1998-03-10 | 5710 Mcfarland Blvd | Northport | AL | 35476 |
5 | Veronica | 1990-06-09 | 2900 Pepperrell Pkwy | Opelika | AL | 36801 |
First, we can see that the table above is in the first normal form; it obeys all the rules of the first normal form.
Secondly, the primary key consists of the Cust_Id
as it uniquely identifies each record in the table.
Therefore the table is in second normal form as there is no composite primary key.
However, the table is not in the third normal form because the area, city, and state are unbreakably bound to their zip code. The dependency between the zip code and the address is called transitive dependency. To comply with the third normal form, all you need to do is to move the Area
, City
, and State
fields into their own table.
Zip code table
Zip | Area | City | State |
---|---|---|---|
2351 | 777 Brockton Avenue | Abington | MA |
13036 | 3018 East Ave | Central Square | NY |
6067 | 80 Town Line Rd | Rocky Hill | CT |
35476 | 5710 Mcfarland Blvd | Northport | AL |
36801 | 2900 Pepperrell Pkwy | Opelika | AL |
The next step is to alter the CUSTOMER table as shown below:
Customer table
Cust_Id | Cust_Name | DOB | Zip |
---|---|---|---|
1 | Jack | 1996-01-13 | 2351 |
2 | Bruce | 1995-09-22 | 13036 |
3 | Amy | 1999-11-17 | 6067 |
4 | James | 1998-03-10 | 35476 |
5 | Veronica | 1990-06-09 | 36801 |
The Zip
field acts as a foreign key in the CUSTOMER table so that we can get the address details of the corresponding customer. It acts as a link between the two tables.
Now all the tables are in 3NF as there is no transitive dependency between any column.
Get hands-on with 1200+ tech skills courses.