Search code examples
databaserelational-databasenormalizationdatabase-normalizationfunctional-dependencies

Relational DB - When looking to functional dependencies among non-key values, should I look to multi-dependence causers?

Ex: Imagine I have this database

A      //primary key
B
C
D

All fields depends functionally of the key.

No non-key field depends functionally of another non-key field.

But: D depends functionally of B and C.Should I consider this (dependence to multiple fields) when trying to achieve the third normal form?

Solution

  • Your question boils down to this: Are R1 and R2 the same?

    • R1( A BCD), A->BCD
    • R2( A BCD), A->BCD, BC->D

    Clearly they are different. R1 is in at least BCNF as it stands now; R2 has a transitive dependency, BC->D, so it is no higher than 2NF.

    We write attributes using uppercase letters, because uppercase letters represent sets in set theory. The relational model is based in part on set theory. So, in a real-world modeling job, A might represent a set of three columns.

    In your example, the fact that the left-and side of one functional dependency is represented by the single letter A, and the left-hand side of the other is represented by the two letters BC isn't important. Both those left-hand sides might represent two, three, or more columns in a relational table. Normalization through BCNF always takes into account every functional dependency you know about, no matter how many letters it takes to express it.

    You could even rewrite R2 like this, after doing E = BC. Doesn't change the transitive dependency at all.

    • R2( A ED), A->ED, E->D