Data Normalization - Information Systems Reengineering, Integration and Normalization - page 342

BCNF

3NF

normalization

StudentID CourseID HKID

StudentID CourseID

StudentID HKID
S1

S1 CS3402 E123456

S1 CS3402

E123456

S1 CS5483 E123456

S1 CS5483

S2 E567890

S2 CS3402 E567890

S2 CS3402

denormalization

S2 CS6287 E567890

S2 CS6287

Fig. 9.6 Normalization and denormalization of Boyce-Codd normal form (BCNF)

Case Study I:
Relation SCH ( StudentID , CourseID , HKID) consists of FD: StudentID, Cour-
seID  HKID and FD: HKID  StudentID. It is not in BCNF because HKID is
a determinant but is not a candidate key. After normalization, Relation SC (Stu-
dentID, CourseID) with FD: StudentID, CourseID  0 and Relation SH (HKID,
StudentID) with FD: HKID  StudentID. Therefore, all determinants are candidate
keys and therefore they are in BCNF.
The normalized relations can be denormalized by joining them together to re-
cover the original 3NF relation (Fig. 9.6 ).
In 3NF, there are anomalies in update. If the fact that Student S1 takes more
courses, then the HKID E123456 needs to repeat many times, which is a waste of
computer time and space, which is similarly for student S2. It is because H is a de-
terminant but not a candidate key.
Case Study II:
Relation SJT ( S , J , T) consists of FD: S, J  T and FD: T  J. It is not in BCNF
because T is a determinant but is not a candidate key. After normalization, Relation
SJ ( S , T ) with FD: S, T  0 and Relation TJ ( T , J) with FD: T  J. Therefore, all
determinants are candidate keys and therefore they are in BCNF.
The normalized relations cannot be denormalized by joining them together be-
cause there is loss of information of who is the teacher of Smith taking Physics in
the joined relation (Fig. 9.7 ).
A relation is in fourth normal form (4NF) if there is no multivalued data depen-
dency (MVD) within the relation.
In general, a MVD is defined as a determinant determines a multiple valued
dependent attribute as shown below:

Determines
MVD: Determinant Dependent multiple values

Normalizedinto
BCNF

4NF (without MVD)

Eliminate MVD