Lec-36: Dependency Preserving Decomposition in DBMS with Examples in Hindi | DBMS

Summary

This video explains the concept of Dependency Preserving Decomposition, a crucial aspect of database normalization. The speaker discusses how to decompose a table while ensuring that all original functional dependencies are preserved. Through a detailed example involving decomposing a table into multiple smaller tables, the video demonstrates how to check if dependencies are maintained using attribute closures. The explanation is tailored to help viewers understand how to apply these concepts in competitive exams and university-level courses, ensuring both clarity and practical relevance.

Takeaways

• 😀 **Definition of Dependency Preserving Decomposition**: The process ensures that all functional dependencies (FDs) are preserved after a database table is decomposed into smaller relations.
• 😀 **Importance for Exams**: Understanding dependency-preserving decomposition is crucial for competitive exams, including university and college-level exams.
• 😀 **Table Decomposition**: Decomposition breaks a table (relation) into smaller tables (e.g., R1, R2) while maintaining the attributes and ensuring no data loss.
• 😀 **Functional Dependencies (FDs)**: Functional dependencies, like A → B and B → C, help in determining how attributes relate to one another in a table.
• 😀 **Closure and Candidate Keys**: The closure of attributes is used to find candidate keys and uncover hidden dependencies, including transitive ones.
• 😀 **Trivial Dependencies**: Trivial dependencies, such as A → A (where an attribute determines itself), are automatically valid and should not be checked in detail.
• 😀 **Preserving Dependencies**: The main goal of decomposition is to preserve all functional dependencies. If the union of FDs from decomposed tables equals the original set of FDs, dependencies are preserved.
• 😀 **Example Walkthrough**: A detailed example demonstrates how to decompose a table with attributes A, B, C, D and verify if the functional dependencies are preserved in the decomposed tables (R1, R2, R3).
• 😀 **Non-trivial Dependencies**: Only non-trivial dependencies (dependencies that are not immediately obvious) need to be checked for preservation after decomposition.
• 😀 **Verifying Dependency Preservation**: To check if dependencies are preserved, take the closure of attributes in the decomposed tables and compare them against the original table's FDs.
• 😀 **Union of FDs**: After decomposing a table, the union of FDs from all decomposed tables must match the original FDs for the decomposition to be considered dependency-preserving.

Q & A

What is Dependency Preserving Decomposition?

Dependency Preserving Decomposition is a process of breaking down a large table (relation) into smaller tables in such a way that all the original functional dependencies (FDs) are preserved in the decomposed tables.

Why is Dependency Preserving Decomposition important in database normalization?

It is important because it ensures that no functional dependencies are lost during the decomposition process, which helps maintain the integrity of the data and supports consistent querying and updating of the database.

What is the first step in performing Dependency Preserving Decomposition?

The first step is to understand the given functional dependencies and compute the closures of relevant attributes to identify candidate keys and hidden dependencies.

What are functional dependencies in the context of a relational database?

Functional dependencies define a relationship between attributes in a table where one attribute (or set of attributes) determines another. For example, A → B means attribute A determines attribute B.

How do you check if a decomposition preserves functional dependencies?

To check if a decomposition preserves functional dependencies, you examine the functional dependencies in each decomposed table and verify if their union equals the original set of dependencies from the original table.

What is meant by 'closure' of an attribute in database design?

The closure of an attribute is the set of attributes that can be determined by that attribute, considering the given functional dependencies. It helps identify candidate keys and the potential hidden dependencies.

What is the role of trivial dependencies in Dependency Preserving Decomposition?

Trivial dependencies, such as A → A or B → B, are always valid and do not need to be considered during the decomposition process, as they do not provide useful information about the actual relationships between attributes.

In the example given, how do you check if A → B is preserved in the decomposition?

You check if A → B is preserved by examining the decomposed table containing A and B (R1) and ensuring that A is indeed determining B in that table, either directly or through its closure.

What happens if the union of dependencies in decomposed tables doesn't equal the original set of dependencies?

If the union of dependencies in the decomposed tables doesn't equal the original set, it means that the decomposition has failed to preserve some dependencies, and thus the decomposition is not dependency-preserving.

What should be the outcome of checking all dependencies after decomposition?

The outcome should be that all functional dependencies in the original table are preserved in the decomposed tables, ensuring the decomposition is both dependency-preserving and correct.