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

Total Price=Item Price x Number of Items

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

SID NAME COURSE AGE
101 Harshit B.Tech 21
102 Vijay MCA 23
103 Gaurav MCA 22
104 Raman BCA 20

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

S# STATUS CITY P# QTY
S1 20 Delhi P1 100
S1 20 Delhi P2 125
S1 20 Delhi P3 130
S1 20 Delhi P4 115
S2 10 Karnal P1 200
S2 10 Karnal P2 215
S3 30 Rohtak P1 200
S4 20 Delhi P4 200
S4 20 Delhi P5 300

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
3. 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.

4. Transitive Functional Dependency
5. 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.

6. Partial Functional Dependency
7. 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