Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    3

    Red face some fundamental concept of db design

    Hi, all

    I would like to ask some fundamental concept about the db design.

    1. what is functional dependence in normalization?

    2. can anyone give some examples on A->B, CD->E,etc. to illustrate the concept?

    Thank you for your attention.

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

    Re: some fundamental concept of db design

    Originally posted by winyun
    Hi, all

    I would like to ask some fundamental concept about the db design.

    1. what is functional dependence in normalization?

    2. can anyone give some examples on A->B, CD->E,etc. to illustrate the concept?

    Thank you for your attention.
    While A->B, CD->E examples are used in theoretical discussions, I think the best way for a beginner to understand the concept of functional dependence is with realistic examples.

    Take the following attributes from an unnormalised relation:

    Employee No = 123
    Employee Name = Fred
    Employee DOB = 01-Jan-1970
    Dept No = 40
    Dept Name = Payroll

    This says that there is an Employee No 123 named Fred born on 01-Jan-1970, who work in Department 40 which is the Payroll department.

    Employee No uniquely identifies an employee
    Dept No uniquely identifies a department

    There is a functional dependency A->B if given a value for A there is only one corresponding value for B. It should be clear that the following functional dependencies apply:

    1) Employee No -> Employee Name, Employee DOB, Dept No, Dept Name
    2) Dept No -> Dept Name

    (i.e. given Employee No 123, there is only one corresponding Employee Name (Fred), Employee DOB (01-Jan-1970), Dept No (40) and Dept Name (Payroll); and further, given Dept No 40 there is only one corresponding Dept Name (Payroll).

    We can therefore normalise the data to:

    R1={Employee No, Employee Name, Employee DOB, Dept No}
    R2={Dept No, Dept Name}

    Note that Dept Name has been removed from R1 because it is not directly dependent on the key (Employee No), it is indirectly dependant via the Dept No. You can remove Dept Name from R1 without losing any information (because you still have Dept No and Dept No -> Dept name).

    Now if I replace the meaningful names with A,B etc. and say that for R={A,B,C,D,E}
    1) A -> B,C,D,E
    2) D -> E

    Then by same logic we can normalise to:

    R1={A,B,C,D}
    R2={D,E}

  3. #3
    Join Date
    Jul 2003
    Posts
    3
    Hi, andrewst

    Thank you for your explanation. However, I still got some confuses:

    In the Employee case, we know Employee No uniquely identifies an employee name, DOB, whatever. But what is the rules behind to set the relationship?

    for example in a more sophisticate situation:

    An organizer books the conference room for activities, he marked serveral time period of the individual days, then in the database there will be entries :

    Organizer RoomNo Date time
    ----------- ---------- ------------ --------------
    A Rm1 10-3-2002 9:00 - 12:00
    A Rm1 10-3-2002 14:00 - 15:30
    A Rm2 10-3-2002 16:00 - 18:00
    A Rm1 11-3-2002 9:00 - 12:00
    B Rm1 10-3-2002 19:00 - 21:00
    B Rm2 10-3-2002 8:00 - 10:00
    .
    .
    .
    so on and so on

    so now we have the database
    booking(Organizer, RoomNo, Date, time)

    but each filed in the database do have duplications, how can we determine the relationship between fields, and which one is functional dependence on the others.

    Thank you for your attention

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by winyun
    Hi, andrewst

    Thank you for your explanation. However, I still got some confuses:

    In the Employee case, we know Employee No uniquely identifies an employee name, DOB, whatever. But what is the rules behind to set the relationship?

    for example in a more sophisticate situation:

    An organizer books the conference room for activities, he marked serveral time period of the individual days, then in the database there will be entries :

    Organizer RoomNo Date time
    ----------- ---------- ------------ --------------
    A Rm1 10-3-2002 9:00 - 12:00
    A Rm1 10-3-2002 14:00 - 15:30
    A Rm2 10-3-2002 16:00 - 18:00
    A Rm1 11-3-2002 9:00 - 12:00
    B Rm1 10-3-2002 19:00 - 21:00
    B Rm2 10-3-2002 8:00 - 10:00
    .
    .
    .
    so on and so on

    so now we have the database
    booking(Organizer, RoomNo, Date, time)

    but each filed in the database do have duplications, how can we determine the relationship between fields, and which one is functional dependence on the others.

    Thank you for your attention
    Ah, yes. I nearly made this point in my previous post, but left it out in the end. There is no automatic process to work out the functional dependencies, you have to know those before you normalise. You may know them because they are "obvious" like Empno->EmpName or you may only find them out by asking searching questions of someone who knows the subject: "can there be 2 bookings for same room at same time on same date?" etc.

    You can DISPROVE purported FDs by inspecting data, e.g. if you see this:

    A B C
    1 2 3
    1 3 4

    then you can say that the FD A->B does NOT hold. However, the converse is not true. If you see this:

    A B C
    1 2 3
    2 2 3
    3 2 3
    4 1 3
    5 3 4

    you might suspect that B->C. The data is consistent with that. But then another record arrives:

    A B C
    6 1 9

    and DISPROVES the theory!

    So, you need to know the FDs to normalise: normalising does not tell you the FDs!

  5. #5
    Join Date
    Jul 2003
    Posts
    3

    Talking

    Andrewst

    Thanks a lot!

Posting Permissions

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