DBMS
Normalization
Module Title?
Normalization
DBMS
Normalization is the process of removing redundant data from
tables in order to improve storage efficiency, data integrity
(accuracy and consistency) and scalability (accomodates
changes).
This improvement is balanced against an increase in
complexity and potential performance losses from the joining
of the normalized tables at query-time.
Module Title?
Reasons for Normalization or
Need for Normalization
DBMS
Insertion anomalies
If the user fails to insert information about a new database entry
into all the places in the database where information about that
new entry needs to be stored.
Deletion anomalies
If the user fails to remove information about an existing data
when it is time to remove that data.
Updating anomalies :
An update of a database involves modifications of data all the
places in the database where information about that new entry
needs to be stored.
Module Title?
Data Normalization DBMS
Primarily a tool to validate and improve a logical design so that it satisfies
certain constraints that avoid unnecessary duplication
of data
The process of decomposing relations with anomalies to produce smaller,
well-structured relations
Module Title?
ANOMALY
DBMS
Question – Is this a relation? Answer – Yes: unique rows and no multivalued
attributes
Question – What’s the primary key? Answer – Composite: Emp_ID, Course_Title
Module Title?
Anomalies in this Table
DBMS
Insertion – can’t enter a new employee without having the
employee take a class
Deletion – if we remove employee 140, we lose information about
the existence of a Tax Acc class
Modification – giving a salary increase to employee 100 forces us
to update multiple records
Why do these anomalies exist?
Because there are two themes (entity types) into one
relation. This results in duplication, and an
unnecessary dependency between the entities
Module Title?
Well-Structured Relations
DBMS
A relation that contains minimal data redundancy
and allows users to insert, delete, and update rows
without causing data inconsistencies
Goal is to avoid anomalies
Insertion Anomaly – adding new rows forces user to create
duplicate data
Deletion Anomaly – deleting rows may cause a loss of data
that would be needed for other future rows
Modification Anomaly – changing data in a row forces
changes to other rows because of duplication
Module Title?
Objectives
DBMS
Data normalization aims to derive record structures which
avoid anomalies in
Insertion
Deletion
Modification
Data normalisation ensures single valuedness of facts
Facts are represented in fields in keyed records
Module Title?
First Normal Form
DBMS
A Table in which the intersection of every column and record contains
one and only value
Steps to Remove Repeating Groups
Remove the repeating columns from the original unnormalized table.
Create a new table with the primary key of the base table and the repeating
columns.
Add another appropriate column to the primary key, which ensures
uniqueness.
Create a foreign key in the new table to link back to the original
unnormalized table.
Module Title?
Table Not Satisfying 1 NF
DBMS
Module Title?
After 1 NF……..
DBMS
Branch Address Branch Number Telno
Number B001 566443
B001 X Street BGL
B001 342342
B002 233444
B002 Y Street
Chennai
B002 456454
PRIMARY KEY
PRIMARY KEY
FOREIGN KEY
Module Title?
DBMS
Module Title?
DBMS
Module Title?
DBMS
Module Title?
DBMS
Module Title?
DBMS
Module Title?
DBMS
Module Title?
DBMS
Module Title?