Re: Newbie question about db normalization theory: redundant keys OK?

"Hugo Kornelis" <hugo_at_perFact.REMOVETHIS.info.INVALID> wrote in message news:32bbm31e3i3v5oiuf45ba59rsh0636o7pn_at_4ax.com...
> On Sun, 16 Dec 2007 08:48:25 -0000, David Portas wrote:
>
> Decomposing R1 would
> wreck the foreign key constraint between R1 and R2, that's why I wrote
> that it can't be nonloss decomposed.
>

A foreign key constraint has nothing to do with whether a relvar can be nonloss decomposed (at least not in any of my textbooks). A decomposition by projection is nonloss if the join of the projections equals the original relation.

Thus:

R1{a,b} JOIN R1{a,c} = R1

The foreign key constraint can still be enforced. It would have to apply to a derived relation (ie the join of the two decomposed relations) instead of directly to R1. That's something not supported by many SQL DBMSs. Constraints are usually only permitted on base tables, not on views or derived tables, which is a quite appaling limitation when you think about it.

-- 
David Portas