Functional Dependency in DBMS: What is, Types, and Examples
What is the definition of Functional Dependency?
Functional Dependency (FD) is a constraint in a Database Management System that establishes the relationship between one attribute and another attribute (DBMS). Functional Dependency is important in ensuring that the data in a database is of high quality. It is critical in determining the difference between excellent and bad database architecture.
Example:
| Employee number | Employee Name | Salary | City |
|---|---|---|---|
| 1 | Dana | 50000 | San Francisco |
| 2 | Francis | 38000 | London |
| 3 | Andrew | 25000 | Tokyo |
In the case of a functional dependency, the arrow symbol “is used. Functional dependencies between X and Y are denoted by the symbol X = Y. Let’s look at an example to better grasp Functional Dependency in DBMS. In this example, if we know the value of the Employee number, we may receive information about the employee such as his or her name, city, income, and so on. It follows from this that Employee number has a functional dependency on the city, Employee Name, and salary of a person.
You will learn the following skills in this tutorial:
| Key Terms | Description |
|---|---|
| Axiom | Axioms is a set of inference rules used to infer all the functional dependencies on a relational database. |
| Decomposition | It is a rule that suggests if you have a table that appears to contain two entities that are determined by the same primary key then you should consider breaking them up into two different tables. |
| Dependent | It is displayed on the right side of the functional dependency diagram. |
| Determinant | It is displayed on the left side of the functional dependency Diagram. |
| Union | It suggests that if two tables are separate, and the PK is the same, you should consider putting them. together |
Essentially, it says that if two tables are independent and have the same PK, you should consider merging them. together
Rules of Functional Dependencies
The following are the three most significant rules for functional dependency in a database system:
– is a reflexive rule. If X is a collection of qualities and Y is a subset of X, then X has a value that is equal to Y.
When the relationship x -> y holds and the attribute c is set, the relationship ac -> bc holds as well. That is, attributes are being added that do not alter the fundamental dependencies.
It is quite similar to the transitivity rule in algebra, which states that if the relationship between two variables is valid and the relationship between two variables is valid, then the relationship between two variables is likewise valid. X -> y is referred to as the functional relationship that determines y.
Functional Dependencies in a Database Management System (DBMS) are classified into several categories.
In relational databases, there are primarily four types of functional dependencies. There are several different forms of functional dependencies in a database management system.
Types of Functional Dependencies in DBMS
Dependency on a Multivalued Scale
Functional Dependency Is Inconsequential
Functional Dependency That Isn’t Trivial
Dependency in the Transitive Sense
Multivalued Dependency in DBMS
The circumstance in which there are numerous independent multivalued properties in a single table is referred to as multivalued dependency. It is possible to have a complete constraint between two sets of attributes in relation by using multivalued dependencies. Specific tuples must be present in relation to it to work. To further comprehend this concept, consider the following Multivalued Dependency Example. Using this example, the variables maf year and color are independent of one another, however, they are dependent on the variable car model. In this example, these two columns are referred to as multivalue dependant on the car model because they contain several values.
Example:
| Car_model | Maf_year | Color |
|---|---|---|
| H001 | 2017 | Metallic |
| H001 | 2017 | Green |
| H005 | 2018 | Metallic |
| H005 | 2018 | Blue |
| H010 | 2015 | Metallic |
| H033 | 2012 | Gray |
This reliance can be described in the following way:
maf year -> car model -> maf year
e.g. car model->color
Functional Dependency in a Relational Database Management System (DBMS) is trivial.
The trivial dependency is a collection of attributes that are said to as trivial if all of the attributes in the collection are included in that attribute.
As a result, if Y is a subset of X, the functional relationship X -> Y is straightforward. Let’s look at a trivial functional dependency example to better grasp what we’re talking about. Consider the following table, which contains two columns: Emp id and Emp name.
Trivial Functional Dependency in DBMS
The functional relationship Emp id, Emp name -> Emp id is straightforward because Emp id is a subset of the set Emp id, Emp name.
Functional Dependency in a Relational Database Management System (DBMS) is not trivial.
When A->B holds in the presence of B that is not a subset of A, this is referred to as a functional dependency, also known as a nontrivial dependency. When the attribute B of a connection is not a subset of the attribute A of the relationship, the relationship is regarded to have a non-trivial dependency. Example
For example:
| Emp_id | Emp_name |
|---|---|
| AS555 | Harry |
| AS811 | George |
| AS999 | Kevin |
:
(Company -> Chief Executive Officer) (if we know the Company, we knows the CEO’s name)
The company is not a subset of the CEO, hence there is a non-trivial functional dependency between the two entities.
Non Trivial Functional Dependency in DBMS
In functional dependency, a transitive dependency is a sort of dependency that occurs when the function “t” is indirectly created by two functional dependents. Please consider the following Transitive Dependency Example to better grasp what we are talking about. ‘Company’ is an abbreviation for Corporation. CEO (if we are familiar with the company, we are familiar with its CEO’s name).
| Company | CEO | Age |
|---|---|---|
| Microsoft | Satya Nadella | 51 |
| Sundar Pichai | 46 | |
| Apple | Tim Cook | 57 |
-> -> -> -> -> {Age} If we know the CEO, we can tell you his or her age.
Therefore, according to the law of transitive dependency, the following is true:
Transitive Dependency in DBMS
The relationship “Company” -> “Age” should be maintained since if we know the name of the company, we may determine his age.
The fact that transitive dependency can only occur in a relation of three or more characteristics should be kept in mind at all times.
Example:
| Company | CEO | Age |
|---|---|---|
| Microsoft | Satya Nadella | 51 |
| Sundar Pichai | 46 | |
| Alibaba | Jack Ma | 54 |
{Company} -> {CEO} (if we know the company, we know its CEO’s name)
What is Normalization?
In database management, normalization refers to a strategy of structuring the data in a way that enables you to avoid data redundancy, insertion, update, and deletion anomalies. Analysis of relation schemas is carried out following their various functional dependencies and main key.
Relational database theory is predicated on the concept of normalization. As a result, it may result in duplication of the same data within the database, which may need the development of new tables.
Advantages of Functional Dependency
Functional Dependency Has several Advantages
Functional Dependency eliminates the need for duplicate data. So the same information does not appear at various locations in that database several times.
It assists you in maintaining the integrity of the data in the database.
It aids you in defining the meanings and limitations of database structures.
It assists you in identifying poor designs.
It assists you in locating information on the database design process.