Functional Dependency in DBMS: Types And Advantages

Table of Contents

Functional dependency in a DBMS is a relationship that exists when an attribute of a table uniquely determines the value of another attribute. In other words, if an attribute(X) acts as a unique identifier for one or more attributes(Y, Z) then we can say that Y and Z are functionally dependent on X.

This dependency can be represented by an equation where an arrow(->) is used as a mark of dependency, the pointed side of the arrow represents the dependent attribute, whereas the other side of the arrow represents the determinant side.

If R is a relation (table) with attributes X, Y, and Z; where X acts as a unique identifier for the value of Y and Z, Then Y and Z are functionally dependent on X, however we can also represent this dependency through an equation X -> Y, Z where the left side of the arrow (X) is Determinant and the right side of the arrow (Y, Z) is the Dependent.

For example:

Employee ID (X) -> First Name (Y), Last Name (Z);

Where every time the value of Employee ID (X) Changes, First name (Y) and Last name (Z) also change.

Hence Y and Z are functionally dependent on X or can be represented in the equation as X -> Y, Z.

6 Types of Functional Dependency in DBMS,

To better understand, let’s learn them with suitable examples:

Trivial Functional Dependency:

Where an attribute or set of attributes that are dependent on a determinant are the subset of the attribute that is determinant there will be Trivial Functional dependency.

For example:

X = {a,b,c,d,e,f} and Y = {b,d,f} ; And X -> Y

In the above example, attribute Y (dependent) is a subset of attribute X (determinant), We can say that there is a trivial functional dependency between attribute X and Y.

Another Example:

{X,Y} -> Y

In the above example, Y is dependent here and Y is a subset of {X, Y} which is determinant here, there will be a trivial functional dependency.

Non-Trivial Functional Dependency:

Where an attribute or set of attributes that are dependent on a determinant is not a sub-set of determinants and their intersection of dependent and determinant is null there will be Non-trivial functional dependency.

For example:

X = {a,b,c,d,e,f} and Y = {p, q, r}; And X -> Y

In the above example attribute Y is dependent on attribute X (Determinant), but as stated in the definition Y dependent is not a subset of X determinant; hence there will be Non Trivial Functional Dependency.

Multivalued Functional Dependency:

Where determinant attribute X acts as an identifier for multiple values of dependent attribute Y, there is a multivalued dependency, this type of dependency is represented by two arrows (-> ->), where the pointed side of the arrow represents the dependent side whereas the other side of the arrow represents the determinant side.

For example:

Name of student	Courses	Department
X	P, R	A
Y	R, Q	B

In the above example, the Attribute “Name of student” is Determinant for the attribute “Department”, hence we can say that “Name of student” -> “Department”;

But the determinant attribute “Name of student ” identifies multiple values for the dependent attribute “Course”, hence there will be Multi-valued Functional Dependency and this can be described as “Name of student” -> -> ”Course”.

Transitive Function Dependency:

where there is an indirect relationship between dependent attribute Y and determinant attribute X, there is said to be Transitive function dependency.

For example:

Name of student	Courses	Department	Building
X	P, R	A	1
Y	R, Q	B	2

In the above example, attribute “ Name of student” is the determinant and attribute “Department” is a dependent; hence “Name of the student” -> “Department” ;

But the attribute “Building” is dependent on the attribute “department” which is also a dependent attribute to the determinant attribute “Name of student”;

hence there is an indirect relationship exists between the determinant attribute “Name of student” and the dependent attribute “Building” which can also be represented as “Name of student” -> “Building”.

Fully Functional Dependency:

Where any subset of determinant attribute X can not determine the dependent attribute Y, there is a fully functional dependency.

For example:

If determinant attribute {A, B,} determined the value of dependent attribute {C} and the value of C can not be determined by {A} or {B} there is a fully functional dependency.

Partial Functional Dependency:

Where any subset of determinant attribute X can also determine the dependent attribute Y, there is a Partial functional dependency.

For example:

If determinant attribute {A, B} determines the value of dependent attribute {C} and the value of C can be determined by {A} or {B}, there is a Partial functional dependency.

Advantages of DBMS

Now we have learned the types of Functional Dependency in DBMS, let’s quickly have a look at the Advantages:

Data Normalization:

with the help of functional dependency, we can identify the Primary keys and the Candidate keys in a table, which helps to normalize the data, smalled normalized data needs less space to store and requires less effort to manage.

Query Optimization:

Functional dependency helps to establish relationships between various data tables. It identifies important attributes that are necessary to retrieve data from the large data tables.

Data Integrity:

By identifying the important attributes we can easily separate inconsistent and irrelevant data attributes. It prevents unnecessary data from entering our database and makes our data more and more relevant and accurate.

Next step:

In case you are interested in learning more about the topic or want to make a bustling career in the field of Data Analytics. Then I recommend you, please check out the “Data Analytics course” facilitated by one of the leading e-learning platforms in India “Henry Harvin Education”.

Henry Harvin Education is one of the largest and most reliable Ed-tech companies globally with a vision to provide individuals and organizations with the latest and state-of-the-art technology. The Institute has more than 1200 courses under its portfolio operating across more than 37 categories.

Conclusion:

Functional Dependency in DBMS acts as a blueprint for maintaining accurate and efficient organizational data in the database. Every organization should consider it as a key factor in determining the data storage and maintenance constraints in the organization. However, the topic is very vast and hard to learn with just an article. But I hope you may have got an overview of the Functional Dependency in DBMS.

Frequently Asked Questions

Q.1 What is the Functional Dependency in DBMS?

Ans. Functional dependency in a DBMS is a relationship where an attribute (value in the table) uniquely determines the value of another attribute.

Q.2 What is the aim of Functional dependency in DBMS?

Ans. Functional Dependency in DBMS acts as a Blueprint for storing and maintaining data efficiently, it helps identify the key attributes and ensures that no irrelevant data gets into the database, and it helps normalize the data into smaller and more relatable tables, which requires less space to store and less efforts to maintain.

Q.3 How many types of Functional dependencies in DBMS exist?

Ans. There are 6 main types of Functional Dependency in DBMS

Trivial Dependency
Non-Trivial Dependency
Multivalued Dependency
Transitive dependency
Fully Dependency
Partial dependency

What is a Trivial Functional dependency in Dbms?
Where an attribute or set of attributes that are dependent on a determinant is the subset
of the determinant attribute, there will be Trivial Functional dependency.
For example: X = {a,b,c,d,e,f} and Y = {b,d,f}; And X -> Y
In the given example, X is the Determinant, and Y is the Dependent; Y is a subset of X, so this is
a Trivial Functional Dependency.
Benefits of Functional Dependency in DBMS?

The following are the benefits of Functional Dependency in DBMS:
● Data Normalization
● Query Optimization
● Data Integrity