Functional Dependency in DBMS (Types and Examples)

Functional dependency in DBMS refers to the relationship between attributes in a database table. A functional dependency (FD) exists between two attributes when the value of one attribute (the determinant) determines the value of another attribute (the dependent).

For example, in a database table of “orders”, the attribute “Order ID” functionally determines the attribute “Customer ID”. This means that for a given “Order ID”, the corresponding “Customer ID” is unique and cannot be changed.

Functional dependencies are used to ensure data integrity and to maintain the consistency of a database. They also play a crucial role in database normalization, which is the process of organizing data in a database to reduce data redundancy and improve data integrity.

In formal terms, if X is a set of attributes, and Y is a set of attributes, a functional dependency X->Y exists if and only if, for any two tuples t1 and t2 in a relation R, if t1[X] = t2[X] then t1[Y] = t2[Y].

Functional dependencies can be represented using a notation called Armstrong’s notation, which is a set of functional dependencies represented by a set of FDs.

An example of Armstrong’s notation: {A->B, B->C, C->D}

This notation means that attribute A functionally determines attribute B, attribute B functionally determines attribute C and attribute C functionally determines attribute D.

The functional dependencies are only one aspect of database design, and they should be combined with other database design principles such as normalization and database modeling, to ensure that the database is efficient and robust.

Types of Functional Dependency

There are several types of functional dependencies in a DBMS:

Trivial functional dependency

A functional dependency is trivial if the dependent attribute is a subset of the determinant attribute.

For example, in a table “orders” the functional dependency (Order ID, Customer ID) -> (Customer ID) is trivial.

Non-trivial functional dependency

A functional dependency is non-trivial if the dependent attribute is not a subset of the determinant attribute.

For example, in a table “orders” the functional dependency (Order ID) -> (Customer ID) is non-trivial.

Full functional dependency

A functional dependency is full if a determinant attribute functionally determines all other attributes in a table.

For example, in a table “orders” (Order ID) -> (Order ID, Customer ID, Product ID, Quantity) is a full functional dependency.

Partial functional dependency

A functional dependency is partial if a determinant attribute functionally determines only some of the other attributes in a table.

For example, in a table “orders” (Order ID, Customer ID) -> (Product ID, Quantity) is a partial functional dependency.

Multi-valued dependency

A multi-valued dependency exists when an attribute functionally determines more than one non-key attribute.

For example, in a table “orders” (Order ID) -> (Customer ID, Product ID) is a multi-valued dependency.

Transitive dependency

A transitive dependency occurs when a non-key attribute functionally determines another non-key attribute.

For example, in a table “orders” (Product ID) -> (Price) and (Price) -> (Discount) is a transitive dependency.

The above mentioned types of functional dependencies are used in database design and normalization to ensure data integrity and consistency, and to minimize data redundancy.

Further Reading

Functional Dependency in DBMS (Types and Examples)