Closures Closure Test Problem We are given a

Closures

Closure Test Problem: We are given a set of FDs named F, which are (X 1 → A 1, X 2 → A 2, …, Xn → An ) and we want to know whether an FD, Y → B, must hold in any relation that satisfies the given FDs. A systematic way to test is to compute the closure of F, denoted F+. The set of all functional dependencies logically implied by F is the closure of F. We can check if Y → B is implied by the FDs of by looking if Y → B is in F+. Base Case: X+ = X. Induction: Look for an FD’s left side X that is a subset of the current Y +. If the FD is X → A, add A to Y +. 2

Closure of a Set of Functional Dependencies An inference axiom is a rule that states if a relation satisfies certain FDs, it must also satisfy certain other FDs Set of inference rules is sound if the rules lead only to true conclusions Set of inference rules is complete, if it can be used to conclude every valid FD on R We can find all of F+ by applying Armstrong’s Axioms: ◦ if Y X, then X → Y (reflexivity, aka triviality) ◦ if X → Y, then Z X → Z Y (augmentation) ◦ if X → Y, and Y → Z, then X → Z (transitivity) These rules are ◦ sound and complete http: //99 volo. com/18 alex-louis-armstrong-fullmetal -alchemist-images/

Example R = {A, B, C, G, H, I} F = { A → B A → C CG → H CG → I B → H } Some members of F+ A→H AG → I by transitivity from A → B and B → H by augmenting A → C with G, to get AG → CG and then transitivity with CG → I

Procedure for Computing F+ To compute the closure of a set of functional dependencies F: F+ = F repeat for each functional dependency f in F+ apply reflexivity and augmentation rules on f add the resulting functional dependencies to F+ for each pair of functional dependencies f 1 and f 2 in F+ if f 1 and f 2 can be combined using transitivity then add the resulting functional dependency to F+ until F+ does not change any further

Example of FD Closure R = {first_name, id, last_name, username} Is "username → username id" in F: F +? ◦ username → first_name last_name ◦ last_name → id 1. Yes 2. No, because it is trivial 3. No, because it isn't in F 4. Depends on the relation's tuples

Closure of Attribute Sets Given a set of attributes , define the closure of under F (denoted by +) as the set of attributes that are functionally determined by under F. Algorithm to compute +, the closure of under F result : = ; while (changes to result) do for each → in F do if result then result : = result

Example of Attribute Set Closure R = {A, B, C, G, H, I} FDs: ◦ ◦ ◦ A→B A→C CG → H CG → I B→H {AG}+ 1. 2. 3. 4. result = AG result = ABCG (A → C and A → B and A result) result = ABCGH (CG → H and CG result) result = ABCGHI (CG → I and CG result) Is AG a key? ◦ Is AG a super key? ◦ Does AG → R? == Is {AG}+ R ◦ Yes Is any subset of AG a superkey? ◦ Is {A} a superkey? ◦ Does A → R? Is {A}+ R? ◦ No ◦ Is {G} a superkey? ◦ Does G → R? Is {G}+ R? ◦ No AG is a key!

Uses of Attribute Closures There are several uses of the attribute closure algorithm: Testing for superkey: ◦ To test if is a superkey, we compute +, and check if + contains all attributes of R. Testing functional dependencies ◦ To check if a functional dependency → holds (or, in other words, is in F+), just check if +. ◦ That is, we compute + by using attribute closure, and then check if it contains . ◦ Is a simple and cheap test, and very useful Computing closure of F ◦ For each R, we find the closure +, and for each S +, we output a functional dependency → S.

Example of Using Attribute Set Closure R = {first_name, id, last_name, username} FDs: ◦ username → first_name last_name ◦ last_name → id Is {username, last_name} a key for R? 1. Yes 2. No, but it is a superkey 3. No, it isn't even a superkey 4. No, it can't open a lock