Question

Consider following table - Convert it to 3NF

StdSSN	StdCity	StdClass	OfferNo	OffTerm	OffYear	EnrGrade	CourseNo	CrsDesc
S1	SEATTLE	JUN	O1	FALL	2006	3.5	C1	DB
S1	SEATTLE	JUN	O2	FALL	2006	3.3	C2	VB
S2	BOTHELL	JUN	O3	SPRING	2007	3.1	C3	OO
S2	BOTHELL	JUN	O2	FALL	2006	3.4	C2	VB

Answer 1

The given table is as follow :

StdSSN	StdCity	StdClass	OfferNo	OffTerm	OffYear	EnrGrade	CourseNo	CrsDesc
S1	SEATTLE	JUN	O1	FALL	2006	3.5	C1	DB
S1	SEATTLE	JUN	02	FALL	2006	3.3	C2	VB
S2	BOTHELL	JUN	03	SPRING	2007	3.1	C3	OO
S2	BOTHELL	JUN	02	FALL	2006	3.4	C2	VB

You can clearly see that the above table is not normalized. We will see the problems that we face when a table is not normalized.

As you know if a table is not normalized then we face the following problems :

1. Update anomaly

2. Insert anomaly

3. Delete anomaly

So, we are going to convert the given table into 3rd normal form step by step. We will do this by using the following steps :

1. First we will check whether the given table is in 1^st normal form or not if the table is not in the first normal form then we will convert it to the first normal form.

2. Second we will check whether the table which we will get from step 1 is in second normal form or not. If the table is not in the second normal form then we will convert it to the second normal form.

3. Final step, we will check whether the table which we will get from step 2 is in third normal form or not. If the table is not in the third normal form then we will convert it to the third normal form.

Let's begin with step 1 :

As you know, as per the rule of the first normal form, an attribute (column) of a table cannot hold multiple values. It should hold only atomic values. There can't be a group of columns representing similar information and there should be a key that uniquely identifies each row of the database table. So currently the given table is not in  1 NF. Why? because it has a group of columns representing similar information. Like (StdSSN , StdCity , StdClass ) , (OfferNo , OffTerm , Offyear) and (EnrGrade , CourseNO , CrsDesc). So, in order to achieve 1NF, we will divide the given table into three tables. So we will separate out the student information into another table. Similarly, we will separate out the course information into another table.

Student Table :

StdSSN	StdCity	StdClass
S1	SEATTLE	JUN
S1	SEATTLE	JUN
S2	BOTHELL	JUN
S2	BOTHELL	JUN

Offer table :

OfferNO	OffTerm	OffYear
01	FALL	2006
02	FALL	2006
03	SPRING	2007
02	FALL	2006

Course Table :

EnrGrade	CourseNo	CrsDesc
3.5	C1	DB
3.3	C2	VB
3.1	C3	OO
3.4	C2	VB

Step 2 :

In order to attain 2 NF, we should adhere to all the rules of 1NF plus all non-key columns should be dependent on the primary key.

-> In the Student table (StdSSN,StdCity) is the primary key and StdClass is the non-key attributes.

-> In Offer table (OfferNo, OffTerm) is a primary key and Offyear is a non-key attribute.

-> In the course table (EnrGrade, CourseNo) is a primary key and CrsDesc is a non-key attribute.

In order to check whether the above tables are in 2NF or not, we know that these tables are already in 1NF now just we have to check on things that are

Do the non-key columns describe what this primary key identifies? So if the answer is yes then that information should be in that table. If the answer is no then that information should not be in that table.

In the case of our tables, the non-key columns describe the primary key identifies. So the above tables are in 2NF.

Step 3 :

All the rules of 2NF pulse all the non-key columns should be NON-TRANSITIVLY depend on the primary key.

Actually , our table already is in 3NF too.

The final normalized form of our table is as follow.

Student Table :

StdSSN	StdCity	StdClass
S1	SEATTLE	JUN
S1	SEATTLE	JUN
S2	BOTHELL	JUN
S2	BOTHELL	JUN

Offer table :

OfferNO	OffTerm	OffYear
01	FALL	2006
02	FALL	2006
03	SPRING	2007
02	FALL	2006

Course Table :

EnrGrade	CourseNo	CrsDesc
3.5	C1	DB
3.3	C2	VB
3.1	C3	OO
3.4	C2	VB

Hope you got your answer!

if you still have any doubts please let me know in the comment box . Thanks! happy learning ;)