Functional Dependency in DBMS

Last Updated on July 2, 2023 by Mayank Dham

Functional dependency is a fundamental concept in the realm of database management, playing a vital role in ensuring the precision and coherence of stored data. It facilitates the establishment of connections between various attributes within a database table, enabling the organized and meaningful storage of information. This article provides an in-depth exploration of functional dependency in DBMS, encompassing its definition, different types, and properties, and ultimately highlighting the advantages it brings to the field of database management.

What is Functional Dependency in DBMS?

Functional dependency in DBMS (Database management systems), refers to a relationship between attributes or sets of attributes within a database table. It indicates that the values of certain attributes (known as dependent attributes) are determined uniquely by the values of other attributes (known as determinant attributes). In other words, knowing the values of the determinant attributes allows us to infer the values of the dependent attributes. For example, let’s consider a database table "Employee" with the following attributes:

Employee (Employee_ID, Employee_Name, Address, Salary)

In this case, the Employee_ID attribute uniquely identifies each employee. Therefore, we can say that Employee_Name, Address, and Salary are functionally dependent on Employee_ID. We can denote this functional dependency as follows:

Employee_ID → Employee_Name, Address, Salary

Take another example, suppose we have a database table "Order" with attributes like Order_ID, Product_Name, Quantity, and Price. In this case, the product name is dependent on the order ID, which means that if we know the Order_ID, we can determine the corresponding Product_Name. We can denote this functional dependency as:

Order_ID → Product_Name

This ensures that the data in the database is organized and easily accessible. By identifying such functional dependencies, we can reduce data redundancy, ensure data consistency, and maintain data integrity in the database.

How to Denote a Functional Dependency in DBMS?

A functional dependency can be denoted using the following notation:

A → B

Here, A is the determinant attribute, and B is the dependent attribute. It means that the value of attribute B is uniquely determined by the value of attribute A.

Let’s consider an example to understand this notation better. Suppose we have a database table "Student" with the following attributes:

Student (Roll_No, Name, Age, Address)

If we know the value of Roll_No, we can determine the corresponding values of Name, Age, and Address. Therefore, we can say that Name, Age, and Address are functionally dependent on Roll_No. We can denote this functional dependency as:

Roll_No → Name, Age, Address

Here, Roll_No is the determinant attribute, and Name, Age, and Address are dependent attributes.

Functional dependency can also be depicted diagrammatically, as shown below.

The dependent attributes are determined by pointing side of arrow and the determinant attribute is determined by the origin of the arrow.

Types of Functional Dependency in DBMS

The types of functional dependency in DBMS are as follows:

1. Trivial Functional Dependency in DBMS

Trivial functional dependency is a special case of a functional dependency in DBMS, where the dependent attribute is a subset of the determinant attribute. In other words, a functional dependency is said to be trivial if the attributes on its right side are a subset of the attributes on its left side.

Consider the following example to better understand this:

Suppose we have a database table "Employee" with the following attributes:

Employee (Employee_ID, Employee_Name, Age, Department)

Here, {Employee_Id, Employee_Name} → {Employee_Name} is a Trivial functional dependency because the dependent Employee_Name is the subset of determinant {Employee_Id, Employee_Name}.

{ Employee_Id } → { Employee_Id }, { Name } → { Name } and { Age } → { Age } are also Trivial functional dependency.

2. Non-Trivial Functional Dependency in DBMS

A non-trivial functional dependency is a functional dependency in DBMS where the dependent attribute is not a subset of the determinant attribute. In other words, X → Y is called a Non-trivial functional dependency if Y is not a subset of X. So, a functional dependency X → Y where X is a set of attributes and Y is also a set of the attribute but not a subset of X, then it is called Non-trivial functional dependency.

Consider the following example to better understand this:

Suppose we have a database table "Customer" with the following attributes:

Customer (Customer_ID, Customer_Name, Address, Phone_Number)

Here, {Customer_ID} → {Customer_Name} is a non-trivial functional dependency because Customer_Name(dependent) is not a subset of Customer_ID(determinant). Similarly,

Customer_ID,Customer_Name} → {Phone_Number} is also a non-trivial functional dependency.

3. Multivalued Functional Dependency in DBMS

Multivalued functional dependency (MVD) is a type of functional dependency in DBMS, where a single determinant attribute can determine multiple sets of independent attributes. Suppose we are given a relation X → { Y, Z }, if there exists no functional dependency between Y and Z, then it is called Multivalued functional dependency.

Consider the following example to better understand this:

Suppose we have a database table "Course" with the following attributes:

Course (Course_ID, Course_Name, Instructor_Name, Textbook_Name)

Here, Course_ID is the primary key. We can say that there is a multivalued functional dependency between Course_Name and Instructor_Name and Textbook_Name.
Here
{Course_ID} → {Instructor_Name, Textbook_Name} is a Multivalued functional dependency, since the dependent attributes Instructor_Name, Textbook_Name are not functionally dependent(i.e. Instructor_Name → Textbook_Name or Textbook_Name → Instructor_Name).

We can denote the multivalued functional dependency as follow:

Course_Name ↠ Instructor_Name, Textbook_Name

4. Transitive Functional Dependency in DBMS

Transitive functional dependency is a type of functional dependency in DBMS where one non-key attribute is functionally dependent on another non-key attribute through a chain of functional dependencies.

Consider the following example to better understand this:

Suppose we have a database table "Student" with the following attributes:

Student (Student_ID, Student_Name, Course_Name, Instructor_Name)

Here, Student_ID is the primary key. We can say that there is a transitive functional dependency between Student_Name and Instructor_Name, as both of them are non-key attributes and the dependency between them is through Course_Name, which is also a non-key attribute.

Here, {Student_Name → Course_Name} and {Course_Name → Instructor_Name} holds true. Hence, according to the axiom of transitivity, { Student_Name → Instructor_Name} is a valid functional dependency.

Properties of Functional Dependency in DBMS

Functional dependency in DBMS have several important properties that help to ensure data consistency and maintain data integrity in the database. The key properties of functional dependency in DBMS are:

1. Reflexivity: If A is a set of attributes and B is a subset of A, then the functional dependency A → B holds true.

2. Augmentation: If a functional dependency A → B holds true, then we can add additional attributes to the both sides without changing the existing functional dependency. For example, if A → B, then we can add attribute C to both sides to get AC → BC.

3. Transitivity: If A → B and B → C, then we can infer that A → C also holds true by the rule of transitivity. This property allows us to detect transitive functional dependencies.

Advantages of Functional Dependency in DBMS

Functional dependency in DBMS has several advantages, including:

1. Data consistency: Functional dependency ensures that data is consistent in DBMS. By identifying and removing redundant data, we can prevent data inconsistencies that can result in incorrect query results.

2. Data integrity: Functional dependency helps to maintain data integrity by ensuring that data is stored correctly in the database. By enforcing rules that govern how data is stored and updated, we can prevent data corruption and ensure that the data is accurate.

3. Database efficiency: By identifying and removing redundant data, functional dependency can improve the efficiency of the database. With fewer data to process and search through, query times can be reduced, and the database can perform more quickly.

4. Easier maintenance: By simplifying the database design, functional dependency makes it easier to maintain the database over time. With a simpler design, it is easier to make changes, and less time is spent on maintenance and troubleshooting.

Conclusion
Functional dependency is a crucial concept in database management systems (DBMS) that establishes relationships between attributes within a database table. It ensures the accuracy, organization, and meaningful storage of data. By defining functional dependencies, data integrity is maintained, redundancy is reduced, and efficient querying and manipulation of data are enabled. Understanding functional dependency is vital for effective database design and normalization.

FAQs related to functional dependency in DBMS:

Q1: What is the purpose of functional dependency in DBMS?
Functional dependency helps to establish relationships between attributes in a database table, ensuring that the values of dependent attributes are uniquely determined by the values of determinant attributes. It aids in database organization, data integrity, and efficient data manipulation.

Q2: How is functional dependency represented in DBMS?
Functional dependency is denoted by X -> Y, where X represents the determinant attributes and Y represents the dependent attributes. It signifies that knowing the values of attributes in X allows the inference of values for attributes in Y.

Q3: How does functional dependency contribute to database normalization?
Functional dependency plays a crucial role in the normalization process of a database. It helps identify dependencies and eliminate redundancy, leading to a more efficient and structured database schema.

Q4: Can functional dependencies be transitive?
Yes, functional dependencies can be transitive. Transitive functional dependency occurs when there is a chain of functional dependencies, such as A -> B and B -> C, which implies A -> C.

Q6: Are functional dependencies relevant in non-relational databases?
Functional dependencies are primarily associated with relational databases. In non-relational databases, different techniques and mechanisms, such as document-oriented or graph databases, handle data relationships and integrity in alternative ways.