Database and Expert System Technology - Information Systems Reengineering, Integration and Normalization - page 44

Relation Student

Student Student-name Sex

012888 Paul Chitson M

120008 Irene Kwan F

117402 John Lee M

Relation Enrollment

* Student# * Course SS# Section# Year Grade

012888 CS101 415223614 1 1995 A

120008 CS101 613557642 2 1996 B

2.3.1 Normalization

The primary problem of relational database design is how the data item types should
be combined to form record types that naturally and completely describe entities
and the relationships between entities. E. F. Codd developed the theory of normal-
ization in the 1970s to overcome this problem. The purpose of normalization is to
reduce complex user-views to a set of manageable and stable data structures.
Normalization theory is built around the concept of normal forms. A relation is
said to be a particular normal form if it satisfies a certain specified set of constraints.
Numerous normal forms have been defined. All normalized relations are in first
normal form (1NF). Some 1NF relations are also in second normal form (2NF).
Some 2NF are also in third normal form (3NF) (Elmasri and Navathe 1989 ).
A 1NF relates to the structure of relations such that the field of a relation should
have simple and atomic values, and relations should have no repeating groups.
A 2NF is one where all partial dependencies have been removed from its 1NF.
That is, no non-key field depends on a subset of a composite key.
A 3NF is one where all transitive dependencies have been removed from its 2NF.
That is, no non-key field depends on another non-key field.
The normalization applies functional dependencies in its normal forms. Func-
tional dependencies are relationships that exist between any two fields. We say that
field A determines field B if each value of A has precisely one value of B. In other
words, field B is functionally dependent on field A. This can be written as
FD:A→BwhereAisadeterminantandBisadependentfield.
The following is an example to illustrate normalization where the students detail
forms a repeating group.