sql - FD theory and normal forms -

if have new

  r1 = {a,b,c,d} , r3 = {c,d,e,h}  

which know not in either bcnf or 3nf because candidate key not in fd. now, how can decompose both of them bcnf if functional dependencies are.

 f = {a->b,a->c,e->f,e->g,ae->h} 

to convert relation r , set of functional dependencies(fd's) 3nf can use bernstein's synthesis. apply bernstein's synthesis -

• first make sure given set of fd's minimal cover
• second take each fd , make own sub-schema.
• third try combine sub-schemas

for example in case:

r = {a,b,c,d,e,f,g,h,i} (according comment above)
fd's = {a->b,a->c,e->f,e->g,ae->h}

first check whether fd's minimal cover (singleton right-hand side , no extraneous left-hand side attribute, no redundant fd). in case given fd's minimal cover.

second make each fd own sub-schema. have - (the keys each relation in bold)

r₁={a,b}
r₂={a,c}
r₃={e,f}
r₄={e,g}
r₅={a,e,h}

third see if of sub-schemas can combined. see r₁ , r₂ can combined have same key. r₃ , r₄ can combined have same key. have -

s₁ = {a,b,c}
s₂ = {e,f,g}
s₃ = {a,e,h}

this in 3nf. check bcnf check if of these relations (s₁,s₂,s₃) violate conditions of bcnf (i.e. every functional dependency x->y left hand side (x) has be superkey) . in case none of these violate bcnf , hence decomposed bcnf.