Functional Dependency (FD) is a DBMS Concept which arises due to the inter dependency between the attributes (columns) of a relation (table). As a result, Functional Dependency forms the basis for the first three normal forms in DBMS.
What Functional Dependency actually means?
In simple terms, Functional Dependency is the description of association of various attributes in a relation. We may also state it as, a state when any non-key attribute is dependent on the key attributes then we call it as Functional Dependency.
Dependence between attributes is shown with –> (arrow symbol). In general terms, say an attribute B is Functional Dependent on an attribute A of a relation R, then we depict it as,
A –> B
Hence, it would mean that A determines the value of B. Hence, the attribute that comes on the left of the arrow is known as the Determinant and the attribute to the right is known as the Determined. This is so because the value of B is determined by the value of A.
Features of Functional Dependency
A few interesting features of this DBMS Concept are:
- If a table contains an attribute as a primary key, then all other attributes in that table must be fully functionally dependent on the primary key.
- If X–>Y holds true then Y–>X may or may not hold.
Types of Functional Dependency
DBMS Concept Functional Dependency can be categorized as follows:
- Full Functional Dependency
- Partial Functional Dependency
- Transitive Functional Dependency
- Multivalued Functional Dependency
- Joint Functional Dependency
Full Functional Dependency
When all non-key attributes in a relation depend on the key attribute (simple or composite) then this is called full functional dependency.
For example: Consider a relation ADMN (Stu_id, Stu_Name, Stu_Class, Stu_Address) with Stu_id as the key attribute. In this relation all the non-key attributes i.e., Stu_Name, Stu_Class, and Stu_Address are dependent on the key attribute Stu_id. Hence, this relation shows full functional dependency.
The dependency can be depicted with the help of the following diagram known as the dependency diagram.
Partial Functional Dependency
Partial functional dependency occurs when some of the non-key attributes in a relation depend on the key attribute as well as a non-key attribute.
Let us consider another example.Consider a relation ADMN (Stu_id, Stu_Name, Stu_Class, Stu_Course, Course_duration) with Stu_id as the key attribute where as Stu_Name, Stu_Class, Stu_Course, and Course_duration as the non-key attributes.
Here, if you will observe, then there is an attribute Course_duration. This attribute is dependent on the Student id as well as the course which the student enrolls for.
The dependency diagram for the above relation will be as follows:
Transitive Functional Dependency
When one non-key attribute in a relation depends on another non-key attribute, then it is known as transitive functional dependency. In this dependency, the value of a non-key attribute is determined by another non-key attribute.
Say, X –> Y and Y–>Z, then the dependency X–>Z will follow automatically.
In order to remove the transitive functional dependency, we split the relation into two separate relations. These relations are then linked with the help of a foreign key.
Multivalued Functional Dependency
For a relation to have multivalued functional dependency, there must be three attributes which are in close dependence with each other. Another important fact about this dependency is the fact that the three attributes must be independent of each other.
For instance, we have a relation COURSE with the following attributes: C_Name, C_Teacher_Name, and Books. Now, here a course can have multiple teachers and that course can even have different textbooks. But name of the courseteacher and the textbooks they follow are independent of each other. Hence, this makes up multiple values between the three attributes leading to multivalued functional dependence.
Multivalued Functional Dependencyis also known as tuple generating dependency. We will learn about it in more detail in further articles.
Joint Functional Dependency
Joint functional dependency states that after a relation has been divided into smaller tables with various relations like foreign keys, then it must be capable of being joined back to form the original relation.