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

1NF 2NF

C# C-Name
C1 Math
C2 Chem
C3 Phys
C4 Math

S# C#
S1 C1
S1 C2
S1 C3
S2 C2
S2 C3
S2 C4
S3 C2
S3 C3

S# S- S# S-Name C# C-Name

Normalization

Name
S1 Brown
S2 Smith
S3 Brown

S1 Brown1Math
S1 Brown2Chem

S1 Brown3Phys
S2 Smith2Chem
S2 Smith3Phys

Denormalization

S2 Smith4Math
S3 Brown2Chem
S3 Brown3Phys

Fig. 9.4 Normalization of second normal form (2NF)

For example, the following is a 1NF relation SCN ( S# , S-Name, C# , C-Name) such
that before normalization, there is PFD: S#, C#  S-Name and PFD: S#, C# 
C-Name. That is, S-Name is totally dependent on S#, but partially dependent on S#,
C#. After normalization, we have 2NF relations without PFD of relation Student
( S# , S-Name) Relation Course ( C# , C-Name) and relation SC( S# , C# ) with FD: S#
 S-Name, FD: C#  C-Name and FD: S#, C#  0.
The normalized 2NF relations can be denormalized by joining them together to
recover the original 1NF (Fig. 9.4 ).
There are anomalies using 1NF as follows:
First, 1NF relations require less complicated application to operate as opposed
to unnormalized relations. Second, anomalies appear in insert. Since Primary key
(PK) is composed of C# and S#, both details of student and course must be known
before inserting a entry. For example, to add a course, at least one student is en-
rolled. Third, anomalies appear in delete. If all students attending a particular course
are deleted, the course will not be found in the database. Fourth, anomalies appear
in update. There is redundancy of S-Name and C-Name. Also, it increases storage
space and effort to modify data item. If a course is modified, all tuples containing
that course must be updated.
A relation is in third normal form (3NF) if there is no transitive functional depen-
dency (TFD) between the attributes in the relation.
TFD: If X is functionally dependent on Y, and Y is functionally dependent on Z,
then X is transitively dependent on Z.

Normalized into

2NF 3NF