Re: How to normalize this?
Date: Tue, 7 May 2013 07:50:03 -0700 (PDT)
Message-ID: <1a7dbb79-1262-4d8a-9ac3-3430b01e0e81_at_googlegroups.com>
On Tuesday, May 7, 2013 4:07:49 PM UTC+2, Jan Hidders wrote:
> On 2013-05-07 13:22:55 +0000, nvitacolonna_at_gmail.com said:
> >> Normalization theory based on functional dependencies says nothing
> >> about the inter-relational constraints of the decomposition
>
> > “It says nothing about the inter-relational constraints of a database
> > schema” would be more precise. Here is another example:
>
> > R(A,B,C), {B -> A}
> > S(D,E), {D -> E}
>
> > R is not in 2NF, the only key being BC. Normalization theory would then
> > suggest that it must be decomposed. Now, suppose that, in addition to
> > the above, you impose R[B,C]⊆S[D,E] (a referential integrity constraint
> > from (B,C) to (D,E). Would you still decompose R? Or would you do
> > something else?
>
> R is then in BCNF, since B has become a key. So no splitting needed.
Sure. You need to look at the whole database schema to infer that. And, in the general case, there is no algorithm that is guaranteed to give you an answer! Data modeling is hard, isn't it? :)
The schema above could then be replaced by:
R(A,B), {B -> A}
plus R[B]⊆S[D] (a foreign key from B to D). Arguably, this schema is better because redundant information (the C attribute) has been removed.
Personally, I believe that this extension of normalization theory, including at least special cases of INDs, hasn't be fully understood yet (or, well, _I_ haven't fully understood it), among other reasons, I guess, because computational complexity results are discouraging. The papers I've mentioned give significant contributions, but I don't think that they are the final answer. And we are still considering only the most basic types of constraints… How lucky NoSQL users are, who don't need data modeling (e.g., http://www.couchbase.com/why-nosql/nosql-database)! :)
S(D,E), {D -> E}