Relational Decomposition

• When a relation in the relational model is not in appropriate normal form then the decomposition of a relation is required.
• In a database, it breaks the table into multiple tables.
• If the relation has no proper decomposition, then it may lead to problems like loss of information.
• Decomposition is used to eliminate some of the problems of bad design like anomalies, inconsistencies, and redundancy.

Types of Decomposition

Lossless Decomposition

• If the information is not lost from the relation that is decomposed, then the decomposition will be lossless.
• The lossless decomposition guarantees that the join of relations will result in the same relation as it was decomposed.
• The relation is said to be lossless decomposition if natural joins of all the decomposition give the original relation.

Example:

EMPLOYEE_DEPARTMENT table:

The above relation is decomposed into two relations EMPLOYEE and DEPARTMENT

EMPLOYEE table:

DEPARTMENT table

Now, when these two relations are joined on the common column "EMP_ID", then the resultant relation will look like:

Employee ⋈ Department

Hence, the decomposition is Lossless join decomposition.

Dependency Preserving

• It is an important constraint of the database.
• In the dependency preservation, at least one decomposed table must satisfy every dependency.
• If a relation R is decomposed into relation R1 and R2, then the dependencies of R either must be a part of R1 or R2 or must be derivable from the combination of functional dependencies of R1 and R2.
• For example, suppose there is a relation R (A, B, C, D) with functional dependency set (A->BC). The relational R is decomposed into R1(ABC) and R2(AD) which is dependency preserving because FD A->BC is a part of relation R1(ABC).