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

 In the relational model, there exist standard methods to quantify how efficient a databases is. These
methods are called normal forms and there are algorithms to covert 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.

Why do we need Normalization?

The primary objective for normalizing the relations is to eliminate the below anomalies. Failure to reduce
anomalies results in data redundancy, which may threaten data integrity and cause additional issues as the
database increases. Normalization consists of a set of procedures that assist you in developing an effective
database structure.

 Insertion Anomalies: Insertion anomalies occur when it is not possible to insert data into a database
because the required fields are missing or because the data is incomplete. For example, if a database
requires that every record has a primary key, but no value is provided for a particular record, it cannot
be inserted into the database.

 Deletion anomalies: Deletion anomalies occur when deleting a record from a database and can result
in the unintentional loss of data. For example, if a database contains information about customers and
orders, deleting a customer record may also delete all the orders associated with that customer.

 Updation anomalies: Updation anomalies occur when modifying data in a database and can result in
inconsistencies or errors. For example, if a database contains information about employees and their
salaries, updating an employee’s salary in one record but not in all related records could lead to
incorrect calculations and reporting.
Before Normalization: The table is prone to redundancy and anomalies (insertion, update, and deletion).
After Normalization: The data is divided into logical tables to ensure consistency, avoid redundancy and
remove anomalies making the database efficient and reliable.

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.

Normal Forms in DBMS

Normal Forms Description of Normal Forms

First Normal
A relation is in first normal form if every attribute in that relation is single-valued attribute.
Form (1NF)

Second A relation that is in First Normal Form and every non-primary-key attribute is fully
Normal Form functionally dependent on the primary key, then the relation is in Second Normal Form
(2NF) (2NF).

A relation is in the third normal form, if there is no transitive dependency for non-prime
attributes as well as it is in the second normal form. A relation is in 3NF if at least one of
the following conditions holds in every non-trivial function dependency X –> Y.

 X is a super key.
Third Normal
Form (3NF)  Y is a prime attribute (each element of Y is part of some candidate key).

For BCNF the relation should satisfy the below conditions

 The relation should be in the 3rd Normal Form.

Boyce-Codd
Normal Form  X should be a super-key for every functional dependency (FD) X−>Y in a given
(BCNF) relation.

A relation R is in 4NF if and only if the following conditions are satisfied:

 It should be in the Boyce-Codd Normal Form (BCNF).

Fourth Normal
Form (4NF)  The table should not have any Multi-valued Dependency.

A relation R is in 5NF if and only if it satisfies the following conditions:

 R should be already in 4NF.

Fifth Normal
Form (5NF)  It cannot be further non loss decomposed (join dependency).
Database Normalization With Real-World Examples

We have already highlighted all the data normalization levels. Let’s further explore each of them in
more depth with examples and explanations.

First Normal Form (1NF) Normalization

1NF ensures that each column cell contains only atomic values. Imagine a library database with a
table storing book information (title, author, genre, and borrowed_by). If the table is not normalized,
borrowed_by could contain a list of borrower names separated by commas. This violates 1NF, as a
single cell holds multiple values. The table below is a good representation of a table that violates 1NF,
as described earlier.

title author genre borrowed_by

John Doe, Jane Doe, James

To Kill a Mockingbird Harper Lee Fiction
Brown,

J. R. R.
The Lord of the Rings Fantasy Emily Garcia, David Lee
Tolkien

Harry Potter and the Sorcerer’s

J.K. Rowling Fantasy Michael Chen
Stone

The solution?

In 1NF, we create a separate table for borrowers and link them to the book table. These tables can
either be linked using the foreign key in the borrower table or a separate linking table. The foreign
key in the borrowers table approach involves adding a foreign key column to the borrowers table that
references the primary key of the books table. This will enforce a relationship between the tables,
ensuring data consistency.

You can find a representation of this below:

Books table

book_id (PK) title author genre

1 To Kill a Mockingbird Harper Lee Fiction

2 The Lord of the Rings J. R. R. Tolkien Fantasy

3 Harry Potter and the Sorcerer’s Stone J.K. Rowling Fantasy

Borrowers table

borrower_id (PK) name book_id (FK)

 1 John Doe 1

2 Jane Doe 1

3 James Brown 1

4 Emily Garcia 2

5 David Lee 2

6 Michael Chen 3

Second Normal Form (2NF)

This level of normalization, as already described, builds upon 1NF by ensuring there are no partial
dependencies on the primary key. In simpler terms, all non-key attributes must depend on the entire
primary key and not just part of it.

From the 1NF that was implemented, we already have two separate tables (you can check the 1NF
section).

Now, let’s say we want to link these tables to record borrowings. The initial approach might be to
simply add a borrower_id column to the books table, as shown below:

book_id borrower_id
title author genre
(PK) (FK)

1 To Kill a Mockingbird Harper Lee Fiction 1

J. R. R.
2 The Lord of the Rings Fantasy NULL
Tolkien

Harry Potter and the Sorcerer’s

3 J.K. Rowling Fantasy 6
Stone

This might look like a solution, but it violates 2NF simply because the borrower_id only partially
depends on the book_id. A book can have multiple borrowers, but a single borrower_id can only be
linked to one book in this structure. This creates a partial dependency.

The solution?

We need to achieve the many-to-many relationship between books and borrowers to achieve 2NF.
This can be done by introducing a separate table:

Book_borrowings table
 borrowing_id (PK) book_id (FK) borrower_id (FK) borrowed_date

1 1 1 2024-05-04

2 2 4 2024-05-04

3 3 6 2024-05-04

This table establishes a clear relationship between books and borrowers. The book_id and borrower_id
act as foreign keys, referencing the primary keys in their respective tables. This approach ensures that
borrower_id depends on the entire primary key (book_id) of the books table, complying with 2NF.

Third Normal Form (3NF)

3NF builds on 2NF by eliminating transitive dependencies. A transitive dependency occurs when a
non-key attribute depends on another non-key attribute, which in turn depends on the primary key. It
basically takes its meaning from the transitive law.

From the 2NF we already implemented, there are three tables in our library database:

Books table

book_id (PK) title author genre

1 To Kill a Mockingbird Harper Lee Fiction

2 The Lord of the Rings J. R. R. Tolkien Fantasy

3 Harry Potter and the Sorcerer’s Stone J.K. Rowling Fantasy

Borrowers table

borrower_id (PK) name book_id (FK)

1 John Doe 1

2 Jane Doe 1

3 James Brown 1

4 Emily Garcia 2

5 David Lee 2
 6 Michael Chen 3

Book_borrowings table

borrowing_id (PK) book_id (FK) borrower_id (FK) borrowed_date

1 1 1 2024-05-04

2 2 4 2024-05-04

3 3 6 2024-05-04

The 2NF structure looks efficient, but there might be a hidden dependency. Imagine we add a
due_date column to the books table. This might seem logical at first sight, but it’s going to create a
transitive dependency where:

 The due_date column depends on the borrowing_id (a non-key attribute) from the
book_borrowings table.
 The borrowing_id in turn depends on book_id (the primary key) of the books table.
The implication of this is that due_date relies on an intermediate non-key attribute (borrowing_id)
instead of directly depending on the primary key (book_id). This violates 3NF.

The solution?

We can move the due_date column to the most appropriate table by updating the book_borrowings
table to include the due_date and returned_date columns.

Below is the updated table:

borrowing_id (PK) book_id (FK) borrower_id (FK) borrowed_date due_date

1 1 1 2024-05-04 2024-05-20

2 2 4 2024-05-04 2024-05-18

3 3 6 2024-05-04 2024-05-10

By placing the due_date column in the book_borrowing table, we have successfully eliminated the
transitive dependency.

What this means is that due_date now directly depends on the combined relationship between
book_id and borrower_id. In this context, book_id and borrower_id are acting as a composite foreign
key, which together form the primary key of the book_borrowings table.

Boyce-Codd Normal Form (BCNF)

BCNF is based on functional dependencies that consider all candidate keys in a relationship.
Functional dependencies (FD) define relationships between attributes within a relational database. An
FD states that the value of one column determines the value of another related column. FDs are very
important because they guide the process of normalization by identifying dependencies and ensuring
data is appropriately distributed across tables.

BCNF is a stricter version of 3NF. It ensures that every determinant (a set of attributes that uniquely
identify a row) in a table is a candidate key (a minimal set of attributes that uniquely identify a row).
The whole essence of this is that all determinants should be able to serve as primary keys.

It ensures that every functional dependency (FD) has a superkey as its determinant. In other words,
if X —> Y (X determines Y) holds, X must be a candidate key (superkey) of the relation. Please note
that X and Y are columns in a data table.

As a build-up from the 3NF, we have three tables:

Books table

book_id (PK) title author genre

1 To Kill a Mockingbird Harper Lee Fiction

2 The Lord of the Rings J. R. R. Tolkien Fantasy

3 Harry Potter and the Sorcerer’s Stone J.K. Rowling Fantasy

Borrowers table

borrower_id (PK) name book_id (FK)

1 John Doe 1

2 Jane Doe 1

3 James Brown 1

4 Emily Garcia 2

5 David Lee 2

6 Michael Chen 3

Book_borrowings table

borrowing_id (PK) book_id (FK) borrower_id (FK) borrowed_date due_date

1 1 1 2024-05-04 2024-05-20
 2 2 4 2024-05-04 2024-05-18

3 3 6 2024-05-04 2024-05-10

While the 3NF structure is good, there might be a hidden determinant in the book_borrowings table.
Assuming one borrower cannot borrow the same book twice simultaneously, the combination of
book_id and borrower_id together uniquely identifies a borrowing record.

This structure violates BCNF since the combined set (book_id and borrower_id) is not the primary key
of the table (which is just borrowing_id).

The solution?

To achieve BCNF, we can either decompose the book_borrowings table into two separate tables or
make the combined attribute set the primary key.

1. Approach 1 (decompose the table): In this approach, we will be decomposing the

book_borrowings table into separate tables:


 A table with borrowing_id as the primary key, borrowed_date, due_date, and

returned_date.
 Another separate table to link books and borrowers, with book_id as a foreign
key, borrower_id as a foreign key, and potentially additional attributes specific
to the borrowing event.
2. Approach 2 (make the combined attribute set the primary key): We can consider
making book_id and borrower_id a composite primary key for uniquely identifying
borrowing records. The problem with this approach is that it won’t serve its purpose
if a borrower can borrow the same book multiple times.
In the end, your choice between these options depends on your specific data needs and how you
want to model borrowing relationships.

Fourth Normal Form (4NF)

4NF deals with multi-valued dependencies. A multi-valued dependency exists when one attribute can
have multiple dependent attributes, and these dependent attributes are independent of the primary
key. It’s quite complex, but we will be exploring it deeper using an example.

The library example we’ve been using throughout these explanations is not applicable at this
normalization level. 4NF typically applies to situations where a single attribute might have multiple
dependent attributes that don’t directly relate to the primary key.

Let’s use another scenario. Imagine a database that stores information about publications. We will be
considering a “Publications” table with columns, title, author, publication_year, and keywords.

publication_id
title author publication_year keywords
(PK)
 To Kill a Harper Coming-of-Age,
1 1960
Mockingbird Lee Legal

The Lord of the J. R. R. Fantasy, Epic,

2 1954
Rings Tolkien Adventure

Pride and Jane Romance, Social

3 1813
Prejudice Austen Commentary

The table structure above is violating 4NF because:

 The keywords column has a multi-valued dependency on the primary key

publication_id. What this means is that a publication can have multiple keywords,
and these keywords are independent of the publication’s unique identifier.
The solution?

We can create a separate table.

Publication_keywords table

publication_id (FK) keyword

1 Coming-of-Age

1 Legal

2 Fantasy

2 Epic

2 Adventure

3 Romance

3 Social Commentary

The newly created table (Publication_keywords) establishes a many-to-many relationship between

publication and keywords. Each publication can have multiple keywords linked through the
publication_id, which is a foreign key, and each keyword can be associated with multiple publications.

With this, we have successfully eliminated the multi-valued dependency and achieved 4NF.

Fifth Normal Form (5NF)

5NF is the most complex form of normalization that eliminates join dependencies. This is a situation
where data needs to be joined from multiple tables to answer a specific query, even when those
tables are already in 4NF.
In simpler terms, 5NF ensures that no additional information can be derived by joining the tables
together that wasn’t already available in the separate tables.

Join dependencies are less likely to occur when tables are already normalized (in 3NF or 4NF), hence
the difficulty in creating a clear and straightforward example for 5NF.

However, let’s take a look at this scenario where 5NF might be relevant:

Imagine a university database with normalized tables for “Courses” and “Enrollments.”

Courses table

course_id (PK) course_name department

101 Introduction to Programming Computer Science

202 Data Structures and Algorithms Computer Science

301 Web Development I Computer Science

401 Artificial Intelligence Computer Science

Enrollments table

enrollment_id (PK) student_id (FK) course_id (FK) grade

1 12345 101 A

2 12345 202 B

3 56789 301 A-

4 56789 401 B+

Assuming these tables are already in 3NF or 4NF, a join dependency might exist depending on how
data is stored. For instance, a course has a prerequisite requirement stored within the “Courses” table
as the “prerequisite_course_id” column.

This might seem efficient at first glance. However, consider a query that needs to retrieve a student’s
enrolled courses and their respective prerequisites. In this scenario, you would need to join the
“Courses” and “Enrollments” tables, then potentially join the “Courses” table to retrieve prerequisite
information.

The Solution?

To potentially eliminate the join dependency and achieve 5NF, we could introduce a separate “Course
Prerequisites” table:

Course_prerequisite table
 course_id (FK) prerequisite_course_id (FK)

202 101

301 NULL

401 202

This approach separates prerequisite information and allows efficient retrieval of enrolled courses and
their prerequisites in a single join between the “Enrollments” and “Course_prerequisites” tables.

Note: We are assuming a student can only have one prerequisite per course.

5NF is a very complex and rare type of normalization, so as someone just starting their learning
journey in data, you might not find an application. However, it’s going to be added knowledge and
will make you prepared when you stumble on complex databases.

ER Model

The Entity-Relationship Model (ER Model) is a conceptual model for designing a database.
This model represents the logical structure of a database, including entities, their attributes
and relationships between them.
 Entity: An object that is stored as data, such as Student, Course or Company.
 Attribute: Properties that describe an entity, such as StudentID, CourseName,
or EmployeeEmail.
 Relationship: A connection between entities, such as "a Student enrols in
a Course".

Components of ER Diagram

The graphical representation of this model is called an Entity-Relation Diagram (ERD).

ER Model in Database Design Process
We typically follow the below steps for designing a database for an application.
 Gather the requirements (functional and data) by asking questions to the
database users.
  Create a logical or conceptual design of the database. This is where ER model
plays a role. It is the most used graphical representation of the conceptual
design of a database.
 After this, focus on Physical Database Design (like indexing) and external design
(like views)
Use ER Diagrams In DBMS
 ER diagrams represent the E-R model in a database, making them easy to
convert into relations (tables).
 These diagrams serve the purpose of real-world modeling of objects which
makes them intently useful.
 Unlike technical schemas, ER diagrams require no technical knowledge of the
underlying DBMS used.
 They visually model data and its relationships, making complex systems easier
to understand.
Symbols Used in ER Model
ER Model is used to model the logical view of the system from a data perspective which
consists of these symbols:

Symbols used in ER Diagram

Entity
An Entity represents a real-world object, concept or thing about which data is stored in a
database. It act as a building block of a database. Tables in relational database represent
these entities.
Example of entities:
 Real-World Objects: Person, Car, Employee etc.
 Concepts: Course, Event, Reservation etc.
 Things: Product, Document, Device etc.
The entity type defines the structure of an entity, while individual instances of that type
represent specific entities.
Entity Set
An entity refers to an individual object of an entity type, and the collection of all entities
of a particular type is called an entity set. For example, E1 is an entity that belongs to the
entity type "Student," and the group of all students forms the entity set.
In the ER diagram below, the entity type is represented as:

Entity Set

We can represent the entity sets in an ER Diagram but we can't represent individual entities
because an entity is like a row in a table, and an ER diagram shows the structure and
relationships of data, not specific data entries (like rows and columns). An ER diagram is a
visual representation of the data model, not the actual data itself.
Types of Entity
There are two main types of entities:
1. Strong Entity
A Strong Entity is a type of entity that has a key Attribute that can uniquely identify each
instance of the entity. A Strong Entity does not depend on any other Entity in the Schema
for its identification. It has a primary key that ensures its uniqueness and is represented by
a rectangle in an ER diagram.
2. Weak Entity
A Weak Entity cannot be uniquely identified by its own attributes alone. It depends on a
strong entity to be identified. A weak entity is associated with an identifying entity (strong
entity), which helps in its identification. A weak entity are represented by a double
rectangle. The participation of weak entity types is always total. The relationship between
the weak entity type and its identifying strong entity type is called identifying relationship
and it is represented by a double diamond.
Example:
A company may store the information of dependents (Parents, Children, Spouse) of an
Employee. But the dependents can't exist without the employee. So dependent will be a
Weak Entity Type and Employee will be identifying entity type for dependent, which means
it is Strong Entity Type.
Strong Entity and Weak Entity

Attributes in ER Model
Attributes are the properties that define the entity type. For example, for a Student entity
Roll_No, Name, DOB, Age, Address, and Mobile_No are the attributes that define entity
type Student. In ER diagram, the attribute is represented by an oval.

Attribute

Types of Attributes
1. Key Attribute
The attribute which uniquely identifies each entity in the entity set is called the key
attribute. For example, Roll_No will be unique for each student. In ER diagram, the key
attribute is represented by an oval with an underline.

Key Attribute

2. Composite Attribute
An attribute composed of many other attributes is called a composite attribute. For
example, the Address attribute of the student Entity type consists of Street, City, State, and
Country. In ER diagram, the composite attribute is represented by an oval comprising of
ovals.
Composite Attribute

3. Multivalued Attribute
An attribute consisting of more than one value for a given entity. For example, Phone_No
(can be more than one for a given student). In ER diagram, a multivalued attribute is
represented by a double oval.

Multivalued Attribute

4. Derived Attribute
An attribute that can be derived from other attributes of the entity type is known as a
derived attribute. e.g.; Age (can be derived from DOB). In ER diagram, the derived attribute
is represented by a dashed oval.

Derived Attribute

The Complete Entity Type Student with its Attributes can be represented as:
 Entity
and Attributes

Relationship Type and Relationship Set

A Relationship Type represents the association between entity types. For example,
‘Enrolled in’ is a relationship type that exists between entity type Student and Course. In
ER diagram, the relationship type is represented by a diamond and connecting the entities
with lines.

Entity-Relationship Set

A set of relationships of the same type is known as a relationship set. The following
relationship set depicts S1 as enrolled in C2, S2 as enrolled in C1, and S3 as registered in
C3.
Relationship Set

Degree of a Relationship Set

The number of different entity sets participating in a relationship set is called the degree
of a relationship set.
1. Unary/Recursive Relationship: When there is only ONE entity set participating in a
relation, the relationship is called a unary relationship. For example, one person is married
to only one person.

Unary Relationship

2. Binary Relationship: When there are TWO entities set participating in a relationship,
the relationship is called a binary relationship. For example, a Student is enrolled in a
Course.

Binary Relationship

3. Ternary Relationship: When there are three entity sets participating in a relationship,
the relationship is called a ternary relationship.

Ternary Relationship
4. N-ary Relationship: When there are n entities set participating in a relationship, the
relationship is called an n-ary relationship.

N-ary Relationship

Cardinality in ER Model
The maximum number of times an entity of an entity set participates in a relationship set
is known as cardinality.
Cardinality can be of different types:
1. One-to-One
When each entity in each entity set can take part only once in the relationship, the
cardinality is one-to-one. Let us assume that one person can be issued only one
passport, and one passport is issued to only one person. So, the relationship will be One-
to-One (1 : 1), meaning that each person has a single passport, and each passport
belongs to a single person.

one to one cardinality

Using Sets, it can be represented as:

Set Representation of One-to-One

2. One-to-Many
In a one-to-many relationship, one entity can be associated with multiple entities. For
example, a single Surgeon Department can have many Doctors. Therefore, the cardinality
of this relationship is 1 to M, meaning one department can have many doctors.

one to many cardinality

Using sets, one-to-many cardinality can be represented as:

Set Representation of One-to-Many

3. Many-to-One
When entities in one entity set can take part only once in the relationship set and entities
in other entity sets can take part more than once in the relationship set, cardinality is many
to one.
Let us assume that multiple surgeries can be performed by one surgeon, but one surgery
is performed by only one surgeon. So, the cardinality will be M to 1, meaning that many
surgeries can be done by a single surgeon, but each surgery is done by only one
surgeon.

many to one cardinality

Using Sets, it can be represented as:

Set Representation of Many-to-One

In this case, each student is taking only 1 course but 1 course has been taken by many
students.
4. Many-to-Many
When entities in all entity sets can take part more than once in the relationship
cardinality is many to many. Let us assume that an employee can work on multiple
projects and each project can have multiple employees working on it. So, the relationship
will be many-to-many (M:N), meaning that one employee may be associated with several
projects, and one project may involve several employees.

many to many cardinality

Using Sets, it can be represented as:

Many-to-Many Set Representation

In this example, student S1 is enrolled in C1 and C3 and Course C3 is enrolled by S1, S3,
and S4. So it is many-to-many relationships.
[Link]

[Link]

[Link]