 Search Introduction To Functional Dependency

Functional Dependency – Before moving ahead, let’s read Cost Based Query Optimization.

### 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:

Share on