Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Last Updated: Jul 15, 2024
Difficulty: Easy

Relational Decomposition in DBMS

Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

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.

Relational Decomposition in DBMS

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.

Employee_ID

Employee_Name

Employee_Age

Employee_City

Department_ID

Department_Name

5

Naman

25

Banglore

125

Testing

10

Aadil

26

Pune

114

Production

12

Shefali

29

Delhi

119

Development

21

Yash

31

Lucknow

155

Sales

22

Vivek

24

Hyderabad

109

Finance

26

Neha

26

Noida

111

HR

31

Rishi

28

Surat

142

Strategy

35

Ajay

34

Gurgaon

134

Operations

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

Employee and Department.

The Employee table is as follows.

Employee_IDEmployee_NameEmployee_AgeEmployee_City

5

Naman

25

Banglore

10

Aadil

26

Pune

12

Shefali

29

Delhi

21

Yash

31

Lucknow

22

Vivek

24

Hyderabad

26

Neha

26

Noida

31

Rishi

28

Surat

35

Ajay

34

Gurgaon

The Department table is as follows.

Department_ID

Employee_ID

Department_Name

125

5

Testing

114

10

Production

119

12

Development

155

21

Sales

109

22

Finance

111

26

HR

142

31

Strategy

134

35

Operations

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:

Employee_ID

Employee_Name

Employee_Age

Employee_City

Department_ID

Department_Name

5

Naman

25

Banglore

125

Testing

10

Aadil

26

Pune

114

Production

12

Shefali

29

Delhi

119

Development

21

Yash

31

Lucknow

155

Sales

22

Vivek

24

Hyderabad

109

Finance

26

Neha

26

Noida

111

HR

31

Rishi

28

Surat

142

Strategy

35

Ajay

34

Gurgaon

134

Operations

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

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Properties of Decomposition

  • Lossless Decomposition: A decomposition is considered lossless if it preserves all the information contained in the original relation. This means that after decomposition, it is possible to reconstruct the original relation using a natural join operation.
  • Dependency Preservation: Decomposition should preserve all functional dependencies present in the original relation. This ensures that the integrity constraints of the database are maintained.
  • Minimal Number of Decompositions: Ideally, decomposition should result in the minimum number of smaller relations necessary to avoid redundancy and maintain dependency preservation.
  • Join Dependency Preservation: Decomposition should preserve join dependencies, which are relationships between attributes in different relations that require a join operation to retrieve certain information.
  • No Additional Dependencies: Decomposition should not introduce any new functional or join dependencies that were not present in the original relation.
  • Concurrent Access: Decomposition should allow for concurrent access to the database without causing anomalies like lost updates, uncommitted data, or inconsistent retrievals.

Lossless vs Lossy Decomposition

AspectLossless DecompositionLossy Decomposition
Information PreservationPreserves all information from the original relation, allowing for reconstruction without loss.May result in loss of some information, making reconstruction of the original relation impossible.
Dependency PreservationMaintains all functional and join dependencies present in the original relation.May not preserve all dependencies, potentially leading to integrity constraint violations.
ComplexityTypically more complex and may require additional computational overhead.Generally simpler and more straightforward to implement.
Space EfficiencyCan result in smaller relations due to minimal redundancy.May result in larger relations with redundant data, impacting space efficiency.
Query PerformanceMay lead to better query performance due to minimized redundancy and optimized join operations.May suffer from slower query performance due to redundant data and increased join operations.
Anomaly AvoidanceLess prone to anomalies like insertion, deletion, and update anomalies.More susceptible to anomalies, requiring additional measures to ensure data consistency.

Frequently Asked Questions

What is the condition in Dependency Preservation?

Dependency Preservation in decomposition ensures that all functional and join dependencies present in the original relation are preserved in the resulting decomposed relations. This ensures data integrity and adherence to integrity constraints.

Why do we use decomposition in DBMS?

Decomposition in DBMS is used to minimize redundancy, improve data integrity, and facilitate efficient query processing. It helps in organizing complex data structures into simpler forms, enhancing the overall structure and performance of the database system.

What do you mean by lossy decomposition?

Lossy decomposition refers to a decomposition process where some information from the original relation may be lost. This can occur when decomposing a relation into smaller parts without preserving all dependencies, potentially leading to data loss and inconsistency.

What are the advantages of decomposition?

The advantages of decomposition include minimizing redundancy, improving data integrity, facilitating efficient query processing, and enhancing database organization. It helps in optimizing storage space, reducing data anomalies, and promoting better maintenance and scalability of the database system.

Conclusion

In this blog, we start by introducing the decomposition of a relational in a database. Then we learn about its two types: Lossless relational decomposition and dependency preserving relational decomposition. We learn about these types in detail with their suitable examples.

You can visit here to learn more about different topics related to Database management systems.

Recommended Reading - Canonical Cover In DBMS.

Also, try Code360 to practice programming problems for your complete interview preparation. Check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

Topics covered
1.
What is Decomposition in DBMS?
2.
Types of Decomposition
2.1.
1. Lossless Join Decomposition
2.1.1.
Example of lossless decomposition
2.2.
2. Dependency Preserving Relational Decomposition
2.2.1.
Example of dependency preserving relational decomposition
3.
Properties of Decomposition
4.
Lossless vs Lossy Decomposition
5.
Frequently Asked Questions
5.1.
What is the condition in Dependency Preservation?
5.2.
Why do we use decomposition in DBMS?
5.3.
What do you mean by lossy decomposition?
5.4.
What are the advantages of decomposition?
6.
Conclusion