Re: How to normalize this?

Op dinsdag 30 april 2013 16:21:47 UTC+2 schreef Erwin het volgende:
> Op zaterdag 16 maart 2013 12:08:12 UTC+1 schreef Jan Hidders het volgende:
>
> > On 2013-03-15 23:27:55 +0000, Erwin said:
>
> >
>
> >
>
> >
>
> > > 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.
>
> >
>
> >
>
> >
>
> > Actually, as was already pointed out, the usual dependency-preserving
>
> >
>
> > 3NF synthesis algorithm gives you the following decomposition:
>
> >
>
> >
>
> >
>
> > R1(a, b, c, d)
>
> >
>
> > R2(a, b, e)
>
> >
>
> > R3(b, c, e)
>
> >
>
> >
>
> >
>
> > And that is in fact in BCNF. But as was also already observed, there is
>
> >
>
> > possibly some redundancy here.
>
> >
>
> >
>
> >
>
> > -- Jan Hidders

On second thought.

I've made the same mistake a couple more times before (of claiming that "you can't preserve dependencies" when in fact it should have been "you can't preserve dependencies, not without _introducing_ redundancy"). Thanks for reminding me.

It's odd to think of normalization theory, which was basically devised for _eliminating_ redundancy, as sometimes "requiring" to _introduce_ it ... Received on Tue Apr 30 2013 - 16:41:41 CEST