Updated June 28, 2023
Introduction to Functional Dependency in DBMS
The term functional dependency means the association between any two attributes. Typically, this relationship is demonstrated between the primary key and non-key attributes within the table of a Database Management System, where the non-key attribute is functionally dependent on the primary key attribute to operate. A Functional Dependency case in a table is termed as ‘Minimal’ if the non-key attribute has dependencies on the primary key attribute with the functional characteristics such as there is only one non-key attribute in the table, any change made in the primary key attribute brings in changes to the non-key attribute as well, and if any alteration made on the functional dependencies will affect the table contents of the primary key.
Syntax:
Functional dependency on any given table can be explained as,
X → YHere, the left side of the arrow is identified as a Determinant, while the right side of the arrow is identified as a Dependent. X will always be the primary key attribute, and Y will be any dependent non-key attribute from the same table as the primary key. This shows X primary key attribute is functionally dependent on the Y non-key attribute. In other words, If the column X attribute of the table uniquely identifies the column Y attribute of the same table, then the functional dependency of column Y on column X is symbolized as X → Y.
Types of Functional Dependency
The Scenario for Functional Dependencies can be classified into the below four types:
1. Multivalued Functional Dependency in DBMS
Multivalued Functional Dependency occurs when there is more than one independent attribute with multiple values in the same table. The Multivalued Dependency case is a complete limitation between two sets of attributes in the relationship of Functional Dependency. Specific row values must be present as a functional dependency connection. This can be represented as,
X → Y
X → Z
X → A,X, Y, Z, A are attributes of the same table, X being the primary key, and Y, Z, A are non-key attributes. Here Y, Z, A are functionally dependent on X and not dependent on each other.
For a better understanding, let us consider the below table,
|
Student_ID |
Student_Name | Dept | DOB |
| S_001 | Sname01 | Computer |
Jan-01 |
|
S_002 |
Sname02 | Maths | Mar-07 |
| S_003 | Sname03 | English |
Sept-11 |
In this example, Student_Name, Dept & DOB are not dependent on each other but are dependent on Student_ID. In terms of Functional Dependency, Student_ID is the determinant, and Student_Name, Dept; DOB are the dependents. Student_ID is the primary key here, while Student_Name, Dept, and DOB are non-key columns. Hence the dependence can be symbolized as,
Student_ID → Student_Name Student_ID → Dept Student_ID → DOB2. Trivial Functional Dependency in DBMS
The Trivial Functional Dependency is a set of attributes or columns known as trivial if the on-key-dependent attribute is a subset of the determinant attribute, which is a primary key attribute. This Trivial Functional Dependency scenario occurs when two columns form the primary key, one functionally dependent on the combined set.
X → Y, where is a trivial functional dependency, if Y is a subset of X. Let us consider the below table,
|
Student_ID |
Student_Name | Dept | DOB |
| S_001 | Sname01 | Computer |
Jan-01 |
|
S_002 |
Sname02 | Maths | Mar-07 |
| S_003 | Sname03 | English |
Sept-11 |
Here, if the primary key is a combination of the columns Student_ID and Student_Name, then the Student_Name column is in a Trivial Functional Dependency relationship with the primary key set [Student_ID, Student_Name]. Any changes in the Student_Name column will affect the primary key set [Student_ID, Student_Name], as the Student_Name column is a subset of the primary key attribute set. For a Student ID, S_001, the primary key combination will be [S_001, Sname01]. If a change to the name is made as Sname001, then the primary key combination will change as [S_001, Sname001], as the Student_Name column is a subset of the primary key.
3. Non-Trivial Functional Dependency in DBMS
A Non Trivial Functional Dependency is a normal functional dependency where the non-key attribute is functionally dependent on a primary key attribute without the occurrence of trivial functional dependency.
X → Y, where is a non-trivial functional dependency, if and only if Y is not a subset of X. Let us consider the below table,
|
Student_ID |
Student_Name | Dept | DOB |
| S_001 | Sname01 | Computer |
Jan-01 |
|
S_002 |
Sname02 | Maths | Mar-07 |
| S_003 | Sname03 | English |
Sept-11 |
Suppose the primary key is the column Student_ID, and the Student_Name column is not a subset of Student_ID. In that case, the Student_Name column is in a non Trivial Functional Dependency relationship with the primary key Student_ID.
4. Transitive Functional Dependency in DBMS
A Transitive Functional Dependency is a type of functional dependency that happens when the non-key attribute is indirectly formed by its functional dependencies on the primary key attributes. The value of the known factors can be the reason for this type of Functional Dependency occurrence. The Transitive Functional Dependency can only occur in relation to three or more non-key attributes that are functionally dependent on the primary key attribute.
Let us consider the below table to understand this,
|
Student_ID |
Student_Name |
Dept |
DOB |
|
S_0101_C |
Sname01 | Computer_C | 01-01-1999 |
| T_0307_M |
Tname02 |
Maths_M |
03-07-1998 |
|
U_0711_E |
Uname03 | English_E |
07-11-1997 |
In this table, the Student_ID column is the primary key. The values in the Student_ID column are formed by combining the first letter from the Student_Name column, the last code from the Dept column, and the date & month from the DOB column. If any change is made in any of these columns will reflect changes in the primary key column, that is, the Student_ID column. Any new record inserted in this table will also have a Student_ID value formed from combining the other three non-key columns.
Conclusion
Defining Functional Dependency between the columns is a crucial part of designing a table, as bad DBMS designs have plenty of disadvantages while querying and make it impossible to implement any potential upgrades. Functional dependency clarifies the values, helps maintain the data’s quality, and keeps the database clean without junk or repeating data.
Recommended Articles
We hope that this EDUCBA information on “Functional Dependency in DBMS” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
