Example: In this example, if we know the value of Employee number, we can obtain Employee Name, city, salary, etc. By this, we can say that the city, Employee Name, and salary are functionally depended on Employee number. In this tutorial, you will learn:
Key terms
Rules of Functional Dependencies
Types of Functional Dependencies in DBMS
Multivalued dependency in DBMS
Trivial Functional dependency in DBMS
Non trivial Functional dependency in DBMS
Transitive dependency in DBMS
What is Normalization?
Advantages of Functional Dependency
Key terms
Here, are some key terms for Functional Dependency in Database:
Rules of Functional Dependencies
Below are the Three most important rules for Functional Dependency in Database:
Reflexive rule –. If X is a set of attributes and Y is_subset_of X, then X holds a value of Y. Augmentation rule: When x -> y holds, and c is attribute set, then ac -> bc also holds. That is adding attributes which do not change the basic dependencies. Transitivity rule: This rule is very much similar to the transitive rule in algebra if x -> y holds and y -> z holds, then x -> z also holds. X -> y is called as functionally that determines y.
Multivalued Dependency in DBMS
Multivalued dependency occurs in the 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. It requires that certain tuples be present in a relation. Consider the following Multivalued Dependency Example to understand.
Multivalued Dependency Trivial Functional Dependency Non-Trivial Functional Dependency Transitive Dependency
In this example, maf_year and color are independent of each other but dependent on car_model. In this example, these two columns are said to be multivalue dependent on car_model. This dependence can be represented like this: car_model -> maf_year car_model-> colour
Trivial Functional Dependency in DBMS
The Trivial dependency is a set of attributes which are called a trivial if the set of attributes are included in that attribute. So, X -> Y is a trivial functional dependency if Y is a subset of X. Let’s understand with a Trivial Functional Dependency Example. For example: Consider this table of 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
Functional dependency which also known as a nontrivial dependency occurs when A->B holds true where B is not a subset of A. In a relationship, if attribute B is not a subset of attribute A, then it is considered as a non-trivial dependency. Example: (Company} -> {CEO} (if we know the Company, we knows the CEO name) But CEO is not a subset of Company, and hence it’s non-trivial functional dependency.
Transitive Dependency in DBMS
A Transitive Dependency is a type of functional dependency which happens when “t” is indirectly formed by two functional dependencies. Let’s understand with the following Transitive Dependency Example. Example: {Company} -> {CEO} (if we know the compay, we know its CEO’s name) {CEO } -> {Age} If we know the CEO, we know the Age Therefore according to the rule of rule of transitive dependency: { Company} -> {Age} should hold, that makes sense because if we know the company name, we can know his age. Note: You need to remember that 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 the database which helps you to avoid data redundancy, insertion, update & deletion anomaly. 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 duplicating the same data within the database which may result in the creation of additional tables.
Advantages of Functional Dependency
Functional Dependency avoids data redundancy. Therefore same data do not repeat at multiple locations in that database It helps you to maintain the quality of data in the database It helps you to defined meanings and constraints of databases It helps you to identify bad designs It helps you to find the facts regarding the database design
Summary
Functional Dependency is when one attribute determines another attribute in a DBMS system. Axiom, Decomposition, Dependent, Determinant, Union are key terms for functional dependency Four types of functional dependency are 1) Multivalued 2) Trivial 3) Non-trivial 4) Transitive Multivalued dependency occurs in the situation where there are multiple independent multivalued attributes in a single table The Trivial dependency occurs when a set of attributes which are called a trivial if the set of attributes are included in that attribute Nontrivial dependency occurs when A->B holds true where B is not a subset of A A transitive is a type of functional dependency which happens when it is indirectly formed by two functional dependencies Normalization is a method of organizing the data in the database which helps you to avoid data redundancy
