NORMALIZATION

1
NORMALIZATION

• Analyzing Data for Representation in a Relational
  Database Model

2
Codds Definition of a Relational Database
A relational database is one that is perceived by
the user to be a collection of time-varying
normalized relations of varying
degrees. EXTENSION set of tuples that exist in
the relation at any given time. INTENSION
permanent part of the structure of the relation.
3
Formal Definition
Given the sets D1, D2, , Dn, not all distinct,
where Di a domain of an attribute, R is a
relation on these sets if R is a subset of D1 x
D2 x x Dn R is a subset of (d1, d2, , dn)
di ? Di
4
Gather Information Analyze data and list all
entities Select appropriate field names Look for
natural relationships to set up groups of
data Check for FUNCTIONAL DEPENDENCIES FD Y
is FD on X iff whenever two or more
tuples agree on X, they also agree on Y.
5
Candidate Keys

• K is a candidate key for R if it is a collection
  of one or more attributes in which
• No two tuples in R have the same K (uniqueness)
• If an attribute is dropped from K, uniqueness is
  lost
• Each other attribute of R is FD on K
• Components of K are a maximal functionally
  independent set

6
First Normal Form
A database is in first normal form INF if and
only if all entries are atomic There are no
levels, multiple entries and no null values.
7
Second Normal Form

• Eliminate repeating groups
• Make a separate table for each set of related
  attributes, and give each table a primary key

8
Heaths Theorem

• The relation R can be separated into groups of
  attributes A, B and C satisfying the FD
• R.A - R.B (B is FD on A)
• can be non-loss decomposed into two projected
  relations R1(A, B) and R2(A, C).

9
Getting to 2NF

• Puppy Table
• Puppy Number ? Primary key
• Puppy Name
• Kennel Code
• Kennel Name
• Kennel Location
• --------------------------------------------------
  -
• Trick Table
• Puppy Number ?
• Trick ID ? Primary key
• Trick Name
• Where Learned
• Skill Level

• UNNORMALIZED DATA
• Puppy number
• Puppy name
• Kennel code
• Kennel Name
• Kennel Location
• Trick ID 1n
• Trick Name 1n
• Where Learned 1n
• Skill Level 1n

10
Third Normal Form 3NF

• Eliminate redundant data
• If an attribute depends on only part of a
  multivalued key, remove it to a separate table

11
3NF

• TRICKS
• Trick ID ? key
• Trick Name
• PUPPY TRICKS
• Puppy Number ? foreign key
• Trick ID ? foreign key
• Where Learned
• Skill Level
• PUPPY TABLE
• Puppy Number ? key
• Puppy Name
• Kennel Code
• Kennel Name
• Kennel Location

• TRICK TABLE
• Puppy Trick Trick Where
  Skill
• ID Name Learned Level
• 52 27 Roll over 16
  9
• 53 16 Nose stand 9
  9
• 54 27 Roll over 9
  5

12
Boyce-Codd Normal Form BCNF

• Sometimes referred to as 3.5 NF
• R is in BCNF iff R is in 3NF and every
  determinant is a K
• A determinant is any attribute on which other
  attributes are fully FD (alternate keys)

13
Third Normal Form is sufficient for most
situations. But if that isnt normal enough for
you .
14
Fourth Normal Form 4NF

• Isolate independent multiple relationships
• No table may contain two or more 1n or nm
  relationships that are not directly related
• Not all designs contain such relationships
• 1n - one kennel can hold many puppies
• nm - a puppy can know several tricks and
  several puppies can know the same trick

15
Resolution
16
Fifth Normal Form 5NF

• Isolate semantically related multiple
  relationships.
• There may be no practical constraints on
  information that justify separating logically
  related many-to-many relationships.
• Special characteristics of the data may make it
  more efficient to separate logically related
  attributes.

17
Resolution

• Separate Kennel-Breeder-Breed relationship
• into three tables
• Kennel-Breed
• Kennel
• Breed
• Kennel-Breeder
• Kennel
• Breeder
• Breeder-Breed
• Breeder
• Breed

• 5NF is seldom necessary

18
The Process
The rules leading to and including 3NF can be
summed up in a single sentence Each attribute
must be a fact about the key, the whole key, and
nothing but the key. In anything at all,
perfection is finally attained not when there is
no longer anything to add, but when there is no
longer anything to take away. -
Saint-Exupery in Wind, Sand and Stars
19
Finale
The only glory most of us hope for is the glory
of being normal.