DBMS - Functional Dependencies

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:

book_idbook_namegenreauthor_idauthor_name
1The HobbitFantasy1J.R.R. Tolkien
2Fire & BloodFantasy2George R.R. Martin
3The Anthropocene ReviewedNon-Fiction3John Green
4A Game of ThronesFantasy2George R.R. Martin
5The Fault in Our StarsFiction3John 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:

  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:

book_idbook_namegenreauthor_idauthor_name
1The HobbitFantasy1J.R.R. Tolkien
2Fire & BloodFantasy2George R.R. Martin
3The Anthropocene ReviewedNon-Fiction3John Green
4A Game of ThronesFantasy2George R.R. Martin
5The Fault in Our StarsFiction3John Green

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:

book_idbook_namegenreauthor_idauthor_name
1The HobbitFantasy1J.R.R. Tolkien
2Fire & BloodFantasy2George R.R. Martin
3The Anthropocene ReviewedNon-Fiction3John Green
4A Game of ThronesFantasy2George R.R. Martin
5The Fault in Our StarsFiction3John Green

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:

book_idbook_namegenreauthor_idauthor_name
1The HobbitFantasy1J.R.R. Tolkien
2Fire & BloodFantasy2George R.R. Martin
3The Anthropocene ReviewedNon-Fiction3John Green
4A Game of ThronesFantasy2George R.R. Martin
5The Fault in Our StarsFiction3John Green

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:

book_idbook_namegenreauthor_idauthor_name
1The HobbitFantasy1J.R.R. Tolkien
2Fire & BloodFantasy2George R.R. Martin
3The Anthropocene ReviewedNon-Fiction3John Green
4A Game of ThronesFantasy2George R.R. Martin
5The Fault in Our StarsFiction3John Green

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