Database Reference
In-Depth Information
Table 3.4
FACULTY Table
FacultyID
FacultyName
1
Smith
2
Lee
3
Fry
4
Garza
Table 3.5
STUDENT_CLASS Table
StudentID
StudentName
Grade
ClassID
10
Liz
A
1000
11
Joe
C
1001
12
Linda
B
1002
13
Don
C
1000
14
Jan
B
1001
10
Liz
A
1003
16
Bruce
B
1004
11
Joe
B
1006
13
Don
A
1005
he table FACULTY uses the column FacultyID as the primary key. Since there is only
one nonkey column in the table, this means that there is no transitive dependency in the table.
herefore, the table FACULTY is in 3NF.
In general, a table has no transitive dependency if each nonkey column except the foreign key
column in the table directly depends on the primary key. his means that even if we add the for-
eign key column FacultyID to the table STUDENT_CLASS (Table 3.6), it is still in 3NF.
Now, if you want to change a faculty name, you can do it in the FACULTY table displayed in
Table 3.4 instead of in the table STUDENT_CLASS displayed in Table 3.3. his will avoid the
update anomaly caused by the transitive dependency FacultyID
→
FacultyName.
As mentioned earlier, tables in 3NF are acceptable for many databases. For some databases,
tables in 3NF are still not good enough. In such a case, the normalization process should continue.
Boyce-Codd normal form
: First, let us take a close look at the modiication anomalies that
cannot be eliminated by 3NF. Consider the table STUDENT_CLASS displayed in Table 3.5. he
table has a combination primary key (StudentID, ClassID). In addition, there is a combination
candidate key (StudentName, ClassID). Here, we have two combination candidate keys, and they
have a common element ClassID. hat is, we have the following functional dependency:
(StudentID, ClassID)
→
StudentName, FacultyID, Grade
StudentName
→
StudentID
