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

[andrewst]

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}

[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

[andrewst]

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!

[winyun]

Andrewst

Thanks a lot!