Converting Table to Third Normal Form

"Om Swastiastu"

Hi guys, this time I wanna tell you how to convert table into third normal form. In this table, StudentNum determines StudentName, NumCredits, AdvisorNum, and AdvisorName. AdvisorNum determines AdvisorName. CourseNum determines Description. The combination of StudentNum and CourseNum determines Grade.

Student (StudentNum, StudentName, NumCredits, AdvisorNum, AdvisorName, (CourseNum, Description, Grade))


STEP 1: Remove the repeating group to convert it to first normal form, yielding:

Student (StudentNum, StudentName, NumCredits, AdvisorNum, AdvisorName, CourseNum, Description, Grade)

The table is now in first normal form because it has no repeating groups. It is not, however, in second normal form, because StudentName, for example, is dependent only on StudentNum, which is only portion of primary key.
STEP 2: Convert the first normal form table to second normal form. First, for each subset of the primary key, start a table with that subsets as its key, yielding:

(StudentNum,
(CourseNum,
(StudentNum, CourseNum,

Next, place the rest of the columns with the smallest collection of columns on which they depend, giving:




(StudentNum, StudentName, NumCredits, AdvisorNum, AdvisorName)
(CourseNum, Description)
(StudentNum, CourseNum, Grade)

Finally, assign names to each of the newly created tables:

Student (StudentNum, StudentName, NumCredits, AdvisorNum, AdvisorName)
Course (CourseNum, Description)
StudentCourse (StudentNum, CourseNum, Grade)

Although this table are all in second normal form, Course and StudentCourse are also in third normal form. The Student table is not in third normal form, however, because it contains a determinant (AdvisorNum) that is not a candicate key.

STEP 3: Convert the secont normal form Student table to third normal form by removing the column that depends on the determinant AdvisorNum and placing it in a separate table.

(StudentNum, StudentName, NumCredits, AdvisorNum)
(AdvisorNum, AdvisorName)

STEP 4: Name these tables and put the entire collection together, giving:

Student(StudentNum, StudentName, NumCredits, AdvisorNum)
Advisor(AdvisorNum, AdvisorName)
Course(CourseNum, Description)
StudentCourse(StudentNum, CourseNum, Grade)

That is the way how to convert table into third normal form. I hope it helps. :-)

"Om Santhi, Santhi, Santhi, Om"