Functional Dependency in DBMS

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 → Y

Here, 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 column X attribute of the table uniquely identifies the column Y attribute of the same table, then the functional dependency of the column Y on the 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 takes place in the conditions 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. It requires that certain row values can be present as a functional dependency connection. This can be represented as,

X → Y
X → Z
X → A,

Where X, Y, Z, A are attributes of the same table, X being the primary key and Y, Z, A is non- key attributes. Here Y, Z, A are functionally dependent on X, and not dependent on each other.

For better understanding, let us consider the below table,

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, 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 → DOB

2. Trivial Functional Dependency in DBMS

The Trivial Functional Dependency is a set of attributes or columns that are known a 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 the primary key is formed by two columns, and one of which is 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,

Here, if the primary key is a combination of the columns Student_ID and Student_Name, then the Student_Name column is in Trivial Functional Dependency relationship with the primary key set [Student_ID, Student_Name]. Any changes made in the Student_Name column will have its effects on 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,

Here, if the primary key is the column Student_ID, and the Student_Name column is not a subset of Student_ID, then 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 which happens when the non- key attribute is indirectly formed by its functional dependencies on the primary key attributes. Either the value or the known factors can be the reason for this type of Functional Dependency occurrence. The Transitive Functional Dependency can only occur in a relation of three or more non-key attributes that are functionally dependent on the primary key attribute.

Let us consider the below table to understand this,

In this table, the Student_ID column is the primary key. The values in the Student_ID column are formed by the combination of the first letter from the Student_Name column, last code from the Dept column and 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 the combination of 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, as well as makes it impossible to implement any potential upgrades. Functional Dependency provides more clarity on the values, helps to maintain the quality of the data, and keep the database clean without junk or repeating data.

Recommended Articles

This is a guide to the Functional Dependency in DBMS. Here we discuss the introduction, Syntax and various types of Functional Dependency in DBMS. You may also look at the following articles to learn more –

1. Different types of Data Models in DBMS
2. Types of DBMS Architecture
3. Key Difference between DBMS vs RDBMS
4. Guide to Specialization in DBMS