BE - Database Management Systems

In Database Management Systems (DBMS), decomposition is the process of breaking a relation (table) into two or more smaller relations.

We do this to:

• Remove redundancy

• Eliminate anomalies (insertion, deletion, update)

• Achieve a desirable normal form (like BCNF, 3NF).

However, while decomposing a relation, it is important to ensure that certain properties are maintained, one of which is dependency preservation.

A decomposition is said to be dependency preserving if all the functional dependencies of the original relation can still be enforced or derived from the dependencies of the decomposed relations, without the need to perform a join.

For example,

consider a relation R(StudentID, CourseID, Instructor) with functional dependencies StudentID → Instructor and CourseID → Instructor.

If we decompose this relation into R1(StudentID, Instructor) and R2(CourseID, Instructor), then both dependencies are preserved in the decomposed relations, making it a dependency preserving decomposition.

On the other hand, consider a relation R(A, B, C) with functional dependencies A → B and B → C.

If we decompose it into R1(A, B) and R2(B, C), then while A → B and B → C are preserved, the dependency A → C can only be derived by joining R1 and R2, which means the decomposition is not dependency preserving. Thus, dependency preserving decomposition ensures that integrity constraints can be checked easily on individual relations without requiring costly joins.