Normalization of Database
Tables
BCA SEM-2 (Gujarat University)
Presentation Prepared By :
Nehal Sheth (SLICA)
Normalization
A large database defined as a single relation may result in data duplication.
This repetition of data may result in:
● Making relations very large.
● It isn't easy to maintain and update data as it would involve searching
many records in relation.
● Wastage and poor utilization of disk space and resources.
● The likelihood of errors and inconsistencies increases.
Presentation Prepared By :
Nehal Sheth (SLICA)
Need for Normalization
● The main reason for normalizing the relations is removing these anomalies.
● Failure to eliminate anomalies leads to data redundancy and can cause data
integrity and other problems as the database grows.
● Normalization consists of a series of guidelines that helps to guide you in
creating a good database structure.
Presentation Prepared By :
Nehal Sheth (SLICA)
Need for Normalization
Data modification anomalies can be categorized into three types:
● Insertion Anomaly: Insertion Anomaly refers to when one cannot insert a new
tuple into a relationship due to lack of data.
● Deletion Anomaly: The delete anomaly refers to the situation where the
deletion of data results in the unintended loss of some other important data.
● Updation Anomaly: The update anomaly is when an update of a single data
value requires multiple rows of data to be updated.
Presentation Prepared By :
Nehal Sheth (SLICA)
Types of Normalization ● Normalization works through a
series of stages called Normal
forms.
● The normal forms apply to
individual relations. The relation
is said to be in particular
normal form if it satisfies
constraints.
Presentation Prepared By :
Nehal Sheth (SLICA)
Conversion to First Normal Form
● A relation will be 1NF if it contains an atomic value.
● It states that an attribute of a table cannot hold multiple values.
It must hold only single-valued attribute.
● First normal form disallows the multi-valued attribute,
composite attribute, and their combinations.
Presentation Prepared By :
Nehal Sheth (SLICA)
Conversion to First Normal Form
Example: Relation EMPLOYEE is not in 1NF because of multi-valued attribute EMP_PHONE.
Presentation Prepared By :
Nehal Sheth (SLICA)
Conversion to First Normal Form
The decomposition of the EMPLOYEE table into 1NF has been shown below:
Presentation Prepared By :
Nehal Sheth (SLICA)
Conversion to Second Normal Form
● In the 2NF, relational must be in 1NF.
● In the second normal form, all non-key attributes are fully functional
dependent on the primary key
Presentation Prepared By :
Nehal Sheth (SLICA)
Conversion to Second Normal Form
Example: Let's assume,
a school can store the
data of teachers and the
subjects they teach. In a
school, a teacher can
teach more than one
subject.
Presentation Prepared By :
Nehal Sheth (SLICA)
Conversion to Second Normal Form To convert the given table into
2NF, we decompose it into two
tables:
In the given table, non-prime attribute
TEACHER_AGE is dependent on TEACHER_ID
which is a proper subset of a candidate key.
That's why it violates the rule for 2NF.
Presentation Prepared By :
Nehal Sheth (SLICA)
Conversion to Third Normal Form
● A relation will be in 3NF if it is in 2NF and not contain any transitive partial
dependency.
● 3NF is used to reduce the data duplication. It is also used to achieve the data
integrity.
● If there is no transitive dependency for non-prime attributes, then the relation
must be in third normal form.
Presentation Prepared By :
Nehal Sheth (SLICA)
Conversion to Third Normal Form
A relation is in third normal form if it holds at least one of the following conditions for every
non-trivial functional dependency X → Y.
1. X is a super key.
2. Y is a prime attribute, i.e., each element of Y is part of some candidate key.
Presentation Prepared By :
Nehal Sheth (SLICA)
Conversion to Third Normal Form Super key in the table below:
1. {EMP_ID}, {EMP_ID, EMP_NAME},
{EMP_ID, EMP_NAME, EMP_ZIP}....
Presentation Prepared By :
Nehal Sheth (SLICA)
Conversion to Third Normal Form
Candidate key: {EMP_ID}
Non-prime attributes: In the given table, all attributes except EMP_ID are
non-prime.
Here, EMP_STATE & EMP_CITY dependent on EMP_ZIP and EMP_ZIP dependent
on EMP_ID. The non-prime attributes (EMP_STATE, EMP_CITY) transitively
dependent on super key(EMP_ID). It violates the rule of third normal form.
Presentation Prepared By :
Nehal Sheth (SLICA)
Conversion to Third Normal Form
That's why we need to move the EMP_CITY and EMP_STATE to the new <EMPLOYEE_ZIP> table, with EMP_ZIP
as a Primary key.
Presentation Prepared By :
Nehal Sheth (SLICA)
Thank You !!
