Functional Dependency in DBMS: Types with Examples

By : Fiona Brown Fiona Brown
Hours Updated

⚡ Smart Summary

Functional Dependency is a constraint that determines how one attribute relates to another in a DBMS. It underpins normalization by identifying determinants and dependents, and it is classified into multivalued, trivial, non-trivial, and transitive dependencies.

  • ➡️ Core Idea: In X → Y, the determinant X fixes the value of the dependent Y, written with an arrow.
  • 🏷️ Key Terms: Determinant, dependent, axiom, decomposition, and union describe how dependencies are named and manipulated.
  • 📐 Armstrong’s Axioms: Reflexivity, augmentation, and transitivity infer every dependency that a set logically implies.
  • 🔀 Four Types: Multivalued, trivial, non-trivial, and transitive dependencies each describe a distinct attribute relationship.
  • 🧹 Normalization Link: Dependencies drive normalization, which removes redundancy and update anomalies.
  • 🔑 Closure: The attribute closure reveals candidate keys and whether a dependency holds.
  • ✅ Benefit: Good dependencies signal good design and separate quality schemas from poor ones.
Read More

Functional Dependency in DBMS

What is Functional Dependency?

Functional Dependency (FD) is a constraint that determines the relation of one attribute to another attribute in a Database Management System (DBMS). Functional dependency helps to maintain the quality of data in the database. It plays a vital role in finding the difference between good and bad database design.

A functional dependency is denoted by an arrow “→”. The functional dependency of Y on X is represented by X → Y. Let us understand functional dependency with an example.

Example:

Employee number Employee Name Salary City
1 Dana 50000 San Francisco
2 Francis 38000 London
3 Andrew 25000 Tokyo

In this example, if we know the value of Employee number, we can obtain Employee Name, City, Salary, and so on. By this, we can say that City, Employee Name, and Salary are functionally dependent on Employee number.

Key Terms

Here are some key terms for functional dependency in a database:

Key Terms Description
Axiom Axioms are a set of inference rules used to infer all the functional dependencies on a relational database.
Decomposition A rule that suggests if you have a table that appears to contain two entities determined by the same primary key, you should consider breaking it into two different tables.
Dependent It is displayed on the right side of the functional dependency diagram.
Determinant It is displayed on the left side of the functional dependency diagram.
Union It suggests that if two tables are separate and the primary key is the same, you should consider putting them together.

Rules of Functional Dependencies (Armstrong’s Axioms)

Below are the three most important rules for functional dependency in a database. They are known as Armstrong’s axioms, and every other inference rule can be derived from them.

  • Reflexive rule: If X is a set of attributes and Y is a subset of X, then X → Y holds.
  • Augmentation rule: When X → Y holds and C is an attribute set, then XC → YC also holds. Adding attributes does not change the basic dependency.
  • Transitivity rule: Similar to the transitive rule in algebra: if X → Y holds and Y → Z holds, then X → Z also holds.

Three further rules are derived from these and are used constantly in practice:

  • Union rule: if X → Y and X → Z, then X → YZ.
  • Decomposition rule: if X → YZ, then X → Y and X → Z.
  • Pseudo-transitivity rule: if X → Y and WY → Z, then WX → Z.

Applying these rules repeatedly to a set of dependencies produces its closure, the complete set of dependencies that logically follow. The closure of an attribute set is also how candidate keys are found, which links directly to the study of DBMS keys.

RELATED ARTICLES

Types of Functional Dependencies in DBMS

There are mainly four types of functional dependency in DBMS:

  • Multivalued Dependency
  • Trivial Functional Dependency
  • Non-Trivial Functional Dependency
  • Transitive Dependency

Multivalued Dependency in DBMS

Multivalued dependency occurs in a situation where there are multiple independent multivalued attributes in a single table. A multivalued dependency is a complete constraint between two sets of attributes in a relation, requiring that certain tuples be present. Consider the following example.

Example:

Car_model Maf_year Color
H001 2017 Metallic
H001 2017 Green
H005 2018 Metallic
H005 2018 Blue
H010 2015 Metallic
H033 2012 Gray

In this example, Maf_year and Color are independent of each other but dependent on Car_model. These two columns are said to be multivalue dependent on Car_model. This is represented as:

car_model → maf_year

car_model → colour

Trivial Functional Dependency in DBMS

A trivial dependency is a set of attributes that is called trivial if the set of attributes is included in that attribute. So, X → Y is a trivial functional dependency if Y is a subset of X. Consider the example below.

Emp_id Emp_name
AS555 Harry
AS811 George
AS999 Kevin

Consider this table with two columns, Emp_id and Emp_name. {Emp_id, Emp_name} → Emp_id is a trivial functional dependency, as Emp_id is a subset of {Emp_id, Emp_name}.

Non-Trivial Functional Dependency in DBMS

A non-trivial dependency occurs when A → B holds true and B is not a subset of A. If attribute B is not a subset of attribute A, the dependency is considered non-trivial.

Company CEO Age
Microsoft Satya Nadella 51
Google Sundar Pichai 46
Apple Tim Cook 57

Example:

{Company} → {CEO} (if we know the Company, we know the CEO name). But CEO is not a subset of Company, and hence it is a non-trivial functional dependency.

Transitive Dependency in DBMS

A transitive dependency is a type of functional dependency that happens when an attribute is indirectly determined by two functional dependencies. Consider the example below.

Company CEO Age
Microsoft Satya Nadella 51
Google Sundar Pichai 46
Alibaba Jack Ma 54

{Company} → {CEO} (if we know the company, we know its CEO’s name).

{CEO} → {Age} (if we know the CEO, we know the age).

Therefore, by the rule of transitive dependency, {Company} → {Age} should hold, which makes sense because if we know the company name, we can find the age.

Note: transitive dependency can only occur in a relation of three or more attributes.

What is Normalization?

Normalization is a method of organizing the data in a database that helps you avoid data redundancy and insertion, update, and deletion anomalies. It is a process of analyzing the relation schemas based on their different functional dependencies and primary key.

Normalization is inherent to relational database theory. It may have the effect of splitting data across additional tables so that each fact is stored once. The full process is covered in the guide to database normalization, which builds directly on the dependency types above.

Advantages of Functional Dependency

  • Functional dependency avoids data redundancy, so the same data does not repeat at multiple locations in the database.
  • It helps you maintain the quality of data in the database.
  • It helps you define the meanings and constraints of databases.
  • It helps you identify bad designs.
  • It helps you find the facts regarding the database design.

FAQs

The determinant is the attribute on the left of the arrow that fixes the value. The dependent is the attribute on the right whose value is determined. In X → Y, X determines Y.

A functional dependency fixes a single value for the dependent. A multivalued dependency allows a set of independent values, which is why it needs its own attribute and leads to fourth normal form.

Compute the closure of an attribute set using the dependencies. If the closure includes every attribute of the relation, that set is a superkey; the minimal such set is a candidate key.

Yes. Dependency-discovery algorithms, some AI assisted, scan a dataset and propose candidate dependencies. A designer must confirm them, because a pattern true in sample data may not hold as a rule.

It stores a fact about a non-key attribute, so updating it risks anomalies. Removing transitive dependencies is exactly what third normal form requires, which keeps each fact in one place.

Summarize this post with: