DBMS - Functional Dependencies

Functional Dependencies

Functional dependencies are a way to describe the relationship between two or more attributes in a database table. Functional dependencies are denoted by the symbol -> and are read as "if A then B". For example, if we have a table with the following attributes:

We can say that book_id is functionally dependent on book_name. This means that if we know the book_id we can uniquely identify the book_name. Similarly, we can say that author_id is functionally dependent on author_name. This means that if we know the author_id we can uniquely identify the author_name.

Rules of Functional Dependencies

There are three rules that we need to follow when defining functional dependencies:

1. Axiom of Reflexivity: If B is a subset of A, then A -> B.
2. Axiom of Augmentation: If A -> B, then A + C -> B + C.
3. Axiom of Transitivity: If A -> B and B -> C, then A -> C.

Axiom of Reflexivity

The axiom of reflexivity states that if B is a subset of A, then A -> B. For example, a attribute address, which contains street, city and postal_code. We can say that address -> {street, city, postal_code}.

Axiom of Augmentation

The axiom of augmentation states that if A -> B, then A + C -> B + C. Axiom of augmentation is also known as partial dependency.

Axiom of Transitivity

The axiom of transitivity states that if A -> B and B -> C, then A -> C. Axiom of transitivity is also known as transitive dependency.

Types of Functional Dependencies

There are four types of functional dependencies:

1. Trivial Functional Dependencies
2. Non-Trivial Functional Dependencies
3. Multivalued Dependencies
4. Transitive Functional Dependencies

Trivial Functional Dependencies

Trivial functional dependencies are those where the dependent attribute is a subset of the determinant attribute. In other words, if A -> B, then B is a subset of A. For example, if we have a table with the following attributes:

Here, if we take the FD {book_id, title} -> title, then title is a subset of book_id and title. This is a trivial functional dependency. We can also say that book_id -> book_id is a trivial functional dependency.

Non-Trivial Functional Dependencies

Non-trivial functional dependencies are those where the dependent attribute is not a subset of the determinant attribute. In other words, if A -> B, then B is not a subset of A. For example, if we have a table with the following attributes:

Here, if we take the FD {book_id, title} -> genre, then genre is not a subset of book_id and title. This is a non-trivial functional dependency.

Multivalued Dependencies

Multivalued dependencies are those where the determinant attributes are not dependent on each other. In other words, if A -> {B, C}, then B and C are not dependent on each other. For example, if we have a table with the following attributes:

Here, if we take the FD book_id -> {book_name, genre}, then book_name and genre are not dependent on each other. This is a multivalued dependency.

Transitive Functional Dependencies

Transitive functional dependencies are those where the determinant attributes are dependent on each other. In other words, if A -> B and B -> C, then A -> C. For example, if we have a table with the following attributes:

Here, if we take the FD book_id -> book_name and book_name -> genre, then book_id -> genre. This is a transitive functional dependency.

Key Points

• A functional dependency is a relationship between two or more attributes of a relation.
• A functional dependency is represented as A -> B, where A is the determinant attribute and B is the dependent attribute.
• Functional dependencies follow three rules: Axiom of Reflexivity, Axiom of Augmentation and Axiom of Transitivity.
• There are four types of functional dependencies: 1. Trivial Functional Dependencies, 2. Non-Trivial Functional Dependencies, 3. Multivalued Dependencies and 4. Transitive Functional Dependencies.

References

• Armstrong's axioms. Wikipedia. Retrieved October 29, 2022, from https://en.wikipedia.org/wiki/Armstrong%27s_axioms
• Functional dependency. Wikipedia. Retrieved October 29, 2022, from https://en.wikipedia.org/wiki/Functional_dependency
• GitHub Copilot · Your AI pair programmer · GitHub. GitHub. Retrieved October 29, 2022, from https://github.com/features/copilot
• Peterson, R. (2022, August 25). Functional Dependency in DBMS: What is, Types and Examples. Guru99. Retrieved October 29, 2022, from https://www.guru99.com/dbms-functional-dependency.html
• Types of Functional dependencies in DBMS. (2021, August 21). GeeksforGeeks. Retrieved October 29, 2022, from https://www.geeksforgeeks.org/types-of-functional-dependencies-in-dbms/