## Decomposition:

- Decomposition is the process of breaking down in parts or elements.
- It replaces a relation with a collection of smaller relations.
- It breaks the table into multiple tables in a database.

Properties of Decomosition:

- Lossy decomposition
- Lossless decomposition
- Dependency Preservation

## Lossy Decomposition

As the name suggests, when a relation is decomposed into two or more relational schemas, the loss of information is unavoidable when the original relation is retrieved.

Let us see an example:

**<EmpInfo> **

Emp_ID |
Emp_Name |
Emp_Age |
Emp_Location |
Dept_ID |
Dept_Name |

E1 | Tom | 23 | Surat | 1 | HR |

E2 | Harry | 24 | Ahmedabad | 2 | Finance |

Decompose the above table into two tables:

**<EmpDetails> **

Emp_ID |
Emp_Name |
Emp_Age |
Emp_Location |

E1 | Tom | 23 | Surat |

E2 | Harry | 24 | Ahmedabad |

**<DeptDetails> **

Dept_ID |
Dept_Name |

1 | HR |

2 | Finance |

Now, you won’t be able to join the above tables, since **Emp_ID **isn’t part of the **DeptDetails ** relation.

Therefore, the above relation has lossy decomposition

## Lossless Decomposition

As the name suggests, when a relation is decomposed into two or more relational schemas, there is** no loss** of information when the original relation is retrieved.

Let us see an example:

<EmpInfo>

Emp_ID |
Emp_Name |
Emp_Age |
Emp_Location |
Dept_ID |
Dept_Name |

E1 | Tom | 23 | Surat | 1 | HR |

E2 | Harry | 24 | Ahmedabad | 2 | Finance |

Decompose the above table into two tables:

**<EmpDetails> **

Emp_ID |
Emp_Name |
Emp_Age |
Emp_Location |

E1 | Tom | 23 | Surat |

E2 | Harry | 24 | Ahmedabad |

<DeptDetails>

Emp_ID |
Dept_id |
Dept_Name |

E1 | Tom | HR |

E2 | Harry | Finance |

Now, Natural Join is applied on the above two tables:

The result will be:

Emp_ID |
Emp_Name |
Emp_Age |
Emp_Location |
Dept_ID |
Dept_Name |

E1 | Tom | 23 | Surat | 1 | HR |

E2 | Harry | 24 | Ahmedabad | 2 | Finance |

Therefore, the above relation had lossless decomposition i.e. no loss of information.

### Dependency-Preserving Decomposition

• When any relation is decomposed, **illegal **relations should not be created. A relation is an **illegal **relation, if it does not preserve given functional dependencies.

• A relation **R **is decomposed into the relation schema **R1, R2, … , Rn **with the functional dependencies F1,F2, .. ,Fn . Let F’ = F1 U F2 U… U Fn .

• This decomposition is dependency preserving decomposition, if closure of **F’ **is identical to **F + **, i.e **F’ + **=**F + **.

Here, closure is considered rather than simple set of FDs. Because even if **F’ ? F **. It may be that, **F’ + **= **F + **.

**Example **

• Let a relation R (A,B,C,D)and a set of FDs **F = { A->B,A->C,C->D} **are given.

• A relation is decomposed into –

R1 = (A, B, C) with FDs

F1 = {A->B, A->C}.R2 = (C, D) with FDs

F2 = {C->D}.F’ = F1 U F2 = {A->B, A->C, C->D}

So,

F’= F.And so,

F’ + = F +.

• Thus, the decomposition is dependency preserving decomposition.