Results 1 to 5 of 5
Thread: Regarding Normalisation

030703, 10:39 #1Registered User
 Join Date
 Mar 2003
 Posts
 3
Regarding Normalisation
Hi.. I'm a student who study DB. I'm not very understand in normalistaion.
In a situation that A,B,C,D,E,F,G in a relation that the composite A,B,C,D is a key in the relation.
If it holds a partial dependency that
A>E,F B>E,F C>E,F C,D>G
After 2NF should decompose into 5 relation.
A>E,F B>E,F C>E,F C,D>G and A,B,C,D
should we still keep the relation A,B,C,D seems no more attribute determine by it. and A,B and C determine the same thing should we just join it into A,B,C>E,F instead of the relation I state just now...
Would you please kindly give me some advice how to overcome this kind of situation . thank you.

030703, 11:27 #2Moderator.
 Join Date
 Sep 2002
 Location
 UK
 Posts
 5,171
Re: Regarding Normalisation
By A > E,F you mean that E and F are functionally dependent on A, right? In other words, if you know the value of A then you know the value of E and F  yes?
You then say B > E,F and C > E,F
That must mean that A,B and C are all candidate keys for the relation {A,B,C,E,F}. You could choose one arbitrarily is the primary key, say A, then you have A>B,C,E,F
You also have C,D > G, i.e. {C,D} is the key for the relation {C,D,G}.
Note that since A>C, this is same as A,D> G so we could have the relation {A,D,G} instead with A,D as the key.
Now regarding {A,B,C,D}. Since A>B,C you can reduce it to just {A,D}, which is then just a projection of {A,D,G} so can be dispensed with.
So I think you will have:
{A,B,C,E,F} with key A
{A,D,G} with key A,DTony Andrews
http://tinyurl.com/tonyandrews

030703, 11:43 #3Registered User
 Join Date
 Mar 2003
 Posts
 3
Thanks for your explaination... it really give me an idea.. but i not understand A,B,C why I can choose any as a primary key becoz from my understanding candidate key must be able to identify the whole relation.
A>E,F B>E,F C>E,F but group together should be A,B,C>E,F
rather than either one can A>B,C,E,F or B>A,C,E,F
Becoz B and C is not determined by A....

030703, 12:50 #4Moderator.
 Join Date
 Sep 2002
 Location
 UK
 Posts
 5,171
Originally posted by icestone
Thanks for your explaination... it really give me an idea.. but i not understand A,B,C why I can choose any as a primary key becoz from my understanding candidate key must be able to identify the whole relation.
A>E,F B>E,F C>E,F but group together should be A,B,C>E,F
rather than either one can A>B,C,E,F or B>A,C,E,F
Becoz B and C is not determined by A....
I think the short answer is that {A,B,C,D} should be retained if it tells you something useful, and it could do.
I have had to use a cutdown and concrete example to work this out. Suppose we had the original relation {Employee, Job, Task, Dept, Manager, Priority} or {E,J,T,D,M,P} for short, with {E,J,T} as the key.
Suppose we also have the following FDs:
E > D,M
J > D,M
J,T > P
So we have the relations:
{E,D,M} key E
{J,D,M} key J
{J,T,P} key J,T
{E,J,T} key {E,J,T}
(I have a sneaking suspicion that there must be further depencies to be found, e.g. perhaps D > M, but I guess that's irrelevent here...)
Now although {E,J,T} is "all key", it may still say something of value  i.e. that Employee E works/has worked on Job J doing Task T.
I hope I'm nearer the mark this time!Tony Andrews
http://tinyurl.com/tonyandrews

031103, 14:39 #5Registered User
 Join Date
 Mar 2003
 Posts
 3
Thank you very much....
It solved my problem already.....