[PDF] - Finding All Implied FD's Motiv atio n: Supp ose w e ha v PDF Document

SLIDE 1 Finding All Implied FD's Motiv atio n: Supp

se

w e ha v e a relation AB C D with some FD's F . If w e decide to decomp

se

AB C D in to AB C and AD , what are the FD's for AB C , AD ?

Example:

F = AB ! C , C ! D , D ! A. It lo

ks

lik e just AB ! C holds in AB C , but in fact C ! A follo ws from F and applies to relation AB C .

Problem

is exp

nen

tial in w

rst

case. 1

SLIDE 2 Algorithm F

r

eac h set

f

attributes X compute X + .

Eliminate

some \ob vious" dep endencies that follo w from

thers:

1. T rivial FD's : righ t side is a subset

f

left side.

✦

Consequence: no p

in

t in computing ; +

r

closure

f

full set

f

attributes. 2. Eliminate X Y ! Z if X ! Z holds.

✦

Consequence: If X + is all attributes, then there is no p

in

t in computing closure

f

sup ersets

f

X . 3. Eliminate FD's whose righ t sides are not single attributes. 2

SLIDE 3 Example Example: F = AB ! C , C ! D , D ! A. What FD's follo w?

A

+ = A; B + = B (nothing).

C

+ = AC D (add C ! A).

D

+ = AD (nothing new).

(AB

) + = AB C D (add AB ! D ; skip all sup ersets

f

AB ).

(B

C ) + = AB C D (nothing new; skip all sup ersets

f

B C ).

(B

D ) + = AB C D (add B D ! C ; skip all sup ersets

f

B D ).

(AC

) + = AC D ; (AD ) + = AD ; (C D ) + = AC D (nothing new).

(AC

D ) + = AC D (nothing new).

All
ther

sets con tain AB , B C ,

r

B D , so skip.

Th

us, the

nly

in teresting FD's that follo w from F are: C ! A, AB ! D , B D ! C . 3

SLIDE 4 Normalization Goal = BCNF = Bo yce-Co dd Normal F

rm

= all FD's follo w from the fact \k ey ! ev erything."

F
rmally

, R is in BCNF if ev ery non trivial FD for R , sa y X ! A, has X a sup erk ey . Wh y? 1. Guaran tees no redundancy due to FD's. 2. Guaran tees no up date anomalies =

ne
ccurrence
f

a fact is up dated, not all. 3. Guaran tees no deletion anomalies = v alid fact is lost when tuple is deleted. 4

SLIDE 5 Example

f

Problems Drinkers(name, addr, beersLiked, manf, favoriteBeer) name addr b eersLik ed manf fa v

riteBeer

Janew a y V

y

ager Bud A.B. Wic k edAle Janew a y ??? Wic k edAle P ete's ??? Sp

c

k En terprise Bud ??? Bud FD's: 1. name ! addr 2. name ! favoriteBeer 3. beersLiked ! manf

???'s

are redundan t, since w e can gure them

ut

from the FD's.

Up

date anomalies: If Janew a y gets transferred to the Intr epid, will w e c hange addr in eac h

f

her tuples?

Deletion

anomalies: If nob

dy

lik es Bud, w e lose trac k

f

Bud's man ufacturer. 5

SLIDE 6 Eac h

f

the giv en FD's is a BCNF violation:

Key

= fname, beersLikedg

✦

Eac h

f

the giv en FD's has a left side a prop er subset

f

the k ey . Another Example Beers(name, manf, manfAddr).

FD's

= name ! manf, manf ! manfAddr.

Only

k ey is name.

✦

manf ! manfAddr violates BCNF with a left side unrelated to an y k ey . 6

SLIDE 7 Decomp

sition

to Reac h BCNF Setting: relation R , giv en FD's F . Supp

se

relation R has BCNF violati

n

X ! A.

Notice:

w e need

nly

lo

k

among FD's

f

F , b ecause an y non trivial FD that follo ws from them m ust con tain

ne
f

their left sides in its left side.

✦

Th us, an y FD that follo ws and has a non- sup erk ey as a left side means there is an FD in F with the same prop ert y . 7

SLIDE 8 1. Exp and righ t side to include X + .

✦

Cannot b e all attributes | wh y? 2. Decomp

se

R in to X + and (R

X

+ ) [ X . X R X + 3. Find the FD's for the decomp

sed

relations.

✦

Pro ject the FD's from F = calculate all consequen ts

f

F that in v

lv

e

nly

attributes from X +

r
nly

from (R

X

+ ) [ X . 8

SLIDE 9 Example R = Drinkers(name, addr, beersLiked, manf, favoriteBeer) F = 1. name ! addr 2. name ! favoriteBeer 3. beersLiked ! manf Pic k BCNF violati

n

name ! addr.

Expand

righ t side: name ! addr favoriteBeer.

Decomp
sed

relations: Drinkers1(name, addr, favoriteBeer) Drinkers2(name, beersLiked, manf)

Pro

jected FD's (skipping a lot

f

w

rk

that leads no w ehere in teresting):

✦

F

r

Drinkers1: name ! addr and name ! favoriteBeer.

✦

F

r

Drinkers2: beersLiked ! manf. 9

SLIDE 10

BCNF

violati

ns?

✦

F

r

Drinkers1, name is k ey and all left sides are sup erk eys.

✦

F

r

Drinkers2, {name, beersLiked} is the k ey , and beersLiked ! manf violates BCNF. Decomp

se

Drinkers2

Expand:

nothing.

Decomp
se:

Drinkers3(beersLi ked, manf) Drinkers4(name, beersLiked)

Resulting

relations are all in BCNF: Drinkers1(name, addr, favoriteBeer) Drinkers3(beersLi ked, manf) Drinkers4(name, beersLiked) 10

SLIDE 11 Wh y Decomp

sition

\W

rks"?

What do es it mean to \w

rk"?

Wh y can't w e just tear sets

f

attributes apart as w e lik e?

Answ

er: the decomp

sed

relations need to represen t the same information as the

riginal.

Pro jection and Join

The
p

erations that relate

riginal

and decomp

sed

relations.

Supp
se

R is decomp

sed

in to S and T . W e pr

je

ct R

n

to S b y: 1. Eliminate columns

f

R not in S . 2. Eliminate duplicate ro ws. 11

SLIDE 12 Example R = name addr b eersLik ed manf fa v

riteBeer

Janew a y V

y

ager Bud A.B. Wic k edAle Janew a y V

y

ager Wic k edAle P ete's Wic k edAle Sp

c

k En terprise Bud A.B. Bud 12

SLIDE 13

Pro

ject

n

to Drinkers1(name, addr, favoriteBeer): name addr fa v

riteBeer

Janew a y V

y

ager Wic k edAle Sp

c

k En terprise Bud

Pro

ject

n

to Drinkers3(beersLik ed, manf): b eersLik ed manf Bud A.B. Wic k edAle P ete's

Pro

ject

n

to Drinkers4(name, beersLiked): name b eersLik ed Janew a y Bud Janew a y Wic k edAle Sp

c

k Bud 13

SLIDE 14 Reconstruction

f

Original Can w e gure

ut

the

riginal

relation from the decomp

sed

relations?

Sometimes,

if w e (natural) join the relations.

R

. / S :

✦

Sc hema = union

f

attributes

f

R and S .

✦

T uples = all formed from a tuple r from R and s from S that agree in all common attributes. Example Drinkers3 . / Drinkers4 = name b eersLik ed manf Janew a y Bud A.B. Janew a y Wic k edAle P ete's Sp

c

k Bud A.B.

Join
f

ab

v

e with Drinkers1 =

riginal

R . 14

SLIDE 15 Theorem Supp

se

w e decomp

se

a relation with sc hema X Y Z in to X Y and X Z and pro ject the relation for X Y Z

n

to X Y and X Z . Then X Y . / X Z is guar ante e d to reconstruct X Y Z if and

nly

if either X ! Y

r

X ! Z holds.

Notice

that whenev er w e decomp

se

b ecause

f

a BCNF violation,

ne
f

these FD's m ust hold. Pro

f

(if ) 1. An ything y

u

pro ject comes bac k in the join.

✦

Do esn't dep end

n

FD's. t 1 t 2 Z X X Y Z X Y t 15

SLIDE 16 2. An ything that comes bac k in the join w as in the

riginal

X Y Z . Z X Z X Y X Y r t 1 s 2 t s

Notice

that t 1 and s 2 agree

n

X .

If

X ! Y , then r = t.

If

X ! Z , then r = s.

Either

w a y , r is in

riginal

X Y Z . 16

SLIDE 17 Pro

f

(only-if ) If neither X ! Y nor X ! Z holds, then w e can nd an example X Y Z relation where the pro ject- join returns to

m

uc h. Z X Y z 1 x y 1 z 2 x y 2 Z X z 1 x z 2 x X Y x y 1 x y 2 Z X Y z 1 x y 1 z 1 x y 2 z 2 x y 1 z 2 x y 2 17