UNIT – IV

Data Normalization

Data Normalization: Anomalies in relational database design. Decomposition. Functional

dependencies. Normalization. First normal form, Second normal form, Third normal form. Boyce-Codd
normal form.

4.1 Anomalies in relational database design

 Anomalies (irregularity) in the relational model refer to inconsistencies or errors that can arise
when working with relational databases, specifically in the context of data insertion, deletion,
and modification.
 Database anomalies are the faults in the database caused due to poor management of storing
everything in the flat database.
 It can be removed with the process of Normalization

Reasons for anomalies

 If lot of redundant data present in our database
 If a table is constructed in a very poor manner
 If all the data is stored in a single table

Types of Anomalies

There are 3 types of database anomalies

1. Insertion Anomalies
2. Deletion Anomalies
3. Update Anomalies

Insert anomaly

 The term "insertion anomaly" is used to describe when a new row is added to a table and it
causes an inconsistency.
 If a tuple is inserted in referencing relation and referencing attribute value is not present in
referenced attribute, it will not allow inserting in referencing relation.
Example
Assume that a new employee is joining the company under training and not assigned to any
department. Then, we would not insert the data into the table if the emp_dept field doesn't allow nulls.

Update anomaly

 If there are some changes in the database, we have to apply that change in all the rows. And if
we miss any row, will create an update anomaly in the database.

 If a tuple is updated from referenced relation and the referenced attribute value is used by
referencing attribute in referencing relation. In that case, it will not allow updating the tuple
from referenced relation.

Example

1
 If we can update the correct address of an employee (assume he belongs to two different
department of the company) in one department but not the other, then according to the database, that
will have two different addresses, which is not correct and would lead to inconsistent data.

Delete anomaly

 The term "deletion anomaly in the database" is used when we delete some rows from a table
and any necessary additional information or data is also lost from the database.

 If a tuple is deleted from referenced relation and the referenced attribute value is used by
referencing attribute in referencing relation, it will not allow deleting the tuple from referenced
relation.

Example
Assume that if the company closes the department D890, then deleting the rows that have emp_dept as
D890 would also delete the information of employee Maggie since she is assigned only to this
department.

4.2 Decomposition

 The process of breaking up or dividing a single relation into two or more sub relations is called as
decomposition of a relation.
 It helps to remove redundancy, inconsistencies and anomalies from a database.
 Relational decomposition can be of two types,
1. Lossless Decomposition
2. Lossy Decomposition

Lossless Decomposition

When a relation is decomposed into two or more relational schemas, if the information does not
lose from the relation , it is known as Lossless decomposition.
Lossless decomposition guarantees that when the decomposed relations are joined back together, then
it will result in the same original relation as before decomposition.
For example −
EmpInfo
Emp_ID Emp_Name Emp_Age Emp_Location Dept_ID Dept_Name
E001 Jacob 29 Alabama Dpt1 Operations
E002 Henry 32 Alabama Dpt2 HR
E003 Tom 22 Texas Dpt3 Finance

Decompose the above table into two tables as follows

EmpDetails
Emp_ID Emp_Name Emp_Age Emp_Location
E001 Jacob 29 Alabama
E002 Henry 32 Alabama
E003 Tom 22 Texas
DeptDetails

Dept_ID Emp_ID Dept_Name

Dpt1 E001 Operations
2
Dpt2 E002 HR
Dpt3 E003 Finance

Now, Natural Join is applied on the above two tables EmpDetails and DeptDetails, The result will be the
original table EmpInfo.

Advantages of Lossless Decomposition

1. Reduced Data Redundancy
2. Easier to maintain and update the database Improved Data Integrity
3. Improved Flexibility( easier modification of the schema)
Disadvantages of Lossless Decomposition
1. Increased Complexity
2. Costly

Lossy Decomposition

When a relation is decomposed into two or more relational schemas, if the loss of information is
unavoidable then it is known as lossy [Link] example,
EmpInfo

Emp_ID Emp_Name Emp_Age Emp_Location Dept_ID Dept_Name

E001 Jacob 29 Alabama Dpt1 Operations
E002 Henry 32 Alabama Dpt2 HR
E003 Tom 22 Texas Dpt3 Finance

Decompose the above table into two tables as follows

EmpDetails

Emp_ID Emp_Name Emp_Age Emp_Location

E001 Jacob 29 Alabama
E002 Henry 32 Alabama
E003 Tom 22 Texas

DeptDetails

Dept_ID Dept_Name
Dpt1 Operations
Dpt2 HR
Dpt3 Finance

Now, we won’t be able to join the above tables, since Emp_ID isn’t part of the DeptDetails relation.
Therefore, the above relation has lossy decomposition.

4.3 Functional dependencies

3
  Dependency in DBMS is a relation between two or more attributes, in other words, a
dependency is a constraint that applies to or defines the relationship between attributes.
 In this, knowing a value of one attribute (or set of attributes) is enough to determine the value of
another attribute(or set of attributes) in the same table.

In a relational database management, functional dependency is a concept that specifies the

relationship between two sets of attributes where one attribute determines the value of another
attribute. It is denoted as X → Y, where the attribute set on the left side of the arrow, X is
called Determinant, and Y is called the Dependent.

Functional dependencies are used to mathematically express relations among database entities
and are very important to understand advanced concepts in Relational Database System

4.4 Normalization

 Normalization is the process of structuring the RDBMS by applying some general rules either by
creating a new database design or by decomposition
 Normalization is the process of splitting the relations into smaller relations to do the operations
more efficiently without any inconsistency.
 Without normalization, the integrity of the table will not be maintained over time.
 To prevent anomalies, we need to normalize the database by efficiently organizing the data in a
database.
 Normalization is a systematic approach to eliminate data redundancy and Insertion,
Modification, and Deletion Anomalies by decomposing tables.

Normalization consists of a series of guidelines that helps to guide us in creating a good database
structure. Redundant data wastes disk space and creates maintenance problems. If data that exists in
more than one place must be changed, the data must be changed in exactly the same way in all
locations. A customer address change is easier to implement if that data is stored only in the Customers
table and nowhere else in the database.

There are a few rules for database normalization. Each rule is called a "normal form." If the first rule
is observed, the database is said to be in "first normal form." If the first three rules are observed, the
database is considered to be in "third normal form." Although other levels of normalization are
possible, third normal form is considered the highest level necessary for most applications.

Advantages of Normalization

 Reduced data redundancy

 Improved data consistency
 Simplified database design
 Improved query performance
 easier database maintenance

Disadvantages of Normalization

 It is very time-consuming and difficult to normalize relations of a higher degree.

4
  The performance degrades when normalizing the relations to higher normal forms, i.e., 4NF,
5NF.
 Careless decomposition may lead to a bad database design, leading to serious problems.

Types of Normal Forms

Normal Form Description

1NF A relation is in 1NF if it contains an atomic value.
2NF A relation will be in 2NF if it is in 1NF and all non-key attributes
are fully functional dependent on the primary key.
3NF A relation will be in 3NF if it is in 2NF and no transition
dependency exists.
BCNF A stronger definition of 3NF is known as Boyce Codd's normal
form.
4NF A relation will be in 4NF if it is in Boyce Codd's normal form and
has no multi-valued dependency.
5NF A relation is in 5NF. If it is in 4NF and does not contain any join
dependency, joining should be lossless.

Note:
In real world database systems generally not required to go beyond BCNF

4.5 First normal form - 1 NF

A relation is in first normal form only if the relational table does not contain any multi valued
attribute. It can contain only single valued attributes.

 This is the most basic level of normalization.

 A relation is in first normal form if every attribute in that relation is singled valued attribute.
 If a relation contain composite or multi-valued attribute, it violates first normal form
 a relation is in first normal form if it does not contain any composite or multi-valued attribute
 The first normal form helps to eliminate duplicate data and simplify queries.

1 NF can be achieved by
 Create a separate table for each set of related data.
 Identify each set of related data with a primary key.
For Example,
Student_Info
REG NO NAME COURSES
S008 Kalyan AWS, ML
S009 Aswin AWS
S010 Sheela ML
S011 Vinay AWS, AI

the above table Student_Info is a multi-valued attribute so it is not in 1NF. So, we can convert this
table to 1NF as there is no multi-valued attribute

Student_Info

5
 REG NO NAME COURSES
S008 Kalyan AWS
S008 Kalyan ML
S009 Aswin AWS
S010 Sheela ML
S011 Vinay AWS
S011 Vinay AI

4.6 Second normal form - 2 NF

To be in second normal form, a relation must be in first normal form and relation must not
contain any partial dependency. A relation is in 2NF if it has No Partial Dependency, i.e., no non-prime
attribute (attributes which are not part of any candidate key) is dependent on any proper subset of
any candidate key of the table.

Partial Dependency – If the proper subset of candidate key determines non-prime attribute, it
is called partial dependency.

2NF eliminates redundant data by requiring that each non-key attribute be dependent on the
primary key. This means that each column should be directly related to the primary key, and not to
other columns.

2NF can be achieved by

 Create separate tables for sets of values that apply to multiple records.
 Relate these tables with a foreign key.
For example,
Consider the table below,
REG NO COURSES COURSE_FEE
S008 AWS 1500
S008 ML 1500
S009 AWS 1500
S010 ML 1500
S011 AWS 1500
S011 AI 2500

Note that, there are many courses having same course fee and
COURSES  COURSE_FEE ie COURSE_FEE is dependent on COURSES
Since,
COURSE_FEE would be a non-prime attribute, as it does belong to REGNO, COURSE
Non -prime attribute COURSE_FEE is dependent on a proper subset of the candidate key, which is a
partial dependency, and so this relation is not in 2NF.

To convert the above relation into 2NF, we need to split the table into two table such as,

Table 1 : REGNO,COURSE
Table 2: COURSE, COURSE_FEE

Table1

6
 REGNO COURSE
S008 AWS
S008 ML
S009 AWS
S010 ML
S011 AWS
S011 AI

Table 2
COURSE COURSE_FEE
AWS 1500
ML 1500
AI 2500

2NF tries to reduce the redundant data getting stored in memory for instance, if there are 100 students
taking AI course, we need not to store its fee as 2500 for all 100 records. Instead, we can store it in the
second table as course fee for AI is 2500.

4.7 Third normal form - 3NF

A relation that is in first and second normal form and in which no non-primary-key
attribute is transitively dependent on the primary key, then it is in third normal form.

 Although 2NF relations have less redundancy than those in 1NF, they may still suffer from
update anomalies.
 If we update only one tuple and not the other, then the database would be in an inconsistent
state.
 This update anomalies is caused by a transitive dependency. We need to remove such
dependencies by progressing to 3NF.

 Transitive dependency refers that non- prime attribute depends on another non- prime
attribute.

2NF can be achieved by

 Eliminate fields that don't depend on the key.

3NF builds on 2NF by requiring that all non-key attributes are independent of each other. This
means that each column should be directly related to the primary key, and not to any other columns
in the same table.

For example
STUDENT
REGNO NAME STATE COUNTRY AGE
S008 Kalyan Haryana India 20
S009 Aswin Delhi India 21
S010 Sheela California USA 20
S011 Vinay kerala India 19

REGNO NAME

7
REGNOSTATE
REGNOAGE

STATECOUNTRY Here COUNTRY transitively depends on STATE. ie that non- prime attribute
depends on another non- prime attribute. So it violates 3NF.

To convert it into 3NF, we should decompose the relation

STUDENT ( REGNO, NAME, STATE, AGE)
STUDENT_ADD(REGNO,STATE,COUNTRY)

Third Normal Form - 3NF is considered as adequate for normal relational database design. Because most
of the 3NF tables are free of insertion, update and deletion anomalies. Moreover, 3NF always ensures
functional dependency preserving and lossless decomposition.

4.8 Boyce-Codd normal form

BCNF is a stricter form of 3NF that ensures that each determinant in a table is a candidate key.
In other words, BCNF ensures that each non-key attribute is dependent only on the candidate key.
 BCNF is the advanced version of 3NF
 A table is in BCNF if every functional dependency XY, X is the super key of the table

For Example,
Consider a relation R with attributes (Student, Subject,Teacher)

R
Student Subject Teacher
Jancy Naresh DBMS
Jancy Das C#
Subbu Naresh DBMS
Subbu Prasad C#

Here
Student,Teacher Subject
Student, Subject Teacher
TeacherSubject

The above relation is in 3NF. But, this relation suffers with anomalies. For example, if we try to delete
the record subbu, we will lose the information that Prasad teaches C#. These difficulties are caused by
the fact that Teacher is determinant but not a candidate key.

To convert into BCNF, we will decompose the relation

R1(Teacher, Subject)
R2(Student,Teacher)

R1
Teacher Subject
Naresh DBMS
Das C#
Prasad C#

R2
8
Student Teacher
Jancy Naresh
Jancy Das
Subbu Naresh
Subbu Prasad

BCNF decomposition does not always satisfy dependency preserving property. After BCNF
decomposition if dependency is not preserved then, we have to decide whether we want to remain in
BCNF or rollback to 3NF. This process of rollback is called denornalization.