NORMALIZATION
Normalization in Database is a process of organizing data in a database to reduce Redundancy
(duplicate data), improve data integrity (accuracy & consistency) and removing dependencies among
key and non-key attributes.
OR
Normalization in Database is a process that involves dividing large tables into smaller related tables and
defining relationships between them, so that each piece of data is stored only once.
Main Goal of Normalization
✓ Eliminate data redundancy (avoid repeating the same data in multiple places)
✓ Ensure data consistency (changes in one place automatically reflect everywhere)
✓ Remove Dependencies (dependencies among key and non-key attributes)
✓ Simplify queries and updates
✓ Improve storage efficiency
Normalization is accomplished in steps, each of which represents a normal form. A normal form is a
state of relation that can be determined by applying simple rules to the relation. The flow of
normalization through different steps can be explained as follows.
Normalization Steps
• Splitting Multivalued Cells Into Separate Rows
• Creating Separate Tables For Repeating Groups
1NF • Removing Duplicated Records
• Must Be In 1NF
• Remove Partial Dependency
2NF
• Must Be In 2NF
• Remove Transitive Dependency
3NF
• Higher Normal Forms (If Required)
HNF
1NF (First Normal Form)
A relation/Table is in 1NF if every intersection of Row and Column contains atomic value.
✓ Produce Atomicity in the Relation/Table
Single Value Within
1NF ✓ Make the Relation/Table Elemental
a Single Cell
✓ Split Multivalued cells into Separate Rows
Given Table Multivalued Cells
ITEM COLOR PRICE TAX
Red
T-Shirt 12 0.6
Blue
Red
Polo 12 0.6
Yellow
Blue
S-Shirt 25 1.25
Black
What to do?
Make Table Atomic/Elemental or Convert Multivalued Cells into Single Cells.
ITEM COLOR PRICE TAX
T-Shirt Red 12 0.6
We simply add
T-Shirt Blue 12 0.6 one more row for
each color and
separate the color
Polo Red 12 0.6
into single cell.
The remaining
Polo Yellow 12 0.6 data of the cells
copied as it is.
S-Shirt Blue 25 1.25
S-Shirt Black 25 1.25
As we can see that now each cell of the Relation/Table has single value within the single cell.
Therefore, the given Relation/Table is now in 1NF.
2NF (Second Normal Form)
A relation/Table is in 2NF if it is already in 1NF and every non-key attribute is fully functionally
dependent on the Primary Key (on all parts of Primary Key).
Partial Dependency Only exist in case when
we have Composite or Concatenate or
Compound Primary Key in the Relation/Table.
✓ Relation/Table Must be in 1NF
2NF
✓ Remove Partial Dependency
Partial dependency occurs when a non-key
attribute depends on part of a composite
primary key rather than on the whole key.
Table is Already in 2NF, if it is in 1NF and following conditions hold:
✓ Primary Key Consist of Only One (single) Attribute
✓ No Non-key Attribute Exist in the Relation/Table
✓ Every Non-key Attribute is Fully Functionally Dependent on the Whole Primary Key in case
of Composite Primary Key
Given Table
✓ We cannot import Surrogate key (attribute added from outside the system)
✓ So, decide Primary key (select single or multiple attributes those are available in the table)
✓ As we can see that no single attribute has the ability to become a Primary key because every attribute
has redundancy (duplication); therefore, we have to consider Composite Primary key
✓ Composite key is [ ITEM + COLOR ]
ITEM COLOR PRICE TAX
What To Do?
T-Shirt Red 12 0.6
* Remove Partial Dependency
* If we want to remove Partial Dependency
T-Shirt Blue 12 0.6 then we have to identify it. Unless we do
not identify, we cannot remove it.
Polo Red 12 0.6 * So, what is Partial Dependency?
“Dependency of non-key attribute on part
Polo Yellow 12 0.6 of Composite Primary key”
* Partial Dependency is:
ITEM PRICE, TAX
S-Shirt Blue 25 1.25 * Now remove this Partial Dependency, it
means split the Table with this dependency
S-Shirt Black 25 1.25
According to Database rules, all Non-Key Attributes must be fully
Now In above Table: functionally dependent on Whole Primary key [ITEM+COLOR], but in
Key-Attributes are : ITEM, COLOR above Table “PRICE” & “TAX” being a Non-key Attributes dependent on
Non-Key Attributes are : PRICE, TAX Part of Primary key i.e. “ITEM”. This is Partial Dependency.
Composite Primary Key
Foreign Key
As Table is split into two Tables. We split what we
have in Partial Dependency i.e.
ITEM COLOR ITEM PRICE, TAX
Primary Key
T-Shirt Red
T-Shirt Blue ITEM PRICE TAX
Polo Red T-Shirt 12 0.6
Polo Yellow
Polo 12 0.6
S-Shirt Blue
S-Shirt 25 1.25
S-Shirt Black
Now Above Table is in 2NF, because we have removed Partial Dependency.
3NF (Third Normal Form)
A relation/Table is in 3NF if it is already in 2NF and every non-key attribute must not dependent on any
other non-key attribute.
✓ Relation/Table Must be in 2NF
3NF Transitive dependency occurs when a non-key
✓ Remove Transitive Dependency
attribute depends on other non-key attribute.
Table is Already in 3NF, if it is in 2NF and following conditions hold:
✓ No Non-key Attribute Exist in the Relation/Table
✓ Every Non-key Attribute Does Not Depend on Any Other Non-key Attribute
Given Table According to Database rules, all Non-Key Attributes
must not depend on any other Non-key Attribute. But
in Table “TAX” being a Non-key Attributes dependent
on other Non-key Attribute i.e. “PRICE”. This is
ITEM COLOR Transitive Dependency (PRICE TAX).
T-Shirt Red
ITEM PRICE TAX
T-Shirt Blue
T-Shirt 12 0.6
Polo Red
Polo Yellow Polo 12 0.6
S-Shirt Blue S-Shirt 25 1.25
S-Shirt Black Nothing to do with this Table because it does not have any non-key attribute.
What To Do?
* Remove Transitive Dependency
* If we want to remove Transitive Dependency then we have to identify it. Unless we do not identify,
we cannot remove it.
* So, what is Transitive Dependency?
“Dependency of non-key attribute on other non-key attribute”
* Transitive Dependency is:
PRICE TAX
* Now remove this Transitive Dependency, it means split the Table with this dependency
Primary Key
Foreign Key
Foreign Key
ITEM COLOR Primary Key
T-Shirt Red
T-Shirt Blue ITEM PRICE
Polo Red T-Shirt 12
PRICE TAX
Polo Yellow Polo 12
12 0.6
S-Shirt Blue S-Shirt 25
25 1.25
S-Shirt Black
As we can see that we have removed Transitive Dependency, therefore the above Table is in 3NF.
NOTE:
When Table splits into Two, we have to connect it with split Table by using Foreign Key because if we
don’t connect them with Foreign key then other Table which is part of the same Table will not be
accessible in Database.