Decomposition in Function Dependency

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.

Leave a Reply

Your email address will not be published. Required fields are marked *