5

Chapter 5

Normalization of Database Tables

Database Systems:
Design, Implementation, and Management,
Seventh Edition, Rob and Coronel
1
 5

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

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 2
 5

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)

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 3
 5
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

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 4
 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 employee’s
position
– Periodically, report is generated that contains
information displayed in Table 5.1

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 5
 5

The Need for Normalization (continued)

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 6
 5

The Need for Normalization (continued)

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 7
 5

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

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 8
 5

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

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 9
 5

The Normalization Process (continued)

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 10
 5

Conversion to First Normal Form

• Repeating group
– Derives its name from the fact that a group of
multiple entries of same type can exist for any
single key attribute occurrence
• Relational table must not contain repeating
groups
• Normalizing table structure will reduce data
redundancies
• Normalization is three-step procedure
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 11
 5
Conversion to First Normal Form
(continued)
• Step 1: Eliminate the Repeating Groups
– Present data in tabular format, where each cell
has single value and there are no repeating
groups
– Eliminate repeating groups, eliminate nulls by
making sure that each repeating group
attribute contains an appropriate data value

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 12
 5
Conversion to First Normal Form
(continued)

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 13
 5
Conversion to First Normal Form
(continued)
• Step 2: Identify the Primary Key
– Primary key must uniquely identify attribute
value
– New key must be composed

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 14
 5
Conversion to First Normal Form
(continued)
• Step 3: Identify All Dependencies
– Dependencies can be depicted with help of a
diagram
– Dependency diagram:
• Depicts all dependencies found within given
table structure
• Helpful in getting bird’s-eye view of all
relationships among table’s attributes
• Makes it less likely that will overlook an
important dependency

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 15
 5
Conversion to First Normal Form
(continued)

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 16
 5
Conversion to First Normal Form
(continued)
• First normal form describes tabular format in which:
– All key attributes are defined
– There are no repeating groups in the table
– All attributes are dependent on primary key
• All relational tables satisfy 1NF requirements
• Some tables contain partial dependencies
– Dependencies based on only part of the primary key
– Sometimes used for performance reasons, but should
be used with caution
– Still subject to data redundancies

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 17
 5

Conversion to Second Normal Form

• Relational database design can be improved

by converting the database into second
normal form (2NF)
• Two steps

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 18
 5
Conversion to Second Normal Form
(continued)
• Step 1: Write Each Key Component
on a Separate Line
– Write each key component on separate line,
then write original (composite) key on last line
– Each component will become key in new table

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 19
 5
Conversion to Second Normal Form
(continued)
• Step 2: Assign Corresponding Dependent
Attributes
– Determine those attributes that are dependent
on other attributes
– At this point, most anomalies have been
eliminated

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 20
 5
Conversion to Second Normal Form
(continued)

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 21
 5
Conversion to Second Normal Form
(continued)
• Table is in second normal form (2NF) when:
– It is in 1NF and
– It includes no partial dependencies:
• No attribute is dependent on only portion of
primary key

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 22
 5

Conversion to Third Normal Form

• Data anomalies created are easily eliminated

by completing three steps
• Step 1: Identify Each New Determinant
– For every transitive dependency, write its
determinant as PK for new table
• Determinant
– Any attribute whose value determines other
values within a row

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 23
 5
Conversion to Third Normal Form
(continued)
• Step 2: Identify the Dependent Attributes
– Identify attributes dependent on each
determinant identified in Step 1 and identify
dependency
– Name table to reflect its contents and function

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 24
 5
Conversion to Third Normal Form
(continued)
• Step 3: Remove the Dependent Attributes from
Transitive Dependencies
– Eliminate all dependent attributes in transitive
relationship(s) from each of the tables that have
such a transitive relationship
– Draw new dependency diagram to show all
tables defined in Steps 1–3
– Check new tables as well as tables modified in
Step 3 to make sure that each table has
determinant and that no table contains
inappropriate dependencies
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 25
 5
Conversion to Third Normal Form
(continued)

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 26
 5
Conversion to Third Normal Form
(continued)
• A table is in third normal form (3NF) when
both of the following are true:
– It is in 2NF
– It contains no transitive dependencies

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 27
 5

Improving the Design

• Table structures are cleaned up to eliminate

troublesome initial partial and transitive
dependencies
• Normalization cannot, by itself, be relied on to
make good designs
• It is valuable because its use helps eliminate
data redundancies

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 28
 5

Improving the Design (continued)

• Issues to address in order to produce a good

normalized set of tables:
– Evaluate PK Assignments
– Evaluate Naming Conventions
– Refine Attribute Atomicity
– Identify New Attributes
– Identify New Relationships
– Refine Primary Keys as Required for Data Granularity
– Maintain Historical Accuracy
– Evaluate Using Derived Attributes

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 29
 5
Improving the Design (continued)

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 30
 5
Improving the Design (continued)

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 31
 5

Surrogate Key Considerations

• When primary key is considered to be

unsuitable, designers use surrogate keys
• Data entries in Table 5.3 are inappropriate
because they duplicate existing records
– Yet there has been no violation of either entity
integrity or referential integrity

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 32
 5

Surrogate Key Considerations (continued)

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 33
 5

Normalization and Database Design

• Normalization should be part of design process

• Make sure that proposed entities meet required
normal form before table structures are created
• Many real-world databases have been improperly
designed or burdened with anomalies if improperly
modified during course of time
• You may be asked to redesign and modify existing
databases

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 34
 5
Normalization and Database Design
(continued)
• ER diagram
– Provides big picture, or macro view, of an
organization’s data requirements and
operations
– Created through an iterative process
• Identifying relevant entities, their attributes and
their relationship
• Use results to identify additional entities and
attributes

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 35
 5
Normalization and Database Design
(continued)
• Normalization procedures
– Focus on characteristics of specific entities
– Represents micro view of entities within ER
diagram
• Difficult to separate normalization process
from ER modeling process
• Two techniques should be used concurrently

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 36
 5
Normalization and Database Design
(continued)

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 37
 5
Normalization and Database Design
(continued)

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 38
 5
Normalization and Database Design
(continued)

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 39
 5
Normalization and Database Design
(continued)

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 40
 5
Normalization and Database Design
(continued)

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 41
 5

Denormalization

• Creation of normalized relations is important

database design goal
• Processing requirements should also be a
goal
• If tables decomposed to conform to
normalization requirements:
– Number of database tables expands

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 42
 5

Denormalization (continued)

• Joining the larger number of tables takes

additional input/output (I/O) operations and
processing logic, thereby reducing system
speed
• Conflicts between design efficiency,
information requirements, and processing
speed are often resolved through
compromises that may include
denormalization
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 43
 5

Denormalization (continued)

• Unnormalized tables in production database

tend to suffer from these defects:
– Data updates are less efficient because
programs that read and update tables must
deal with larger tables
– Indexing is more cumbersome
– Unnormalized tables yield no simple strategies
for creating virtual tables known as views

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 44
 5

Denormalization (continued)

• Use denormalization cautiously

• Understand why—under some circumstances
—unnormalized tables are better choice

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 45
 5

Summary

• Normalization is technique used to design

tables in which data redundancies are
minimized
• First three normal forms (1NF, 2NF, and 3NF)
are most commonly encountered
• Table is in 1NF when all key attributes are
defined and when all remaining attributes are
dependent on primary key
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 46
 5

Summary (continued)

• Table is in 2NF when it is in 1NF and contains no

partial dependencies
• Table is in 3NF when it is in 2NF and contains no
transitive dependencies
• Table that is not in 3NF may be split into new
tables until all of the tables meet 3NF
requirements
• Normalization is important part—but only part—of
design process
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 47
 5
Summary (continued)

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 48
 5
Summary (continued)

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 49
 5

Summary (continued)

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 50
 5

Summary (continued)

• Tables are sometimes denormalized to yield

less I/O which increases processing speed

Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 51