Quick Contact
DBMS Tutorial
 DBMS Tutorial
 What is Database Management System (DBMS)?
 Components of DBMS
 Applications of DBMS
 Three Schema DBMS Architecture
 Difference between DBMS and RDBMS?
 Difference between File Oriented System and DBMS
 Types of Data Models
 DBMS Schema and Instances
 Data Independence and Data Abstraction
 Database Users and Administrator
 DBMS Languages and Interfaces
DBMS ER Model
DBMS Relational Data Model
DBMS Normalization
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 nonkey attribute from a similar table as the primary key. This display P primary key attribute is functionally dependent on the Q nonkey 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 Xvalue in R has linked with it exactly one Yvalue in R.
In other words, whenever two tuples of R agree on their Xvalue, then they also agree on their Yvalue. 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
 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.
 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

Trivial Functional Dependency
An FD is trivial if and only if the righthand side is a subset of the lefthand 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.
 NonTrivial Functional Dependency
 Transitive Functional Dependency
 P → Q
 Q does not →P
 Q→R
 Partial Functional Dependency
An FD, which is not trivial FD, is known as NonTrivial Functional Dependency.
P→Q is said to be a nontrivial 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 nontrivial functional dependency as Name is not a subset of SID.
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:
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.
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