Normalization
Intro
• Good database design must be matched with good
table structures
• E-R diagrams help use with overall design, but a good
conceptual design does not necessarily lead to a good
table structures
• Tables are the basic building blocks of the database
• We wish to avoid data anomalies/redundancies by
controlling the table structure logically
• The process of identifying and eliminating data
anomalies and redundancies is called normalization
Redundancy & Anomalies
• Data redundancy = data stored in several places
– Too much data redundancy causes problems--which value is
correct?
– Data integrity and consistency suffer
• Data anomaly = abnormal data relationships
– Insertion anomaly - Can’t add data because don’t know
entire primary key value, e.g., primary key based on first,
middle, and last name
– Deletion anomaly - Deletions result in too many fields
being removed unintentionally, e.g., delete an employee
but lose transaction data
– Update anomaly - Change requires many updates, e.g., if
you store customer names in transaction tables
Normalization
• Step-by-step process for eliminating data
redundancies and anomalies
• Enables us to recognize bad table structures
• Enables us to create good table structures
• Stages are called normal forms each better than
previous (less anomalies/redundancy)
– First normal form (1NF)
– Second normal form (2NF)
– Third normal form (3NF)
Normalization
• There are fourth and fifth normal forms that
are seldom used
• Highest level not always the most desirable
• In general, the higher the level the slower
the database response because of underlying
pointer movement
• Most professionally designed databases
reach third normal form
Need for Normalization
• To recognize good design, first look at bad one
• Example, construction company manages
several projects and whose charges are
dependent on employee’s position
Desired Report
Proj No Proj Name Emp No Emp Name Job Class Chg/Hr Hrs Billed Tot Chg
1 Hurricane 101 John News Elec Eng 65 13 845
102 David Senior Comm Tech 60 16 960
104 Anne Ramoras Comm Tech 60 19 1,140
Sub Tot 2,945
2 Coast 101 John News Elec Eng 65 15 975
103 June Arbough Biol Eng 55 17 935
Sub Tot 1,910
3 Satellite 104 Anne Ramoras Comm Tech 60 18 1,080
102 David Senior Comm Tech 60 14 840
Sub Tot 1,920
Total 6,775
Table
P_No P_Name E_No E_Name Job_Class Chg_Hr Hrs
1 Hurricane 101 John News Elec Eng 65 13
102 David Senior Comm Tech 60 16
104 Anne Ramoras Comm Tech 60 19
2 Coast 101 John News Elec Eng 65 15
103 June Arbough Biol Eng 55 17
3 Satellite 104 Anne Ramoras Comm Tech 60 18
102 David Senior Comm Tech 60 14
Another View of Table
Group 1 Group 2 Etc.
P_No P_Name E_No1 E_Name1 Job_Class1 Chg_Hr1 Hrs1 E_No2 E_Name2 Job_Class2 Chg_Hr2 Hrs2
1 Hurricane 101 John News Elec Eng 65 13 102 David Senior Comm Tech 60 16
2 Coast 101 John News Elec Eng 65 15 103 June Arbough Biol Eng 55 17
3 Satellite 104 Anne Ramoras Comm Tech 60 18 102 David Senior Comm Tech 60 14
Problems
• P_No intended to be primary key but contains null
values
• Data redundancies
– Invites data inconsistencies (Elect Eng & EE)
• Anomalies
– Update anomaly – modify Job_Class for E_No 101 requires
many alterations
– Insert anomaly – to add a project row we need an employee
– Deletion anomaly – delete E_No 101, we delete other vital
data too
Problems
• Date redundancy
– If add new employee to project 2 must type:
2 Coast 104 Anne Ramoras Comm Tech 60 19
• Wastes data entry time
• Wastes storage space
• Leads to data inconsistency
– Huricane or Hurricane or Hurracaine
Conversion to 1NF
• Table above has repeating groups
• Each P_No has a group of entries
P_No P_Name E_No E_Name Job_Class Chg_Hr Hrs
1 Hurricane 101 John News Elec Eng 65 13
102 David Senior Comm Tech 60 16
104 Anne Ramoras Comm Tech 60 19
1NF
• Eliminate repeating groups
• By adding entries in primary key column (at
least)
P_No P_Name E_No E_Name Job_Class Chg_Hr Hrs
1 Hurricane 101 John News Elec Eng 65 13
1 Hurricane 102 David Senior Comm Tech 60 16
1 Hurricane 104 Anne Ramoras Comm Tech 60 19
2 Coast 101 John News Elec Eng 65 15
2 Coast 103 June Arbough Biol Eng 55 17
3 Satellite 104 Anne Ramoras Comm Tech 60 18
3 Satellite 102 David Senior Comm Tech 60 14
Problems
• Primary key P_No does not uniquely
identify all attributes in row
• Must create composite key made up of
P_No & E_No
Dependency Diagram
• Helps us to discover relationships between entity attributes
• Upper arrows implies dependency on P_No & E_No
• Lower arrows implies dependency on only one attribute
P_No P_Name E_No E_Name Job_Class Chg_Hr Hrs
Dependencies
• Upper arrows
– If you know P_No & E_No you can determine the other row
values
• Lower arrows
– Partial dependencies – based on only part of key
– P_Name only dependent on P_No
– E_Name, Job_Class, Chg_Hr only dependent on E_No
• Dependency diagram may be written:
– P_No, E_No P_Name, E_Name, Job_Class, Chg_Hr, Hrs
– P_No P_Name
– E_No E_Name, Job_Class, Chg_Hr
New Table
• Composite primary key P_No & E_No
Charges Table
P_No E_No P_Name E_Name Job_Class Chg_Hr Hrs
1 101 Hurricane John News Elec Eng 65 13
1 102 Hurricane David Senior Comm Tech 60 16
1 104 Hurricane Anne Ramoras Comm Tech 60 19
2 101 Coast John News Elec Eng 65 15
2 103 Coast June Arbough Biol Eng 55 17
3 104 Satellite Anne Ramoras Comm Tech 60 18
3 102 Satellite David Senior Comm Tech 60 14
1NF Definition
1. All the key attributes are defined
– Any attribute that is part of the primary key
2. There are no repeating groups in the table
– Each cell can contain one and only one value,
rather than set
3. All attributes are dependent on the primary key
Problems
• Contains partial dependencies
– Dependencies base on only part of the primary key
• This makes table subject to data redundancies and
hence to data anomalies
• Redundancy caused by fact that every row entry
requires duplicate data
– E.g., suppose E_No 105 is entered 20 times, must also
enter E_Name, Job_Class, Chg_Hr
• Anomalies caused by redundancy
– E.g., employee name may be spelled Dave Senior or D.
Senior or David Senior
Conversion to 2NF
1. Starting with 1NF write each of the key
components on separate lines, then write the
original key on the last line
P_No
E_No
P_No E_No
• Each will become key in a new table
• Original table split into three tables
Conversion to 2NF
2. Write the dependent attributes after each of
the new keys using the dependency diagram
P_No P_Name
E_No E_Name, Job_Class, Chg_Hr
P_No E_No Hrs
Three New Tables
Project Table Employee Table
P_No P_Name E_No E_Name Job_Class Chg_Hr
1 Hurricane 101 John News Elec Eng 65
2 Coast 102 David Senior Comm Tech 60
3 Satellite 103 June Arbough Biol Eng 55
104 Anne Ramoras Comm Tech 60
Assign Table
P_No E_No Hrs
1 101 13
1 102 16
1 104 19
2 101 15
2 103 17
3 104 18
3 102 14
2NF Definition
1. Table is in 1NF and
2. It includes no partial dependencies (no attribute
is dependent on only a portion of the primary
key)
• Note: Since partial dependencies can exist only
if there is a composite key, a table with a single
attribute as primary key is automatically in 2NF
if it is in 1NF
Problem - Transitive Dependency
• Note that Chg_Hr is dependent on Job_Class, but
neither Chg_Hr nor Job_Class is part of the
primary key
• This is called transitive dependency
– A condition in which an attribute is functionally
dependent on non-key attributes (another attribute that
is not part of the primary key)
• Transitive dependency yields data anomalies
Conversion to 3NF
• Break off the pieces that are identified by the
transitive dependency arrows (lower arrows) in the
dependency diagram
• Store them in a separate table
P_No P_Name
E_No E_Name, Job_Class
P_No E_No Hrs
Job_Class Chg_Hr
• Note: Job_Class must be retained in Employee
table to establish a link to the newly created Job
table
New Tables
Project Table Employee Table
P_No P_Name E_No E_Name Job_Class
1 Hurricane 101 John News Elec Eng
2 Coast 102 David Senior Comm Tech
3 Satellite 103 June Arbough Biol Eng
104 Anne Ramoras Comm Tech
Assign Table Job Table
P_No E_No Hrs Job_Class Chg_Hr
1 101 13 Biol Eng 55
1 102 16 Comm Tech 60
1 104 19 Elec Eng 65
2 101 15
2 103 17
3 104 18
3 102 14
3NF Definition
1. Table is in 2NF and
2. It contains no transitive dependencies
Problem
• Although the four tables are in 3NF, we
have a potential problem
• The Job_Class is entered for each new
employee in the Employee table
• For example, too easy to enter Electrical
Engr, or EE, or El Eng
Problem
Employee Table
E_No E_Name Job_Class
101 John News Elec Eng
102 David Senior Comm Tech
103 June Arbough Biol Eng
104 Anne Ramoras Comm Tech
104 John Smith Comm Tech
105 Alice White Biol Eng
106 Bob Jones Elec Eng
New Attribute
• Create a Job_Code attribute to serve as
primary key in the Job table and as a
foreign key in the Employee table
Changed Tables
Project Table Employee Table
P_No P_Name E_No E_Name Job_Code
1 Hurricane 101 John News 502
2 Coast 102 David Senior 501
3 Satellite 103 June Arbough 500
104 Anne Ramoras 501
Assign Table Job Table
P_No E_No Hrs Job_Code Job_Class Chg_Hr
1 101 13
500 Biol Eng 55
1 102 16
501 Comm Tech 60
1 104 19
2 101 15
502 Elec Eng 65
2 103 17
3 104 18
3 102 14
3NF Version
• Vast improvement over original design
• No data anomalies
– In the Job table each job code has single job class and
charge per hour entry
• No opportunities to use different values describing same object
– Similarly for Employee & Project tables – only one
entry for each attribute
– Also, Assign table has only what is needed
• Data redundancy has been minimized
– Keys are redundant but these are small
– Assign table is very active but requires only the P_No,
E_No, and hours
Other Normal Forms
• 3NF is the most appropriate form for most
applications
• There are other normal forms
• 4NF – Isolate independent multiple relationships
• 5NF – Isolate semantically related multiple
relationships
• These are advanced and go beyond the scope of
the course
Summary
• 1NF – Eliminate repeating groups
• 2NF – Eliminate partial dependencies
• 3NF – Eliminate transitive dependencies
• Tables are the critical building blocks for the
database
• It is important to design their structure well
• Normalization is a formal process for doing this
that reduces data redundancy and anomalies
End
References:
New Perspectives on Microsoft Access 2000, Introductory, by Adamski, Hommel, and Finnegan, Course
Technology, 1999.
Access Database Design and Programming, Third Edition, by Roman, O’Reilly, 2002.
Database Systems: Design, Implementation, and Management, by Rob & Coronel, Boyd & Fraser, 1995.