Data normalization in a DBMS is the process of organizing data to reduce

redundancy and improve data integrity by breaking large tables into smaller, linked
tables. This process involves applying a set of rules, called normal forms, to
eliminate issues like data inconsistency and to make the database more efficient and
easier to maintain.
Why normalize data?
 Reduces data redundancy:

Eliminates the repetition of the same data in multiple places, which saves
storage space.

 Ensures data consistency:

Prevents the problems that arise from having inconsistent data, especially
during insert, update, and delete operations.

 Improves data integrity:

Creates a more logical and reliable database structure.

 Increases efficiency:
Simplifies data management and improves the performance of database
operations.
How it works
1. Splitting tables:

Large tables are divided into smaller tables that are logically related to each
other.
2. Relating tables:

Relationships are established between the new tables, often using foreign
keys, to link the data back together when needed.
3. Applying normal forms:
A series of normal forms (like 1NF, 2NF, and 3NF) are used to systematically
apply rules and ensure the data meets specific criteria for organization and
structure.

ANOMALIES IN REALATIONAL DATABASE DESIGN:

Anomalies in relational databases are inconsistencies or issues that arise during
data manipulation (insertion, deletion, or updating) due to poor database design,
often characterized by excessive data redundancy and poorly structured
tables. These anomalies can compromise data integrity and lead to unreliable
information.
There are three main types of anomalies:
 Insertion Anomaly:

This occurs when one cannot insert a new record into a table without also
inserting data for another, unrelated entity. For example, if a student table
includes course information, one might not be able to add a new student
without assigning them to a course, even if they haven't enrolled yet.
  Deletion Anomaly:

This occurs when deleting a record inadvertently removes other, related

information that is not intended to be deleted. For example, if deleting the last
student enrolled in a particular course also removes all information about that
course, even if the course is still offered.

 Update Anomaly:
This occurs when updating a piece of information requires updating it in
multiple places within the database, leading to potential inconsistencies if all
instances are not updated. For example, if a student's mobile number is
stored in multiple records within a table and is updated in some but not all, it
creates conflicting information.
These anomalies are typically addressed through normalization, a process of
organizing the columns and tables of a relational database to minimize data
redundancy and improve data integrity. Normalization involves decomposing tables
into smaller, more manageable sub-tables and establishing relationships between
them, ensuring that each piece of information is stored only once and that
dependencies are handled correctly.

DECOMPOSITION:
The term decomposition refers to the process in which we break down a
table in a database into various elements or parts. Thus, decomposition
replaces a given relation with a collection of various smaller relations.
Thus, in a database, we can make any table break down into multiple
tables when we want to collect a particular set of data.

Decomposition must always be lossless. This way, we can rest assured

that the data/information that was there in the original relation can be
reconstructed accurately on the basis of the decomposed relations. In
case the relation is not decomposed properly, then it may eventually lead
to problems such as information loss.

Types of Decomposition
Decomposition is of two major types in DBMS:

 Lossless
 Lossy

1. Lossless Decomposition
A decomposition is said to be lossless when it is feasible to reconstruct the
original relation R using joins from the decomposed tables. It is the most
preferred choice. This way, the information will not be lost from the
relation when we decompose it. A lossless join would eventually result in
the original relation that is very similar.
  Lossless Join Decomposition:

This is the primary goal of decomposition. It ensures that when the new tables
are joined back together, the result is identical to the original table. This
requires that the decomposed tables can be perfectly reconstructed and no
data is lost.

2. Lossy Decomposition
Just like the name suggests, whenever we decompose a relation into
multiple relational schemas, then the loss of data/information is
unavoidable whenever we try to retrieve the original relation.

 This occurs when a decomposition results in the loss of information. It's

impossible to perfectly recreate the original table from the smaller ones
because some data is lost during the process.

Properties of Decomposition
Decomposition must have the following properties:

1. Decomposition Must be Lossless

2. Dependency Preservation

3. Lack of Data Redundancy

1. Decomposition Must be Lossless

Decomposition must always be lossless, which means the information
must never get lost from a decomposed relation. This way, we get a
guarantee that when joining the relations, the join would eventually lead
to the same relation in the result as it was actually decomposed.

2. Dependency Preservation
Dependency is a crucial constraint on a database, and a minimum of one
decomposed table must satisfy every dependency. If {P → Q} holds, then
two sets happen to be dependent functionally. Thus, it becomes more
useful when checking the dependency if both of these are set in the very
same relation. This property of decomposition can be done only when we
maintain the functional dependency. Added to this, this property allows us
to check various updates without having to compute the database
structure’s natural join.

3. Lack of Data Redundancy

It is also commonly termed as a repetition of data/information. According
to this property, decomposition must not suffer from data redundancy.
When decomposition is careless, it may cause issues with the overall data
in the database. When we perform normalization, we can easily achieve
the property of lack of data redundancy.

Practice Questions on Decomposition in

DBMS
1. Apply Natural Join decomposition on the below two tables:

Cust_ID Cust_Name Cust_Age Cust_Location

C001 Monica 22 Texas

C002 Rachel 33 Toronto

C003 Phoebe 44 Minnesota

Sec_ID Cust_ID Sec_Name

Sec1 S001 Accounts

Sec2 S002 Marketing

Sec3 S003 Telecom

Answer: The result will be:

Cust_ID Cust_Name Cust_Age Cust_Location Sec_ID Sec_Name

S001 Monica 22 Texas Sec1 Accounts

S002 Rachel 33 Toronto Sec2 Marketing

 S003 Phoebe 44 Minnesota Sec3 Telecom

Thus, the relation mentioned above had lossless decomposition, which

means there was no loss of data/information here.

Functional dependencies:

A functional dependency occurs when one attribute uniquely

determines another attribute within a relation. It is a constraint
that describes how attributes in a table relate to each other. If
attribute A functionally determines attribute B we write this as
the A→B.
A functional dependency in a DBMS is a relationship between two sets of attributes in a
table, where the first set (the determinant) uniquely determines the value of the second set
(the dependent). It is represented as
X → Y , where knowing the value of X allows you to know the value of Y

Types of Functional Dependencies in DBMS

1. Trivial functional dependency
2. Non-Trivial functional dependency
3. Multivalued functional dependency
4. Transitive functional dependency
1. Trivial Functional Dependency
In Trivial Functional Dependency, a dependent is always a subset
of the determinant. i.e. If X → Y and Y is the subset of X, then it
is called trivial functional dependency.

Symbolically: A→B is trivial functional dependency if B is a

subset of A.
The following dependencies are also trivial: A→A & B→B
Example 1 :
 ABC -> AB
 ABC -> A
 ABC -> ABC
Example 2:
roll_n nam ag
o e e

42 abc 17

43 pqr 18

44 xyz 18

Here, {roll_no, name} → name is a trivial functional dependency,

since the dependent name is a subset of determinant set
{roll_no, name}. Similarly, roll_no → roll_no is also an example of
trivial functional dependency.
2. Non-trivial Functional Dependency
In Non-trivial functional dependency, the dependent is
strictly not a subset of the determinant. i.e. If X → Y and Y is
not a subset of X, then it is called Non-trivial functional
dependency.
Example 1 :
 Id -> Name
 Name -> DOB
Example 2:
roll_n nam ag
o e e

42 abc 17

43 pqr 18

44 xyz 18

Here, roll_no → name is a non-trivial functional dependency,

since the dependent name is not a subset of determinant roll_no.
Similarly, {roll_no, name} → age is also a non-trivial functional
dependency, since age is not a subset of {roll_no, name}
3. Semi Non Trivial Functional Dependencies
A semi non-trivial functional dependency occurs when part of the
dependent attribute (right-hand side) is included in the
determinant (left-hand side), but not all of it. This is a middle
ground between trivial and non-trivial functional dependencies. X
-> Y is called semi non-trivial when X intersect Y is not NULL.
Example:

Consider the following table:

Student_ Course_I Course_Nam
ID D e

Computer
101 CSE101
Science

102 CSE102 Data Structures

Computer
103 CSE101
Science
Functional Dependency:
{StudentID,CourseID}→CourseID
This is semi non-trivial because:
 Part of the dependent attribute ( Course_ID) is already
included in the determinant ( {Student_ID, Course_ID}).
 However, the dependency is not completely trivial
because {StudentID}→CourseID is not implied directly.
4. Multivalued Functional Dependency
In Multivalued functional dependency, entities of the dependent
set are not dependent on each other. i.e. If a → {b, c} and there
exists no functional dependency between b and c, then it is
called a multivalued functional dependency.
Example:
bike_mod manuf_ye colo
el ar r

Blac
2007
tu1001 k

tu1001 2007 Red

Blac
2008
tu2012 k

tu2012 2008 Red

Blac
2009
tu2222 k

tu2222 2009 Red

In this table:
 X: bike_model
 Y: color
 Z: manuf_year
For each bike model ( bike_model):
1. There is a group of colors ( color) and a group of
manufacturing years ( manuf_year).
2. The colors do not depend on the manufacturing year, and
the manufacturing year does not depend on the colors.
They are independent.
3. The sets of color and manuf_year are linked only
to bike_model.
That’s what makes it a multivalued dependency.
In this case these two columns are said to be multivalued
dependent on bike_model. These dependencies can be
represented like this:
Read more about Multivalued Dependency in DBMS .
5. Transitive Functional Dependency
In transitive functional dependency, dependent is indirectly
dependent on determinant. i.e. If a → b & b → c, then according
to axiom of transitivity, a → c. This is a transitive functional
dependency.
Example:
 enrol_ nam dep building_
no e t no

42 abc CO 4

43 pqr EC 2

44 xyz IT 1

45 abc EC 2

Here, enrol_no → dept and dept → building_no. Hence, according

to the axiom of transitivity, enrol_no → building_no is a valid
functional dependency. This is an indirect functional
dependency, hence called Transitive functional dependency.
6. Fully Functional Dependency
In full functional dependency an attribute or a set of attributes
uniquely determines another attribute or set of attributes. If a
relation R has attributes X, Y, Z with the dependencies X->Y and
X->Z which states that those dependencies are fully functional.
Read more about Fully Functional Dependency.
7. Partial Functional Dependency
In partial functional dependency a non key attribute depends on
a part of the composite key, rather than the whole key. If a
relation R has attributes X, Y, Z where X and Y are the composite
key and Z is non key attribute. Then X->Z is a partial functional
dependency in RBDMS