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

2NF

3NF

P# M# E#

P# M#
P1 M1
P2 M2
P3 M1
P4 M1
P5 M3
P6 M4

M# E#
M1 E1
M2 E3
M3 E2
M4 E1

P1 M1 E1
P2 M2 E3
P3 M1 E1
P4 M1 E1
P5 M3 E2
P6 M4 E1

normalization

denormalization

Fig. 9.5 Normalization of third normal form (3NF)

For example, the relation PME (P#, M#, E#) with FD: P#  M#, FD: M# 
E#, and TFD: P#  E# can be normalized into 3NF as relation PM ( P# , M#) and
ME ( M# , E#) with FD: P#  M# and FD: M#  E#. The normalized relations
can be denormalized by joining them together to recover the original 2NF relation
(Fig. 9.5 ).
However, there are anomalies in 2NF. Suppose we have the relations,
PRODUCT, MACHINE, and EMPLOYEE as

Relation PME ( P# , M# , E# ) with FD: P# M#, FD: P# E#, FD: M#
E#.

The tuple (P1, M1, E1) means product P1 is manufactured on machine M1 which is
operated by employee E1. There are anomalies in insert. It is not possible to store
the fact that which machine is operated by which employee without knowing at
least one product produced by this machine. There are anomalies in delete. If an
employee is fired the fact that which machine he operated and what product that
machine produced are also lost. There are normalizes in update. If one employee is
assigned to operate another machine then several tuples have to be updated as well.
A relation is in Boyce-Codd normal form (BCNF) if all determinant is a candi-
date key.

Normalizedinto

3NF BCNF

Each determinant is a candidate key.