Normalization of Database Tables

1
Chapter 5

• Normalization of Database Tables

2
In this chapter, you will learn

• What normalization is and what role it plays in
  the database design process
• About the normal forms 1NF, 2NF, 3NF
• How normal forms can be transformed from lower
  normal forms to higher normal forms
• That normalization and ER modeling are used
  concurrently to produce a good database design
• That some situations require denormalization to
  generate information efficiently

3
Database Tables and Normalization

• Normalization
• Process for evaluating and correcting table
  structures to minimize data redundancies
• Reduces data anomalies
• Works through a series of stages called normal
  forms
• First normal form (1NF)
• Second normal form (2NF)
• Third normal form (3NF)

4
Database Tables and Normalization (continued)

• Normalization (continued)
• 2NF is better than 1NF 3NF is better than 2NF
• For most business database design purposes, 3NF
  is as high as we need to go in normalization
  process
• Highest level of normalization is not always most
  desirable

5
The Need for Normalization

• Example Company that manages building projects
• Charges its clients by billing hours spent on
  each contract
• Hourly billing rate is dependent on employees
  position
• Periodically, report is generated that contains
  information displayed in Table 5.1

6
The Need for Normalization (continued)
7
The Need for Normalization (continued)

• Structure of data set in Figure 5.1 does not
  handle data very well
• The table structure appears to work report
  generated with ease
• Unfortunately, report may yield different results
  depending on what data anomaly has occurred

8
The Normalization Process

• Each table represents a single subject
• No data item will be unnecessarily stored in more
  than one table
• All attributes in a table are dependent on the
  primary key

9
The Normalization Process (continued)
10
NF Definitions

• 1NF A table is in 1NF if it has a primary key
  and it has no multi-valued attributes.
• 2NF A table is in 2NF if it is in 1NF and it
  has no partial dependencies.
• 3NF A table is in 3NF if it is in 2NF and it
  has no transitive dependencies. (When a non-key
  attribute depends on another non-key attribute,
  it is called a transitive dependency.)
• STUDENT (Student-ID, Student-Name,
  Student-Phone)
• Key attribute Non-key
  attributes

11
Examples of Tables Not in 1NF

• STUDENT(Student-ID, Student-Name, Class-Code,
  Grade)
• QUESTION What normal form is the above table in?
• ANSWER It is not normalized at all (or it is in
  0NF) because It has multi-valued attributes
  Given one student ID, Class-Code and Grade are
  multi-valued.

12
Examples of Tables Not in 2NF

• ENROLLMENT(Student-ID, Class-Code, Class-Title,
  Grade)
• QUESTION What normal form is the above table in?
• ANSWER It is in 1NFbecause (1) It has no
  multi-valued attributes. (2) However, it has
  partial dependency, i.e., Class-Title depends on
  Class-Code only, which is part of the key.

13
Examples of Tables Not in 3NF

• CLASS (Class-Code, Class-Title, Instructor-ID,
  Instructor-Name)
• QUESTION What normal form is the above table in?
• ANSWER It is in 2NFbecause (1) It has no
  multi-valued attributes. (2) It has no partial
  dependencies. (3) However, it has a transitive
  dependency since Instructor-Name depends on
  Instructor-ID.

14
Examples of Tables Not in 3NF

• ENROLLMENT (Class-Code, Student-ID, Grade)
• QUESTION What normal form is the above table in?
• ANSWER It is in 3NFbecause (1) It has no
  multi-valued attributes. (2) It has no partial
  dependencies. (3) It has no transitive
  dependencies.

15
A Simplified Process of Converting Tables to 3NF

• Identify all functional dependencies, i.e.,
  identify determinants and their dependent fields.
• Create tables such that every determinant becomes
  the PK of the table and the dependent fields
  become other columns of the same table.

16
A Simplified Process of Converting Tables to 3NF

• EXAMPLE
• PROJECT(Project, Project-Name, Employee-ID,
  Employee-Name, Employee-Project-Hours)
• Step 1 - Identify Functional Dependencies
• Project ? Project_Name
• Employee-ID ? Employee-Name
• Employee-ID, Project ? Employee-Project-Hours

17
A Simplified Process of Converting Tables to 3NF

• EXAMPLE
• PROJECT(Project, Project-Name, Employee-ID,
  Employee-Name, Employee-Project-Hours)
• Step 2 - Define Tables
• PROJECT (Project, Project_Name)
• EMPLOYEE (Employee-ID, Employee-Name)
• PROJECT_ASSIGNMENT(Employee-ID, Project,
  Employee-Project-Hours)