18 - Decomposition (Lossless & Dependency-Preserving)

🎯 Learning Objectives

• Explain lossless vs. lossy decomposition
• Test whether a decomposition is lossless
• Test whether a decomposition preserves dependencies
• Apply the 3NF synthesis algorithm

📋 Prerequisites

• 16 - Normalization (1NF-3NF) — Normal forms, FDs
• 15 - Canonical Cover — Canonical cover computation

📖 Core Content

18.1 Intuition: Splitting Relations Correctly

1. Lossy join: When you join the decomposed tables back, you get MORE rows than the original (spurious tuples)
2. FD loss: The decomposed tables don't allow checking all original FDs, so updates could violate constraints A GOOD decomposition must be:

• Lossless: The natural join of the projections must equal the original relation
• Dependency-preserving: All original FDs can be checked on the decomposed relations

Why This Matters: A bad decomposition is WORSE than no decomposition — it can silently produce wrong results.

18.2 Lossless Join Decomposition

18.3 Testing Lossless Join (Binary)

1. Check if $R₁ \cup R₂ = R$ (all attributes preserved)
2. Check if $R₁ \cap R₂ \neq \emptyset$ (there's some common attribute)
3. Check if $R₁ \cap R₂ \rightarrow R₁$ or $R₁ \cap R₂ \rightarrow R₂$ If all three conditions hold, the decomposition is lossless.

Worked Example

• $R₁ \cup R₂ = \{A, B, C, D, E\} = R$ ✓
• $R₁ \cap R₂ = \emptyset$ ✗
• Lossy! Decomposition 2: $R₁(A, B, C, D)$, $R₂(B, E)$
• $R₁ \cup R₂ = \{A, B, C, D, E\} = R$ ✓
• $R₁ \cap R₂ = \{B\} \neq \emptyset$ ✓
• $\{B\}^+$ using F: {B} → $B \rightarrow E$ → {B, E}. Not all of R₁ or R₂.
• But check: Does $B \rightarrow R₂$? R₂ = {B, E}. $\{B\}^+$ = {B, E} = R₂! Yes!
• Lossless!

18.4 Dependency Preservation

1. Compute $X^+$ using only FDs that can be checked on the decomposed relations
2. If $Y \subseteq X^+$, the FD is preserved
3. All FDs must be preserved for the decomposition to be dependency-preserving Simpler approach: For each relation $R_i$, project the FDs onto $R_i$ (keep only FDs where both sides are in $R_i$). If the union of these projected FDs is equivalent to $F$, the decomposition preserves dependencies.

Worked Example

• FDs on $R₁$: $A \rightarrow B$
• FDs on $R₂$: $B \rightarrow C$
• All original FDs are preserved. ✓

18.5 Lossless vs. Dependency-Preserving

• A lossless decomposition may NOT preserve dependencies
• A dependency-preserving decomposition may be lossy Goal: Get BOTH properties. | BCNF | Guarantees lossless | Does NOT guarantee dependency preservation | | 3NF | Guarantees lossless | Guarantees dependency preservation |

18.6 3NF Synthesis Algorithm

sql
Input: R (relation), F (FD set)
Output: 3NF decomposition
1. Compute canonical cover F_c of F
2. For each FD X → Y in F_c, create relation R_i = X ∪ Y
3. If no relation contains a candidate key of R, add a relation with a candidate key
4. Optimize: Merge any two relations where one is a subset of the other
5. The resulting decomposition is lossless and dependency-preserving

Worked Example

• $A \rightarrow B$ → $R₁(A, B)$
• $B \rightarrow D$ → $R₂(B, D)$ Step 3: Check if any relation contains a candidate key. CK = {A, C}. No relation has {A, C}. Add $R₃(A, C)$. Step 4: No merges needed. Final 3NF: $R₁(A, B)$, $R₂(B, D)$, $R₃(A, C)$

18.7 Lossless Join Test (Chase) for Multiple Relations

1. Create a table with one row per relation and columns for all attributes
2. Fill each cell: If the attribute is in the relation, put a ; otherwise put
3. Apply FDs: If two rows match on LHS, force equality on RHS
4. If any row becomes all , the decomposition is lossless

📐 Key Formulas / Concepts

⚠️ Common Pitfalls

Pitfall 1: Assuming Lossless Means "No Data Loss"

Pitfall 2: Not Checking $R₁ \cup R₂ = R$

Pitfall 3: Thinking Decomposition Always Improves Design

📝 Practice Questions

Q1. What makes a decomposition lossless? State the binary decomposition condition.

Answer

A binary decomposition of R into R₁ and R₂ is lossless iff:

1. $R₁ \cup R₂ = R$ (all attributes covered)
2. $R₁ \cap R₂ \neq \emptyset$ (common attributes exist)
3. $R₁ \cap R₂ \rightarrow R₁$ OR $R₁ \cap R₂ \rightarrow R₂$ (common attributes determine one relation)

If satisfied, joining R₁ and R₂ will return exactly the original R without spurious tuples.

Q2. Check if decomposition of $R(A,B,C,D)$ with $F=\{A→B, B→C\}$ into $R₁(A,B)$ and $R₂(C,D)$ is lossless.

Answer

• $R₁ \cup R₂ = \{A,B,C,D\} = R$ ✓
• $R₁ \cap R₂ = \emptyset$ ✗

Condition 2 fails. The decomposition is lossy.

Even without checking the FD condition, having no common attributes between the two relations means the Cartesian product will produce spurious tuples.

Q3. What does dependency preservation mean? Why is it important?

Answer

Dependency preservation means all original FDs can be checked on the individual decomposed relations — no joins are needed to verify constraints.

Importance: If a decomposition doesn't preserve dependencies, the DBMS must do JOINs to check FDs, which is expensive. Updates on the decomposed relations might violate FDs that can't be checked locally.

Example: If $F = \{A→B, B→C\}$ and decomposition is $R₁(A,B)$, $R₂(A,C)$, the FD $B→C$ can't be checked on either relation alone.

Q4. Apply the 3NF synthesis algorithm to $R(A,B,C,D)$ with $F=\{A→B, C→D\}$.

Answer

Step 1: Canonical cover. F is already minimal: $F_c = \{A→B, C→D\}$.

Step 2: Create relations:

• $A→B$ → $R₁(A, B)$
• $C→D$ → $R₂(C, D)$

Step 3: Check for candidate key. CK = {A, C}. Add $R₃(A, C)$.

Step 4: No merges.

Final 3NF: $R₁(A,B)$, $R₂(C,D)$, $R₃(A,C)$ — lossless and dependency-preserving.

Q5. Is it possible to have a lossless decomposition that doesn't preserve dependencies?

Answer

Yes! These properties are independent. Example:

$R(A,B,C)$ with $F=\{A→B, B→C\}$. Decompose into $R₁(A,B)$ and $R₂(A,C)$.

• Lossless? $R₁∩R₂=\{A\}$, $\{A\}^+ = \{A,B,C\} = R₁$? Wait, $R₁$ is just $\{A,B\}$. $\{A\}^+$ in F = $\{A,B,C\}$, so $\{A\}^+$ contains $R₁$. ✓ Lossless.
• Dependency-preserving? $B→C$ can't be checked on either $R₁$ or $R₂$ (B not in $R₂$, C not in $R₁$). ✗

This is lossless but NOT dependency-preserving.

Q6. What is the chase test used for?

Answer

The chase test is a general method to check lossless join for decompositions involving more than two relations. It creates a symbolic table tracking which relations can supply which attributes, and uses FDs to deduce equality constraints. If any row becomes entirely marked, the decomposition is lossless.

Q7. Find a lossless, dependency-preserving 3NF decomposition of $R(A,B,C,D)$ with $F=\{A→BC, B→D\}$.

Answer

Canonical cover: Decompose $A→BC$ → $A→B, A→C$. $F_c = \{A→B, A→C, B→D\}$.

Synthesis:

• $A→B$ → $R₁(A,B)$
• $A→C$ → Already covered? $R₁$ has A,B but not C. Make $R₂(A,C)$ Actually merge: $A→B$ and $A→C$ can be combined: $R₁(A,B,C)$
• $B→D$ → $R₂(B,D)$

Check candidate key: CK = ? Compute: A⁺ = {A,B,C,D} = R → {A} is a CK. Already covered by $R₁$ (which has A). No need to add extra relation.

Final: $R₁(A,B,C)$, $R₂(B,D)$ — both in 3NF, lossless, dependency-preserving.

Q8. Why is the 3NF synthesis algorithm preferred over BCNF decomposition?

Answer

The 3NF synthesis algorithm guarantees BOTH lossless join AND dependency preservation. BCNF decomposition guarantees lossless join but may lose FDs.

In practice, if you need to preserve all FDs (which you usually do), 3NF synthesis is the safer choice. If BCNF is achievable while preserving all FDs, it's preferred for its stronger guarantees.

🔗 Cross-References

• Next Topic: 19 - Application Design
• Previous Topic: 17 - BCNF & Higher Normal Forms
• Related: 14 - Functional Dependencies (basis for decomposition)
• Textbook: Silberschatz, Korth, Sudarshan — Chapter 8 (Relational Database Design) Join Discord Previous17 - BCNF & Higher Normal FormsNext19 - Application Design