Properties of Relational Decomposition

Properties of Relational Decomposition

When a relation is not appropriate normal form in a relational model, then the decomposition of a relation is required.

In a database, breaking down the table into multiple tables termed as decomposition.

The properties of a relational decomposition are listed below :

            1. Attribute Preservation

            2. Dependency Preservation

            3. Lossless Join

            4. Non Additive Join Property

            5. No redundancy

1. Attribute Preservation:

Using functional dependencies, the universal relation schema R is decomposed into a set of relation schemas D = { R1, R2, ….. Rn } relational database schema, where ‘D’ is called the Decomposition of R.

The attributes in R will appear in at least one relation schema Ri, i=1, 2…..n, in the decomposition. It means that no attribute in relation R is lost after decomposition. This is called the Attribute Preservation condition of decomposition.

Dependency Preservation:

If each functional dependency X->Y specified in F, set of functional dependencies,  appears directly in one of the relation schemas Ri in the decomposition D or could be inferred from the dependencies that appear in some Ri. Then the decomposition is the Dependency Preservation.

If decomposition is not dependency preserving some dependency is lost in decomposition. To check this condition, take the JOIN of 2 or more relations in the decomposition.

For example:

R = (A, B, C)

F = {A → B, B → C}

Key = {A}

R is not in BCNF.

Decomposition R1 = (A, B), R2 = (B, C)

In above example each Functional Dependency specified in F either appears directly in one of the relations in the decomposition. R1 and R2 are in BCNF, Lossless-join decomposition, Dependency preserving.

3. Lossless Join:

Lossless join is a feature of decomposition supported by normalization. It is the ability to ensure that any instance of the original relation can be identified from corresponding instances in the smaller relations.

For example:

If R is a relation, and  F is set of functional dependencies on R. And R is decomposed into {R1, R2, …, Rn}. This decomposition is called a lossless decomposition for R if the natural join of R1, R2, …, Rn produces exactly the relation R.

A decomposition is lossless join if R(A, B, C) is decompose into two relations R1(A, B), R2(A, C) and recover the relation R’(A, B, C) using natural join of R1 and R2.

4. Non Additive Join Property:

The Non Additive Join Property, ensures that no illegitimate tuples are generated when a NATURAL JOIN operation is applied to the relations resulting from the decomposition.

  

5. No redundancy:

Decomposition is used to eliminate some of the problems of bad design like anomalies, inconsistencies, and redundancy. If the relation has no proper decomposition, then it may lead to problems like loss of information.