Normalization
Normalization is an important process in database design that helps improve the
database's efficiency, consistency, and accuracy. It makes it easier to manage and
maintain the data and ensures that the database is adaptable to changing business
needs.
Database normalization is the process of organizing the attributes of the database
to reduce or eliminate data redundancy (having the same data but at different
places).
Data redundancy unnecessarily increases the size of the database as the same data
is repeated in many places. Inconsistency problems also arise during insert,
delete, and update operations.
efficient a
In the relational model, there exist standard methods to quantify how
algorithms to
databases is. These methods are called normal forms and there are
covert a given database into normal forms.
Normalization generally involves splitting a table into multiple ones
which must
from the split tables.
be linked each time a query is made requiring data
Why do we need Normalization? relations is to eliminate the below
The primary objective for normalizing the data redundancy, which may
anomalies. Failure to reduce anomalies results in
issues as the database increases.
threaten data integrity and cause additional
Normalization consists of a set of procedures that
assist you in developing an
effective database structure.
occur when it is not possible to insert
Insertion Anomalies: Insertion anomalies because the data is
fields are missing or
data into a database because the required
that every record has a primary
incomplete. For example, if a database requiresrecord, it cannot be inserted into
particular
key, but no value is provided for a
the database.
occur when deleting a record from a
Deletion anomalies: Deletion anomalies if a
unintentional loss of data. For example,
database and can result in the customer
customers and orders, deleting a
database contains information about
delete all the orders associated with that customer.
record may also in a
anomalies: Updation anomalies occur when modifying data
Updation errors. For example, if a database
result in inconsistencies or
database and can
their salaries, updating an employee's
contains information about employees and related records could lead to incorrect
but not in all
salary in one record
calculations and reporting.
Before Normalization
Employee_Department
Emp_ ID Emp_Name Department Dept_Location Emp_Skills
101 Nick Wise HR London Recruitment, Payroll
102 John Cader Finance Australia Budgeting
103 Lily Case HR London Recruitment
104 Ford Dawid Chicago Programming, Testing
Problems in the Employee_Department Relation
1. Insertion Anomaly:
" Ifanew department is created but noemployee is assigned to it yet, we cannot store
its location because we need an employee recordto insert.
2. Update Anomaly:
" If the locationof the HR department changes, we must update it in multiple rows (for
both Nick Wise and Lily Case). If one row is missed, the data becomes inconsistent.
3. Deletion Anomaly:
" If all employees in the IT department leave, we lose the department information,
including its location.
4. Data Redundancy:
. The department location is repeated for every employee in the same department.
After Normalization
Employee Department Employee_skills
Emp_ID Emp_Name Dept_IDDept_D DepartmentDept_Location Emp_ID Emp_Skilis
HR 101 Recruitment
10 Nick Wise D1 D1 London
102 John Cader D2 D2 Finance Australia
101 Payroll
103 Lily Case 01 D3 IT Chicago 102 Budgeting
103 Recruitment
104 Ford Dawid D3
104 Programming
104 Testing
Features of Database Normalization
normalization is
Elimination of Data Redundancy: One of the main features of
to eliminate the data redundancy that can occur in a database. Data redundancy
rerers to the repetition of data in different parts of the database. Normalization
helps in reducing or eliminating this redundancy, which can improve the
eticiency and consistency of the database.
Ensuring Data Consistency: Normalization helps in ensuring that the data in the
database is consistent and accurate. By eliminating redundancy, normalization
can arise due to
belps in preventing inconsistencies and contradictions that
different versions of the same data.
simplifies the process of
Simplification of Data Management: Normalization
down acomplex data structure into
managing data in adatabase. By breaking
tables, normalization makes it easier to manage the data, update it, and
simpler
retrieveily Normalization helps in improving
the overal
Improved Database Design:
By organizing the data in a structured and systematic
design of the database. to design and maintain the database. It also
it easier
way, normalization makes adaptable to changing business
nceds.
more flexible and avoiding update
makes thedatabase Normalization helps in
Anomalies: affects
Avoiding Update
when updating a single record in a table
anomalies, which can ccur Normalization ensures that each
table contains
tables. tables are clearly
multiple records in other relationships between the
and that the
only one type of data such anomalies.
defined, which helps in avoiding in standardizing the data in the
database.
helps
Standardization: Normalization
defining relationships between them,
into tables and and uniform
By organizing the data ensuring that the data is stored in a consistent
normalization helps in
manner.
DBMS
Normal Forms in
Normal
Description of NormalForms
Forms is
relation
normal form if every attribute in that
First Normal A relation is in first
single-valued attribute.
Form (1 NF)
non-primary-key
every
relation that is in First Normal Form and primary key, then the
Second A functionally dependent on
the
attribute is fully
Normal Forn Form (2NF).
is in Second Normal
relation
(2NF) there is no transitive
third normal form, if [Link]
the
A relation is in wellas it is in the
attributes as conditions
dependency for non-prime least one of the following
3NF if at
form. A relation is in function dependency X -> Y.
holds in every non-trivial candidate
X isa super key. element of Y is part of some
attribute (each
. Y is a prime
Third Normal
Form (3NF) key).
conditions
should satisfy the below
relation
For BCNF the be in the 3rd Normal
Form.
dependency (FD)
should
Boyce-Codd The relation every functional
Normal Form a super-key for
X should be relation.
(BCNE} X->Y in a given
Normal
Forms
Description of Normal Forms
Fourth
A relation Ris
in 4NF if and
Normal Form satisfie: only if the
(4NF) It should be in following conditions
The table shouldthe Boyce-Codd Normal Form are
not have any (BCNF),
Multi-valued Dependency.
A relation R is
conditions: in SNF if and only
Fifth Normal if it satisfies the
R should be already in following
Form (5NF) It cannot be further non 4NF.
loss decomposed (join
dependency ).
Advantages of Normalization
Normalization eliminates data redundancy and
stored in only one place, reducing the risk ensures that each piece of data is
easier to maintain data accuracy. of data inconsistency and making it
By breaking down data into smaller,
more specific tables, normalization helps
ensure that each table stores only relevant data,
which improves the overall data
integrity of the database.
Normalization simplifies the process of updating data, as it only needs
changed in one place rather than in multiple places throughout the database. to be
Normalization enables users to query the database using a variety of different
criteria, as the data is organized into smaller, more specific tables that can be
joined together as needed.
Normalization can help ensure that data is consistent across different applications
that use the same database, making it easier to integratedifferent applications and
ensuring that allusers have access to accurate and consistent data.
Disadvantages of Normalization
Normalization can result in increased performance overhead due to the need for
additional joinoperations and the potential for slower query execution times.
Normalization can result in the loss of data context, as data may be split across
multiple tables and require additional joins to retrieve.
Proper implementation of normalization requires expert knowledge of datab:
design and the normalization process.
Normalization can increase the complexity of adatabase design, especially if the
data model is not well understood or if the normalization process is not carried
out correctly.