📗 Definition of Functional Dependency
In database management, Functional Dependency is a relationship between two attributes or columns within a table, where the value of one attribute determines the value of the other. More specifically, functional dependency defines a relationship between a set of attributes in a relation, such that one attribute determines the values of other attributes in the same relation. It is a fundamental concept in relational database design and normalization. The existence of functional dependencies between attributes can have a significant impact on the design of a database schema and the efficiency of database operations. Here's an example of functional dependency:
Consider a table called "employees" with the following columns: "employee_id", "employee_name", "department", and "salary". In this table, the department in which an employee works determines the salary they receive. Therefore, there is a functional dependency between the "department" and "salary" columns.
Employees table -
We can represent this functional dependency as:
Department -> Salary
This means that for any given department value, there can be only one corresponding salary value. In other words, if we know the value of the "department" column, we can determine the value of the "salary" column for that row.
Another example of functional dependency could be a table of customers and their contact information. If the customer_id column is unique, then there is a functional dependency between customer_id and the customer's name, email, and phone number. In this case, we can represent the functional dependency as:
Customer_id -> Customer_name, Email, Phone_number
This means that for any given customer_id value, there can be only one corresponding value for the customer's name, email, and phone number.
📗 Functional Dependencies Rules
In database management, there are several rules that govern functional dependencies. These rules are as follows:
Left-hand side (LHS): A functional dependency is denoted by an arrow, where the left-hand side represents a set of one or more attributes that determine the values of another set of attributes on the right-hand side. In other words, the LHS of a functional dependency must be a set of one or more attributes.
Right-hand side (RHS): The right-hand side of a functional dependency represents the set of attributes whose values are determined by the attributes on the left-hand side. The RHS of a functional dependency must be a single attribute or a set of attributes.
Determination: The values of the attributes on the RHS are uniquely determined by the values of the attributes on the LHS. In other words, given the value of the LHS attribute(s), there can be only one corresponding value for the RHS attribute(s).
Transitivity: If A → B and B → C, then A → C. This means that if two functional dependencies are given, and the RHS of one functional dependency is the same as the LHS of another functional dependency, then the first two attributes together determine the third attribute.
Reflexivity: A → A, which means that an attribute is always functionally dependent on itself.
Augmentation: If A → B, then A, C → B. This means that if an attribute is functionally dependent on another attribute, then it is also functionally dependent on any set of attributes that includes the first attribute.
Reduction: If A, B → C, then A → C and A → B. This means that if an attribute is functionally dependent on a set of attributes, then it is also functionally dependent on any subset of that set.
These rules are important for understanding how functional dependencies work and how they can be used to design efficient and effective database schemas.
📗 Different types of Functional Dependencies
In DBMS, there are several types of functional dependencies that can exist between attributes in a relation. Here are the most common types of functional dependencies, along with examples:
♦ Trivial functional dependency
This is a functional dependency of the form A → B, where B is a subset of A. In other words, B is already fully determined by A. Trivial functional dependencies are not usually interesting, as they simply state that an attribute depends on itself.
Example: If we have a relation with attributes "name" and "name", the functional dependency "name → name" is trivial, as the right-hand side is a subset of the left-hand side.
♦ Simple functional dependency
A simple functional dependency is a functional dependency of the form A → B, where B is not a subset of A. Simple functional dependencies are useful for describing the relationships between attributes in a relation.
Example: If we have a relation with attributes "employee_id", "employee_name", and "salary", the functional dependency "employee_id → employee_name" is a simple functional dependency, as the employee name depends on the employee ID.
♦ Full functional dependency
A full functional dependency is a functional dependency of the form A → B, where B is fully dependent on A and not on any subset of A. In other words, there is no proper subset of A that determines B.
Example: In the same relation as the example above, the functional dependency "employee_id → salary" is a full functional dependency, as the salary is fully determined by the employee ID.
♦ Partial Functional Dependencies
Partial functional dependencies exist when the value of a non-key attribute is dependent on only part of the primary key. For example, suppose we have a table called "Employee_Details" with columns Employee_ID, Employee_Name, and Department_ID. The primary key is Employee_ID, but the Department_ID depends on Employee_ID. Therefore, the following functional dependency exists:
Employee_ID → Department_ID
♦ Transitive Functional Dependencies
Transitive functional dependencies exist when the value of a non-key attribute is dependent on another non-key attribute, which in turn is dependent on the primary key. For example, suppose we have a table called "Employee_Details" with columns Employee_ID, Employee_Name, Department_ID, and Department_Name. The primary key is Employee_ID, and Department_Name depends on Department_ID. Therefore, the following functional dependency exists:
Department_ID → Department_Name
Employee_ID → Department_Name
♦ Multi-Valued Dependencies
Multi-valued dependencies exist when the value of one attribute is dependent on the values of two or more other attributes that are independent of each other. For example, suppose we have a table called "Student_Courses" with columns Student_ID, Course_ID, and Professor_ID. A multi-valued dependency exists if the following functional dependencies hold:
Student_ID → Course_ID
Student_ID → Professor_ID
Course_ID → Professor_ID
These are some of the different types of functional dependencies that exist in database management systems. Understanding these dependencies is crucial for designing effective and efficient database schemas.
Here are some more examples to illustrate all the above examples -
Order_Details table:
Employee_Details table:
Student_Courses table:
In the Order_Details table, the Quantity attribute is fully dependent on the primary key (Order_ID, Product_ID).
In the Employee_Details table, the Department_ID attribute is partially dependent on the primary key (Employee_ID).
In the Student_Courses table, there is a multi-valued dependency between Student_ID, Course_ID, and Professor_ID, as the value of one attribute is dependent on the values of two other attributes that are independent of each other.
📗 Advantages of functional dependency
Functional dependencies have several advantages in database design, including:
Reducing data redundancy: By ensuring that each attribute in a relation depends only on the primary key, functional dependencies can help eliminate redundant data in the database. This can help reduce storage space and improve performance.
Ensuring data consistency: Functional dependencies help ensure that data in the database is consistent, meaning that each piece of information is only stored in one place. This can help prevent inconsistencies and errors that can arise from redundant or conflicting data.
Simplifying queries: By reducing redundancy and ensuring consistency, functional dependencies can simplify queries and make it easier to retrieve the data that is needed.
Facilitating updates: By reducing redundancy and ensuring consistency, functional dependencies can also make it easier to update the data in the database without introducing errors or inconsistencies.
Enforcing data integrity: Functional dependencies can be used to enforce data integrity constraints, such as foreign key constraints, that help ensure that the data in the database is accurate and reliable.
Thanks for reading, and happy coding!
Understanding Functional Dependency in DBMS: Definition, Types, and Examples -> Understanding Normalization in DBMS : Maximizing Data Efficiency