Re: How to normalize this?
From: Jan Hidders <hidders_at_gmail.com>
Date: Tue, 30 Apr 2013 23:39:58 +0200
Message-ID: <51803a2d$0$6337$e4fe514c_at_dreader35.news.xs4all.nl>
>> On 2013-03-15 23:27:55 +0000, Erwin said:
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> 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
Date: Tue, 30 Apr 2013 23:39:58 +0200
Message-ID: <51803a2d$0$6337$e4fe514c_at_dreader35.news.xs4all.nl>
On 2013-04-30 14:21:47 +0000, Erwin said:
> 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
> > And an ugly set of constraints to control it. > > In particular, that at all times R1 JOIN R2 === R1 JOIN R3. > > No problem if you have CREATE ASSERTION (plus multiple assignment or > its inferior nephew deferred constraint checking), but if enforcement > is up to the programmer, it's not the simplest thing to achieve.
True. But on the other hand, this type of restriction is often an artifact of the initial table design and might not be a valid business rule per se. And the getting rid of it is actually a feature and not a bug.
- Jan Hidders