Re: How to normalize this?

On Monday, May 6, 2013 5:44:46 PM UTC+2, Jan Hidders wrote:
> On 2013-05-06 15:19:36 +0000, nvitacolonna_at_gmail.com said:

> > One exercise that I usually give to my students seems to fit exactly
> > the above. First, I ask them to prove that the schema R(A,B,C,D,E) with
> > FDs F = {AB -> C, C -> D, D -> B, E ->D} is not in 3NF (which they
> > usually do easily). Then I ask them to decompose it in 3NF and they
> > typically come out with:
>
> > R1(A,B,C), {AB -> C}
> > R2(C,D), {C -> D}
> > R3(D,B), {D -> B}
> > R4(D,E), {E -> D}
> > R5(A,E), {}
>
> > Finally I ask them: does this decomposition preserve FDs? (“yes, by
> > construction”). Is C -> B implied by F? (“yes, by transitivity”). Is C
> > -> B preserved or not in the decomposition? (panic) :)
>
> Precisely! So you have identified my "error". :-) The FD C-> B should
> apparently be added to T1, and so it is not in BCNF, only in 3NF. But
> why not allow it to be omitted? It is reintroduced anyway if we join
> everything back together again, and apparently we are fine if the
> resulting schema is a bit more liberal then the old schema anyway.

Very well said!

> what is the underlying rationale / philosophy here that dicttates that
> we include it in R1 and thereby prevents us from reaching BCNF?

I think the idea is that C -> B “applies” to R1(A,B,C) because it is defined on a subset of R1's attributes. We can preserve it “for free” by projecting it to R1, instead of indirectly by joining with other schemes, and one may argue that is usually what is wanted. But, honestly, my arguments have never convinced me too much :) I'm curious about your answer.

Nicola Received on Mon May 06 2013 - 19:33:51 CEST