Relational Decomposition

PRASHANT SINGH
Last Updated: May 13, 2022

Introduction  

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 is categorized into two types. They are

  • LOSSLESS DECOMPOSITION
  • DEPENDENCY PRESERVING DECOMPOSITION


Let us discuss these decomposition types in detail.

1. Lossless Decomposition

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

Lossless 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 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} .Lets assume its functional dependency 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.

Frequently Asked Questions

  1. What does dependency mean in DBMS(Data Base Management System)?
    Dependency in DBMS is a relation between two or more attributes, in other words, a dependency is a constraint that applies to or defines the relationship between attributes.
    In this, knowing a value of one attribute (or set of attributes) is enough to determine the value of another attribute(or set of attributes) in the same table.
    An attribute is a characteristic, and it describes the instances in the column of a database.
     
  2. What is a functional dependency?
    In the database, if the information(attribute or set of attributes) stored in a table can be uniquely determined by some other information(attribute or set of attributes) in the same table, then it is known as functional dependency.
     
  3. What is a relation in DBMS(Data Base Management System)?
    The relation is used to refer to a table in a relational database. Still, a relation is more commonly used to describe the relationship that can be created between those tables in a database.

Key Takeaways

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.

Also, try CodeStudio to practice programming problems for your complete interview preparation. Don't stop here, Ninja; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

Was this article helpful ?
1 upvote

Comments

No comments yet

Be the first to share what you think