Properties of Armstrong’s Axioms

1. Properties of Armstrong’s Axioms • Soundness • All dependencies generated by the Axioms are correct • Completeness • Repeatedly applying these rules can generate all correct dependency (i.e., any FDs in F+ be generated)

2. Closure of Attribute Set X Let F be a set of functional dependencies on a set of attributes U and let X U. We define X+ to be the set of all attributes that are dependent on X (under F). X+ enables us to tell at a glance whether a dependency XA follows from F.

3. Algorithm to compute X+ under F // initialization X+:= X; // reflexive rule // find new attribute For each function dependency YZ in F, if Y X+and Z has an attribute not in X+, add the new attribute to X+; //transitive rule Repeat Step 2 until no new attribute can be found Example 1 R(A,B,C,D,E,F), F={A->D, B->E, DB, C->F}, What is {A}+?

4. Closure of Attribute Set: Example 2 EMP_PROJ(SSN,PNUMBER,HOURS,ENAME,PNAME,PLOCATION) F={ {SSN}->{ENAME}, {PNUMBER}->{PNAME,PLOCATION}, {SSN,PNUMBER}{HOURS} } (1) Compute {SSN}+ Initialization:{SSN}+={SSN} 1st iteration: SSN->ENAME, ENAME to is a new attribute {SSN}+={SSN,ENAME} 2nd iteration: {SSN}+={SSN,ENAME}, cannot find any new attribute

5. F={ {SSN}->{ENAME}, {PNUMBER}->{PNAME,PLOCATION}, {SSN,PNUMBER}{HOURS} } (2) Compute {PNUMBER}+ Initialization:{PNUMBER}+={PNUMBER} 1st iteration: {PNUMBER} -> {PNAME,PLOCATION} {PNUMBER}+={PNUMBER,PNAME,PLOCATION} 2nd iteration: cannot find any new attribute {PNUMBER}+={PNUMBER,PNAME,PLOCATION} (3) Compute {SSN, PNUMBER}+

6. Use of X+ 1: Checking if XY • Steps of checking if an FD X Y is in the closure of a set of FDs F : • Compute X+wrtF. • Check if Y is in X+. • Y X+ XY is in F+ • Does F = {AB, BC, CDE } imply AE? • i.e, is A  E in F+? Equivalently, is E in A+? • A+ (w.r.t. F)={A,B,C} • E is not in A+, thus, AE is not in F+.

7. Use of X+ 2: Finding a key K Let R be the set of attributes for a schema and F be its functional dependency set Set K:=R For each attribute A in K Compute (K-A)+ w.r.t. F If (K-A)+ contains all the attributes in R then set K:= K-A • This algorithm returns only one key out of the possible candidate keys for R. • The key returned depends on the order in which attributes are removed from R. Examples: (1) R={A,B,C,D} F={AB,BC,ABD}; find a key of R. (2) R={A,B,C,D,E,F} F= {A->C, A->D, B->C, E->F}; find a key of R

8. Use of X+ 3: Compute F+ Given a set of functional dependencies F, we define F+ to be the set of all functional dependencies that can be inferred from F. • F+ ={}; • For each attribute set A in R, computing A+ • For each XY implied by A+, add XY to F+

9. Equivalence of Sets of Functional Dependencies • Let E&F be two sets of functional dependencies. • F covers E if E F+. • E and F are equivalent if E+=F+. • E+=F+iff E covers F and F covers E. Note: Equivalence means that every FD in E can be inferred from F, and every FD in F can be inferred from E. Determine whether F covers E: For each FD XY in E, calculate X+ with respect to F, then check whether X+ Y.

10. EXAMPLE: • Check whether or not F is equivalent to G. • F={AC, ACD, EAD,EH} • G={ACD, EAH} • Prove that F is covered by G. {A}+={A,C,D} (wrt to G). Since {C} A+, AC can be inferred from G. {AC}+={A,C,D} (wrt to G). Since {D} {AC}+, ACD is covered by G. {E}+={E,A,H,C,D} (wrt G), Since {AD} {E}+, EAD is covered by G. Since {H} {E}+, EH is covered by G.

11. F={AC, ACD, EAD,EH} • G={ACD, EAH} • Prove that G is covered by F: • {A}+={A,C,D} (wrt F), • Since {CD} {A}+, ACD is covered by F. • {E}+={E,A,D,H,C} (with respect to F) • {A,H} {E}+, EAH is covered by F. • Since F covers G and G covers F, F and G are equivalent.

12. Minimal Cover of Functional Dependencies • A set of functional dependencies F is minimal if it satisfies the following three conditions: • Every FD in F has a single attribute for its right-hand side. • (This is a standard form, not a requirement.) • We cannot replace any dependency XA in F with a dependency YA, where Y is a proper subset of X, and still have a set of dependencies that is equivalent to F. • We cannot remove any dependency from F and still have a set of dependencies that is equivalent to F. There can be several minimal covers for a set of functional dependencies!

13. Minimal Cover Definition: A minimal cover of a set of FDs F is a minimal set of functional dependencies Fmin that is equivalent to F. /* The following procedure finds one minimal cover of F. */ Procedure: Find a minimal cover Fmin for F. • Set Fmin=F • /*put every FD in a standard form, i.e., it has a single attribute as its right-hand side*/ • Replace each FD XA1,A2,…,An in Fmin by the n FDs XA1,…,XAn. • /* minimize the left side of each FD, i.e., every attribute is needed */ • For each FD XA in Fmin • For each B X, • Let T=(Fmin- {XA})U{(X-{B})A} • Check whether T is equivalent to Fmin (1) • If (1) is true, then set Fmin = T. • /* delete redundant FDs, i.e., No redundant FDs remain in Fmin. */ • For each FD XA in Fmin • Let T=Fmin-{XA} • Check whether T is equivalent to Fmin. (2) • If (2) is true, set Fmin = T.

14. Minimal Cover • F={X1Y1, X2Y2, … XnYn} is a minimum cover • Any Yi is a single attribute • For any XiYi, it is impossible that X’Y and X’ is a subset of X • No XiYi can be taken out • F’ = F - {XiYi} is not equivalent to F

15. Example: Find the minimal cover of the set F={ABCDE,ED,AB,ACD}.

16. Example: Find the minimal cover of the set F={ABCDE,ED,AB,ACD}. Step 2: Fmin={ABCDE,ED,AB,ACD} • Step 3: • Replace ACD with AD; • T={ABCDE,ED,AB,AD} • Compute {A}+ wrt to F, {A}+={A,B} • Compute {A}+ wrt to T, {A}+={A,B,D} Is T equivalent to Fmin? Can Fmin Cover T? No, keep ACD Cannot replace Fmin with T. Replace ABCDE with ACDE, T={ACDE,ED,AB,ACD} Compute {ACD}+wrt to F, {ACD}+={A,C,D,B,E} Compute {ACD}+wrt to T,{ACD}+={A,C,D,E,B} Replace Fmin with T.

17. Step 3: (cont’d) Replace ACDE with ACE, T={ACE,ED,AB,ACD} Compute {AC}+wrt to F, {AC}+={ACDBE} Compute {AC}+wrt to T,{AC}+={ACEDB} Replace Fmin with T. Step 4: Consider T={ACE,ED,ACD,AB} (take out ACD) {AC}+={A,C,E,D,B} with respect to T; {D} {AC}+; we don’t have to include ACD • The minimal cover of F is: • {ACE,ED,AB}

18. Some Important Concepts • X+: Closure of an attribute set X • The set of all attributes that are determined by X • K: a key • minimum set of attributes that determines all attributes • F+ : Closure of a dependency set F • The set of all dependencies that are implied from F • Fmin: a minimum cover of a dependency set F • a minimum set of FDs that is equivalent to F