Functional Dependencies
Functional Dependencies (FD) in the relational database management system occurs when one attribute in a relation uniquely determines other attribute in that relation. It describes the relation between the attributes. The term functional dependency was introduced by E.F Codd. It is used to define different normal forms and helps in preventing data redundancy.
For an attribute
X, there should exist only one value of Y. It means that X determines Y or Y is
functionally dependent on X.
A functional dependency of X on Y can be represented as X ? Y.
Here, Y is the dependent attribute, and X is the determinant set. Functional dependency
may also be based on the composite attributes of the relation.
For example:
Table: Student
student_id | Name | Course | Age |
101 | Amit | MCA | 20 |
102 | Kartikey | MBA | 21 |
103 | Krishna | MCA | 20 |
In the above table Student, if you know the value of student_id of any student which is unique for each student, you can obtain Name, Course, and age of that student with the help of that student_id.
By this, we can say that the Age, Name, and Course are functionally dependent on student_id, or we can say student_id determines Age, Name and Course of the Student.
student_id ? Name, Age, Course
Types of Functional Dependencies:
There are two types of functional dependencies:
- Trivial Functional Dependency
- Non-trivial Functional Dependency
1. Trivial Functional Dependency
X ? Y is said to be trivial functional dependency
when Y is a subset of X, i.e., Y ? X.
X ? X and Y ? Y are also trivial
functional dependency.
Example:
Table:
Student
student_id | Name |
101 | Amit |
102 | Kartikey |
103 | Krishna |
Above table consists of two columns: student_id and Name.
{student_id, Name} -> student_id is a trivial functional dependency as student_id is a subset of {student_id,Name}.
2. Non-trivial Functional Dependency
X ? Y is said to bea non-trivial functional dependency, if and only if Y is not a subset of X, i.e., Y ? X. It can also be defined as when at least one attribute in the right side of FD is not a part of the left side of FD.
Example:
Table:
Student
student_id | Name |
101 | Amit |
102 | Kartikey |
103 | Krishna |
Above table consists of two columns student_id and Name.
student_id -> Name is a non-trivial functional dependency as Name is not a subset of student_id
Advantages of Functional Dependency
- Functional Dependency removes data redundancy where the same values should not be repeated at multiple locations in the same database table.
- It maintains the quality of data in the database.
- FD allows clearly defined meanings and constraints of databases.
- It helps in identifying bad designs of the database.
- It expresses the facts about the database design.