Lossless Decomposition in DBMS

In DBMS, a lossless decomposition is a process of decomposing a relation schema into multiple relations in such a way that it preserves the information contained in the original relation. In a lossless decomposition, the natural join of the decomposed relations must always produce exactly the original relation with no extra or missing tuples.

How to check for Lossless Decomposition?

For lossless decomposition, we have to ensure three major things. Suppose, any relation R (A,B,C,D) is decomposed into relations R1 (A,B,C) and R2 (C,D) then check for :

• Union of attributes set of R1 & R2 should must be equal to attribute set of R, i.e., R1 ∪ R2 = R.
• Intersection of attributes set of R1 & R2 should not be empty set, i.e., R1 ∩ R2 \neq \phi
• Attribute closure of intersection of attributes set of R1 & R2 is either superset of R1 or R2, i.e., (R1 ∩ R2)⁺ ⊇ R1 or (R1 ∩ R2)⁺ ⊇ R2

Note: Not all decompositions into 1NF, 2NF, 3NF, or BCNF are guaranteed to be lossless. You must verify using dependency preservation and join conditions.

Example of Lossless Decomposition

Consider the relation:

Employee (Employee_Id, Ename, Salary, Department_Id, Dname)

We can decompose it using lossless decomposition into:

• R1: Employee_desc (Employee_Id, Ename, Salary, Department_Id) 
• R2: Department_desc (Department_Id, Dname).

Here:

• Common attribute: Dept_Id
• Closure of Dept_Id (with proper FDs) should cover either R1 or R2 to ensure lossless join:

(R1 ∩ R2 -> R1) or (R1 ∩ R2 -> R2)

Armstrong’s Axioms and Lossless Decomposition

Armstrong’s Axioms are a set of inference rules used to derive all possible functional dependencies (FDs) from a given set. These FDs help in analyzing whether a decomposition is lossless. However, Armstrong's Axioms do not directly guarantee a lossless decomposition- they are used to derive closures and test dependency-related conditions.

The three core axioms are:

• Reflexivity: If Y ⊆ X, then X → Y
• Augmentation: If X → Y, then XZ → YZ for any Z
• Transitivity: If X → Y and Y → Z, then X → Z

Using these rules, you can determine the closure of attribute sets, which helps verify if (R1 ∩ R2)⁺ covers R1 or R2.

Note: Algorithms for performing lossless decomposition in DBMS are:

• BCNF (Boyce-Codd Normal Form) decomposition
• 3NF (Third Normal Form) decomposition.

Advantages of Lossless Decomposition

1. Reduced Data Redundancy: Helps remove repetitive data and optimize storage.
2. Maintenance and Updates: Smaller tables are easier to update and manage.
3. Improved Data Integrity: Ensures that only relevant attributes stay together.
4. Improved Flexibility: Easier to modify individual relations without affecting others.

Disadvantages of Lossless Decomposition

1. Increased Complexity: More joins might be needed to fetch original data.
2. Increased Processing Overhead: Joins can increase computational load.
3. Join Operations: Designing and validating decompositions requires effort.
4. Costly: Normalization and decomposition may be resource-intensive in large databases.

Question Asked in GATE

Q.1: Let R (A, B, C, D) be a relational schema with the following functional dependencies: 

A -> B, B -> C,
C -> D and D -> B.
The decomposition of R into
(A, B), (B, C), (B, D)

(A) gives a lossless join and is dependency preserving 
(B) gives a lossless join, but is not dependency preserving 
(C) does not give a lossless join, but is dependency preserving 
(D) does not give a lossless join and is not dependency preserving 

Click here for solution.

Q.2: R(A,B,C,D) is a relation. Which of the following does not have a lossless join, dependency preserving BCNF decomposition? 

(A) A->B, B->CD 
(B) A->B, B->C, C->D 
(C) AB->C, C->AD 
(D) A ->BCD 

Click here for solution. 

Lossless Join Decomposition in DBMS

Visit Course