Decomposition in DBMS

The process of decomposition in DBMS helps us remove redundancy, inconsistencies and anomalies from a database when we divide the table into numerous tables. In simpler words, the process of decomposition refers to dividing a relation X into {X1, X2,……Xn}. Decomposition is dependency preserving as well as lossless.

In this article, we will take a look at the Decomposition in DBMS according to the GATE Syllabus for CSE (Computer Science Engineering). Read ahead to learn more.

Table of Contents

• What is Decomposition in DBMS?
• Types of Decomposition
  • Lossless Decomposition
  • Lossy Decomposition
• Properties of Decomposition
  • Decomposition Must be Lossless
  • Dependency Preservation
  • Lack of Data Redundancy
• Practice Questions on Decomposition in DBMS
• FAQs

What is Decomposition in DBMS?

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.

For example,

Let us take ‘A’ as the Relational Schema, having an instance of ‘a’. Consider that it is decomposed into: A1, A2, A3, . . . . An; with instance: a1, a2, a3, . . .. an, If a1 ⋈ a2 ⋈ a3 . . . . ⋈ an, then it is known as ‘Lossless Join Decomposition’. Read more about Lossless Decomposition in DBMS here.

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. Read more about Lossy Decomposition in DBMS here.

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:

Answer: The result will be:

Thus, the relation mentioned above had lossless decomposition, which means there was no loss of data/information here.

2. Check whether this decomposition given is a lossy join decomposition.

Relational Schema = A (X, Y, Z)

Decompositions,

A1 (X, Y)

A2 (X, Z)

Relational Schema

Decompositions

Answer: Lossy Decomposition

Solution: Now, if we want this decomposition to be lossy, then

A ⊂ A1 ⨝ A2

Thus, A1 ⨝ A2 will be equal to

Here, since A ⊂ A1 ⨝ A2,

Thus, this is a lossy join decomposition.

3. Apply Lossless Join decomposition on the below two tables.

Student Table:

Detail Table:

Answer: If we join both of these relations, then the resultant relation would look like the table given below:

Student_Detail Table

Keep learning and stay tuned to get the latest updates on GATE Exam along with GATE Eligibility Criteria, GATE 2023, GATE Admit Card, GATE Syllabus for CSE (Computer Science Engineering), GATE CSE Notes, GATE CSE Question Paper, and more.

Also Explore,

• Introduction to DBMS
• Join Dependency in DBMS
• Lossless Decomposition in DBMS
• Lossy Decomposition in DBMS
• Multivalued Dependency in DBMS