Relational Decomposition in DBMS

In DBMS (database management system), decompositions mean breaking down a relation into multiple smaller and simpler relations. Decomposition of a Relation occurs when a relation in a database is not in an appropriate normal form.

What is Decomposition in DBMS?

Decomposition in Database Management System (DBMS) refers to the process of breaking down a relation schema into smaller, more manageable parts called decomposition schemas. This is often done to minimize redundancy, improve data integrity, and facilitate efficient query processing. Decomposition can involve splitting a relation into multiple relations, each representing a subset of attributes or a specific functional dependency. By decomposing a relation, complex data structures can be organized into simpler forms, enhancing the overall structure and performance of the database system.

Types of Decomposition

Relational decomposition is categorized into two types. They are

1. Lossless Join Decomposition
2. Dependency Preserving Decomposition

Let us discuss these decomposition types in detail.

1. Lossless Join Decomposition

During decomposition of a relation, if the information does not lose from the relation, then the decomposition will be lossless.

Lossless Join decomposition guarantees that when the decomposed relations are joined back together, then it will result in the same original relation before decomposition, which shows that no information is lost from the original relation during decomposition. Hence, it is known as Lossless decomposition.

Example of lossless decomposition

Let us take an example to understand lossless decomposition clearly.

The following Employee_Department table represents the data of the employee of a company.

When the above relation is decomposed, the decomposed two relations will be

Employee and Department.

The Employee table is as follows.

The Department table is as follows.

Since, in Lossless Decomposition, if we join the decomposed relations (using natural join), it will give the original relation before decomposition.

Now when the above two relations Employee and Department are joined on the common column "Employee_ID", the resultant relation will look like:

The above is the same as the relation before decomposition. Hence, the decomposition is Lossless Decomposition.

2. Dependency Preserving Relational Decomposition

In the dependency preserving relational decomposition, if any relation R is decomposed into multiple sub-relations, let's say R1 and R2, then the dependency of R either must be a part of relation R1 or R2 or it must be derivable from the combination of the functional dependencies of relation R1 and R2.

In dependency-preserving relational decomposition, at least one decomposed table must satisfy every dependency.

Example of dependency preserving relational decomposition

Let us take an example to understand this decomposition.

Suppose there is a relation R{A,B,C,D}. Let's assume its functional dependency is set to A→BC. Now, decomposing this relation into two sub-relation results :

Relation R1{A,B,C} and relation R2{A,D} .

Now, if we observe them carefully, then we'll be able to see that the functional dependency A →BC is being preserved in the decomposed sub-relation R1. Thus, this decomposition is dependency-preserving.

Must Recommended Topic, Schema in DBMS