Re: How to normalize this?

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. Received on Mon May 06 2013 - 22:05:57 CEST