Properties of Armstrong’s Axioms - PowerPoint Presentation

Slide1

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)Slide2

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.Slide3

Algorithm to

compute X

+ under

F // initializationX+:= X

; // reflexive rule// find new attributeFor 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 ruleRepeat Step 2 until no new attribute can be found

R(

A,B

,C,D,E,F), F

={A-

>D, B->E, D

B, C->

F},

What is {A

}

+?

Example 1Slide4

Closure

of Attribute

Set: Example 2

EMP_PROJ(SSN,PNUMBER,HOURS,ENAME,PNAME,PLOCATION)

F={ {SSN}->{ENAME}, {

PNUMBER}->{PNAME,PLOCATION}, {SSN,PNUMBER}{HOURS} }

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

(1) Compute

{SSN

}+Slide5

(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}+

F={ {SSN}->{ENAME}, {PNUMBER}->{PNAME,PLOCATION}, {SSN,PNUMBER}{HOURS} }Slide6

Steps of checking if an FD

X



Y is in the closure of a set of FDs F :Compute X+ wrt

F.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+.

Use of X

+

1: Checking

if X

YSlide7

Set K:=R

For each attribute A in K

Compute (K-A)

+

w.r.t. FIf (K-A)+

contains all the attributes in R then set K:= K-AThis 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

Use of X

+

2: Finding a key K

Let R be the set of attributes for a schema and F be its functional dependency setSlide8

Given a set of functional dependencies F, we define F

+

to be the set of all functional dependencies that can be inferred from F.

Use of X+ 3: Compute F

+

F

+

={};

For each attribute set A in R, computing A

+

For each X

Y implied by

A

+

, add XY to F+Slide9

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.Slide10

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. Slide11

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.

F={A

C, ACD, EAD,EH}

G={ACD, EAH}Slide12

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!Slide13

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,A

2,…,An in Fmin by the

n

FDs XA

1

,…,

XAn.

/* minimize the left side of each FD, i.e., every attribute is needed */For each FD X

A in FminFor 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 F

min

Let T=F

min

-{X

A}

Check whether T is equivalent to F

min

. (2)

If (2) is true, set F

min

= T. Slide14

Minimal Cover

F={X1

Y1, X2Y2, … XnYn} is a minimum cover

Any Yi is a single attributeFor any Xi

Yi, it is impossible that X’Y and X’ is a subset of X

No XiYi can be taken outF’ = F - {XiYi} is not equivalent to FSlide15

Example:

Find the minimal cover of the set F={ABCD

E,ED,AB,ACD}.Slide16

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}

Cannot replace F

min

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.

Is T equivalent to F

min

?

Can Fmin Cover T?

No, keep AC

DSlide17

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}Slide18

Some Important Concepts

X

+

: Closure of an attribute set X

The set of all attributes that are determined by XK: a key

minimum set of attributes that determines all attributes F+

: Closure of a dependency set FThe set of all dependencies that are implied from FF

min: a minimum cover of a dependency set Fa minimum set of FDs that is equivalent to F