Search code examples
databasedatabase-designrelational-databasethird-normal-form

3nf functional dependency in wiki's example

I read the wiki about the 3nf https://en.wikipedia.org/wiki/Third_normal_form

it is the example that wiki give

Tournament Winners
Tournament              Year    Winner          Winner Date of Birth
Indiana Invitational    1998    Al Fredrickson  21 July 1975
Cleveland Open          1999    Bob Albertson   28 September 1968
Des Moines Masters      1999    Al Fredrickson  21 July 1975
Indiana Invitational    1999    Chip Masterson  14 March 1977

it say that the non-prime attribute Winner Date of Birth is transitively dependent on the candidate key {Tournament, Year} via the non-prime attribute Winner

I think functional dependency is that 

for two row X1 , X2 if X1.col1 = X2.col1 and 
X1.col2 = X2.col2, then col1 -> col2

I cannot understand that Winner Date of Birth->Winner(there may be someone who have same birthday and same name?) and winner can -> candidate key {Tournament, Year} given the winner name Al Fredrickson, it may be Indiana Invitational 1998 or Des Moines Masters 1999)

So, how does it jump to the conclusion?

Solution

  • Informally, a functional dependency means one value on the left side cannot produce multiple values on the right, even when the left side exists in more than one row.1

    So, in Wikipedia example, there is a functional dependency Winner -> Winner Date of Birth, simply because the same winner cannot have different dates of birth even when he/she exists in multiple rows (because he/she won multiple tournaments).

    Since...

    • {Tournament, Year} -> Winner (since one tournament cannot have multiple winners)
    • and Winner -> Winner Date of Birth (as explained above)
    • and not Winner -> {Tournament, Year} (since one person can win multiple tournaments)

    ...then by definition there is a transitive dependency.

    I cannot understand that Winner Date of Birth->Winner(there may be someone who have same birthday and same name?)

    You flipped the direction. The functional dependency is not "from" the single value, it's "toward" it. Therefore Winner -> Winner Date of Birth, but not Winner Date of Birth -> Winner.

    BTW, There cannot be two people with different names in this model. A better (more realistic) model would probably use a surrogate key to identify people, allowing for duplicated names.

    1 Which is compliant with the mathematical concept of "function". No matter how many times you "call" a function (i.e. how many rows contain the f.d. left side), it always produces the same "result" (the f.d. right side). If it could produce multiple results, it would not be a function, it would be a "relation".