Database 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 normalisation 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.

 In the relational model, there exist standard methods to quantify how efficient a database is.
These methods are called normal forms, and there are algorithms to convert a given
database into normal forms.

 Normalization generally involves splitting a table into multiple ones, which must be linked
each time a query is made requiring data from the split tables.

Features of Database Normalization

 Elimination of Data Redundancy: One of the main features of normalization is to eliminate
the data redundancy that can occur in a database. Data redundancy refers to the repetition
of data in different parts of the database. Normalization helps in reducing or eliminating this
redundancy, which can improve the efficiency 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 helps in preventing
inconsistencies and contradictions that can arise due to different versions of the same data.

 Simplification of Data Management: Normalization simplifies the process of managing data

in a database. By breaking down a complex data structure into simpler tables, normalization
makes it easier to manage the data, update it, and retrieve it.

 Improved Database Design: Normalization helps in improving the overall design of the
database. By organizing the data in a structured and systematic way, normalization makes it
easier to design and maintain the database. It also makes the database more flexible and
adaptable to changing business needs.

 Avoiding Update Anomalies: Normalization helps in avoiding update anomalies, which can
occur when updating a single record in a table affects multiple records in other tables.
Normalization ensures that each table contains only one type of data and that the
relationships between the tables are clearly defined, which helps in avoiding such anomalies.

 Standardization: Normalization helps in standardizing the data in the database. By organizing

the data into tables and defining relationships between them, normalization helps in
ensuring that the data is stored in a consistent and uniform manner.

Data Redundancy and Associated Problems

Data Redundancy means repetition of same data in more than one place in a database.
Storing same data several times in a database leads to waste of storage space. The data
becomes inconsistent due to this. User may face major problems in manipulating the data in
database. Some of the associated problems due to data redundancy results in data
anomalies.

Data Anomaly:

 An Anomaly is something that is unusual or unexpected; an abnormality

 Data Anomalies are the problems in database that can occur because of poorly planned,
un-normalized databases where all the data is stored in one table (a flat-file database)

 There are 3 types of Data Anomalies: insert, update and delete anomalies

2.1.1 Insert Anomalies:

 An insertion anomaly occurs when some data (i.e. values for certain attributes) cannot be
inserted into a database due to other missing data (i.e. missing of values for other required
attributes). This is most common for fields where a foreign key must not be NULL, but lacks
the presence of appropriate data

 Example: Suppose we want to insert data in a "user" table in which we defined "Group ID"
as a foreign key. But, still Groups are not created in the database (which means values for
Group ID are still not available). Thus, a user can not be inserted in to the database as the
Group ID must not be NULL in "user" table

2.1.2 Update Anomalies:

 An update anomaly occurs when a table has redundant data (repeated data) in it but only a
partial update of that data is done in the database. In other words, an un-normalized
database may reference the same data element in more than one location. As these
locations haven't been consolidated and referenced, we have to make sure that data item in
each location is manually updated. This can cause problems as we then need to spend time
searching for and updating each reference to the data element

 Example:

Suppose a database contains two tables named "Users" and "Mailing List".

Users table has a record for Srinivas with email id srini@[Link].

Mailing List table has a record for him with the same email id.

Now Srinivas decides to change his email id, which in turn updates the User record for
Srinivas. However, the system did not automatically update the Mailing List record, leaving
Srinivas with two different associated email ids and thus creating inconsistencies within our
database RDBMS Unit-2 Notes (for BCom) – By Ravi sir Page 4

2.1.3 Delete Anomalies:

 A deletion anomaly occurs when a legitimate deletion (i.e. intentional deletion) of some
data results in the unintentional deletion of some other required data (i.e. some data which
is required after the delete operation is unintentionally lost)

 Example: Because of the presence of data redundancy (all the details of book, book loan
etc in a single table), while deleting a "book loan" record from a library database, we may
unintentionally remove “book” record (i.e. all details of the associated book such as the
author, book title etc) from the library database

2.2 Database Dependencies/Functional Dependencies

 A dependency occurs in a database when some information stored in the database table
uniquely determines other information stored in the same table. We can also describe this as
a relationship where knowing the value of one attributes (or a set of attributes) is enough to
tell the value of another attribute (or set of attributes) in the same table.

 Saying that there is a dependency between attributes in a table is the same as saying that
there is a functional dependency between those attributes. If there is a dependency in a
database such that attribute B is dependent upon attribute A, we would write this as “A -> B”.

 Example: In a table that lists the employee attributes including EmpNo and Name, it can be
said that Name is dependent upon EmpNo (or EmpNo -> Name) because an employee's
Name can be uniquely determined from their EmpNo. However, the reverse statement
(Name -> EmpNo) is not true because more than one employee can have the same name but
different EmpNo

2.2.1 Functional Dependency

 A Functional dependency indicates the relationship between attributes. Given the value of
one attribute, we can obtain the value of other attribute(s).

 For example, if we know the value of customer account number, we can obtain customer
address, balance etc. By this, we say that customer address and balance is functionally
dependent on customer account number.

 In general terms, attribute Y (customer address and balance) is functionally dependent on

the attribute X (customer account number), if the value of X determines the value of Y.

 Functional dependency is represented by an arrow sign (→) that is, X→Y, where X
functionally determines Y.
The left-hand side attributes determine the values of attributes on the right-hand side.
Attributes on the left side are called Determinants and the values on the right-hand side are
called Dependants.

2.2.2 Partial Functional Dependency

 A Functional Dependency in which one or more non-key attributes of a table are

functionally depending on a part of the primary key (not dependent on the entire primary
key) is called partial functional dependency.

 For example, consider a relation R {A, B, C, D, E} having FD: AB → CDE where AB is the PK
(Primary Key).

Then, { A → C; A → D; A → E; B → C; B → D; B → E } all are Partial Functional Dependencies

(because they are depending on part of the primary key instead of entire primary key AB)
RDBMS Unit-2 Notes (for BCom) – By Ravi sir Page 5

2.2.3 Full Functional Dependency

 A full functional dependency occurs when we already meet the requirements for a
functional dependency and the set of attributes on the left side of the functional
dependency statement cannot be reduced any further.

 For example, “{EmpNo, Age} -> Name” is a functional dependency, but it is not a full
functional dependency because we can remove Age from the left side of the statement
without impacting the dependency relationship.

2.2.4 Transitive Functional Dependency

 A functional dependency is said to be transitive if it is indirectly formed by two functional

dependencies.

 X -> Y -> Z is a transitive dependency if the following functional dependencies hold true:

X->Y (i.e. Y is functionally dependent on X)

Y->Z (i.e. Z is functionally dependent on Y)

But, X does not -> Z (i.e. Z is not functionally dependent on X. But, Z is said to be transitively
dependent on X)

 Example:

{Book} -> {Author} (if we know the book name, we can know the author name)
{Author} -> {Author_age} (if we know the author name, we can know the author's age)

{Book} does not -> {Author_age} (if we know the book name, we cannot directly know the
author’s age. But, indirectly through transitive dependency we can know it)

Therefore as per the rule of transitive dependency: {Book} -> {Author} -> {Author_age}
should hold true. It makes sense because if we know the book name we can know the
author’s age.

2.2.5 Multi-valued Functional Dependency

 A multi-valued dependency can be described as follows: “A table involves a multi-valued

dependency if it contains multiple values for one or more of its attributes in an
entity/record”

 A multi-valued dependency between X and Y is indicated by X Y, when for each value of X
we can have more than one value of Y. In other words, X multi-determines Y

 For Example: The RollNo or Name of a person is usually single-valued information where
as qualification, phone no, email id may be multi-valued information.

2.3 Normalization RDBMS Unit-2 Notes (for BCom) – By Ravi sir Page 6

 Normalization is the step by step process of removing different kinds of data redundancy
and dependency by organizing fields and tables of a database. At each step a specific rule is
followed to remove specific kind of impurity

 It is a method to remove all data anomalies (insertion anomaly, update anomaly & deletion
anomaly) and bring the database to a consistent state

 It divides larger tables in to smaller tables and links them using relationships

 Un-Normalized Form (UNF): If a table contains multi-valued attributes in 1 or more rows, it

is said to be in UNF

Example: Consider the below table Employee

Employe EmpName PhoneNum Address

e EmpNo

1 A 1111111111 Miyapur

2222222222
 2 B 3333333333 Ameerpet

4444444444

5555555555

Types of normal forms in DBMS

In DBMS, there are several levels of normalization, commonly referred to as normal forms. Each form
addresses specific types of anomalies and redundancies. Here, we’ll touch on 1NF, 2NF, 3NF, BCNF,
4NF, and 5NF to understand how they differ in their functionalities.

First Normal Form (1NF)

First Normal Form (1NF) focuses on ensuring that the values in each column of a table are atomic,
meaning they cannot be further divided. A table is in 1NF if:

 All attributes (columns) contain only single, indivisible values

 Each record is unique and there are no repeating groups

Scenario:

Consider a table storing student details:

StudentID Name Phone Numbers

101 Robert 9876543210, 9123456780

This violates 1NF because the “Phone Numbers” column holds multiple values. To convert this into
1NF, we can split the phone numbers into two rows:

StudentID Name Phone Number

101 Robert 9876543210

101 Robert 9123456780

Why 1NF matters:

Applying 1NF removes nested or grouped data and lays the groundwork for further normalization.
It’s the first step in structuring data for consistency and clarity.

Second Normal Form (2NF)

Second Normal Form (2NF) builds upon 1NF and addresses partial dependencies. A table is in 2NF if:

 It is already in 1NF

 Every non-prime attribute (an attribute that’s not part of a candidate key) is fully functionally
dependent on the entire primary key

Scenario:
Suppose we have a table for student-course enrollment:

StudentID CourseID StudentName CourseName

101 CS101 Robert DBMS

102 CS102 Sam OS

Here, the composite primary key is (StudentID, CourseID). But “StudentName” depends only on
“StudentID” and “CourseName” depends only on “CourseID”, which indicates partial dependency.

To convert this into 2NF, we can split the table into three parts:

 Student table

 Course table

 Enrollment table

The student table will look like:

StudentID StudentName

101 Robert

102 Sam

The course table will look like:

CourseID CourseName

CS101 DBMS

CS102 OS

The enrollment table will look like:

StudentID CourseID

101 CS101

102 CS102

Why 2NF matters:

2NF ensures that data is placed in the appropriate tables and that every column is fully dependent
on the whole key, not just part of it. This eliminates data duplication and makes updates more
efficient.

Third Normal Form (3NF)

Third Normal Form (3NF) eliminates transitive dependencies. A table is in 3NF if:

 It is already in 2NF

 No non-prime attribute is dependent transitively on the primary key

In simpler terms, non-key attributes should not depend on other non-key attributes.
Scenario:

Consider a table storing employee details:

EmpID EmpName DeptID DeptName

1 John D01 HR

2 Emma D02 IT

Here, “DeptName” depends on “DeptID”, which in turn depends on “EmpID”. This is a transitive
dependency.

To convert this into 3NF, we can split the table into two parts:

 Employee table

 Department table

The employee table will look like:

EmpID EmpName DeptID

1 John D01

2 Emma D02

The department table will look like:

DeptID DeptName

D01 HR

D02 IT

Why 3NF matters:

3NF promotes data integrity and reduces redundancy by ensuring that each non-key attribute is
directly dependent on the primary key.

Boyce-Codd Normal Form (BCNF)

BCNF (Boyce-Codd Normal Form) is a higher version of 3NF. A table is in BCNF if:

 It is in 3NF

 Every functional dependency has a super key on the left-hand side

BCNF handles certain anomalies that 3NF cannot. If a table has overlapping candidate keys, it might
violate BCNF even while being in 3NF.

Scenario:

Suppose we have a table for storing course details:

Professor Course Time

Smith DBMS 10AM

Professor Course Time

Smith DBMS 2PM

Johnson OS 11AM

Here, a professor can teach multiple courses, and each course is taught by one professor. However,
some professors may teach the same course at different times.

The functional dependencies in this scenario include:

 Professor → Course (Each professor teaches only one course)

 Course → Professor (Each course is taught by one professor)

But in this table, neither “Professor” nor “Course” is a super key, so it violates BCNF.

To convert this into BCNF, we can split the table into two parts:

 Professor-course table

 Course-schedule table

The professor-course table will look like:

Professor Course

Robert DBMS

Sam OS

The course-schedule table will look like:

Course Time

DBMS 10AM

DBMS 2PM

OS 11AM

Why BCNF matters:

BCNF ensures stricter normalization by resolving complex dependencies, especially in tables with
multiple candidate keys.

Fourth Normal Form (4NF)

A table is in the Fourth Normal Form (4NF) if:

 It is in BCNF

 It contains no multi-valued dependencies

Scenario:

Consider this table:

Teacher Subject Language

Robert Math English

Robert Math Spanish

Robert Physics English

Robert Physics Spanish

In this scenario, a teacher can teach multiple subjects and speak multiple languages.

This has multi-valued dependencies:

 Teacher →→ Subject

 Teacher →→ Language

These are independent facts stored in the same table, leading to unnecessary repetition.

To convert this into 4NF, we can split the table into two:

 Teacher-subject table

 Teacher-language table

The teacher-subject table will look like:

Teacher Subject

Robert Math

Robert Physics

The teacher-language table will look like:

Teacher Language

Robert English

Robert Spanish

Why 4NF matters:

4NF separates logically independent data, avoiding data explosion and maintaining clarity.

Fifth Normal Form (5NF)

Fifth Normal Form (5NF), also known as Project-Join Normal Form (PJNF), ensures that a relation is
broken down into smaller relations that can be joined back without any loss of information or
introduction of invalid combinations, and that all join dependencies are implied by candidate keys.

Scenario:

A school tracks which students are learning which subjects from which teachers. However, each
combination (Student, Subject, Teacher) is valid only as a complete triple — it’s not enough to just
know who is learning what or who teaches what.
Student Subject Teacher

Alice Math Mr. A

Alice Science Ms. B

Bob Math Mr. A

We can’t decompose this into binary relations (e.g., Student-Subject, Student-Teacher, Subject-
Teacher) without possibly recreating invalid combinations when we rejoin the data.

To convert this into 5NF, we can create three separate projections:

1. Student-Subject

Student Subject

Alice Math

Alice Science

Bob Math

1. Student-Teacher

Student Teacher

Alice Mr. A

Alice Ms. B

Bob Mr. A

1. Subject-Teacher

Subject Teacher

Math Mr. A

Science Ms. B

Why 5NF matters:

5NF handles highly complex relationships and ensures complete reconstruction of data without
redundancy.

By understanding these normal forms, we can build databases that are logically structured, scalable,
and efficient. Applying these rules during the design phase can save time, reduce maintenance effort,
and prevent anomalies during data operations.

With a good understanding of the normal forms, let’s now discuss why normalization is important in
DBMS.