Re: functional dependencies

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]"

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.

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."

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.

Regards,
Rohan.

andrewst <member14183_at_dbforums.com> wrote in message news:<2984440.1055247639_at_dbforums.com>...
> Originally posted by Rohan Hathiwala
> > I already know what a FD is my original question was
> > something else, however coming to the person table example that
> > you have given me, I am not satisfied by it. The reason being that
> > when you include the date of birth attribute the age becomes a
> > derived arttribute and it is considerd bad practice to include
> > derived attributes in a relation refer to Database System Concepts.
> > Now I will give you an example that I think would illustrate
> > FD's well.
> > Consider a University where a Prof. is involved in several
> > different projects and so he is able to take only one course per
> > semester. Now for a pirticular sem if we make the following table
> >
> > create table Prof_activity{
> > project_name varchar(20) primary key,
> > prof_name varchar(20) NOT NULL,
> > course_taught varchar(20) NOT NULL
> > }
> >
> > In the above example we automatically have an FD i.e.
> > prof_name -> course_taught and the prof_name can repeate itself
> > across various tuples in the relation since he can be associated
> > with various projects. I donot know why anyone would want to make
> > the above table but it serves as a good example to explain a
> > functional dependency as in the above case the course_taught
> > attribute depends soley on the prof's personal interest and cannot
> > be derived from a mathematical function. I would like to have your
> > feedback on this.
> >
> I don't think anyone (myself included) understands what your question
> actually is. You know what a functional dependency is. You presumably
> know that your Prof_activity table example is not properly normalised
> because of the FD of course_taught on a non-key column. You know that
> Prof_name is FD on the Project_name column, which is the primary key, so
> that is OK. So presumably you know that the normalised version of your
> example would be something like:
>
> create table Project(
> project_name varchar(20) primary key,
> prof_name varchar(20) NOT NULL,
> );
> create table Professor(
> prof_name varchar(20) primary key,
> course_taught varchar(20) NOT NULL
> );
>
> ..because all non-key columns are now FD on the key columns.
>
> So what is your question?
Received on Wed Jun 11 2003 - 08:21:09 CEST