L23 - Design4

# L23 - Design4 - On Monday, we reviewed computation of...

This preview shows pages 1–6. Sign up to view the full content.

Based in part on slides from Database System Concepts - 5 th Edition On Monday, we On Monday, we - - ± reviewed computation of attribute closure and talked about uses for attribute closure ± defined and saw how to determine a canonical cover ² concept of extraneous attributes on RHS and LHS ² algorithms for determining when an attribute is extraneous on LHS and on RHS ² algorithm for computing a canonical cover ± conditions for lossless join (decomposition to two relations) ± test for lossless join with larger decompositions ± Next – dependency preservation

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
Based in part on slides from Database System Concepts - 5 th Edition Dependency Preservation Dependency Preservation ± Let F i be the set of dependencies in F + that include only attributes in R i . ± A decomposition is dependency preserving , if ( F 1 F 2 F n ) + = F + ± If it is not, then checking updates for violation of functional dependencies may require computing joins, which is expensive. ± Example: ² Let R = (A, B, C, D, E, F, G) with ± F = {AB D, C DF, D CGE, G AB} ² And suppose that ± R1 = (ABDG) and R2 = (CDEF) ² Is this lossless join? ² Look at the dependencies – ± AB D and G AB lie in R1 and C DF lies in R2 ± What about D CGE? How would we verify if it holds in an instance of the relation decomposition?
Based in part on slides from Database System Concepts - 5 th Edition Testing for Dependency Preservation Testing for Dependency Preservation ± To check if a dependency α→β is preserved in a decomposition of R into R 1 , R 2 , …, R n we apply the following test (with attribute closure done with respect to F ) ² result = α while (changes to result ) do for each R i in the decomposition t = ( result R i ) + R i result = result t ² If result contains all attributes in β , then the functional dependency is preserved. ± We apply this test on all dependencies in F to check if a decomposition is dependency preserving ± This procedure takes polynomial time, instead of the exponential time required to compute F + and ( F 1 F 2 F n ) +

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
Based in part on slides from Database System Concepts - 5 th Edition Example Example ± R = ( A, B, C ) F = { A B B C } Key = { A } ± R is not in BCNF ± Decomposition R 1 = ( A, B), R 2 = (B, C) ² R 1 and R 2 in BCNF ² Lossless-join decomposition ² Dependency preserving ± but this example is pretty trivial – let’s look at another one
Based in part on slides from Database System Concepts - 5 th Edition Another Example Another Example ± R= (A, B, C, D, E, F, G, H, I, J) ± F = {A B C, A DE, B F, F GH, D IJ} ± Let’s look at the same decomposition of R that we had before and check for dependency preservation -- R1 = (ABC), R2 = (ADE), R3 = (BF), R4 = (FGH), R5 = (DIJ) ² in this case it is obvious that the dependencies are preserved ² Try R1 = (ABCDE), R2 = (BFGH), R3 = (DIJ) result = α while (changes to result ) do for each Ri in the decomposition t = ( result Ri )+ Ri result = result t If result contains all attributes in β , then the functional dependency α→β is preserved.

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
This is the end of the preview. Sign up to access the rest of the document.

## This note was uploaded on 04/08/2008 for the course EE 468 taught by Professor Conry during the Spring '08 term at Clarkson University .

### Page1 / 24

L23 - Design4 - On Monday, we reviewed computation of...

This preview shows document pages 1 - 6. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online