Relational Decomposition

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.

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 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.