Re: functional dependencies

Dear Andre and Larry,

   Larry, let me quote a few lines from your recent posting.

"Yes, the primary key, by definition functionally determines  all non-key attributes in the tuple. The same is true for  any candidate key, not just the primary key."  

That is precisely what I am having trouble with. As I see it both functional dependencies and primary keys are seperate integrity constraints. So if in a FD say A -> B if we make A a primary key the whole thing degenerates to a primary key constraint and according to me it does not remain a FD anymore because by definition of FD we can have two tuples t1 and t2 with t1[A] = t2[A] but if A is primary key this will never happen. To make it more clear I have trouble accepting that a primary key functionally determines all non-key attributes.

I would like you to give me your opinion on the following.

"Aren't primary key constraints stronger and take precedence over FD's."

As far as the trivial FD is concerned it was not a home work question I was just curious about tem. I am a B.E. in Computer Engineering from University of Mumbai (India) and at present I am working for a Software firm that deals with Voice XML. I joined this group because I like and I am very much interested in databases.

Andre you wrote and I quote.
" But if there is an FD A->B where A is not a key attribute then the relation not normalised by definition: "The data shall depend on the key, the whole key and nothing but the key". "

Now I will give you an example which I know is not normalized. Consider a resource manager of a university where he procures various hardware items from companies gives them a unique id and allocates them to various projects which are headed by a single professor.

Create table item_allocation {
item_id varchar(10) primary key,
item_name varchar(20) NOT NULL,
project_name varchar(20) NOT NULL,
prof_name varchar(30) NOT NULL,
}

Now in this table we have an FD project_name -> prof_name where project_name is a non-prime attribute.
If we normalize the above table into

item_allocation1(item_id (PK) , item_name, project_name) projects(project_name, prof_name)

Now both tables are in normal forms but the FD that existed in item_allocation has got degenerated into primay key which is project_name.
So does the whole process of normalizing relations comes down to identifying FD's where the attribute that functionally determines the other one is non-prime in the present relation and then normalizing it so that it (the attribute) becomes a primay key in the new relation? If so then I believe we will have FD's only in theory but we will never ever have FD's in practice.

Regards,
Rohan.

Larry Coon <larry_at_assist.org> wrote in message news:<3EE75E05.3D24_at_assist.org>...
> Rohan Hathiwala wrote:
> >
> > Dear Adre and Larry
> > I would like to thank you for the feedback you have given me
> > and I am sorry I did not realize that the example I gave was
> > not in 3NF at that time. Now coming to the normalization that
> > Andre did to my example and agree that it is correct. Now we have
> > 2 FD's
> > project_name -> prof_name
> > prof_name -> course_taught
> >
> > Now let me quote the definition of an FD as given in Korth's book
> > "In a relation R if we have 2 attributes A and B and suppose an
> > FD exists i.e A -> B then for any two tuples t1 and t2 in
> > R if t1[A] = t2[A] the we must have t1[B] = t2[B]"
>
> Yes, and Date has a similar definition:
>
> Let R be a relation avariable, and let X and Y be
> arbitrary subsets of the set of attributes of R.
> Then we say that Y is functionally dependent on X,
> in symbols X -> Y (read "X functionally determines
> Y," or simply "X arrow Y") -- if and only if, in
> every possible legal value of R, each X-value has
> associated with it precisely one Y-value.
>
> So both are saying that for the set of all possible valid
> tuples in R, if any two tuples that have the same value
> for X also have the same value for Y, then X -> Y.
>
> > Now if we make A as the primary key then we would never ever
> > have any 2 tuples in t1 and t2 such that t1[A] = t2[A] because
> > it would violate the primary key constraint so does it make
> > sense in this case say that A funcationally determines B since
> > for and FD like A -> B to exist we should also have a situation
> > where we can have t1[A] = t2[A] as per definition of FD and I
> > repeat this would never happen if we make A a primary key.
>
> Yes, the primary key, by definition functionally determines
> all non-key attributes in the tuple. The same is true for
> any candidate key, not just the primary key.
>
> > Now let me rephrase my original question (in quotes).
> > "Give me an example of a relation where there is an FD where the
> > attributes invloved are not key attributes and also see to that
> > it does not violate any of the normal forms."
>
> You just jumped from the more precise "primary key" to the
> more vague "key attributes," so I'm afraid it's still not
> clear exactly what you're asking for. If by "key attributes"
> you're still referring specifically to the primary key,
> then this is easy.
>
> > I would also like to post another question at this point.
> > Can any one give me an example of a relation where we have a
> > trivial FD?
> > A trivial FD is of the form
> > A -> B where B is a subset of A.
>
> To quote Date again, "Trivial dependencies are not very
> interesting in practice." Is this a homework question?
>
>
> Larry Coon
> University of California
> larry_at_assist.org
> and lmcoon_at_home.com
Received on Thu Jun 12 2003 - 08:48:26 CEST