Functional Dependency in DBMS with examples

What is Functional Dependency in DBMS

In Relational Database Management Systems, the data object represents some Entities that are associated with other Entities via some Relationship. But, Relationship is not limited to Entities, it is also found among the attributes of the Entities. This is called Functional Dependency in DBMS.

Functional dependency is a database concept that describes the relationship between two fields in a database table. In simple terms, a functional dependency occurs when one field in a table (the dependent field) is related to another field in the same table (the independent field).

For example, consider a table that stores information about Passengers. The table might have fields for the Passenger's ID number, first name, and last name. In this table, the Passenger's ID number is the independent field, and the employee's last name and first name are the dependent fields.

This is because if we know Passenger’s ID number, we can determine the first name and last name associated with it. This can be written as:

P_ID -> FIRST_NAME and P_ID->LAST_NAME

Here, P_ID is the determinant, and FIRST_NAME and LAST_NAME are dependent. Thus, FIRST_NAME and LAST_NAME are Functionally Dependent on P_ID. Therefore, Functional Dependency in DBMS binds the two attributes using a relation.

Functional Dependency Set

A Functional Dependency Set of a relation (table in database) is the set of all Functional Dependencies present in the table. For example, the Functional Dependency Set for the above table will be:

{P_ID -> FIRST_NAME, P_ID->LAST_NAME }

Attribute Closure in DBMS

An Attribute Closure of an attribute is the set of all attributes which are functionally dependent on that attribute. For example, in the above case,   FIRST_NAME and LAST_NAME are functionally dependent on P_ID. Thus, we say that the set {FIRST_NAME, LAST_NAME} is the Attribute Closure of P_ID for the given relation.

Types of Functional Dependency in DBMS

Partial Functional Dependency

This occurs when the dependent field depends on a proper subset of the independent field. This is also known as the Trivial Functional Dependency. For example, the Functional Dependency in the Passenger table given by

P_ID,  FIRST_NAME -> FIRST_NAME  is a Partial Functional Dependency. This is because FIRST_NAME is the Subset of {P_ID,  FIRST_NAME}. So, it can be determined using a subset of Determinant i.e. {P_ID,  FIRST_NAME}.

Fully Functional Dependency

This Dependency occurs when the dependent field depends upon the whole set of independent fields. In our example,  P_ID->LAST_NAME  is a Fully Functional Dependency because LAST_NAME doesn’t belong to any proper subset of P_ID. This is also called Non-Trivial Functional Dependency.

Advantages of Functional Dependency

The Functional Dependency in DBMS allows us to create a less redundant database and establishing the relationship between the attributes assures data consistency and reduces the risk of errors while storing the information.  Not only this, but it also helps us to define the proper constraints.

For example, the Functional Dependency, Empolyee_ID-> Employee_Name shows that Employee Name is determined by Emmployee_ID. This means that the Employee name depends on Employee Id, so Employee Id must be a unique value and it should not be null. This is because, in case of null values or duplicate values, the Functional Dependency fails.