[PDF] - But y ou can't c hec k the FD ! in AB C these PDF Document

SLIDE 1 3NF One FD structure causes problems:

If

y

u

decomp

se,

y

u

can't c hec k the FD's in the decomp

sed

relations.

If

y

u

don't decomp

se,

y

u

violate BCNF. Abstractly: AB ! C and C ! B .

In

b

k:

title city ! theatre and theatre ! city.

Another

example: street city ! zip, zip ! city. Keys: fA; B g and fA; C g, but C ! B has a left side not a sup erk ey .

Suggests

decomp

sition

in to B C and AC .

✦

But y

u

can't c hec k the FD AB ! C in these relations. 1

SLIDE 2 Example A = street, B = city, C = zip. street zip 545 T ec h Sq. 02138 545 T ec h Sq. 02139 cit y zip Cam bridge 02138 Cam bridge 02139 Join: cit y street zip Cam bridge 545 T ec h Sq. 02138 Cam bridge 545 T ec h Sq. 02139 2

SLIDE 3 \Elegan t" W

rk

around Dene the problem a w a y .

A

relation R is in 3NF i for ev ery non trivial FD X ! A, either: 1. X is a sup erk ey ,

r

2. A is prime = mem b er

f

at least

ne

k ey .

Th

us, the canonical problem go es a w a y: y

u

don't ha v e to decomp

se

b ecause all attributes are prime and therefore no 3NF violati

ns

can

ccur.

3

SLIDE 4 T aking Adv an tage

f

3NF Theorem: F

r

an y relation R and set

f

FD's F , w e can nd a decomp

sition
f

R in to 3NF relations, suc h that if the decomp

sed

relations satisfy their pro jected dep endencies from F , then their join will satisfy F itself.

In

fact, with some more eort, w e can guaran tee that the decomp

sition

is also \lossless"; i.e., the join

f

the pro jections

f

R

n

to the decomp

sed

relations is alw a ys R itself, just as for the BCNF decomp

sition.
But

what w e giv e up is absolute absence

f

redundancy due to FD's.

The

\ob vious" approac h

f

doing a BCNF decomp

sition,

but stopping when a relation sc hema is in 3NF, do esn't alw a ys w

rk

| it migh t still allo w some FD's to get lost. 4

SLIDE 5 Roadmap 1. Study minimal sets

f

FD's: needed for the decomp

sitions.

✦

Requires study

f

when t w

sets
f

FD's are e quivalent, in the sense that they are satised b y exactly the same relation instances. 2. Giv e the algorithm for constructing a decomp

sition

in to 3NF sc hemas that preserv es all FD's.

✦

Called the synthesis algorithm. 3. Sho w ho w to mo dify this construction to guaran tee losslessness. 5

SLIDE 6 3NF Syn thesis Algorithm Roughly , w e create for eac h FD in F a relation con taining

nly

its attributes.

But:

w e need rst to mak e F minimal in the sense that: a) No FD can b e eliminated from F . b) No attribute can b e eliminated from a left side

f

an FD

f

F .

Note

that minimal sets

f

FD's are not necessarily unique. Equiv alen t Sets

f

FD's FD sets are e quivalent if they eac h deriv e the

ther,

i.e., if they allo w the same set

f

relation instances.

F
r

eac h

f

(a) and (b) in the denition

f

\minimalit y ," w e mean \without making a set

f

FD's inequiv al en t to F ." 6

SLIDE 7 T esting Equiv alence X 1 ! A1 Y 1 ! B 1 X 2 ! A2 Y 2 ! B 2

X

n ! An Y m ! B m

F
r

eac h i, Y i ! B i m ust follo w from the set

n

the left.

✦

i.e, (Y i) + m ust con tain B i, when closure is computed using the FD's

n

the left.

Also,

eac h X i ! Ai m ust follo w from the set

n

the righ t.

Imp
rtan

t sp ecial case: no need to c hec k an FD that app ears in b

th

sets. 7

SLIDE 8 Example Supp

se

F has A ! B , B ! C , and AC ! D .

F

is not minimal.

F

1 with A ! B , B ! C , and A ! D is minimal.

✦

Note that from F w e can infer A ! D , and from F 1 w e can infer AC ! D .

F

2 consisting

f

A ! B , B ! C and C ! D is not equiv al en t to F .

✦

Note y

u

cannot infer C ! D from F . 8

SLIDE 9 A Dep endency-Preserving Decomp

sition

1. Minimize the giv en set

f

dep endencies. 2. Create a relation with sc hema X Y for eac h FD X ! Y . 3. Eliminate a relation sc hema that is a subset

f

another. 4. Add in a relation sc hema with all attributes that are not part

f

any FD. 9

SLIDE 10 Example

Start

with R = AB C D and F consisting

f

A ! B , B ! C , and AC ! D .

F

1 with A ! B , B ! C , and A ! D is a minimal equiv alen t.

With

F 1 as

ur

minimal set

f

FD's, w e get database sc hema AB , B C , and AD , whic h is sucien t to c hec k F 1 and therefore F . 10

SLIDE 11 Dep endency Preserv ation with Losslessness Same as for just dep endency preserv ation, but add in a relation sc hema consisting

f

a k ey for R . Example In ab

v

e example, A is a k ey for R , so w e should add A as a relation sc hema. Ho w ev er, A is a subset

f

AB , and so nothing is needed; the

riginal

database sc hema fAB ; B C ; AD g is lossless. Not Co v ered

Wh

y basing the decomp

sition
n

a minimal equiv ale n t set

f

FD's guaran tees 3NF.

Wh

y the k ey + FD's syn thesis approac h guaran tees losslessness. 11

SLIDE 12 Multiv alued Dep endencies Consider the relation Drinkers(name, addr, phone, beersLiked), with the FD name ! addr. That is, drink ers can ha v e sev eral phones and lik e sev eral b eers. T ypical relation: name addr phone b eersLik ed jo e a p1 b1 jo e a p1 b2 jo e a p1 b3 jo e a p2 b1 jo e a p2 b2 jo e a p2 b3

Key

= fname, phone, beersLikedg.

BCNF

violati

n:

name ! addr. Decomp

se

in to D1(name, addr), D2(name, phone, beersLiked).

✦

Both are in BCNF. 12

SLIDE 13

But

lo

k

at D 2: name phone b eersLik ed jo e p1 b1 jo e p1 b2 jo e p1 b3 jo e p2 b1 jo e p2 b2 jo e p2 b3

The

phones and b eers are eac h rep eated.

✦

If Jo e had n phones and lik ed m b eers, there w

uld

b e nm tuples for Jo e, when max(n; m) should b e enough. 13

SLIDE 14 Multiv alued Dep endencies The multivalue d dep endency X ! ! Y holds in a relation R if whenev er w e ha v e t w

tuples
f

R that agree in all the attributes

f

X , then w e can sw ap their Y comp

nen

ts and get t w

new

tuples that are also in R . X Y

thers

14

SLIDE 15 Example In Drinkers, w e ha v e MVD name ! ! phone. F

r

example: name addr phone b eersLik ed jo e a p1 b1 jo e a p2 b2 with phone comp

nen

ts sw app ed yields: name addr phone b eersLik ed jo e a p1 b2 jo e a p2 b1 whic h are also tuples

f

the relation.

Note:

w e m ust c hec k this condition for al l pairs

f

tuples that agree

n

name, not just

ne

pair. 15

SLIDE 16 MVD Rules 1. Ev ery FD is an MVD.

✦

Because if X ! Y , then sw apping Y 's do esn't create new tuples.

✦

Example, in Drinkers: name ! ! addr. 2. Complementation: if X ! ! Y , then X ! ! Z , where Z is all attributes not in X

r

Y .

✦

Example: since name ! ! phone holds in Drinkers, so do es name ! ! addr beersLiked. 16

SLIDE 17 Splitting Do esn't Hold Sometimes y

u

need to ha v e sev eral attributes

n

the left

f

an MVD. F

r

example: Drinkers(name, areaCode, phone, beersLiked, beerManf) name areaCo de phone BeersLik ed b eerManf Jo e 650 555-1111 Bud A.B. Jo e 650 555-1111 Wic k edAle P ete's Jo e 415 555-9999 Bud A.B. Jo e 415 555-9999 Wic k edAle P ete's

name

! ! areaCode phone holds, but neither name ! ! areaCode nor name ! ! phone do. 17

SLIDE 18 4NF Eliminate redundancy due to m ultiplicati v e eect

f

MVD's.

Roughly:

treat MVD's as FD's for decomp

sition,

but not for nding k eys.

F
rmally:

R is in F

urth

Normal F

rm

if whenev er MVD X ! ! Y is nontrivial (Y is not a subset

f

X , and X [ Y is not all attributes), then X is a sup erk ey .

✦

Remem b er, X ! Y implies X ! ! Y , so 4NF is more stringen t than BCNF.

Decomp
se

R , using 4NF violati

n

X ! ! Y , in to X Y and X [ (R

Y

). R X Y 18

SLIDE 19 Example Drinkers(name, addr, areaCode, phone, beersLiked, beerManf)

FD:

name ! addr

Non

trivial MVD's: name ! ! areaCode phone and name ! ! beersLiked beerManf.

Only

k ey: fname, areaCode, phone, beersLiked, beerManfg

All

three dep endencies violate 4NF.

Successiv

e decomp

sition

yields 4NF relations: D1(name, addr) D2(name, areaCode, phone) D3(name, beersLiked, beerManf) 19