Functional Dependency in DBMS

In the field of database management, functional dependency is a crucial concept that helps to maintain the accuracy and consistency of data stored in a database. It is used to establish a relationship between different attributes of a database table, which ensures that the data is stored in an organized and meaningful manner. This article discusses the definition of functional dependency in DBMS, its types, properties, and at last the advantages of functional dependency in DBMS.

What is Functional Dependency in DBMS?

Functional dependency in DBMS, as name suggests, is a relationship between two or more attributes of a database table related to each other, where the value of one attribute uniquely determines the value of another attribute. 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
In conclusion, functional dependency in DBMS is a very crucial concept. It helps to ensure data consistency, integrity, and efficiency by identifying relationships between attributes and eliminating redundant data. By applying functional dependency in database design and maintenance, we can create more accurate and efficient databases that are easier to manage and maintain over time.

FAQs

Here are some frequently asked questions on functional dependency in DBMS:

Q1: Why is functional dependency important in database design?
Ans: Functional dependency in DBMS is very important because it helps to ensure that data is stored in a way that is consistent, accurate, and efficient. By identifying and removing redundant data, functional dependency can help to reduce the storage requirements of the database and improve its performance.

Q2: Can functional dependency be violated?
Ans: Yes, functional dependency can be violated if there are inconsistencies or errors in the database design or data management process.

Q3: What is a key attribute in functional dependency?
Ans: A key attribute is an attribute or set of attributes that uniquely identifies each record or tuple in a database.

Q4: What is the difference between a functional dependency and a constraint?
Ans: A functional dependency is a relationship between two attributes in a database, where the value of one attribute determines the value of another attribute. A constraint, on the other hand, is a rule or condition that must be met in order for data to be stored in the database.