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.