In relational database management, functional dependency is a
concept that specifies the relationship between two sets of
attributes where one attribute determines the value of another
attribute. It is denoted as X → Y, where the attribute set on the left
side of the arrow, X is called Determinant, and Y is called the
Dependent.
What is Functional Dependency?
A functional dependency occurs when one attribute uniquely
determines another attribute within a relation. It is a constraint that
describes how attributes in a table relate to each other. If attribute A
functionally determines attribute B we write this as the A→B.
Functional dependencies are used to mathematically express
relations among database entities and are very important to
understanding advanced concepts in Relational Database Systems.
Example:
roll_n
o name dept_name dept_building
42 abc CO A4
43 pqr IT A3
44 xyz CO A4
45 xyz IT A3
46 mno EC B2
47 jkl ME B2
From the above table we can conclude some valid functional
dependencies:
roll_no → { name, dept_name, dept_building }→ Here, roll_no can
determine values of fields name, dept_name and dept_building,
hence a valid Functional dependency
roll_no → dept_name , Since, roll_no can determine whole set of
{name, dept_name, dept_building}, it can determine its subset
dept_name also.
dept_name → dept_building , Dept_name can identify the
dept_building accurately, since departments with different
dept_name will also have a different dept_building
More valid functional dependencies: roll_no → name, {roll_no,
name} ⇢ {dept_name, dept_building}, etc.
Here are some invalid functional dependencies:
name → dept_name Students with the same name can have
different dept_name, hence this is not a valid functional
dependency.
dept_building → dept_name There can be multiple departments
in the same building. Example, in the above table departments
ME and EC are in the same building B2, hence dept_building →
dept_name is an invalid functional dependency.
More invalid functional dependencies: name → roll_no, {name,
dept_name} → roll_no, dept_building → roll_no, etc.
What is Functional Dependency in DBMS ?
Types of Functional Dependencies in DBMS
1. Trivial functional dependency
2. Non-Trivial functional dependency
3. Multivalued functional dependency
4. Transitive functional dependency
1. Trivial Functional Dependency
In Trivial Functional Dependency, a dependent is always a subset of
the determinant. i.e. If X → Y and Y is the subset of X, then it is
called trivial functional dependency.
Symbolically: A→B is trivial functional dependency if B is a subset of
A.
The following dependencies are also trivial: A→A & B→B
Example 1 :
ABC -> AB
ABC -> A
ABC -> ABC
Example 2:
roll_no name age
42 abc 17
43 pqr 18
roll_no name age
44 xyz 18
Here, {roll_no, name} → name is a trivial functional dependency,
since the dependent name is a subset of determinant set {roll_no,
name}. Similarly, roll_no → roll_no is also an example of trivial
functional dependency.
2. Non-trivial Functional Dependency
In Non-trivial functional dependency, the dependent is strictly
not a subset of the determinant. i.e. If X → Y and Y is not a subset
of X, then it is called Non-trivial functional dependency.
Example 1 :
Id -> Name
Name -> DOB
Example 2:
roll_no name age
42 abc 17
43 pqr 18
44 xyz 18
Here, roll_no → name is a non-trivial functional dependency, since
the dependent name is not a subset of determinant roll_no.
Similarly, {roll_no, name} → age is also a non-trivial functional
dependency, since age is not a subset of {roll_no, name}
3. Semi Non Trivial Functional Dependencies
A semi non-trivial functional dependency occurs when part of the
dependent attribute (right-hand side) is included in the determinant
(left-hand side), but not all of it. This is a middle ground between
trivial and non-trivial functional dependencies. X -> Y is called semi
non-trivial when X intersect Y is not NULL.
Example:
Consider the following table:
Course_I
Student_ID D Course_Name
101 CSE101 Computer Science
102 CSE102 Data Structures
103 CSE101 Computer Science
Functional Dependency:
{StudentID,CourseID}→CourseID
This is semi non-trivial because:
Part of the dependent attribute (Course_ID) is already included in
the determinant ({Student_ID, Course_ID}).
However, the dependency is not completely trivial because
{StudentID}→CourseID is not implied directly.
4. Multivalued Functional Dependency
In Multivalued functional dependency, entities of the dependent set
are not dependent on each other. i.e. If a → {b, c} and there exists
no functional dependency between b and c, then it is called a
multivalued functional dependency.
Example:
bike_model manuf_year color
tu1001 2007 Black
tu1001 2007 Red
tu2012 2008 Black
tu2012 2008 Red
tu2222 2009 Black
tu2222 2009 Red
In this table:
X: bike_model
Y: color
Z: manuf_year
For each bike model (bike_model):
1. There is a group of colors (color) and a group of manufacturing
years (manuf_year).
2. The colors do not depend on the manufacturing year, and the
manufacturing year does not depend on the colors. They are
independent.
3. The sets of color and manuf_year are linked only to bike_model.
That’s what makes it a multivalued dependency.
In this case these two columns are said to be multivalued dependent
on bike_model. These dependencies can be represented like this:
5. Transitive Functional Dependency
In transitive functional dependency, dependent is indirectly
dependent on determinant. i.e. If a → b & b → c, then according to
axiom of transitivity, a → c. This is a transitive functional
dependency.
Example:
enrol_n dep
o name t building_no
42 abc CO 4
43 pqr EC 2
44 xyz IT 1
45 abc EC 2
Here, enrol_no → dept and dept → building_no. Hence, according to
the axiom of transitivity, enrol_no → building_no is a valid functional
dependency. This is an indirect functional dependency, hence called
Transitive functional dependency.
6. Fully Functional Dependency
In full functional dependency an attribute or a set of attributes
uniquely determines another attribute or set of attributes. If a
relation R has attributes X, Y, Z with the dependencies X->Y and X-
>Z which states that those dependencies are fully functional.
7. Partial Functional Dependency
In partial functional dependency a non key attribute depends on a
part of the composite key, rather than the whole key. If a relation R
has attributes X, Y, Z where X and Y are the composite key and Z is
non key attribute. Then X->Z is a partial functional dependency in
RDBMS.
Conclusion
Functional dependency is very important concept in database
management system for ensuring the data consistency and
accuracy. In this article we have discuss what is the concept behind
functional dependencies and why they are important. The valid and
invalid functional dependencies and the types of most important
functional dependencies in RDBMS.
