What Are Database Dependencies?

A dependency is a constraint that applies to or defines the relationship between attributes. It occurs in a database when information stored in the same database table uniquely determines other information stored in the same table. You can also describe this as a relationship where knowing the value of one attribute (or a set of attributes) is enough to tell you the value of another attribute (or set of attributes) in the same table.

Database dependencies often confuse both students and database professionals alike. Fortunately, they are not as complicated as they may seem. They can best be explained using a few examples. Here, we'll examine common database dependency types.

Database Dependencies and Functional Dependencies

Saying that there is a dependency between attributes in a table is the same as saying that there is a functional dependency between those attributes. If there is a dependency in a database such that attribute B is dependent upon attribute A, you would write this as:

A -> B

For example, in a table listing employee characteristics, including Social Security number (SSN) and name, it can be said that name is dependent upon SSN (or SSN -> name) because an employee's name can be uniquely determined from an SSN. However, the reverse statement (name -> SSN) is not true because more than one employee can have the same name but always have different SSNs.

Trivial Functional Dependencies

A trivial functional dependency occurs when you describe a functional dependency of an attribute on a collection of attributes that includes the original attribute. For example, {A, B} -> B is a trivial functional dependency, as is {name, SSN} -> SSN. This type of functional dependency is called trivial because it can be derived from common sense. It is obvious that if you already know the value of B, then the value of B can be uniquely determined by that knowledge.

Full Functional Dependencies

A full functional dependency occurs when you already meet the requirements for a functional dependency and the set of attributes on the left side of the functional dependency statement cannot be reduced any further. For example, {SSN, age} -> name is a functional dependency, but it is not a full functional dependency because you can remove age from the left side of the statement without impacting the dependency relationship.

Transitive Dependencies

Transitive dependencies occur when there is an indirect relationship that causes a functional dependency. For example, A -> C is a transitive dependency when it is true only because both A -> B and B -> C are true.

Multivalued Dependencies

Multivalued dependencies occur when the presence of one or more rows in a table implies the presence of one or more other rows in that same table. For example, imagine a car company that manufactures many models of car, but always makes both red and blue colors of each model. If you have a table that contains the model name, color, and year of each car the company manufactures, there is a multivalued dependency in that table. If there is a row for a certain model name and year in blue, there must also be a similar row corresponding to the red version of that same car.

Importance of Dependencies

Database dependencies are important to understand because they provide the basic building blocks used in database normalization, the process of efficiently organizing data in a database. For example:

• For a table to be in second normal form (2NF), there must be no case of a nonprime attribute in the table that is functionally dependent upon a subset of a candidate key.
• For a table to be in third normal form (3NF), every nonprime attribute must have a nontransitive functional dependency on every candidate key.
• For a table to be in Boyce-Codd Normal Form (BCNF), every functional dependency (other than trivial dependencies) must be on a superkey.
• For a table to be in fourth normal form (4NF), it must have no multivalued dependencies.