1. Registered 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.

2. Moderator.
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,D

3. Registered 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....

4. Moderator.
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....
Yes, I was talking nonsense! I don't know the formal logic for manipulating FDs and got confused. I have to think of concrete examples to understand it...

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 cut-down 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!

5. Registered User
Join Date
Mar 2003
Posts
3
Thank you very much....