SQL Tutorial

Canonical Cover of Functional Dependencies in DBMS

Whenever a user updates the database, the system must check whether any of the functional dependencies are getting violated in this process. If there is a violation of dependencies in the new database state, the system must roll back. Working with a huge set of functional dependencies can cause unnecessary added computational time. This is where the canonical cover comes into play. A canonical cover of a set of functional dependencies F is a simplified set of functional dependencies that has the same closure as the original set F. Important definitions: Extraneous attributes: An attribute of a functional dependency is said to be extraneous if we can remove it without changing the closure of the set of functional dependencies. Canonical cover: A canonical cover $F_{c}$ of a set of functional dependencies F such that ALL the following properties are satisfied:

F logically implies all dependencies in $F_{c}$ .
$F_{c}$ logically implies all dependencies in F.
No functional dependency in $F_{c}$ contains an extraneous attribute.
Each left side of a functional dependency in $F_{c}$ is unique. That is, there are no two dependencies $\alpha_{1} \rightarrow \beta_{1}$ and $\alpha_{2} \rightarrow \beta_{2}$ in such that $\alpha_{1} \rightarrow \alpha_{2}$ .

Finding Canonical Cover

Algorithm to compute canonical cover of set F:

repeat
    1. Use the union rule to replace any dependencies in 
        and  with .    2. Find a functional dependency  with an        extraneous attribute either in  or in .    3. If an extraneous attribute is found, delete it from .       until F does not change

Example1: Consider the following set F of functional dependencies: F= { A $\rightarrow$ BC B $\rightarrow$ C A $\rightarrow$ B AB $\rightarrow$ C } Steps to find canonical cover:

There are two functional dependencies with the same set of attributes on the left: A $\rightarrow$ BC A $\rightarrow$ B These two can be combined to get A $\rightarrow$ BC. Now, the revised set F becomes: F= { A $\rightarrow$ BC B $\rightarrow$ C AB $\rightarrow$ C }
There is an extraneous attribute in AB $\rightarrow$ C because even after removing AB $\rightarrow$ C from the set F, we get the same closures. This is because B $\rightarrow$ C is already a part of F. Now, the revised set F becomes: F= { A $\rightarrow$ BC B $\rightarrow$ C }
C is an extraneous attribute in A $\rightarrow$ BC, also A $\rightarrow$ B is logically implied by A $\rightarrow$ B and B $\rightarrow$ C (by transitivity). F= { A $\rightarrow$ B B $\rightarrow$ C }
After this step, F does not change anymore. So, Hence the required canonical cover is, $F_{c}$ = { A $\rightarrow$ B B $\rightarrow$ C }

Example2: Consider another set F of functional dependencies: F={ A $\rightarrow$ BC CD $\rightarrow$ E B $\rightarrow$ D E $\rightarrow$ A }

The left side of each functional dependency in F is unique.
None of the attributes in the left or right side of any functional dependency is extraneous (Checked by applying definition of extraneous attributes on every functional dependency).
Hence, the canonical cover $F_{c}$ is equal to F.

Note: There can be more than one canonical cover $F_{c}$ of a set F of functional dependencies. How to check whether a set of f.d.’s F canonically cover another set of f.d.’s G? Consider the following two sets of functional dependencies: F = { A $\rightarrow$ B AB $\rightarrow$ C D $\rightarrow$ AC D $\rightarrow$ E } G = { A $\rightarrow$ BC D $\rightarrow$ AB } Now, we are required to find out whether one of these f.d.’s canonically covers the other set of f.d.’s. This means, we need to find out whether F canonically covers G, G canonically covers F, or none of the two canonically cover the other. To find out, we follow the following steps:

Create a singleton right hand side. This means, the attributes to the right side of the f.d. arrow should all be singleton. The functional dependency D $\rightarrow$ AC gets broken down into two functional dependencies, D $\rightarrow$ A and D $\rightarrow$ C. F = { A $\rightarrow$ B AB $\rightarrow$ C D $\rightarrow$ A D $\rightarrow$ C D $\rightarrow$ E }
Remove all extraneous attributes. Consider any functional dependency XY $\rightarrow$ Z. If X in itself can determine Z, then the attribute Y is extraneous and can be removed. As we can see, the occurrence of extraneous attributes is possible only in those functional dependencies where there are more than one attributes in the LHS. So, consider the functional dependency AB $\rightarrow$ C. Now, we must find the closures of A and B to find whether any of these is extraneous. $[A]^{+}$ =AB $[B]^{+}$ =B As we can see, B can be determined from A. This means we can remove B from the functional dependency AB $\rightarrow$ C. F = { A $\rightarrow$ B A $\rightarrow$ C D $\rightarrow$ A D $\rightarrow$ C D $\rightarrow$ E }
Remove all redundant functional dependencies. Check all f.d.’s one by one, and see if by removing a f.d. X $\rightarrow$ Y, we can still find out Y from X by some other f.d. A more formal way to state this is find $[X]^{+}$ without making use of the f.d. we are testing and check whether Y is a part of the closure. If yes, then the f.d. is redundant. Here, when checking for the f.d. D $\rightarrow$ C, we observe that even after hiding it, the closure of D contains C. This is because we can obtain C from D by the combination of two other f.d.’s D $\rightarrow$ A and A $\rightarrow$ C. So, $\rightarrow$ C is redundant. F = { A $\rightarrow$ B A $\rightarrow$ C D $\rightarrow$ A D $\rightarrow$ E }

Now, do the same for G.

Create a singleton right hand side. This means, the attributes to the right side of the f.d. arrow should all be singleton. G = { A $\rightarrow$ B A $\rightarrow$ C D $\rightarrow$ A D $\rightarrow$ B }
Remove all extraneous attributes. Since the RHS of all f.d.’s contains only 1 attribute, there is no extraneous attribute possible.
Remove all redundant functional dependencies. By looping over all f.d.’s and checking the closure of the LHS in all cases, we observe that the f.d. D $\rightarrow$ B is redundant as it can be obtained through a combination of 2 other f.d.’s, D $\rightarrow$ A and A $\rightarrow$ B. G = { A $\rightarrow$ B A $\rightarrow$ C D $\rightarrow$ A }

In DBMS, a canonical cover is a set of functional dependencies that is equivalent to a given set of functional dependencies, but is minimal in terms of the number of dependencies. The process of finding the canonical cover of a set of functional dependencies involves three main steps:

Reduction: The first step is to reduce the original set of functional dependencies to an equivalent set that has the same closure as the original set, but with fewer dependencies. This is done by removing redundant dependencies and combining dependencies that have common attributes on the left-hand side.
Elimination: The second step is to eliminate any extraneous attributes from the left-hand side of the dependencies. An attribute is considered extraneous if it can be removed from the left-hand side without changing the closure of the dependencies.
Minimization: The final step is to minimize the number of dependencies by removing any dependencies that are implied by other dependencies in the set.
To illustrate the process, let’s consider a set of functional dependencies: A -> BC, B -> C, and AB -> C. Here are the steps to find the canonical cover:
Reduction: We can reduce the set by removing the redundant dependency B -> C and combining the two remaining dependencies into one: A -> B, A -> C.
Elimination: We can eliminate the extraneous attribute B from the dependency A -> B, resulting in A -> C.
Minimization: We can minimize the set by removing the dependency AB -> C, which is implied by A -> C.
The resulting canonical cover for the original set of functional dependencies is A -> C.

The canonical cover is useful because it provides a simplified representation of the original set of functional dependencies that can be used to determine the key, superkey, and candidate key for a relation, as well as to check for normalization violations and perform other database design tasks.

Features of the canonical cover:

Minimal: The canonical cover is the smallest set of dependencies that can be derived from a given set of dependencies, i.e., it has the minimum number of dependencies required to represent the same set of constraints.

Lossless: The canonical cover preserves all the functional dependencies of the original set of dependencies, i.e., it does not lose any information.

Unique: The canonical cover is unique, i.e., there is only one canonical cover for a given set of dependencies.

Deterministic: The canonical cover is deterministic, i.e., it does not contain any redundant or extraneous dependencies.

Reduces data redundancy: The canonical cover helps to reduce data redundancy by eliminating unnecessary dependencies that can be inferred from other dependencies.

Improves query performance: The canonical cover helps to improve query performance by reducing the number of joins and redundant data in the database.

Facilitates database maintenance: The canonical cover makes it easier to modify, update, and delete data in the database by reducing the number of dependencies that need to be considered.

Now, since all f.d.’s of G are already covered in F, we conclude that F covers G. This article is contributed by Anannya Uberoi. If you like GeeksforGeeks and would like to contribute, you can also write an article using write.geeksforgeeks.org or mail your article to review-team@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks. Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.

My Personal Notes

Last Updated : 08 May, 2023

Related Articles

Canonical Cover of Functional Dependencies in DBMS

Features of the canonical cover:

GATE CS & IT 2024

Data Structures and Algorithms - Self Paced

Complete Interview Preparation - Self Paced

Related Articles

Canonical Cover of Functional Dependencies in DBMS

Features of the canonical cover:

Please Login to comment...

GATE CS & IT 2024

Data Structures and Algorithms - Self Paced

Complete Interview Preparation - Self Paced