Table Normalization and Windows Azure SQL Database - Cloud Database Development and Management - page 82

Table 3.6 STUDENT_CLASS Table

StudentID StudentName FacultyID Grade ClassID

10 Liz 1 A 1000

11 Joe 2 C 1001

12 Linda 1 B 1002

13 Don 3 C 1000

14 Jan 4 B 1001

10 Liz 1 A 1003

16 Bruce 3 B 1004

11 Joe 2 B 1006

13 Don 3 A 1005

or

(StudentName, ClassID) → StudentID, FacultyID, Grade

StudentID → StudentName

As you can see, StudentName determines StudentID or vice versa. But, the determinant
StudentName itself is not a candidate key. he functional dependency can cause the following
modiication anomalies:
Update anomalies : In the STUDENT_CLASS table, the update of the student name Joe has
to be done at multiple locations; this causes the occurrence of update anomalies.
Insertion anomalies : You cannot add a new student into the table STUDENT_CLASS until
you provide information about a class.
Deletion anomalies : If the student with the ID 13 is removed from the table STUDENT_
CLASS, the class ID 1005 is lost.
To solve the problem, we need to further split the table STUDENT_CLASS to get to the next
level of normalization called Boyce-Codd normal form . A table is in BCNF if it meets the fol-
lowing requirements:

◾ It is in 3NF.

◾ Every determinant is a candidate key.

he requirements listed above indicate that a table in 3NF with only a single key column and
a single nonkey column is already in the BCNF because the only determinant is the primary key;
that is, every determinant is a candidate key. In our example, to be in the BCNF, the STUDENT_
CLASS table is split and multiple tables are created as shown in the following relation notation:
STUDENT( StudentID , StudentName, FacultyID )
STUDENT_CLASS( StudentID , ClassID , Grade)
CLASS( ClassID , TimeBlock, WeekDay, Semester)