DBMS Menu

Decompositions and its problems in DBMS



Decomposition in the context of database design refers to the process of breaking down a single table into multiple tables in order to eliminate redundancy, reduce data anomalies, and achieve normalization. Decomposition is typically done using rules defined by normalization forms.

However, while decomposition can be helpful, it is not without challenges. Done incorrectly, decomposition can lead to its own set of problems.

Problems Related to Decomposition

1. Loss of Information

  • Non-loss decomposition: When a relation is decomposed into two or more smaller relations, and the original relation can be perfectly reconstructed by taking the natural join of the decomposed relations, then it is termed as lossless decomposition. If not, it is termed "lossy decomposition."
  • Example: Let's consider a table `R(A, B, C)` with a dependency `A → B`. If you decompose it into `R1(A, B)` and `R2(B, C)`, it would be lossy because you can't recreate the original table using natural joins.

Example: Consider a relation R(A,B,C) with the following data:


| A  | B  | C  |
|----|----|----|
| 1  | X  | P  |
| 1  | Y  | P  |
| 2  | Z  | Q  |

Suppose we decompose R into R1(A,B) and R2(A,C).

R1(A, B): 

| A  | B  |
|----|----|
| 1  | X  |
| 1  | Y  |
| 2  | Z  |
R2(A, C): 

| A  | C  |
|----|----|
| 1  | P  |
| 1  | P  |
| 2  | Q  |

Now, if we take the natural join of R1 and R2 on attribute A, we get back the original relation R. Therefore, this is a lossless decomposition.

2. Loss of Functional Dependency

  • Once tables are decomposed, certain functional dependencies might not be preserved, which can lead to the inability to enforce specific integrity constraints.
  • Example: If you have the functional dependency `A → B` in the original table, but in the decomposed tables, there is no table with both `A` and `B`, this functional dependency can't be preserved.

Example: Let's consider a relation R with attributes A,B, and C and the following functional dependencies:

A → B
B → C

Now, suppose we decompose R into two relations:

R1(A,B) with FD A → B
R2(B,C) with FD B → C

In this case, the decomposition is dependency-preserving because all the functional dependencies of the original relation R can be found in the decomposed relations R1 and R2. We do not need to join R1 and R2 to enforce or check any of the functional dependencies.

However, if we had a functional dependency in R, say A → C, which cannot be determined from either R1 or R2 without joining them, then the decomposition would not be dependency-preserving for that specific FD.

3. Increased Complexity

  • Decomposition leads to an increase in the number of tables, which can complicate queries and maintenance tasks. While tools and ORM (Object-Relational Mapping) libraries can mitigate this to some extent, it still adds complexity.

4. Redundancy

  • Incorrect decomposition might not eliminate redundancy, and in some cases, can even introduce new redundancies.

5. Performance Overhead

  • An increased number of tables, while aiding normalization, can also lead to more complex SQL queries involving multiple joins, which can introduce performance overheads.

Best Practices

  1. Ensure decomposition is non-lossy. After decomposition, it should be possible to recreate the original data using natural joins.
  2. Preserve functional dependencies to enforce integrity constraints.
  3. Strike a balance. While normalization and decomposition are essential, in some scenarios (like reporting databases), a certain level of denormalization might be preferred for performance reasons.
  4. Regularly review and optimize the database design, especially as the application's requirements evolve.

In essence, while decomposition is a powerful tool in achieving database normalization and reducing anomalies, it must be done thoughtfully and judiciously to avoid introducing new problems.

Next Topic :Functional Dependencies and its reasoning in DBMS