Relational Decomposition

In a database, decomposition refers to the breaking down of a relation into multiple smaller relations. Relational Decomposition is done when a relation in the database is not in an appropriate normal form.

Need of Relational Decomposition

Decomposition of relations is done for the following reasons:

• Eliminate problems of bad database design.
• Eliminating anomalies arising due to insert, update and delete operations.
• Minimizing of redundancy of data.
• Avoiding problems like loss of information.

NOTE: If the composition of the relation is not done properly then it may lead to a lossy decomposition leading to loss of useful information.

Decomposition Types

Relational Decomposition is mainly of two types. These are:

1. Lossless Decomposition.
2. Dependency Preserving Decomposition.

Now let us discuss both of these in detail one at a time.

Lossless Decomposition

As the name suggests, Lossless Decomposition guarantees us that when the decomposed relations are joined back (using natural join) then it will result in the same original relation. And hence, it guarantees that no information was lost when the original relation was being decomposed.

Let us understand this with the help of an example.

Example

Let us assume a relation EMP. This relation is used to store information of employees that is their name, id, salary, contact number, name of department in which they work, and id of department.

Relation Name:EMP

Now, let us decompose this relation into two sub relations R1 – Employees and R2 – Departments. Here, relation R1 will store information of employees and R2 will store information relating to departments.

Relation R1: EMPLOYEES

Relation R2: DEPARTMENTS

Now, in order to check for Lossless Decomposition of the relation, let us apply Natural Join on the relations R1 and R2.

Employees⋈Departments

We get the following resultant relation.

When we observe closely, it will be the same relation R which we had before the relational decomposition. Hence, our decomposition was a Lossless Join Decomposition.

NOTE: If in the relation R2 we had only two columns i.e., DEPT_ID and DEPT_NAME, then the decomposition would have been a lossy one. This is because of the fact that the column EMP_ID has acted as the column which has helped maintain the consistency of the data in these relations.

Dependency Preserving Relational Decomposition

This decomposition states that, if any relation R is decomposed into sub relations Ri, then for any functional dependency X ->Y defined in the relation R must be inferred in any of the sub relations Ri in the decomposition of the relation R.

That is, X -> Y must be defined in D = {R1, R2, R3, ……, Ri}.

Where, R1, R2, R3, ……, Ri are the sub relations and D is the decomposition of the relation R.

Let us take up an example.

Example

Let us assume a relation R {A, B, C, D, E}, where A, B, C, D and E are its attributes. Let us assume that the relation has the functional dependency as A -> BC. Now, let us decompose this relation into two sub relations as follows: R1 {A, B, C} and R2 {A, D, E}.

If we observe carefully, then we will be able to see that the functional dependency A -> BC is being preserved in the sub relation R1. Hence, this is termed as dependency preservation.

Anupreet Kalsi