Normalization of Databases

1
Normalization of Databases

• Farrokh Alemi Ph.D.
• Francesco Loaiza Ph.D. J.D.
• Vikas Arya

2
Objectives of Design

• Increase efficiency
• Reduce redundancy
• Reduce missing data entries
• Allow users access to data without knowing
  location of data

3
Design Principles

1. Each Table should correspond to a single Entity
   or a single Relationship
2. Rows in the Table should correspond to individual
   occurrences of that Entity or Relationship
3. The Primary Key should uniquely identify
   individual occurrences of the Entity or
   Relationship

4
Design Principles

1. Each Table should correspond to a single Entity
   or a single Relationship
2. Rows in the Table should correspond to individual
   occurrences of that Entity or Relationship
3. The Primary Key should uniquely identify
   individual occurrences of the Entity or
   Relationship

5
Design Principles

1. Each Table should correspond to a single Entity
   or a single Relationship
2. Rows in the Table should correspond to individual
   occurrences of that Entity or Relationship
3. The Primary Key should uniquely identify
   individual occurrences of the Entity or
   Relationship

6
Principles of Design (Continued)

1. Non-key fields should be facts about the
   occurrence identified by the Primary Key
2. Each fact should be represented only once in the
   database

7
Principles of Design (Continued)

• Non-key fields should be facts about the
  occurrence identified by the Primary Key
• Each fact should be represented only once in the
  database

8
Normalization

• Normalization is the process of applying
  principles of design to data structures so that
  they conform to out expectations
• This lecture covers 3 additional rules for
  putting databases in Normal form

9
Design Flaw 1
10
First Normal Form

• A Table is in first Normal form (1NF) if and only
  if all fields contain only atomic values and
  there are no repeating fields within a row.
• No Composite Fields
• A total is an example of a non-atomic field
• No Repeating Groups
• If names are listed in two columns under a
  household, then the field is repeated.

11
First Normal Form
12
Functional Dependency

• An Attribute Y is Functionally Dependent on an
  Attribute X, if a Value for X Determines a Unique
  Value for Y
• X may be a Set of Attributes
• Notation
• X Y (read X determines Y)

13
Functional Dependency Example
Employee Number
Employee Name
Is it also true that
Employee Number
Employee Name
14
Full Functional Dependency

• An Attribute Y may be Determined by a Set of
  Attributes A,B,C (ABC Y)
• If X is a Set of Attributes Such That X Y
  and there is no Subset Z of X so that Z Y
  Then Y is Fully Functionally Dependent on X

15
Example for Full Functional Dependence
Employee Number, Dept
Employee Name
But
Employee Number
Employee Name
So Employee Name is not Fully Functionally Depende
nt on Employee Number and Department
16
Second Normal Form

• A Table is in Second Normal Form if and only if
  informational fields (facts) are fully functional
  dependent on the primary key.

17
Violation of Second Normal Form
18
Solution to Second Normal Form
19
Third Normal Form

• A Table is in Third Normal form if and only if
  there are no combination of strictly
  informational fields (not primary key fields)
  that determines the value of another field

20
Violation of Third Normal Form
21
Rules for Good Design

1. Each table should correspond to a single entity
2. Each row should correspond to occurrences of the
   entity
3. Facts in the table should describe the primary
   key
4. Each fact should be represented only once in the
   database
5. No composite or repeating fields should be used
6. No combination of facts should determine the
   value of another
7. The primary key should uniquely identify the
   entity
8. All facts should be fully functional dependent on
   the primary key