Re: How to normalize this?

On Tuesday, May 7, 2013 3:38:18 PM UTC+2, Erwin wrote:
> Op dinsdag 7 mei 2013 11:54:47 UTC+2 schreef nvitac..._at_gmail.com het volgende:
>
> "Normalization theory based on functional dependencies says nothing about the inter-relational constraints of the decomposition—and it can't, because normal forms are defined wrt a single relational schema."
>
> Well, that is not entirely true, is it ?
>
> I'm willing to bet that many a course in database normalization will effectively mention something along the lines of "and you have to introduce a foreign key constraint between the decomposed tables". Date does it too in "Introduction to Database Systems", 8ed. !
>
> Why is that ????????
>
> And why is it that only one of the possible two inclusion dependencies are typically ever mentioned/considered ??????? Why is it that "spurious tuples" must indeed be prevented, through the IND, from appearing in R(abcd), but that "spurious tuples" must _not_ be prevented from appearing in S(abe) or in T(bce) ???????
>
> Because "usually", it is exactly what is desired anyway ???????? I'm inclined to agree, pragmatically, but I cannot accept that a "true scientist" would content himself with that answer. A "true scientist" would set out to seek, formally and precisely, when _exactly_ "usually" is indeed the case, and when exactly it is not.

The reasons why you “have to” introduce foreign key constraints after decomposing are the same as why you “have to” introduce functional dependencies in the first place: it's because the semantics of the data requires it.

Normalization does not provide an answer to the questions you pose: you “see”, by looking carefully at the meaning of the data, that some inter-relational constraints must hold much in the same way as you “see” that some FDs must hold (unless, of course, you insist that the decomposition must be “information-equivalent” to the original schema, in which case, you may probably infer such constraints automatically).

> I would love to discuss "database normal forms". As explicitly opposed to "relation normal forms".

Give me some time to refresh my mind on the topic ;)

Nicola Received on Tue May 07 2013 - 16:10:43 CEST