Re: How to normalize this?

On Monday, May 6, 2013 10:05:57 PM UTC+2, nvitac..._at_gmail.com wrote:
> Il giorno lunedì 6 maggio 2013 20:27:36 UTC+2, Erwin ha scritto:
>
> > Op maandag 6 mei 2013 17:19:36 UTC+2 schreef nvitac..._at_gmail.com het volgende:
>
> >
>
> > > 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) :)
>
> >
>
> > > Nicola
>
> >
>
> > You've added a bit of another dimension.
>
> >
>
> > c->b is not an explicitly stated FD (not member of the canonical cover ???) but a "derivable" one.
>
>
>
> Yes, the set of FDs I have proposed is a canonical cover.
>
>
>
> > Does the issue of dependency preservation also apply to "derived"(/-able) FDs ? Should it ? Was it consciously so intended ?
>
>
>
> I'd say yes to all questions. The fact that a dependency is not explicitly stated doesn't make it less real. Besides, it's a good thing that we don't have to make all dependencies explicit: a set of FDs may well have an exponential number of logical consequences (although these may be pathological cases)!
>
>
>
> > If so, then what about the "derived" FDs that express what the candidate keys are in a relation schema (say, ae->abcde) ? What is _their_ preservation status in any decomposition ?
>
>
>
> Good point. Dependency preservation is about all dependencies, explicit or not; key dependencies are no different. As it has been accurately said by Jan Hidders, if you take the join of all the schemata of a dependency-preserving decomposition, the resulting relation satisfies all the original dependencies (explicit or not). In the particular example, if you take r1 join r2 join … join r5, AE -> ABCDE is always satisfied. in this sense, the key dependency is preserved.
>
>
>
> Formally, a decomposition is “dependency-preserving” if the union U of all the FDs associated to the decomposed schemata is _equivalent_ (not equal!) to the set F of FDs in the original schema. That is, every dependency in F _or derivable from F_ must be _derivable_ from U, and vice versa.
>
>
>
> The point with C -> B it is that it is not strictly needed as far as dependency preservation, as just defined, is concerned. So, Jan Hidders was asking, why would anyone insist in adding it to R1(A,B,C), making the schema be in 3NF instead of BCNF, when, after all, it is satisfied by the join of R1,…, R5 anyway?
>
>
>
> >Of course, if C->B effectively applies, then AB is no longer really the sole candidate key, since AC is a candidate key too, but even so the FD at hand "starts from" a proper subset of a candidate key, meaning it's not even 2NF
>
>
>
> No, R(A,B,C) with {AB -> C, C -> B} is the classical example of a schema that is in 3NF (because all attributes are prime, that is, belong to some key) but not in BNCF.

"why would anyone insist in adding it to R1(A,B,C), making the schema be in 3NF instead of BCNF, when, after all, it is satisfied by the join of R1,…, R5 anyway?"

I would say :

(a) because it's a rule that applies
(b) not enforcing a rule that applies means that "nonconforming data" can enter R1
(c) and that means that "nonconforming data" can be shown to the user anytime R1 is inspected without being joined with the four others.

That's what I call the "narrowed down vision of normalization theory". Decompositions are proposed, and properties of those decompositions are calculated and proven, but always under some apparent assumption that the decomposition will always be undone by always joining everything back together again.

That simply isn't how it (reality) works, is it ?

All those "dependency preservation" and "lossless" properties _depend_ on "all decompositions always being undone back again", and that's exactly what underpins that bold claim of mine (where I said "narrow and perverse meaning of the term" and "essentially useless").

"R(A,B,C) with {AB -> C, C -> B} is the classical example of ..."

Shit. So when Boyce & Codd "fixed" the definition of 3NF for coping with multiple keys, what they actually should have been addressing was 2NF as well :-) Received on Mon May 06 2013 - 22:56:48 CEST