Hi there!

I'm confused about functional dependency and the third normal form.
Therefor I have the following questions:

The Password-entity in the attached jpeg-image. Can one say that
the password is functional dependent on the username? And also
in the other direction; the username is functional dependent on
the password?

I believe it depends on whether how many usernames and passwords I
want the employees to have. In other words, if I make the decission to
only allow one username and one password to that username, there will
be a functional dependency...otherwise not....

Is the above (my assumptions) correct?

In other words I believe that I have to replace the password-table
with two new ones one for username and one for passwords to reach
the third normal form...

Is the above correct?

In the litterature I have found the following definiation of functional-
dependeny:

Let r be a relation (they mean table right?) and let X and Y be arbitary
subsets of attributes of r (they mean columns right?). Then we say that
Y is functional dependent on X if and only if each X value in r has associated
with it precisely one Y value in R. In other words, whenever two tuples (they mean rows right?)
of r agree on their X value, they also agree on their Y value.

That was the definition...and I think the password table in the attached file
seems to agree with it. I mean if I decide to only give the employees
one password and one username, two tuples will agree on username if they agree on password.

Please tell me if my reasoning is correct


Lots of thanks

/Marcus

functional dependence in normalization refers to situations between attributes and keys

in your example, there is a functional dependency of both username and password on the PK of that table

i.e. Employeenr


oh, and since Employeenr is a PK, it must be unique

therefore an employee can have only one username/password

Thanks r937

Yes you're right about that an employee only can have one username -
stupid me I didn't thought that far....

And yes there is a functional dependency of both username and password on the PK of that table....

Ok I'm just a student and not an SQl-consultant..... but from what I have read in the book "An Introduction to database systems" (Date 2000)... there are two requirements for a table to be in third normal form. The first one is as you say that the nonkey attributes are irreducibly dependent on the primary key. But the second requirement points out that the nonkey attributes also shall be mutually independent.

(two or more attributes are mutually independent if none of them is functionally dependent on any combination of the others. For instance the author gives an example where two nonkey attributes, city and status, are dependent on the primary key S#. But the status is also dependent of the city which is unwished....

So I mean can it not also be so that the two nonkey attributes in the table password, username and password are dependent on each other?

Please help me sort this out

/Marcus

Username is also a candidate key for that table, presumably (unless 2 or more people can share the same username?) In which case the Password depends on Username as well as on Employeenr. Nothing is dependent on Password - unless you intend that no 2 people shall have the same password, in which case that becomes a third candidate key.

So no, username and password cannot be dependent on each other unless both are keys for the table, in which case 3rd normal form is not violated.

Thanks andrewst!

I hadn't read the definition enough careful... I see know that there shall be no functional dependencies between nonprime-attributes. In other words I don't have to split up a table because of a candidate-key!..

Thats what you mean right?


Thanks again

That's right.