In database management systems (DBMS), functional dependencies are relationships that exist between attributes in a database. They indicate that the value of one attribute (the determinant) uniquely determines the value of another attribute (the dependent). There are several types of functional dependencies that can exist in a database, and they are used to enforce business rules, constraints, and policies on the data in a database, and they are used to maintain the integrity, consistency, and security of the data in a database.
The types of functional dependencies in DBMS are:
- Full functional dependency: This is a relationship between two attributes in a database where the value of the determinant attribute uniquely determines the value of the dependent attribute. For example, consider an employee database with the following attributes:
- Employee ID (determinant)
- Employee name (dependent)
In this database, the employee ID attribute is fully functionally dependent on the employee name attribute because the employee ID is uniquely determined by the employee name. This means that if the employee name is known, the employee ID can be uniquely determined.
- Partial functional dependency: This is a relationship between two attributes in a database where the value of the determinant attribute determines the value of the dependent attribute, but the dependent attribute is not uniquely determined. For example, consider an employee database with the following attributes:
- Employee ID (determinant)
- Employee department (dependent)
In this database, the employee department attribute is partially functionally dependent on the employee ID attribute because the employee department is determined by the employee ID, but the employee department is not uniquely determined. This means that if the employee ID is known, the employee department can be determined, but there may be multiple employees with the same department and employee ID.
- Transitive functional dependency: This is a relationship between three attributes in a database where the value of attribute A determines the value of attribute B, and the value of attribute B determines the value of attribute C. For example, consider an employee database with the following attributes:
- Employee ID (determinant)
- Employee department (dependent)
- Employee location (dependent)
In this database, the employee location attribute is transitively functionally dependent on the employee ID attribute because the employee location is determined by the employee department, and the employee department is determined by the employee ID. This means that if the employee ID is known, the employee department and employee location can be determined.
- Multivalued dependency: This is a relationship between three attributes in a database where the value of attribute A determines multiple values of attribute B, and the value of attribute B determines multiple values of attribute C. For example, consider a database with the following attributes:
- Employee ID (determinant)
- Employee skill (dependent)
- Employee certification (dependent)
In this database, the employee certification attribute is multivalued dependent on the employee ID attribute because the employee certification is determined by the employee skill, and the employee skill is determined by the employee ID. This means that if the employee ID is known, multiple employee skills and employee certifications can be determined.
In summary, functional dependencies are relationships that exist between attributes in a database. They are used to enforce business rules, constraints, and policies on the data in a database, and they are used to maintain the integrity, consistency, and security of the data in a database. The types of functional dependencies in DBMS are full functional dependency, partial functional dependency, transitive functional dependency, and multivalued dependency.