- DBMS Tutorial
- DBMS - Home
- DBMS - Overview
- DBMS - Architecture
- DBMS - Data Models
- DBMS - Data Schemas
- DBMS - Data Independence

- Entity Relationship Model
- DBMS - ER Model Basic Concepts
- DBMS - ER Diagram Representation
- DBMS - Generalization, Aggregation

- Relational Model
- DBMS - Codd's Rules
- DBMS - Relational Data Model
- DBMS - Relational Algebra
- DBMS - ER to Relational Model
- DBMS- SQL Overview

- Relational Database Design
- DBMS - Database Normalization
- DBMS - Database Joins

- Storage and File Structure
- DBMS - Storage System
- DBMS - File Structure

- Indexing and Hashing
- DBMS - Indexing
- DBMS - Hashing

- Transaction And Concurrency
- DBMS - Transaction
- DBMS - Concurrency Control
- DBMS - Deadlock

- Backup and Recovery
- DBMS - Data Backup
- DBMS - Data Recovery

- DBMS Useful Resources
- DBMS - Quick Guide
- DBMS - Useful Resources
- DBMS - Discussion

# What is Decomposition in DBMS?

Decomposition means dividing a relation R into {R1, R2,......Rn}. It is dependency preserving and lossless.

## Dependency preserving decomposition

Let R is decomposed into {R1, R2,....,Rn} with projected FD set {F1,F2,......Fn}. This decomposition is dependency preserving if F^{+} ={F1 U F2 U.........Fn}^{+}.

## Example

Let the relation R{A,B,C,D,E} F:{AB->C, C->D, AB->D} R is decomposed to R1(A,B,C), R2(D,E). Prove decomposition is dependency preserving.

## Solution

F1={AB->C}

F2={C->D}

=> (F1 u F2) = {AB->C, C->D}

AB+ under (F1 U F2) = {A,B,C,D} => AB->D is under (F1 U F2)

F+ = (F1 U F2)^{+}

=> Decomposition is dependency preserving.

## Decomposition is not preserving

Now consider another example where decomposition is not preserved.

Let the relation R{A,B,C,D,E,F,G,H,I,J} where F: {AB->C, A->DE, B->F, F->GH. D->IJ}

R is decomposed to R1(A,B,C,D), R2(D,E), R3(B,F), R4(F,G,H) AND R5(D,I,J). Check decomposition is dependency preserving or not.

## Solution

F1={AB->C}

F2={}

F3={B->F}

F4={F->GH}

F5={D->IJ}

=> (F1 U F2 U F3 U F4 U F5) = {AB->C, B->F, F->GH, D->IJ}

A+ under (F1 U F2 U F3 U F4 U F5) = {AB->C, B->F, F->GH, D->IJ}

=>A->DE is not under (F1 U F2 UF3 U F4 U F5)

=>F+ ≠ (F1 U F2 U F3 U F4 U F5)^{+}

=> Decomposition is not dependency preserving.

## Lossless-join decomposition

It is a process in which a relation is decomposed into two or more relations. This property guarantees that the extra or less tuple generation problem does not occur, and no information is lost from the original relation during the decomposition. It is also known as non-additive join decomposition.

When the sub relations combine again then the new relation must be the same as the original relation was before decomposition.

Consider a relation R if we decomposed it into sub-parts relation R1 and relation R2.

The decomposition is lossless when it satisfies the following statement −

If we union the sub–Relation R1 and R2 then it must contain all the attributes that are available in the original relation R before decomposition.

Intersections of R1 and R2 cannot be Null. The sub relation must contain a common attribute. The common attribute must contain unique data.

The common attribute must be a super key of sub relations either R1 or R2.

Here,

R = (A, B, C)

R1 = (A, B)

R2 = (B, C)

The relation R has three attributes A, B, and C. The relation R is decomposed into two relation R1 and R2. . R1 and R2 both have 2-2 attributes. The common attributes are B.

The Value in Column B must be unique. if it contains a duplicate value then the Lossless-join decomposition is not possible.

Draw a table of Relation R with Raw Data −

**R (A, B, C)**

A | B | C |
---|---|---|

12 | 25 | 34 |

10 | 36 | 09 |

12 | 42 | 30 |

It decomposes into the two sub relations as follows −

**R1 (A, B)**

A | B |
---|---|

12 | 25 |

10 | 36 |

12 | 42 |

**R _{2} (B, C)**

B | C |
---|---|

25 | 34 |

36 | 09 |

42 | 30 |

We can now check the first condition for Lossless-join decomposition.

The union of sub relation R1 and R2 is the same as relation R.

**R _{1}U R_{2} = R**

We get the following result −

A | B | C |
---|---|---|

12 | 25 | 34 |

10 | 36 | 09 |

12 | 42 | 30 |

The relation is the same as the original relation R hence, the above decomposition is Lossless-join decomposition.

- Related Articles
- What is lossless join decomposition in DBMS?
- Lossless and Lossy Decomposition in DBMS
- What is Biasâ€“Variance Decomposition?
- What is 4NF in DBMS?
- What is RAID in DBMS?
- What is decomposition reaction? Explain with example.
- What is functional dependency in DBMS?
- What is Transitive dependency in DBMS?
- What is an instance in DBMS?
- What is Hierarchical model in DBMS?
- What is Data Independence in DBMS?
- What is multivalued dependency in DBMS?
- What is heuristic optimization in DBMS?
- What is concurrency control in DBMS?
- What is shadow paging in DBMS?