Question

In the relational model of database, what does decomposition of a relation scheme means and why such decomposition is desirable? Give example(s) to support your discussion.

Answer 1

`Hey,

Note: Brother in case of any queries, just comment in box I would be very happy to assist all your queries

A functional decomposition is the process of breaking down the functions of an organization into progressively greater (finer and finer) levels of detail.
In decomposition, one function is described in greater detail by a set of other supporting functions.
The decomposition of a relation scheme R consists of replacing the relation schema by two or more relation schemas that each contain a subset of the attributes of R and together include all attributes in R.
Decomposition helps in eliminating some of the problems of bad design such as redundancy, inconsistencies and anomalies.
There are two types of decomposition :

1. Lossy Decomposition
2. Lossless Join Decomposition

Lossy Decomposition :

"The decomposition of relation R into R1 and R2 is lossy when the join of R1 and R2 does not yield the same relation as in R."
One of the disadvantages of decomposition into two or more relational schemes (or tables) is that some information is lost during retrieval of original relation or table.

In lossy decomposition ,spurious tuples are generated when a natural join is applied to the relations in the decomposition.

Lossless Join Decomposition :

"The decomposition of relation R into R1 and R2 is lossless when the join of R1 and R2 yield the same relation as in R."
A relational table is decomposed (or factored) into two or more smaller tables, in such a way that the designer can capture the precise content of the original table by joining the decomposed parts. This is called lossless-join (or non-additive join) decomposition.

This is also refferd as non-additive decomposition.

The lossless-join decomposition is always defined with respect to a specific set F of dependencies.

In lossless decomposition, no any spurious tuples are generated when a natural joined is applied to the relations in the decomposition.

Kindly revert for any queries

Thanks.