Functional Dependency – Before moving ahead, let’s read Cost Based Query Optimization.
This article is about Functional Dependency which describes what is Functional Dependency? And what is the condition to define Functional Dependency.
Table of Contents
Functional Dependency
Functional Dependency is the relationship between two attributes or variables. It typically exists between the primary key (considered as X) and non-key (considered as Y) attribute within a table.
X → Y
The left side of an arrow is called the determinant and the right side is dependent.
It is clear that X → Y means that attribute Y is dependent on attribute X.
For example, we have a table named Class with attributes ID, Name, Roll_No.
So with the help of a unique attribute ID, it is easy to find the attribute values of Name & Roll_No associated with that unique ID.
Functional Dependency is written as
ID → Name
ID → Roll_No
Therefore we can say that attribute Name & Roll_No is dependent on attribute ID.
Types of Functional Dependency
Trivial Functional Dependency
ID → Name has trivial functional dependency if attribute Name is a subset of an attribute ID.
The following dependencies are also trivial like ID → ID, Name → Name
Example:
Consider a table with two columns ID and Name.
{ID, Name} → ID is a trivial functional dependency as ID is a subset of {ID, Name}.
Non-trivial Functional Dependency
ID → Name has trivial functional dependency if attribute Name is not a subset of an attribute ID.
When ID intersection Name is NULL, then ID → Name is called a complete non-trivial.
ID → Name
Name → Roll_No
Let’s consider a table and understand it through an example.
Table: Student_data
Roll_No | Name | Class | Marks | Subject |
01 | A | 10th | 98 | Eco |
02 | B | 10th | 75 | Eco |
03 | A | 10th | 98 | Math |
04 | B | 12th | 75 | Eng |
05 | C | 12th | 90 | Eng |
06 | D | 11th | 90 | Math |
07 | A | 10th | 98 | Eco |
If the value doesn’t repeat of the attribute:
Example: Check whether the functional dependency of Roll_No → Name exits or not.
Like Roll_No → Name
01 → A
03 → A
06 → D
Considered attribute Roll_No as X and attribute Name as Y.
Since the attribute Roll_No contains distinct values, there is no need to check for the value of the attribute Y.As per the rule of functional dependency, the value of Primary Key values must be distinct. Here attribute Roll_No as X (Primary Key) values are distinct; therefore, it is true that Roll_No → Name.
Example: Check whether the functional dependency of Roll_No → Class exits or not.
Like Roll_No → Class
01 → 10th
02 → 10th
04 → 12th
Considered attribute Roll_No as X and attribute Name as Y.
Since the attribute Roll_No contains distinct values, there is no need to check for the value of the attribute Y.
As per the rule of functional dependency, the value of Primary Key values must be distinct. Here attribute Roll_No as X (Primary Key) values are distinct; therefore, it is true that Roll_No → Class.
Note: If we find that the value of attribute X (Primary Key) has distinct values then no need to check the values of attribute Y (Non-primary key) as functional dependency X → Y is true.
Example: Check whether the functional dependency of Roll_No, Class → Marks exits or not.
Like Roll_No, Class → Marks
01, 10th → 98
02, 10th → 75
04, 12th → 75
Let a set of attributes Roll_No, Class considered as X and Marks as Y.
Since, attribute X contains distinct values, therefore, is no need to check for the value of the attribute Y.
If the value repetition of the attribute:
Formula:
If X = X
Then Y = Y
Note: If the value of the attribute X is same then only we have to check for attribute value of Y, else there are no functional dependency exits.
Example: Check whether the functional dependency of Name → Roll_No exits or not.
Like Name → Roll_No
A → 01
A → 03
Since the attribute Roll_No contains different values, therefore, there is no functional dependency exits for attribute Name → Roll_No.
Considered attribute Roll_No as X and attribute Name as Y.
As per the rule of functional dependency, the value of Primary Key values and non-primary Key values must be the same. Here attribute Name as X (Primary Key) values are the same, but the value of attribute Y (non-primary Key) is not the same; therefore, it is not true that Name → Roll_No.
In the above example, only one condition is true X = X but Y != Y; therefore, no functional dependency is found.
Example: Check whether the functional dependency of Class → Roll_No exits or not.
Like Class → Roll_No
10th → 01
10th → 02
Since the attribute Roll_No contains different values, there are no functional dependency exits for attribute Class → Roll_No.
Considered attribute Class as X and attribute Roll_No Y.
As per the rule of functional dependency, the value of Primary Key values and non-primary Key values must be the same. Here attribute Class as X (Primary Key) values are the same, but the value of attribute Y (non-primary Key) is not the same; therefore, it is not true that Class → Roll_No.
In the above example, only one condition is true X = X but Y != Y; therefore, no functional dependency is found.
Example: Check whether the functional dependency of Class → Subject exits or not.
Like Class → Subject
10th → Eco
10th → Eco
Since attributes Class and Subjects contain the same values, there are functional dependency exits for attributes Class → Subject.
Considered attribute Class as X and attribute Roll_No Y.
As per the rule of functional dependency, the value of X and Y values must be the same if X = X. Here, attribute Class as X (Primary Key) values are the same as Y = Y; therefore, functional dependency Class → Subject exits.
Example: Check whether the functional dependency of Roll_No, Class → Marks exits or not.
Like Roll_No, Class → Marks
01, 10th → 98
02, 10th → 75
04, 12th → 75
Let a set of attributes Roll_No, Class considered as X and Marks as Y.
Since the attribute X contains distinct values for each row, therefore, X != X so no need to check for the attribute Y, and therefore, it is not true that Roll_No, Class → Marks.
Example: Check whether the functional dependency of Name, Class → Subject exits or not.
Like Roll_No, Class → Subject
A, 10th → Eco
A, 10th → Eco
A, 10th → Eco
Let a set of attributes Name, Class considered as X and Subject as Y.
Since the attribute X contains the same values for each row, therefore, X = X, and we need to check for the value of the attribute Y, and since the value of the attribute Y is also the same therefore, it is true that Roll_No, Class → Subject.
If you find anything incorrect in the above-discussed topic and have further questions, please comment below.
Connect on:
