Re: How to normalize this?

On 2013-05-05 19:32:35 +0000, Erwin said:

> Op zondag 5 mei 2013 10:10:49 UTC+2 schreef Jan Hidders het volgende:

>>> On Thursday, May 2, 2013 6:23:07 AM UTC-7, Jan Hidders wrote:

>>>> But most> textbooks I know are correct and clear on this and will 
>>>> state>> >> that the> goal is for example a lossless-join and>> >> 
>>>> dependency-preserving> decomposition.

>>> 

>>> Can you name some? Because I don't think books are clear about>> > 
>>> normalization within design, which involves generated predicates and>> 
>>> > constraint preservation. Normalization per se assumes the generated>> 
>>> > components are projections of the original, ie are suitably>> > 
>>> constrained, without addressing the management of those constraints 
>>> (eg>> > introducing FKs for lost FDs).

>>> 

>>>> 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.

> 
> 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.

> That one does have the inter-component rule that R1 JOIN R2 === R1 JOIN > R3 (as I pointed out), no ?

Only if you want to be information-equivalent but conventional normalization does not require that. It only aims at obtaining a lossless decomposition. What I am now pointing out is that if that is our goal, it seems actually possible to always normalize to BCNF without introducing inter-component dependencies. That cannot be right now, can it? So what then is exactly meant with the conventional claim that this is not possible?

> "What went wrong" imo is that your decomposition did not match the 
> original "spirit" of the operation.  In which relation schemas get 
> split in _exactly_ two, with the dependent attributes of the "small" FD 
> (bc->e) moved _out of_ the schema.  your three-way split is either not 
> a split in exactly two, or it is two subsequent splits-in-two where the 
> first does _not_ move any attribute "out of the schema" (precisely 
> because it is still needed for the second split).

Note that I was applying one of the usual textbook 3NF normalization algorithms. Normalizing stepwise with pair-wise splits is not the only option and will in fact sometimes not allow you to find a perfectly valid 3NF decomposiition.

• Jan Hidders