Here, In this article, we are going to discuss the Functional Dependency in DBMS. Data is very crucial whether it is for business or for individuals. Therefore, we should be aware of how the data is being stored, and behave in the database. Also, read about the Data Model in DBMS (Database Management System).
Functional Dependency in DBMS
Functional Dependency in DBMS is a method that describes the relationship between the attributes. It is used to reduce redundancy and to improve the design of the database. We represent functional dependency using an arrow symbol (->).
Key Terms Used in Functional Dependency in DBMS
Here, listing some of the key terms used in Functional Dependency in DBMS.
| Key terms | Descriptions |
| Axiom | It is a rule that is basically used to infer all the Functional Dependency in DBMS |
| Decomposition | If 2 different entities are determined by the same primary key in the same table then they can be decomposed into two different tables. |
| Dependent | It always appears on the right-hand side of the equation of the Functional Dependency in DBMS |
| Determinant | It always appears on the left-hand side of the equation of the Functional Dependency in DBMS |
| Union | If the primary key is the same for 2 different tables, then they can be put together in the same table. |
Like, let’s say in a table, there are many attributes, so how will decide what is the relationship between those attributes, or how these attributes are connected, that is being represented through the Functional Dependency in DBMS?
The general format is of Functional Dependency in DBMS.
X -> Y
It means X determines Y, or it can also be represented as Y is determined by X. Here, X is a determinant ( the main attribute ) and Y is the dependent attribute.
Let us try to understand this with an example:
id -> name
It shows id determines the name.
| Id | Name |
| 1 | John |
| 2 | John |
In this table, if we see we have 2 entries and the name is the same in the 2 entries, but the id is different. So it clearly depicts that there are 2 different people with the same name.
Now, let’s say this table is like this
| Id | Name |
| 1 | John |
| 1 | John |
In this table, there are 2 exactly the same entries. So it clearly depicts that there is only 1 person with the name John who has duplicate entries in the database.
So, if we query using the id column, it will help in reducing a lot of errors, and hence will always give the correct value. This is how the functional dependency work.
There are four different cases available for this example. Let’s see all of them.
Case: 1
| Id | Name |
| 1 | John |
| 1 | John |
Case: 2
| Id | Name |
| 1 | John |
| 2 | John |
Case: 3
| Id | Name |
| 1 | John |
| 2 | Maria |
Case: 4
| Id | Name |
| 1 | John |
| 1 | Maria |
We need to figure out among these 4 mentioned cases what all are the valid cases using the concept of functional dependency.
- Case 1 is a valid case as 2 entries got created for the same person.
- Case 2 is a valid case as it shows 2 different persons with the same name as their ids are different
- Case 3 is also a valid case as it shows 2 different people with different names and different ids.
- Case 4 is an invalid case as the determinant variable is the same for 2 dependent values. Hence, 2 different people cannot have the same id.
Also, read: What is Data Independence in DBMS?
Types of Functional Dependency in DBMS
There are mainly 2 types of functional dependency
- Trivial Functional Dependency in DBMS
- Non Trivial Functional Dependency in DBMS
Let us talk about each type of Function Dependency one by one.
Trivial Functional Dependency in DBMS
If there is any functional dependency, like X -> Y, then Y is the subset of X. Trivial Functional Dependencies are always valid, which means they are always true.
The reason for this always being valid is because the value Y which is being determined is the subset of X, which can never be wrong.
This can be checked and verified by taking the intersection of the left-hand side and the right-hand side, as this will never give the empty value.
Non – Trivial Functional Dependency in DBMS
If there is any functional dependency, like X -> Y, then Y is not the subset of X. The intersection of the left-hand side and the right-hand side will always give the empty value.
In the case of non-trivial functional dependency, we need to properly check which one is a valid case and which is an invalid case.
Properties of Functional Dependency in DBMS
- Reflexivity: This is the first property of the Functional Dependency in DBMS which basically suggests If Y is a subset of X, X -> Y
- Augmentation: It is one of the properties that basically tells If X -> Y, then X.Z -> Y.Z
- Transitive: This is one of the most important properties of the Functional Dependency in DBMS which says, If X -> Y and Y -> Z, then X -> Z. It is one of the important rules in Functional Dependency in DBMS.
- Union: This property says If the primary key is the same for 2 different tables, then they can be put together in the same table. i.e., If X -> Y and X -> Z, then X -> Y.Z
- Decomposition: This property says If 2 different entities are determined by the same primary key in the same table then they can be decomposed into two different tables. i.e., If X-> Y.Z, then X -> Y and X -> Z. It is one of the important rules in Functional Dependency in DBMS.
- Pseudo transitivity: If X -> Y and W.Y -> Z, then W.X -> Z
- Composition: This property basically says, If X -> Y and Z -> W, then X.Z -> Y.W
Important Notes regarding properties of the functional dependency in DBMS
Reflexivity and the Transitive are the most important properties in Functional Dependency in DBMS and will be widely used, and in the decomposition, it will only break the right-hand side, the left-hand side will never be broken into parts. For example:
If X.Y -> Z, then we can’t write it as X -> Z and Y -> Z, this is wrong
Advantages of the Functional Dependency in DBMS
Functional Dependency in DBMS is a very important concept in database management systems as it helps companies, organizations and businesses in the following manner:
- It prevents Data redundancy: Functional Dependency in DBMS ensures that the same data should not be repeated recursively in the database.
- It reduces the risks of attacks and errors: Keeping all types of data like personal, transactions in the database with Functional Dependency in DBMS helps in reducing the risk of errors in the database.
- It maintains the integrity and the quality of the database: Functional Dependency leads to reliable and accurate data. It is because the parameters defined by the Functional Dependency in DBMS create a less redundant and effective system.
- It defines the constraints and the meanings of the databases: Functional dependency in DBMS always follows some rules that control and restrict the data how they are stored, and how they behave in the database.
- It saves cost and gains productivity: Since the data is stored in a good manner, that avoids redundant data, it becomes easier to access the data and it leads to saving time and cost.
- It helps in identifying the poor design of the databases: It helps in seeing how the data is being stored and what all attributes are configured in the table. Is there any missing value or not, and how the data is being populated across the attributes of the table?
Also, read: What is Normalization in DBMS?
Normalization in the Functional Dependency in DBMS
Normalization is a method that allows for avoiding redundancy in the data. It helps in organizing and storing the data in the database. It also helps in deleting the anomaly in the data. It is an easy process for analyzing the schemas based on the different primary keys and the Functional Dependency in DBMS of the data.
It is the process where the big tables are divided into smaller tables and are linked with each other using some relationships. The major purpose of normalization is to avoid repetition in the data and to ensure that the data is being stored well-mannered.
So, data comes everywhere whether it’s for individuals or for businesses. so it should be stored in a very well-mannered database. It should be easy to fetch the value from the database and there should be no redundancy. Functional Dependency in DBMS talks about all these factors.
