Quick Contact

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

    Functional Dependency (FD)
    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

    Functional Dependency (FD)
    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.

    Copyright 1999- Ducat Creative, All rights reserved.