Database Dependencies

 

Functional Dependencies

A dependency 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.

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 their SSN. However, the reverse statement (name -> SSN) is not true because more than one employee can have the same name but different SSNs.

 

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 farther. 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.

 

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.

 

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. For example:

• For a table to be in second normal form (2NF), there must be no case of a non-prime attribute in the table that is functionally dependendent upon a subset of a candidate key.
• For a table to be in third normal form (3NF), every non-prime attribute must have a non-transitive 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.

 

Blogger Labels: Database,Dependencies,Functional,dependency,information,relationship,example,employee,characteristics,Social,Number,statement,SSNs,Full,requirements,Transitive,Trivial,collection,knowledge,presence,version,Importance,normalization,subset,candidate,Boyce,Codd,Normal,Form,BCNF,fourth,upon