Functional Dependency

The functional dependency is a relationship that exists between two attributes. It typically exists between the primary key and non-key attribute within a table.

• Introduced by E. F. Codd, it helps in preventing data redundancy and gets to know about bad designs.
• Bad DBMS designs have plenty of disadvantages while querying, as well as make it impossible to implement any potential upgrades.
• Functional Dependency is represented by  → (arrow sign).

To understand this concept ,Let us assume X is a relation with attributes A and B . Then the following function dependency between attributes can be represent by

A → B

Here, the left side of the arrow is identified as a Determinant, while the right side of the arrow is identified as a Dependent. “A” will always be the primary key attribute and “B” will be any dependent non-key attribute from the same table as the primary key. This shows A primary key attribute is functionally dependent on the B non-key attribute.

Types of Functional Dependency

Functional Dependencies can be classified in four types as follows:

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 one- 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,

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.