DBMS - Functional Dependencies

Published on: October 29, 2022

Database

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 $\to$ and are read as "if A then B". For example, if we have a table with the following attributes:

book_id	book_name	genre	author_id	author_name
1	The Hobbit	Fantasy	1	J.R.R. Tolkien
2	Fire & Blood	Fantasy	2	George R.R. Martin
3	The Anthropocene Reviewed	Non-Fiction	3	John Green
4	A Game of Thrones	Fantasy	2	George R.R. Martin
5	The Fault in Our Stars	Fiction	3	John Green

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:

Axiom of Reflexivity: If B is a subset of A, then $A \to B$ .
Axiom of Augmentation: If $A \to B$ , then $A + C \to B + C$ .
Axiom of Transitivity: If $A \to B$ and $B \to C$ , then $A \to C$ .

Axiom of Reflexivity

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

Axiom of Augmentation

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

Axiom of Transitivity

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

Types of Functional Dependencies

There are four types of functional dependencies:

Trivial Functional Dependencies
Non-Trivial Functional Dependencies
Multivalued Dependencies
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 \to B$ , then B is a subset of A. For example, if we have a table with the following attributes:

book_id	book_name	genre	author_id	author_name
1	The Hobbit	Fantasy	1	J.R.R. Tolkien
2	Fire & Blood	Fantasy	2	George R.R. Martin
3	The Anthropocene Reviewed	Non-Fiction	3	John Green
4	A Game of Thrones	Fantasy	2	George R.R. Martin
5	The Fault in Our Stars	Fiction	3	John Green

Here, if we take the FD {book_id, title} $\to$ title, then title is a subset of book_id and title. This is a trivial functional dependency. We can also say that book_id $\to$ 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:

book_id	book_name	genre	author_id	author_name
1	The Hobbit	Fantasy	1	J.R.R. Tolkien
2	Fire & Blood	Fantasy	2	George R.R. Martin
3	The Anthropocene Reviewed	Non-Fiction	3	John Green
4	A Game of Thrones	Fantasy	2	George R.R. Martin
5	The Fault in Our Stars	Fiction	3	John Green

Here, if we take the FD {book_id, title} $\to$ 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 $\to$ {B, C}, then B and C are not dependent on each other. For example, if we have a table with the following attributes:

book_id	book_name	genre	author_id	author_name
1	The Hobbit	Fantasy	1	J.R.R. Tolkien
2	Fire & Blood	Fantasy	2	George R.R. Martin
3	The Anthropocene Reviewed	Non-Fiction	3	John Green
4	A Game of Thrones	Fantasy	2	George R.R. Martin
5	The Fault in Our Stars	Fiction	3	John Green

Here, if we take the FD book_id $\to$ {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 \to B$ and $B \to C$ , then $A \to C$ . For example, if we have a table with the following attributes:

book_id	book_name	genre	author_id	author_name
1	The Hobbit	Fantasy	1	J.R.R. Tolkien
2	Fire & Blood	Fantasy	2	George R.R. Martin
3	The Anthropocene Reviewed	Non-Fiction	3	John Green
4	A Game of Thrones	Fantasy	2	George R.R. Martin
5	The Fault in Our Stars	Fiction	3	John Green

Here, if we take the FD book_id $\to$ book_name and book_name $\to$ genre, then book_id $\to$ 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 \to 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/