Re: How to normalize this?

On 2013-05-06 15:19:36 +0000, nvitacolonna_at_gmail.com said:

> On Monday, May 6, 2013 12:52:44 AM UTC+2, Jan Hidders wrote:
>

>>>>>> 1. Apply the 3NF normalization procedure we discussed earlier> >>>> 2. 
>>>>>> Include with each component only the FDs that generated that component
>> 
>>>>> It's not clear to me what you are describing or how it relates to>> > 
>>>>> arguing against stopping at 3nf.
>> 
>>>> I've described to you a normalization algorithm that seems to produce 
>>>> a>> schema in BCNF that is also lossless-join and dependency> >> 
>>>> preserving,>> but also has no inter-component dependencies. That's not> 
>>>> >> supposed to be>> possible according to the usual normalization> >> 
>>>> explanation. So what went>> wrong here?
>> 
>>> That's simply not true is it ?> > You are referring to the three-table 
>>> decomposition, right ?
>> 
>> No. I'm referring to the algorithm that is described by steps 1. and 2. 
>> above. It's an adapted variant of the usual algorithm that produced 
>> the> three-table decomposition. That older algorithm does not always 
>> produce> a schema in in BCNF. Mine seems to do so.

Please do. The water appears to be very nice today. :-)

> 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. So what is the underlying rationale / philosophy here that dicttates that we include it in R1 and thereby prevents us from reaching BCNF?

• Jan Hidders