Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Location
    Sweden
    Posts
    17

    Question About the third normal form and functional dependency

    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
    Attached Thumbnails Attached Thumbnails databasenvengelsk.jpg  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Location
    Sweden
    Posts
    17

    Unhappy Follow up question

    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

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Follow up question

    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.

  5. #5
    Join Date
    Apr 2004
    Location
    Sweden
    Posts
    17

    Red face Thanks!

    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

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Thanks!

    That's right.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •