Functional Dependency (FD)

Functional Dependency (FD)

A functional dependency is a relationship between any two attributes. Assume that given the value of one attribute, we can get the value of another attribute. For example, if we know the value of the customer account number, we can get the value of customer balance. We say that customer balance is functionally dependent on the customer account number. For example, if we have the price of an item, and if we have the number of items purchased, then we can evaluate the total price for that element as follows.

In this case, we would say that Total Price is functionally dependent on Item Price and Number of Items.

Syntax of Functional Dependency (FD)

P → Q

In this syntax, the left side of the symbol is defined as a Determinant, while the right side of the symbol is defined as a Dependent. P would always be the primary key attribute, and Q would be any dependent non-key attribute from a similar table as the primary key. This display P primary key attribute is functionally dependent on the Q non-key attribute. In some other terms, If the column P attribute of the table recognizes the column Q attribute of the similar table, then the functional dependency of the column Q on column P is represented as P→Q .

Example

Consider the relation STUDENT

STUDENT

In the above table Student, if you know the value of SID of any student, which is unique for each student, we can obtain NAME, AGE, and COURSE of that student with the help of that SID.

By this, we can say that the NAME, AGE, and COURSE are functionally dependent on SID, or we can say SID determines NAME, AGE, and COURSE of the Student.

SID→NAME,AGE,COURSE

Formal Definition of Functional Dependency

Let R be a relation, and X and Y be arbitrary subsets on the set of attributes of R. Then, Y is functional dependent on X (in token of X→Y) if and only if each X-value in R has linked with it exactly one Y-value in R.

In other words, whenever two tuples of R agree on their X-value, then they also agree on their Y-value. Mathematically for any two tuples t_1 and t_2 in R that have
t_1 [X]=t_2 [X]
We must also have

t_1 [Y]=t_2 [Y]

Example

Consider the relation FIRST (S#, STATUS, CITY, P#, QTY), as shown in the figure.

FIRST

Here we assume that each supplier has a unique supplier number (S#), precisely one status code and location. A supplier can supply different parts. Further, we assume that STATUS is functionally dependent on CITY. Therefore all Delhi suppliers must have a status of 20.

The following functional dependencies should influence in FIRST Relational.

S# CITY

S# STATUS

CITY STATUS

(S#,P#) QTY

The figure demonstrates the functional dependency diagram for this relation. The key to the FIRST relation is the combination of (S# , P#).

Fully Functional Dependency

In a relation R, an attribute of Y is said to be fully functionally dependent on attribute X if it is functionally dependent on X and not functionally dependent on any proper subset of X

Example

1. In the relation STUDENT, AGE is not fully functionally dependent on concatenated key (SID, NAME, COURSE) because AGE is functionally dependent on the NAME and COURSE.
2. In the FIRST relation, QTY is fully functionally dependent on key (S#, P#).

Types of Functional Dependency

Following are the various types of functional dependencies are

1. Trivial Functional Dependency

   An FD is trivial if and only if the right-hand side is a subset of the left-hand side.

   P→Q is a trivial functional dependency if Q is a subset of P.

   The following dependencies are also trivial: P→P and Q→Q.

   Example

   Consider the table STUDENT

   SID	NAME
   111	Shubham
   112	Ankita
   113	Mahesh
   114	Ramesh

   
   [SID,NAME]→SID is a trivial functional dependency, as SID is a subset of
   
   
   [SID,NAME]. That creates sense because if we see the values of SID and NAME, then the value of SID can be simply determined.
   

2. Non-Trivial Functional Dependency

An FD, which is not trivial FD, is known as Non-Trivial Functional Dependency.

P→Q is said to be a non-trivial FD, if and only if Q is not a subset of P.

Example

Consider the table STUDENT

SID	NAME
111	Shubham
112	Ankita
113	Mahesh
114	Ramesh


SID→NAME is a non-trivial functional dependency as Name is not a subset of SID.


3. Transitive Functional Dependency

The functional dependency is said to be transitive if two functional dependencies indirectly produced it. For example,

P→R is a transitive dependency if the following three functional dependencies contains true:

• P → Q
• Q does not →P
• Q→R

Example

Consider the table STUDENTConsider the table STUDENT

SID	NAME	AGE
111	Shubham	20
112	Ankita	19
113	Mahesh	18

[SID→NAME] [If we know the SID, we know its student name]

[NAME→AGE] [If we know the Name, we know the Age]

Therefore according to the rule of transitive dependency:

[SID→AGE] It should hold, that makes sense because if we know the SID, we can know his age.

Note: We want to understand that transitive dependency can merely appear in a relationship of three or additional attributes.

4. Partial Functional Dependency

A→B is a partial dependency if B is dependent on a proper subset of A and B is said to be partially dependent upon A. A→B where A is not the minimal set of attributes that uniquely determines B. Some attributes could be removed from A, and the dependency would still hold. Partial dependencies must be decomposed into separate relations. It is partial if the removal of any attribute from A does not result in the violation of the rule.

Apply now for Advanced DBMS Course