NORMALIZATION OF
RELATIONAL DATABASES
Advanced Database Management System
Mr. Joel Lezlee L. Domingo Jr.
What are Database Dependencies and Why Do They Matter?
A database dependency is a constraint that defines the relationship between
attributes. It happens when information stored in the same database table
uniquely determines other information stored in the same table. It’s important to
understand what database dependencies are because they provide the basic
building blocks for database normalization.
What are the Different Types of Database Dependencies?
● A functional dependency occurs when one attribute in a relation uniquely determines another
attribute.
Notation: A B (A functionally determines B)
● StudentID FullName (because each student ID uniquely determines a full name)
● StudentID Email
This means if you know the StudentID, you can find exactly one FullName and Email.
● Partial dependency. A partial dependency occurs when a non-prime attribute is
functionally dependent on part of the candidate key.
● Transitive dependency. A transitive dependency occurs when a non-prime attribute depends on
another non-prime attribute rather than on the primary key directly.
What is Normalization?
Normalization is the process of evaluating and correcting table structures
to minimize data redundancies and to eliminate anomalies in adding,
updating, and deletion of records.
Normal
Forms
● 1NF (First Normal Form)
● 2NF (Second Normal Form)
● 3NF (Third Normal Form)
● BCNF (Boyce-Codd Normal Form)
Example of User’s View of Information
First Normal
Form
To convert user’s view of information to 1NF, repeating groups must be eliminated
while deriving its name from group of multiple entries that may exist in any single
key attribute.
Relational table must not contain repeating groups and normalizing the table
structure will reduce data redundancies.
First Normal Form (Continued)
Three-step procedure:
● Step 1 – Eliminate Repeating Groups
● Step 2 – Identify the Primary Keys
● Step 3 – Identify All Dependencies
First Normal Form (Continued)
For Step 1, present data in a tabular format, where each
cell has a single value and there are no repeating groups.
Eliminate repeating groups by removing nulls to make
sure that each repeating group attribute contains an
appropriate data value.
First Normal Form (Continued)
Example
First Normal Form (Continued)
For Step 2, identify the primary keys that uniquely identify
an attribute value.
For Step 3, identify the possible functional
dependencies through a dependency diagram.
First Normal Form (Continued)
Example
The arrows above the attributes indicate desirable dependencies, those
that are based on the primary key and the arrows below the attributes
indicate less desirable dependencies.
First Normal Form (Continued)
Partial dependencies are dependent on primary key
while transitive dependencies are dependencies of one
non- prime attribute on another non-prime attribute that
can produce data anomalies.
Functional Dependency
PROJ_NUM + EMP_NUM PROJ_NAME, EMP_NAME, JOB_CLASS, CHG_HOURS, HOURS
Partial Dependency
PROJ_NUM PROJ_NAME
EMP_NUM EMP_NAME, JOB_CLASS, CHG_HOUR
First Normal Form (Continued)
Transitive Dependency
JOB_CLASS CHG_HOUR
First Normal Form (Continued)
The table is in 1NF if:
● All key attributes has been defined;
● There is no repeating groups in the table; and
● All attributes are dependent on primary key
And the 1NF dependency diagram shows that all relational
tables satisfy the 1NF requirements.
Second Normal
Form
The design of relational database can be improved by
converting the 1NF to 2NF.
Steps to convert 1NF to 2NF:
● Step 1. Identify All Key Components
● Step 2. Identify the Dependent Attributes
Second Normal Form (Continued)
For Step 1, to identify the key components, write each key
on a separate line and write the original (composite) key
on last line.
Example:
● PROJ_NUM
● EMP_NUM
● PROJ_NUM, EMP_NUM
Note: This key component will be used on its corresponding table.
Second Normal Form (Continued)
For Step 2, to identify the dependent attributes, from
1NF Dependency Diagram, determine which attributes
are dependent on other attributes.
Example
● PROJECT (PROJ_NUM, PROJ_NAME)
● EMPLOYEE (EMP_NUM, EMP_NAME, JOB_CLASS, CHG_HOURS)
● ASSIGN (PROJ_NUM, EMP_NUM, ASSIGN_HOURS)
Second Normal Form (Continued)
2NF Dependency Diagram
Third Normal
Form
Data anomalies in any raw source are eliminated by
converting 2NF design to 3NF.
Steps to convert 2NF to 3NF:
● Step 1. Identify Each New Determinant;
● Step 2. Identify the Dependent Attributes; and
● Step 3. Remove the dependent attributes from
transitive
dependencies.
Third Normal Form (Continued)
For Step 1, to identify a new determinant, write the
determinant of transitive dependency as the primary key
of the new table (determinant is an attribute whose value
determines other values within a row).
For Step 2, identify the dependent attributes of
determinant identified in Step 1.
Example
● JOB_CLASS CHG_HOUR
Third Normal Form (Continued)
And give a name for the new table that reflects its
content
and function.
Example
● JOB (JOB_CLASS, CHG_HOUR)
Third Normal Form (Continued)
For Step 3, to remove the dependent attributes from
transitive dependencies:
● Eliminate all dependent attributes in transitive
relationship(s) from each table;
● Draw a new dependency diagram to show all tables
defined in in Step 1 to 3; and
● Check the new and modified tables in Step 3 to make
sure that each has a determinant and does not
contain inappropriate dependencies.
Third Normal Form (Continued)
By removing the dependent attributes the result will have
the following tables:
● PROJECT (PROJ_NUM, PROJ_NAME)
● EMPLOYEE (EMP_NUM, EMP_NAME, JOB_CLASS)
● ASSIGN (PROJ_NUM, EMP_NUM, ASSIGN_HOURS)
● JOB (JOB_CLASS, CHG_HOURS)
Third Normal Form (Continued)
3NF Dependency Diagram
End