## Canonical CoverIn the case of updating the database, the responsibility of the system is to check whether the existing functional dependencies are getting violated during the process of updating. In case of a violation of functional dependencies in the new database state, the rollback of the system must take place. A canonical cover or irreducible a set of functional dependencies FD is a simplified set of FD that has a similar closure as the original set FD. ## Extraneous attributesAn attribute of an FD is said to be extraneous if we can remove it without changing the closure of the set of FD.
- B → A
- AD → B ( using decomposition inference rule on AD → BC)
- AD → C ( using decomposition inference rule on AD → BC)
- C → A ( using decomposition inference rule on C → ABD)
- C → B ( using decomposition inference rule on C → ABD)
- C → D ( using decomposition inference rule on C → ABD)
Now set of FD = { B → A, AD → B, AD → C, C → A, C → B, C → D } The next step is to find closure of the left side of each of the given FD by including that FD and excluding that FD, if closure in both cases are same then that FD is redundant and we remove that FD from the given set, otherwise if both the closures are different then we do not exclude that FD.
1a. Closure B+ = BA using FD = { 1b. Closure B+ = B using FD = { AD → B, AD → C, C → A, C → B, C → D } From 1 a and 1 b, we found that both the Closure( by including 2 a. Closure AD+ = ADBC using FD = { B →A, 2 b. Closure AD+ = ADCB using FD = { B → A, AD → C, C → A, C → B, C → D } From 2 a and 2 b, we found that both the Closure (by including
3 a. Closure AD+ = ADCB using FD = { B →A, 3 b. Closure AD+ = AD using FD = { B → A, C → A, C → B, C → D } From 3 a and 3 b, we found that both the Closure (by including
4 a. Closure C+ = CABD using FD = { B →A, AD → C, 4 b. Closure C+ = CBDA using FD = { B → A, AD → C, C → B, C → D } From 4 a and 4 b, we found that both the Closure (by including
5 a. Closure C+ = CBDA using FD = { B →A, AD → C, 5 b. Closure C+ = CD using FD = { B → A, AD → C, C → D } From 5 a and 5 b, we found that both the Closure (by including
6 a. Closure C+ = CDBA using FD = { B →A, AD → C, C → B, 6 b. Closure C+ = CBA using FD = { B → A, AD → C, C → B } From 6 a and 6 b, we found that both the Closure( by including
- Since FD = { B → A, AD → C, C → B, C → D } is resultant FD, now we have checked the redundancy of attribute, since the left side of FD AD → C has two attributes, let's check their importance, i.e. whether they both are important or only one.
Closure AD+ = ADCB using FD = { B →A, Closure A+ = A using FD = { B →A, Closure D+ = D using FD = { B →A, Since the closure of AD+, A+, D+ that we found are not all equivalent, hence in FD AD → C, both A and D are important attributes and cannot be removed. Hence resultant FD = { B → A, AD → C, C → B, C → D } and we can rewrite as
- W → X
- Y → X
- Z → W ( using decomposition inference rule on Z → WXY )
- Z → X ( using decomposition inference rule on Z → WXY )
- Z → Y ( using decomposition inference rule on Z → WXY )
- WY → Z
Now set of FD = { W → X, Y → X, WY → Z, Z → W, Z → X, Z → Y } The next step is to find closure of the left side of each of the given FD by including that FD and excluding that FD, if closure in both cases are same then that FD is redundant and we remove that FD from the given set, otherwise if both the closures are different then we do not exclude that FD.
From 1 a and 1 b, we found that both the Closure (by including
From 2 a and 2 b we found that both the Closure (by including
From 3 a and 3 b, we found that both the Closure (by including
From 4 a and 4 b, we found that both the Closure (by including
From 5 a and 5 b, we found that both the Closure (by including
From 6 a and 6 b, we found that both the Closure (by including
Since FD = { W → X, Y → X, Z → W, Z → Y, WY → Z } is resultant FD now, we have checked the redundancy of attribute, since the left side of FD WY → Z has two attributes at its left, let's check their importance, i.e. whether they both are important or only one. Closure WY+ = WYZX using FD = { W → X, Y → X, Z → W, Z → Y, WY → Z } Closure W+ = WX using FD = { W → X, Y → X, Z → W, Z → Y, WY → Z } Closure Y+ = YX using FD = { W → X, Y → X, Z → W, Z → Y, WY → Z } Since the closure of WY+, W+, Y+ that we found are not all equivalent, hence in FD WY → Z, both W and Y are important attributes and cannot be removed. Hence resultant FD = { W → X, Y → X, Z → W, Z → Y, WY → Z } and we can rewrite as:
- V → W
- VW → X
- Y → V ( using decomposition inference rule on Y → VXZ )
- Y → X ( using decomposition inference rule on Y → VXZ )
- Y → Z ( using decomposition inference rule on Y → VXZ )
Now set of FD = { V → W, VW → X, Y → V, Y → X, Y → Z }. The next step is to find closure of the left side of each of the given FD by including that FD and excluding that FD, if closure in both cases are same then that FD is redundant and we remove that FD from the given set, otherwise if both the closures are different then we do not exclude that FD.
From 1 a and 1 b, we found that both the Closure( by including
From 2 a and 2 b, we found that both the Closure( by including
From 3 a and 3 b, we found that both the Closure( by including
From 4 a and 4 b, we found that both the Closure( by including
From 5 a and 5 b, we found that both the Closure( by including
Since FD = { V → W, VW → X, Y → V, Y → Z } is resultant FD now, we have checked the redundancy of attribute, since the left side of FD VW → X has two attributes at its left, let's check their importance, i.e. whether they both are important or only one. Closure VW+ = VWX using FD = { V → W, VW → X, Y → V, Y → Z } Closure V+ = VWX using FD = { V → W, VW → X, Y → V, Y → Z } Closure W+ = W using FD = { V → W, VW → X, Y → V, Y → Z } Since the closure of VW+, V+, W+ we found that all the Closures of VW and V are equivalent, hence in FD VW → X, W is not at all an important attribute and can be removed. Hence resultant FD = { V → W, V → X, Y → V, Y → Z } and we can rewrite as
Compare Closure of STEP (6a, 6b, 6c) if the closure of AD+, A+, D+ are not equivalent, hence in FD AD → C, both A and D are important attributes and cannot be removed, otherwise, we remove the redundant attribute. Next TopicTransaction Processing concept |