Dependency Preserving Decomposition - DBMS
Last Updated : 06 Feb, 2025
In a Database Management System (DBMS), dependency-preserving decomposition refers to the process of breaking down a complex database schema into simpler, smaller tables, such that all the functional dependencies of the original schema are still enforceable without needing to perform additional joins.
This approach is crucial for database normalization as it minimizes redundancy, prevents anomalies, and improves the efficiency of database queries. To achieve dependency-preserving decomposition, algorithms like lossless join decomposition and dependency-preserving decomposition are applied, ensuring that all original dependencies can be represented directly in the decomposed tables.
Example:
Suppose R is a relational schema and F is the set of functional dependencies on R. If R is decomposed into relations R1, R2, …..........…… Rn , each holding functional dependencies F1, F2, …....…...… Fn respectively. We can say, F` = F1 U F2 U ...……..… U Fn.
Now this decomposition will be considered as dependency preserving decomposition if and only if-
Every dependency in F is logically implied by F` i.e. F`+ = F+ It is obvious that F1 ⊆ F + , F2 ⊆ F + and so on.
If we verify that F` is satisfied in R, we have verified that decomposition is dependency preserving decomposition i.e. F1 U F2 = F.
Let's say:
- The original relation R has a set of functional dependencies (FDs) called F.
- When we decompose R into R1 and R2, each gets its own FDs:
- f1: FDs in R1
- f2: FDs in R2
- The combined FDs from R1 and R2 are f1∪f2.
Now, there are three possible cases:
Case 1: f1∪f2=F
- This means the FDs from R1 and R2 together exactly match the original FDs F.
- Result: The decomposition is dependency-preserving because we haven’t lost any FDs.
Example:
Original R:
| StudentID | CourseID | Instructor |
Functional Dependencies F:
- CourseID→Instructor
- StudentID,CourseID→Instructor
After decomposition:
- R1(StudentID,CourseID): f1={StudentID,CourseID→Instructor}
- R2(CourseID,Instructor): f2={CourseID→Instructor}
Here, f1∪f2=F.
The decomposition is dependency-preserving.
Case 2: f1∪f2⊂F
- This means some FDs from the original set F are missing in f1∪f2.
- Result: The decomposition is not dependency-preserving, as we’ve lost some FDs.
Example:
Original R:
| StudentID | CourseID | Instructor |
Functional Dependencies F:
- StudentID,CourseID→Instructor
- CourseID→Instructor
After decomposition:
- R1(StudentID,CourseID): f1={StudentID,CourseID→Instructor}
- R2(CourseID,Instructor): f2={}
Here, f1∪f2⊂F.
The FD CourseID→InstructorCourseID is missing.
The decomposition is not dependency-preserving.
Case 3: f1∪f2⊃F
- This means the FDs from R1R_1R1 and R2R_2R2 contain extra dependencies that were not part of F.
- Result: This case is technically possible but uncommon. These extra dependencies may not cause direct problems but could lead to inconsistencies or unexpected behavior.
Example:
Original R:
| StudentID | CourseID | Instructor |
Functional Dependencies F:
After decomposition:
- R1(StudentID,CourseID): f1={CourseID→Instructor}
- R2(CourseID,Instructor): f2={Instructor→CourseID}
Here, f1∪f2⊃F, as the FD Instructor→CourseID was added unnecessarily.
The decomposition has extra dependencies, which could lead to confusion but doesn’t directly violate dependency preservation.
Key Concepts of Dependency Preserving Decomposition in DBMS
The key concepts of dependency-preserving decomposition include:
- Functional Dependency Preservation: This means that after decomposition, the functional dependencies in the original schema must still hold true in the decomposed schema.
- Lossless Join Property: The decomposition must allow for the original relation to be reconstructed from the decomposed relations without any data loss, ensuring no information is discarded.
- Normalization: The decomposition often aims to normalize the schema to higher normal forms (like 3NF or BCNF), which further eliminates redundancy and dependency anomalies.
- Minimal Redundancy: By ensuring the decomposition preserves functional dependencies, it minimizes data redundancy and helps in avoiding data anomalies.
Problem: Let a relation R (A, B, C, D ) and functional dependency {AB --> C, C --> D, D --> A}. Relation R is decomposed into R1( A, B, C) and R2(C, D). Check whether decomposition is dependency preserving or not.
Solution:
R1(A, B, C) and R2(C, D)
Let us find closure of F1 and F2
To find closure of F1, consider all combination of ABC. i.e., find closure of A, B, C, AB, BC and AC
Note ABC is not considered as it is always ABC
closure(A) = { A } // Trivial
closure(B) = { B } // Trivial
closure(C) = {C, A, D} but D can't be in closure as D is not present R1.
= {C, A}
C--> A // Removing C from right side as it is trivial attribute
closure(AB) = {A, B, C, D}
= {A, B, C}
AB --> C // Removing AB from right side as these are trivial attributes
closure(BC) = {B, C, D, A}
= {A, B, C}
BC --> A // Removing BC from right side as these are trivial attributes
closure(AC) = {A, C, D}
NULL SET
F1 {C--> A, AB --> C, BC --> A}.
Similarly F2 { C--> D }
In the original Relation Dependency { AB --> C , C --> D , D --> A}.
AB --> C is present in F1.
C --> D is present in F2.
D --> A is not preserved.
F1 U F2 is a subset of F. So given decomposition is not dependency preserving.
How Dependency Preserving Decomposition Enhances Database Efficiency?
Dependency-preserving decomposition enhances database efficiency by:
- Eliminating Redundancy: It helps reduce unnecessary repetition of data, leading to smaller storage requirements.
- Maintaining Integrity: By preserving functional dependencies, the database ensures consistent data with fewer chances of anomalies like update, insert, or delete anomalies.
- Improving Query Performance: With a well-decomposed schema, it’s easier to optimize queries as the smaller tables are often faster to process.
- Simplifying Updates: Since data is more normalized, updates become simpler and more efficient, reducing the risk of inconsistencies.
Imp Note: The 1NF, 2NF, and 3NF are valid for dependency-preserving decomposition.
Step-by-Step Approach to Dependency Preserving Decomposition in DBMS
- In this technique, the original relation is decomposed into smaller relations in such a way that the resulting relations preserve the functional dependencies of the original relation. This is important because if the decomposition results in losing any of the original functional dependencies, it can lead to data inconsistencies and anomalies.
- To achieve dependency preserving decomposition, there are various algorithms available, such as the Boyce-Codd Normal Form (BCNF) decomposition and the Third Normal Form (3NF) decomposition. These algorithms are based on the concept of functional dependencies and are used to identify the attributes that should be grouped together to form smaller relations.
- The BCNF decomposition algorithm is used to decompose a relation into smaller relations in such a way that each resulting relation is in BCNF. BCNF is a higher normal form than 3NF and is used when there are multiple candidate keys in a relation.
- The 3NF decomposition algorithm is used to decompose a relation into smaller relations in such a way that each resulting relation is in 3NF. 3NF is a normal form that ensures that there are no transitive dependencies between the attributes of a relation.
- Overall, dependency preserving decomposition is an important technique in DBMS for improving database efficiency while maintaining data consistency and integrity. It is important to choose the right decomposition algorithm based on the specific requirements of the database to achieve the desired results.
GATE Previous Year Question's
For further details you can also refer to the Quiz of the previous year's GATE Questions. https://www.geeksforgeeks.org/dbms-gq/database-design-normal-forms-gq/
Similar Reads
Enhanced ER Model As data complexity grows, the traditional ER model becomes less effective for database modeling. Enhanced ER diagrams extend the basic ER model to better represent complex applications. They support advanced concepts like subclasses, generalization, specialization, aggregation, and categories.ER mod
7 min read
Mapping from ER Model to Relational Model Converting an Entity-Relationship (ER) diagram to a Relational Model is a crucial step in database design. The ER model represents the conceptual structure of a database, while the Relational Model is a physical representation that can be directly implemented using a Relational Database Management S
7 min read
Relational Model in DBMS The Relational Model organizes data using tables (relations) consisting of rows and columns. Each column represents a specific attribute with a unique name, while each row holds data about a real-world entity or relationship. As a record-based model, it stores data in fixed-format records with defin
10 min read
Introduction of Relational Algebra in DBMS Relational Algebra is a formal language used to query and manipulate relational databases, consisting of a set of operations like selection, projection, union, and join. It provides a mathematical framework for querying databases, ensuring efficient data retrieval and manipulation. Relational algebr
9 min read
Anomalies in Relational Model Anomalies in the relational model refer to inconsistencies or errors that can arise when working with relational databases, specifically in the context of data insertion, deletion, and modification. There are different types of anomalies that can occur in referencing and referenced relations which c
5 min read
Types of Keys in Relational Model (Candidate, Super, Primary, Alternate and Foreign) In the context of a relational database, Keys are one of the basic requirements of a relational database model. keys are fundamental components that ensure data integrity, uniqueness, and efficient access. It is widely used to identify the tuples(rows) uniquely in the table. We also use keys to set
8 min read
Basic Operators in Relational Algebra The Relational Model is a way of structuring data using relations, which are a collection of tuples that have the same attributes. Relational Algebra is a procedural query language that takes relations as input and returns relations as output. Here, we'll explore the basic operators of Relational Al
4 min read
Extended Operators in Relational Algebra Extended operators in relational algebra are operators that go beyond the basic set of relational algebra operations. They are also known as derived operators because they can be constructed from combinations of the fundamental operators. There are mainly three types of extended operators in Relatio
7 min read
Tuple Relational Calculus (TRC) in DBMS Tuple Relational Calculus (TRC) is a non-procedural query language used in relational database management systems (RDBMS) to retrieve data from tables. TRC is based on the concept of tuples, which are ordered sets of attribute values that represent a single row or record in a database table. TRC is
4 min read
Introduction of Database Normalization Normalization is an important process in database design that helps improve the database's efficiency, consistency, and accuracy. It makes it easier to manage and maintain the data and ensures that the database is adaptable to changing business needs.Database normalization is the process of organizi
8 min read