Fundamentals of Database Systems
1
Database and Database Users
Module 6 Normalization
Course Learning Outcomes:
1. Understand what Normalization is
2. Learn what are the normal forms
3. Learn how to normalize relations
Introduction
Normalization is a process of producing a set of tables (relations) with a proper
attribute. In normalization you usually divide a table into 2 or more tables using
Normal Forms as a formal guide.
The goal of normalization is to remove redundancy and data modification problems
like:
• Insertion Anomaly
• Update Anomaly
• Deletion Anomaly
Two basic way to design a table
1. Normalization – Splitting a big table into multiple related table to avoid
anomalies.
2. Three (3) Level modeling approach:
a. Conceptual Design
b. Logical Design
c. Physical Design
Course Module
Example of an Anomaly
As you can see if you change one (1) domain, you also need to change the value of the other
domain. If you delete one domain, the rest of the row will also be deleted.
Example of a Normalized Tables
Fundamentals of Database Systems
3
Database and Database Users
Normal Forms
Steps use for normalization process
1st Normal Form (1NF)
The table is in a 1NF if
• It satisfies the definition of a relation
• NO “repeating groups” (columns)
Example of a Repeating Groups
Course Module
Avoid Repeating Groups by transforming the data into additional rows, rather than additional columns
There’s still a problem in 1st normal form.
The problems are:
• Redundancy
• Anomalies
2nd Normal Form (2NF)
The table is in a 2NF if
• It is in 1st NF, and
• NO Partial Dependency
Partial Dependency – a non-key attribute is dependent on part of a
composite primary key.
Example of a 1st NF
Fundamentals of Database Systems
5
Database and Database Users
How to transform 1st Normal Form into 2nd Normal Form
Steps
1. Identify the Primary Key (PK)
2. If Primary Key consists of only one field, then it is in 2NF
3. If PK is a composite key, then look for partial dependency
4. If there is partial dependency, move the partial dependency involved attributes to
another relation.
There’s still a problem in 2nd normal form.
The problems are:
• Redundancy
• Inconsistency
Course Module
3rd Normal Form (3NF)
The table is in a 3NF if
• It is in 2nd NF, and
• All attributes must, and only, be functionally dependent on the primary key
• NO Transitive Dependency
Transitive Dependency – indirect relationship causes functional
dependency.
Example of a 2nd NF
How to transform 2nd Normal Form into 3rd Normal Form
• Just move the attributes involved in a transitive dependency to another relation or table.
Fundamentals of Database Systems
7
Database and Database Users
Boyce Codd Normal Form (BC/NF)
It is a stricter form of 2nd and 3rd normal form
Example of a BC/NF
Course Module
4th Normal Form (4NF)
Multi-value dependency
Example of a 4NF
Fundamentals of Database Systems
9
Database and Database Users
References and Supplementary Materials
Books and Journals
1. Ramez Elmasri and Shamkant B. Navathe; 2016; Fundamentals of Database Systems;
USA; Pearson
2. Dr. Kashif Qureshi; 2018; Advanced concepts of information technology; educreation
publishing; India.
Online Supplementary Reading Materials
1. RelationalDBDesing; https://www.relationaldbdesign.com/basic-sql/module3/intro-
relational-databases.php; March 31, 2020
2. Advantages of Database Management System;
https://www.tutorialspoint.com/Advantages-of-Database-Management-System;
March 31, 2020
3. DesigningandManagingData;
https://www.academia.edu/36712448/Entity_Relationship_Diagram_ERD_Basics_CIS
_3730_Designing_and_Managing_Data; April 01, 2020
4. DesigningandManagingData; http://jackzheng.net/teaching/archive/cis3730-2010-
fall/; April 03,2020
Online Instructional Videos
1. Introduction to Database; https://www.youtube.com/watch?v=8e-
wgQnsFxE&list=PLJ5C_6qdAvBHKccG0ZyOxcf_2YO6r4Q4l; March 21, 2020
2. Three levels of Architecture/DBMS;
https://www.youtube.com/watch?v=j6xh8wKfjkY; April 01,2020
3. Relational Data Model; https://www.youtube.com/watch?v=TsSf1Z3g0Kk; Arpil 06,
2020.
4. Basic Concept of Database Normalization;
https://www.youtube.com/watch?v=xoTyrdT9SZI; April 08, 2020
Course Module
