Re: How to normalize this?
From: Jan Hidders <hidders_at_gmail.com>
Date: Sat, 16 Mar 2013 12:08:12 +0100
Message-ID: <51445299$0$582$e4fe514c_at_dreader34.news.xs4all.nl>
>> 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
Date: Sat, 16 Mar 2013 12:08:12 +0100
Message-ID: <51445299$0$582$e4fe514c_at_dreader34.news.xs4all.nl>
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