Database Systems: Design,

Implementation, and
Management
DEGREE

Normalization of Database
 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, 8th Edition 2

 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 needed in normalization
– Highest level of normalization is not always most
desirable
• Denormalization produces a lower normal
form
– Price paid for increased performance is greater
data redundancy
Database Systems, 8th Edition 3
Database Systems, 8th Edition 4
Database Systems, 8th Edition 5
 The Need for Normalization
(continued)
• Structure of data set in Figure 5.1 does not
handle data very well
• Table structure appears to work; report
generated with ease
• Report may yield different results depending on
what data anomaly has occurred
• Relational database environment suited to help
designer avoid data integrity problems

Database Systems, 8th Edition 6

 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
• Each table void of insertion, update, deletion
anomalies

Database Systems, 8th Edition 7

Database Systems, 8th Edition 8
 The Normalization Process
(continued)
• Objective of normalization is to ensure all tables
in at least 3NF
• Higher forms not likely to be encountered in
business environment
• Normalization works one relation at a time
• Progressively breaks table into new set of
relations based on identified dependencies

Database Systems, 8th Edition 9

Database Systems, 8th Edition 10
 Conversion to First Normal Form

• Repeating group
– Group of multiple entries of same type 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, 8th Edition 11

 Conversion to First Normal Form
(continued)
• Step 1: Eliminate the Repeating Groups
– Eliminate nulls: each repeating group attribute
contains an appropriate data value
• Step 2: Identify the Primary Key
– Must uniquely identify attribute value
– New key must be composed
• Step 3: Identify All Dependencies
– Dependencies depicted with a diagram

Database Systems, 8th Edition 12

Database Systems, 8th Edition 13
 Conversion to First Normal Form
(continued)
• 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 you will overlook an
important dependency

Database Systems, 8th Edition 14

Database Systems, 8th Edition 15
 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 part of the primary key
– Should be used with caution

Database Systems, 8th Edition 16

 Conversion to Second Normal Form
• 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
• 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, 8th Edition 17
Database Systems, 8th Edition 18
 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, 8th Edition 19

 Conversion to Third Normal Form

• 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
• Step 2: Identify the Dependent Attributes
– Identify attributes dependent on each
determinant identified in Step 1
• Identify dependency
– Name table to reflect its contents and function
Database Systems, 8th Edition 20
 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
– Draw new dependency diagram to show all
tables defined in Steps 1–3
– Check new tables as well as tables modified in
Step 3
• Each table has determinant
• No table contains inappropriate dependencies
Database Systems, 8th Edition 21
Database Systems, 8th Edition 22
 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, 8th Edition 23

 Improving the Design

• Table structures cleaned up to eliminate 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, 8th Edition 24

 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, 8th Edition 25

Database Systems, 8th Edition 26
 Normalization and Database Design

• Normalization should be part of the 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
• You may be asked to redesign and modify
existing databases

Database Systems, 8th Edition 27

 Normalization and Database Design
(continued)
• ER diagram
– Identify relevant entities, their attributes, and
their relationships
– Identify additional entities and attributes
• Normalization procedures
– Focus on characteristics of specific entities
– Micro view of entities within ER diagram
• Difficult to separate normalization process from
ER modeling process
Database Systems, 8th Edition 28
 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, 8th Edition 29

 Denormalization (continued)
• Joining the larger number of tables reduces
system speed
• Conflicts often resolved through compromises
that may include denormalization
• Defects of unnormalized tables:
– Data updates are less efficient because tables
are larger
– Indexing is more cumbersome
– No simple strategies for creating virtual tables
known as views

Database Systems, 8th Edition 30