Re: How to normalize this?

Op vrijdag 8 februari 2013 21:33:27 UTC+1 schreef hugoko..._at_gmail.com het volgende:
> Hi all,
>
>
>
> I've been thinking about a scenario for a few days now, and I still am not sure how this should be normalized. Maybe someone here can help?
>
>
>
> The first part is easy. I have five attributes (a, b, c, d, e) and the following FDs:
>
> 1. {a, b, c} --> d
>
> 2. {a, b, c} --> e
>
> Obviously, just a single relation with all five attributes and (a, b, c) as candidate key.
>
>
>
> But then I find that the second FD isn't full. It should be replaced with:
>
> 2a. {a, b} --> e
>
> 2b. {b, c} --> e
>
>
>
> The single relation now obviously violates 2NF. But how to replace it? The only option I see is to remove e from the first relation, and add a relation (a, b, e), with candidate key (a, b). But that does not represent FD 2b, and it allows extensions that would violate it. Nothing I could not fix with additional code in the RDBMS, but I don't like using code to prevent FD violations.
>
>
>
> Does anyone know a better way to normalize this set of FDs?
>
>
>
> Cheers,
>
> Hugo

You have run into the issue of dependency preservation.

No, there is no better way.

Moving to 3NF/BCNF makes at least one of the FDs inexpressible, meaning it has to be reinstated as a constraint, and SQL has no support for enforcing that kind of constraint declaratively, meaning that enforcing it needs code.

That's how simple the "solution" is. Received on Sat Mar 16 2013 - 00:27:55 CET