What is Database Normalization?

Database Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing large tables into smaller related tables and establishing relationships between them according to predefined rules called normal forms.

Why Database Normalization is Used?

Reduces Data Redundancy: Normalization eliminates duplicate data by storing information in only one place.

Improves Data Consistency: Since data is stored centrally, updates are reflected consistently across the database.

Enhances Data Integrity: It ensures accurate and reliable data by enforcing relationships and constraints.

Efficient Data Maintenance: Insert, update, and delete operations become easier and less error-prone.

Better Database Design: Normalization produces a well-structured and organized database.

Anomalies in Database Management System (DBMS)

A database anomaly is a problem that occurs in a database because of poor design and data redundancy.
When the same data is stored repeatedly in a table, it may create inconsistency and difficulty in data insertion, update, and deletion.
These problems are called database anomalies.

Why Anomalies Occur

Database anomalies usually occur when:

• Data is stored in an unorganized way.
• The same information is repeated in many rows.
• The table is not properly normalized.
• One table contains too much unrelated information.

Types of Database Anomalies

1. Insertion Anomaly
An insertion anomaly occurs when we cannot insert new data into the table without adding some unnecessary or unavailable data.
This happens because all information is stored in a single table and some required attributes may be missing at the time of insertion.

Example:
Suppose a table stores StudentID, StudentName, CourseID, CourseName, TeacherName.
If we want to insert a new course but no student has enrolled in it yet, we may not be able to insert the course information because StudentID and StudentName are not available.

2. Update Anomaly
An update anomaly occurs when the same data is repeated in multiple rows and we need to update all of them.
If one row is updated and another is not, the database becomes inconsistent.

Example:
If the name of a teacher is stored in many rows for different students, and the teacher’s name changes, we must update all rows.
If some rows are updated and some are not, the same teacher will appear with different names in the database.

3. Deletion Anomaly
A deletion anomaly occurs when deleting one piece of data also removes other important information unintentionally.

Example:
If a student is the only one enrolled in a course and we delete that student’s record, then the course information may also be deleted from the table.
As a result, we lose important course data even though we only wanted to remove the student record.

How to Remove Anomalies

Database anomalies can be reduced or removed by normalization.
Normalization divides a large table into smaller related tables and reduces redundancy.
As a result, insertion, update, and deletion problems are minimized.

Normal Forms in DBMS

Normalization is a process used in Database Management System (DBMS) to organize data in a database.
The main goal of normalization is to reduce data redundancy and remove database anomalies.
Normalization divides large tables into smaller related tables and establishes relationships between them.

In relational databases, normalization is performed using different stages called Normal Forms.

Main Types of Normal Forms

• First Normal Form (1NF):
  A table is in 1NF if all attributes contain atomic (indivisible) values and each field contains only a single value.
• Second Normal Form (2NF):
  A table is in 2NF if it is already in 1NF and all non-key attributes are fully dependent on the entire candidate key.
• Third Normal Form (3NF):
  A table is in 3NF if it is in 2NF and there is no transitive dependency between attributes.
• Boyce–Codd Normal Form (BCNF):
  A table is in BCNF if it is in 3NF and every functional dependency has a super key on the left-hand side.

These normal forms help design a well-structured database that is efficient, consistent, and easy to maintain.
In practice, databases are usually normalized up to 3NF or BCNF to avoid redundancy and anomalies.

Functional Dependency in DBMS
In a Relational Database Management System (RDBMS), data is stored in the form of tables.
Each table consists of rows and columns. Columns represent the attributes (characteristics of data), while each row represents a record.
Every row in a table has the same structure and a row is also called a tuple in DBMS.

Example: Employee Table

From this table we can observe that Employee_Id uniquely identifies each employee.
If we know the Employee_Id, we can determine the employee’s name, department and salary.

Definition of Functional Dependency

A Functional Dependency (FD) describes the relationship between attributes in a table.
It means that the value of one attribute determines the value of another attribute.

Functional dependency is written using the arrow symbol:

X → Y

This means attribute X determines attribute Y.

Example:
Employee_Id → Employee_Name, Employee_Department, Salary

This means if we know the Employee_Id, we can determine the employee’s name, department and salary.

Example Relation

Suppose we have a relation:

R(A, B, C, D)

Possible Functional Dependencies:

A → BCD
B → CD

Explanation:
• In A → BCD, attribute A determines attributes B, C and D.
• In B → CD, attribute B determines attributes C and D.

In Functional Dependency:
• The left side is called the Determinant.
• The right side attributes are called the Dependent Attributes.

Functional dependency is very important because it helps maintain data consistency and is the foundation of database normalization.

Types of Functional Dependency

There are several types of functional dependencies used in DBMS.

1. Trivial Functional Dependency

A functional dependency is called Trivial if the dependent attribute is a subset of the determinant attribute.

Example Table:

Example:

{Employee_Id, Name} → {Name}

Explanation:
Here Name is already part of the determinant set {Employee_Id, Name}.
So the dependency is trivial.

Other examples:
Employee_Id → Employee_Id
Name → Name

2. Non-Trivial Functional Dependency

A functional dependency is called Non-Trivial if the dependent attribute is not a subset of the determinant attribute.

Example Table:

Example:

Employee_Id → Name

Explanation:
Here Name depends on Employee_Id and Name is not part of the determinant.
Therefore this is a Non-Trivial Functional Dependency.

Another example:

{Employee_Id, Name} → Age

3. Multivalued Functional Dependency

A Multivalued Dependency occurs when one attribute determines multiple independent attributes.

Example Table:

Example:

Employee_Id → {Name, Age}

Explanation:
Here Name and Age both depend on Employee_Id, but Name does not determine Age and Age does not determine Name.
Therefore this dependency is called a Multivalued Dependency.

4. Transitive Functional Dependency

A Transitive Dependency occurs when one attribute indirectly depends on another attribute through a third attribute.

If:
A → B
B → C

Then according to the rule of transitivity:

A → C

Example Table:

Functional Dependencies:

Employee_Id → Department
Department → Street_Number

From these dependencies we can derive:

Employee_Id → Street_Number

Explanation:
Street_Number depends on Department, and Department depends on Employee_Id.
Therefore Street_Number indirectly depends on Employee_Id.
This is called a Transitive Functional Dependency.

First Normal Form (1NF) is the first step of database normalization.
A table is said to be in 1NF if every attribute contains only atomic (single) values.
This means that each column must contain only one value for each row and the table should not contain any multi-valued or composite attributes.

If a column contains multiple values in a single cell, then the table violates the 1NF rule.
To convert such a table into 1NF, we separate those multiple values into different rows so that each cell contains only one value.

Example (Table not in 1NF)

Suppose we have the following table storing employee and their phone numbers.

In this table, the column Phone_Number contains multiple values in a single cell.
Therefore, the table violates the rule of First Normal Form.

Converting the Table into 1NF

To convert the table into 1NF, we create separate rows for each phone number so that every column contains a single value.

Now every column contains a single value and each record is atomic.
Therefore, the table is now in First Normal Form (1NF).

Second Normal Form (2NF) is the second step of database normalization.
The main goal of 2NF is to remove partial dependency from a table.

A table is said to be in Second Normal Form if it satisfies the following conditions:

• The table must already be in First Normal Form (1NF).
• All non-prime attributes must be fully dependent on the entire primary key.

A partial dependency occurs when a non-key attribute depends only on a part of a composite primary key instead of the whole key.
Such dependencies create redundancy and must be removed to achieve 2NF.

Example: Table not in 2NF

Consider the following table EmployeeProjectDetail:

In this table, the primary key is a combination of:

Employee_Code + Project_ID

However we observe the following dependencies:

Employee_Code → Employee_Name
Project_ID → Project_Name

This means:

• Employee_Name depends only on Employee_Code
• Project_Name depends only on Project_ID

Since these attributes depend on only part of the composite key, this creates partial dependency.
Therefore the table is not in Second Normal Form.

Converting the Table into 2NF

To remove partial dependencies, we divide the table into smaller related tables.

EmployeeDetail Table

ProjectDetail Table

EmployeeProject Table

Now:

• Each table is in 1NF
• All non-key attributes depend on the whole primary key

Therefore the database is now in Second Normal Form (2NF).

Third Normal Form (3NF)

Third Normal Form (3NF) is the third step of database normalization.
The main goal of 3NF is to remove transitive dependency from a table.

A transitive dependency occurs when a non-key attribute depends on another non-key attribute instead of depending directly on the primary key.

A functional dependency X → Z is called transitive if the following conditions exist:

• X → Y
• Y → Z
• Y does not determine X

This means attribute Z depends on X indirectly through Y.

Rules for Third Normal Form (3NF)

For a table to be in Third Normal Form, it must satisfy the following conditions:

• The table must already be in Second Normal Form (2NF).
• No non-prime attribute should depend transitively on the primary key.
• For every functional dependency X → Z, at least one of the following must hold:
  • X is a super key.
  • Z is a prime attribute.

If transitive dependency exists, we divide the table into smaller tables so that each non-key attribute depends directly on the primary key.

Example: Table not in 3NF

Consider the following table EmployeeDetail:

In this table we observe the following functional dependencies:

Employee_Code → Employee_Zipcode
Employee_Zipcode → Employee_City

From these two dependencies we get:

Employee_Code → Employee_City

Here Employee_City depends on Employee_Zipcode, not directly on Employee_Code.
Therefore this is a Transitive Dependency.

Since a non-prime attribute (Employee_City) depends on another non-prime attribute, the table is not in Third Normal Form.

Converting the Table into 3NF

To remove the transitive dependency, we divide the table into two tables.

EmployeeDetail Table

EmployeeLocation Table

Now:

• Both tables are in Second Normal Form (2NF).
• No transitive dependency exists.
• Each non-key attribute depends directly on the primary key.

Therefore the database is now in Third Normal Form (3NF).

Boyce–Codd Normal Form (BCNF) is an advanced version of Third Normal Form (3NF).
It provides stricter rules than 3NF to eliminate certain types of redundancy and anomalies that may still remain in 3NF tables.

Rules for BCNF

For a relational table to be in Boyce–Codd Normal Form, it must satisfy the following conditions:

• The table must already be in Third Normal Form (3NF).
• For every non-trivial functional dependency X → Y, the attribute X must be a super key.

A super key is a set of one or more attributes that can uniquely identify each record in a table.

If a dependency exists where the determinant is not a super key, the table violates BCNF and must be decomposed into smaller tables.

Example: Table not in BCNF

Consider the following table EmployeeProjectLead:

In this table the candidate key is:

{Employee_Code, Project_ID}

However we observe the following functional dependency:

Project_Leader → Project_ID

Here:

• Project_ID is a prime attribute
• Project_Leader is not a super key

Since the determinant (Project_Leader) is not a super key, the table violates the rule of BCNF.

Converting the Table into BCNF

To remove this violation, we decompose the table into two separate tables.

EmployeeProject Table

ProjectLead Table

Now:

• Each functional dependency has a super key as determinant.
• No BCNF rule is violated.

Therefore, the database is now in Boyce–Codd Normal Form (BCNF).

Relational Integrity Constraints

In the Relational Model, certain rules are applied to maintain the accuracy, consistency, and reliability of data. These rules are called Relational Integrity Constraints.

These constraints are checked whenever an Insert, Delete, or Update operation is performed to ensure that the data remains valid.

Main Types of Relational Constraints:

1. Domain Constraint:
Ensures that each attribute contains values only from a predefined domain (range or set).
Example: An Age attribute should accept only numeric values within a valid range (e.g., 0–150).

2. Key Constraint:
Ensures that every relation has a Primary Key that uniquely identifies each tuple (row).
A Primary Key cannot be NULL and cannot contain duplicate values.

3. Referential Integrity Constraint:
Maintains consistency between related tables.
If a table contains a Foreign Key referencing another table, the referenced key must exist.
This prevents invalid or orphan records.

In conclusion, Relational Integrity Constraints ensure that the database remains accurate, consistent, and reliable.