Database Reference
In-Depth Information
Q&A
Question: Convert the following table to 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) )
Answer: Step 1. Remove the repeating group to convert it to first normal form, yielding the following:
Student (StudentNum, StudentName, NumCredits, AdvisorNum, AdvisorName,
CourseNum, Description, Grade)
This 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 a portion
of the 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 subset as its key, yielding the following:
(StudentNum,
(CourseNum,
(StudentNum, CourseNum,
Next, place the rest of the columns with the smallest collection of columns on which they depend, giving
the following:
(StudentNum, StudentName, NumCredits, AdvisorNum, AdvisorName)
(CourseNum, Description)
(StudentNum, CourseNum, Grade)
Finally, assign names to each of the newly created tables as follows:
Student (StudentNum, StudentName, NumCredits, AdvisorNum, AdvisorName)
Course (CourseNum, Description)
StudentCourse (StudentNum, CourseNum, Grade)
Although these tables 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 candidate key.
Step 3. Convert the second 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 the following:
Student (StudentNum, StudentName, NumCredits, AdvisorNum)
Advisor (AdvisorNum, AdvisorName)
Course (CourseNum, Description)
StudentCourse (StudentNum, CourseNum, Grade)
173
MULTIVALUED DEPENDENCIES AND FOURTH NORMAL FORM
By converting a given collection of tables to an equivalent third normal form collection of tables, you remove
problems arising from functional dependencies. Usually this means that you eliminate the types of previously
discussed update anomalies. Converting to third normal form doesn
t avoid all problems related to dependen-
cies, however. A different kind of dependency also can lead to the same types of problems.
To illustrate the problem, suppose you are interested in faculty members at Marvel College. In addition
to faculty members, you are interested in the students they advise and the committees on which the faculty
members serve. A faculty member can advise many students. Because students can have more than one
major, a student can have more than one faculty member as an advisor. A faculty member can serve on zero,
'
Search WWH ::




Custom Search