Dependency Preserving Decomposition

Dependency preserving decomposition is a property of database normalization where functional dependencies in a relational schema are preserved after decomposing the relation into smaller sub-relations.

Definition

A decomposition of a relation R into sub-relations R1, R2, ..., Rn is dependency preserving if the functional dependencies (FDs) of R can be enforced by enforcing the FDs in each sub-relation without needing to join the decomposed relations.

Why is Dependency Preservation Important?

Example

Consider a relation:

R(A, B, C)

with functional dependencies:

A → B, B → C

Now, suppose we decompose R into two relations:

Here:

Since all functional dependencies of R are preserved in R1 and R2, the decomposition is dependency preserving.

Non-Dependency Preserving Decomposition

If a decomposition loses some functional dependencies, it is not dependency preserving, meaning we might need to join tables to enforce some constraints.

For example, if we decompose R(A, B, C) into:

The dependency A → B is lost because neither R1 nor R2 contains both A and B, making it not dependency preserving.

Conclusion

A good decomposition should be:


Lossless vs Dependency Preserving Decomposition

Lossless vs Dependency Preserving Decomposition

No, non-dependency preserving decomposition is not the same as lossless decomposition. These are two different properties of decomposition in database normalization.

Key Differences

Property Dependency Preserving Decomposition Lossless Decomposition
Definition Ensures that all functional dependencies are preserved in the decomposed relations. Ensures that when decomposed relations are joined, they reconstruct the original relation without any loss of information.
Focus Functional dependencies (FDs) Data integrity
Loss of Information? No loss of functional dependencies, ensuring constraints are maintained. No loss of tuples, ensuring all data remains intact.
Example of Violation If a decomposition removes an FD that was originally present, making some constraints unenforceable. If the decomposition results in extra or missing tuples when relations are joined back.

Example to Differentiate

1. Lossless But Not Dependency Preserving

Consider a relation R(A, B, C) with functional dependencies:

A → B, B → C

Decomposing into:

This decomposition is lossless because joining R1 and R2 on A gives back R(A, B, C).

However, it is not dependency preserving because the FD B → C is not enforceable in either R1 or R2.

2. Dependency Preserving But Not Lossless

Consider a relation R(A, B, C) with functional dependencies:

A → B, B → C

Decomposing into:

This decomposition is dependency preserving because both A → B and B → C are preserved in R1 and R2.

However, it is not necessarily lossless because if we join R1 and R2, we might get extra tuples.

Conclusion


First Normal Form (1NF) Case Study

First Normal Form (1NF) - Case Study

Definition of 1NF

Rule Description
Atomicity Each column must contain atomic (indivisible) values.
Single-Valued Attributes No multi-valued attributes (each column should have only one value per row).
Uniform Data Types All values in a column must be of the same type.
Uniqueness Each row must have a unique identifier (Primary Key).

Step 1: Unnormalized Table (UNF)

Student_ID Name Subjects Contact Numbers
101 Alice Math, Science 9876543210, 9876504321
102 Bob English 9876512345
103 Carol Math, History 9876587654, 9876523456

Issues in the Unnormalized Table

Step 2: Converted Table in 1NF

Student_ID Name Subject Contact Number
101 Alice Math 9876543210
101 Alice Science 9876504321
102 Bob English 9876512345
103 Carol Math 9876587654
103 Carol History 9876523456

Checking for 1NF Compliance

Advantages of 1NF

Conclusion

1NF is the first step in database normalization that ensures atomicity and removes repeating groups. By breaking multi-valued attributes into separate rows, we improve data integrity and search efficiency.