Database Reference
In-Depth Information
he two tables resulting from the table split are displayed in Tables 3.2 and 3.3. he table
COURSE displayed in Table 3.2 has a single-column primary key; this means it is impossible for a
partial dependency to occur. he violation of 2NF is only relevant when a table has a combination
key. hus, any relation that has a single-column primary key is in 2NF.
he table STUDENT_CLASS displayed in Table 3.3 is also in 2NF even though it has func-
tional dependencies as shown below:
StudentID
→
StudentName
StudentName
→
StudentID
Why is the dependency StudentID
→
StudentName not a partial dependency? Since
StudentID uniquely determines StudentName and vice versa, there are two combination candi-
date keys (StudentID, ClassID) and (StudentName, ClasssID). his indicates that StudentName
is considered as part of a key. Remember that a partial dependency happens between a key column
and a nonkey column. herefore, the dependency StudentID
→
StudentName is not considered
a partial dependency.
hird normal form
: Being in 2NF, a table may still have modiication anomalies. As an
example, let us consider the modiication anomalies that occurred during data modiication in the
table STUDENT_CLASS displayed in Table 3.3.
Update anomalies
: In the STUDENT_CLASS table, there is data redundancy among the
columns FacultyID and FacultyName. For example, a change to the faculty name related to the
faculty with the ID 1 has to be done in multiple locations; this causes the occurrence of an update
a noma ly.
Insertion anomalies
: Suppose we want to add a new student into the table STUDENT_
CLASS. You would not be able to do so until information about the faculty is provided.
Deletion anomalies
: Suppose that the student with the ID 14 is removed from the table
STUDENT_CLASS. he information about the faculty Garza is lost.
he above modiication anomalies are caused by another type of functional dependency called
transitive dependency
. In the table STUDENT_CLASS, the nonkey column FacultyName is
determined by another nonkey column FacultyID. he transitive dependency can be represented
with the following relation notation:
(StudentID, ClassID)
→
StudentName, FacultyID, FacultyName, Grade
FacultyID
→
FacultyName
When a nonkey column determines another nonkey column in a table, the table is not in 3NF.
For a table to be in 3NF, we must eliminate the
transitive dependency
. 3NF is speciied by the
following rules:
◾
It is in 2NF.
◾
It contains no
transitive dependency
.
he transitive dependency can be eliminated by splitting the table. In our example, we split
the table STUDENT_CLASS into two tables, FACULTY and STUDENT_CLASS as shown in
Tables 3.4 and 3.5.
