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?

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:

 

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.

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

X Y Z
X1 Y1 Z1
X2 Y1 Z1
X1 Y2 Z2
X1 Y3 Z3

Decompositions

X Y
X1 Y1
X2 Y1
X1 Y2
X1 Y3
X Z
X1 Z1
X2 Z1
X1 Z2
X1 Z3

Answer: Lossy Decomposition

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

A ⊂ A1 ⨝ A2

Thus, A1 ⨝ A2 will be equal to

X Y Z
X1 Y1 Z1
X1 Y1 Z2
X2 Y1 Z1
X1 Y2 Z2
X1 Y2 Z1
X1 Y3 Z3
X1 Y3 Z1

Here, since A ⊂ A1 ⨝ A2,

Thus, this is a lossy join decomposition.

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

Student Table:

Name Class
Nikita 8th
Aditya 9th
Ayush 10th

Detail Table:

Name Roll Number
Nikita 21
Aditya 22
Ayush 23

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

Student_Detail Table

Name Class Roll Number
Nikita 8th 21
Aditya 9th 22
Ayush 10th 23

FAQs

What are the advantages of decomposition?

Decomposition is a process that saves a lot of time. For instance, the code that is there for a complex program could be easily run to multiple lines of code. In case we make a mistake, it would take a very prolonged time to discover. Another advantage of decomposition is that it lets programmers copy useful chunks of code and then reuse them easily for various other programs.

Why do we use 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.

What is lossy decomposition in DBMS?

In the case of lossy decomposition, whenever we decompose a relation into multiple relational schemas, then the loss of data/information occurs whenever we try to retrieve the original relation.

What is lossless decomposition in DBMS?

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.

What is the difference between lossy and 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. In the case of lossy decomposition, whenever we decompose a relation into multiple relational schemas, then the loss of data/information occurs whenever we try to retrieve the original relation.

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

Also Explore,

Leave a Comment

Your Mobile number and Email id will not be published.

*

*