Partial Dependency (Databases)

rert588 picture rert588 · Sep 9, 2014 · Viewed 160.4k times · Source

I fabricated a definition that a partial dependency is when fields are indirectly dependent on the primary key or partially dependent but are also dependent on other keys that depend on the primary such that if the field which another field depends on is deleted then that field will still exist due to its dependence on the primary key. I am not sure if it is correct. I have researched and every definition sounds misleading. Is my definition correct and if not what is?

Answer

philipxy picture philipxy · Sep 13, 2014

A FD (functional dependency) that holds in a relation is partial when removing one of the determining attributes gives a FD that holds in the relation. A FD that isn't partial is full.

Eg: If {A,B} → {C} but also {A} → {C} then {C} is partially functionally dependent on {A,B}.

Eg: Here's a relation value where that example condition holds. (A FD holds in a relation variable when it holds in every value that can arise.)

A  B  C
1  1  1
1  2  1
2  1  1

The non-trivial FDs that hold: {A,B} determines {C}, {B,C}, {A,C} & {A,B,C}; {A}, {B} & {} also determine {C}. Of those: {A,B} → {C} is partial per {A} → {C}, {B} → {C} & {} → {C}; {A} → {C} & {B} → {C} are partial per {} → {C}; the others are full.

A functional dependency X → Y is a full functional dependency if removal of any attribute A from X means that the dependency does not hold any more; that is, for any attribute A ε X, (X – {A}) does not functionally determine Y. A functional dependency X → Y is a partial dependency if some attribute A ε X can be removed from X and the dependency still holds; that is, for some A ε X, (X – {A}) → Y.

-- FUNDAMENTALS OF Database Systems SIXTH EDITION Ramez Elmasri & Navathe

Notice that whether a FD is full vs partial doesn't depend on CKs (candidate keys), let alone one CK that you might be calling the PK (primary key).

(A definition of 2NF is that every non-CK attribute is fully functionally determined by every CK. Observe that the only CK is {A,B} & the only non-CK attribute C is partially dependent on it so this value is not in 2NF & indeed it is the lossless join of components/projections onto {A,B} & {A,C}, onto {A,B} & {B,C} & onto {A,B} & {C}.)

(Beware that that textbook's definition of "transitive FD" does not define the same sort of thing as the standard definition of "transitive FD".)