Normalize A Database To 3nf

Simple STEP BY STEP METHOD TO NORMALIZE TABLES TO 3NF

 STEP 1:

Ask the following question:

 DOES THE TABLE IN QUESTION HAVE ANY REPEATING GROUPS?

NO:

IT IS IN 1NF.

YES:

IT IS UNNORMALIZED, SO TO PUT IT IN 1NF, REMOVE THE REPEATING GROUPS.

 STEP 2:

 DOES THE PRIMARY KEY CONTAIN ONLY ONE COLUMN?

YES- THEN THE TABLE IS ALREADY IN 2NF.

NO- ASK THE FOLLOWING QUESTION:

o DO ANY OF THE COLUMNS THAT ARE NOT KEYS HAVE THEIR VALUES DETERMINED BY ONLY SOME, AND NOT ALL, OF THE COLUMNS THAT MAKE UP THE KEY?

YES- THEN THE TABLE IS NOT IN 2NF.

To put it in 2NF, remove any columns that are dependent upon only a portion of the key, and create separate or separate tables.

NO- THEN THE TABLE IS IN 2NF.

 STEP 3:

 ARE ALL OF THE COLUMNS IN THE TABLE DETERMINED ONLY BY CANDIDATE KEYS?

YES- THEN THE TABLE IS IN 3NF.

NO- REMOVE ANY COLUMNS THAT ARE NOT FULLY DETERMINED BY CANDIDATE KEYS, AND AGAIN CREATE ADDITIONAL TABLE(S) THAT CONTAIN THOSE COLUMNS.

CONSIDER THE FOLLOWING TABLE:

Assumptions: Assume a student only has one advisor, and can only take a course once.

Note that in this example, the primary key consists of 2 columns -STUDENTID and COURSECODE.

STUDENT (STUDENTID, STUDENTLASTNAME, STUDENTFIRSTNAME, ADVISORID, ADVISORLASTNAME, ADVISORFIRSTNAME, GRADE, COURSECODE)

SAMPLE TABLE:

10 Smith Mary 100 Jones Sally A

B

C

A CIT150

CIT160

CIT170

CIT180

20 Adams William 200 Johnson Charles A

B CIT150

CIT160

30 Davis Robert 100 Jones Sally B

A CIT150

CIT160

Consider the above table.

STEP 1:

Does it have any repeating groups?

YES. Notice that student 10 has 4 grades corresponding to 4 courses that she took. These are repeating groups. Therefore, the table is unnormalized, because it isn't in 1NF.

So, to put it in 1NF, we must REMOVE the repeating groups. One way to do that is as follows:

10 Smith Mary 100 Jones Sally A CIT150

10 Smith Mary 100 Jones Sally B CIT160

10 Smith Mary 100 Jones Sally C CIT170

10 Smith Mary 100 Jones Sally A CIT180

20 Adams William 200 Johnson Charles A CIT150

20 Adams William 200 Johnson Charles B CIT160

30 Davis Robert 100 Jones Sally B CIT150

30 Davis Robert 100 Jones Sally A CIT160

It is now in 1NF.

STEP 2:

 DOES THE PRIMARY KEY CONTAIN ONLY ONE COLUMN?

o No, it consists of 2 columns, so we need to ask an additional question:

 DO ANY OF THE COLUMNS THAT ARE NOT KEYS HAVE THEIR VALUES DETERMINED BY ONLY SOME, AND NOT ALL, OF THE COLUMNS THAT MAKE UP THE KEY?

The answer is YES. Let's look at the columns that are determined by only a portion of the key:

The easiest way to express this is by using the determinant expression

A  B

STUDENTID  STUDENTLASTNAME, STUDENTFIRSTNAME,

STUDENTFIRSTNAME, ADVISORID, ADVISORLASTNAME, ADVISORFIRSTNAME

ADVISORID  ADVISORLASTNAME,ADVISORFIRSTNAME

STUDENTID, COURSECODE  GRADE

In this case, the ONLY column that is determined by the entire key is GRADE.

That means there is some real work to do here. Every other non-key column violates the 2NF condition.

Remember