Solution to Exercise 2

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.