SQL Database Design » 14. Normalization » 14.4 Functional Dependencies

14.4 Functional Dependencies

The second and third normal forms rely on a fact called a functional dependency. A functional dependency between two columns of a table exists, if the values of one column can be concluded from another column. This simply means, when there is one value in the one column, you can derive the value for the other column, which marks redundant data. Regarding this, Pascal writes: a dependency relationship between columns is, essentially, an integrity constraint on the values of the dependent column, representing a business rule in the real world ...; it constrains the values of the dependent column to conform with the dependency, that is, the column on which is depended on.

"So what?" you might ask. The point about functional dependencies is, that full normalization .. makes the database easier to understand and query. There are quite a few queries that become unnecessarily complicated when compared to (further) normalized designs. There are other queries, that are impossible to write. Ignoring normalization issues usually returns when you need it the least: in the middle of implementation. When expressing already complicated issues, badly normalized designs further add to the pain and you wished you had designed it better up-front instead of patching your way through SQL selects, which also takes its toll on performance.

If a table represents one entity type, all non-key attributes belong to the whole key. Pascal further states: When a table represents one entity type, the only dependencies are those of nonkey columns on the key column(s). If the key constraint, uniqueness, is enforced, key values will never repeat and, thus, neither will the dependent nonkey values, which means there will be no redundancy due to dependency ... . In other words, with fully normalized tables, enforcing the key constraint is all that is required to guarantee no redundancy and no inconsistencies.

But what if a table does not represent one entity type? Well, the 1NF precondition eliminated all obvious entity types from a table, but you cannot assume full normalization yet. Thus, the original author had to make a case differentiation. Because the key values are unique and the non-key values basically are not, there cannot be any direct functional dependencies between the whole key and any non-key columns. Redundancies in the table can only occurr between non-key columns and sub-key columns. This is, because the non-key columns may contain duplicates, which keys cannot - only parts of a key. This is what functional dependencies is all about, as Pascal writes:

... Conversely, in tables that are not fully normalized - that is, tables that represent mixed entity types - dependencies are not on the key, or not directly on the key, or not on the whole key and therefore

  • Entities of one type cannot be added or dropped independently of entities of another type, causing possible insert/delete anomalies.

  • Values of the independent columns are not unique; they can repeat and cause the dependent values to repeat, causing redundancy ... and thus potential update anomalies.

In such cases, the key constraint is not sufficient to prevent redundancy and database inconsistencies.

Functional dependencies may cause insert, delete, and update anomalies, which could be avoided when further normalizing tables. Insert anomalies occur when certain attributes cannot be inserted into the database without the presence of other attributes. Its opposite, delete anomalies, exist when certain attributes are lost because of the deletion of other attributes. Insert and delete anomalies occurr because of the bundling of actually distinct entity types. Update anomalies exist when one or more instances of duplicated data is updated, but not all. For example, when a last name must be changed due to marriage, it requires several rows to be updated. For more examples of anomalies, follow this link.

Pascal finalizes the topic of functional dependencies by writing: There are several types of column dependencies. The correct design rules, or the further normalization (redesign) rules to eliminate those dependencies, are governed by dependency theory .. .:

Dependency theory formalizes what is rather intuitive to knowledgable database designers. It is "nothing more than formalized common sense. [But] the whole point of the theory underlying this area is to try and identify common sense principles and formalize them ... then we can mechanize [them] ... write a program and get the machine to do the work [enforce integrity] ... criticy of normalization usually miss this point; they claim (quite rightly) that the ideas are basically common sense, but they typically do not realize that it is a significant achievement to state what 'common sense' means in a precise and formal way".

Note, that the described anomalies are properties of functional dependencies and not just a specific normal form. It is the type of functional dependency that characterizes the following normal forms.

Last updated: 2010-08-04