Types of Functional Dependency In DBMS

Functional dependency in a database management system refers to the relationship between attributes in a table, where one attribute uniquely determines another. It helps maintain data accuracy and consistency by ensuring that the value of one attribute (the determinant) defines the value of another attribute (the dependent). In simpler terms, if you know the value of one attribute, you can predict the value of the other.

Determinant

As mentioned earlier, the determinant is an attribute or a set of attributes that determines the value of another attribute. When expressing, it is the left-hand side (LHS) of a functional dependency. For example, in the functional dependency A → B, "A" is the determinant because it uniquely determines the value of "B."

Dependent

The dependent is an attribute or set of attributes whose values are determined by the determinant. It forms the right-hand side (RHS) of a functional dependency. For example, in A → B, "B" is the dependent because its value depends on the value of "A."

It’s imperative to understand that functional dependencies in DBMS are represented by a symbol. This notation has two main components: what is found above or below an arrow (→) as the left-hand side (LHS) and the right-hand side (RHS).

Example of Functional Dependency Notation

Suppose we have a table with attributes A, B, and C. If attribute A determines the values of attributes B and C, we denote this as:

A → B, C

This notation means that if you know the value of A, you can determine the values of B and C.

Now consider a Students table with the following attributes:

From the above table, we can conclude some valid functional dependencies:

Student_ID → Name, Department

Each Student_ID uniquely determines both the Name and Department of a student. For example, Student_ID 142 corresponds to "Alice" in the "Physics" department, and other Student_IDs cannot share these values.

Name→ 

The Name does not uniquely determine the Department, as the same name (e.g., "Bob" and "Charlie") appears in multiple departments. Thus, Name → Department is not valid.

Department→ 

The Department does not uniquely determine the Name because multiple students can belong to the same department. For example, both "Bob" and "Charlie" are in the "Mathematics" department. Therefore, Department → Name is not valid.

Armstrong’s Axioms OR Properties of Functional Dependency in DBMS

Armstrong’s Axioms are a set of rules used to infer all possible functional dependencies in a database. They are used to make sure the database is consistent and well structured. These properties such as reflexivity, augmentation, and transitivity are essential for understanding and managing functional dependencies during normalization.

Reflexivity:

If an attribute or set of attributes is a subset of another, it determines itself.
Example: If A is part of AB, then AB → A.

Augmentation:

Adding extra attributes to both sides of a dependency keeps it valid.
Example: If A → B, then AC → BC.

Transitivity:

If one attribute depends on another, and that one depends on a third, the first can determine the third.
Example: If A → B and B → C, then A → C.

In DBMS, functional dependencies assist in describing how attributes of a table are related to one another. Different types of dependency in DBMS exist, and each is extremely important in the nature of the data being arranged and the accuracy's consistency. It is important for the database to be designed and normalised to these types of specialisations.

Trivial Functional Dependency 

A trivial functional dependency occurs when an attribute or a set of attributes (Coumn) determines itself or any subset of itself. In other words, the dependency is obvious and doesn’t provide any useful information about the relationship between attributes. 

Let’s go back to the student table from earlier: 

These can be considered as trivial functional dependencies:

Student_ID → Student_ID

This is a trivial functional dependency because Student_ID trivially determines itself. All attributes will always functionally determine themselves; therefore, this is a trivial dependency.

Name → Name

Similarly, Name trivially determines Name. Like before, an attribute always determines itself. 

Non-Trivial Functional Dependency 

A non-trivial functional dependency occurs when an attribute or set of attributes determines another attribute. This dependency is also not self-evident. Also the right-hand side of the dependency adds meaningful information that isn't already contained within the left-hand side. Consider the table: 

 Student_ID → Name, Department

Here, Student_ID uniquely determines both Name and Department. This is a non-trivial functional dependency because the left-hand side (Student_ID) determines two other attributes (Name and Department), which are not subsets of Student_ID. The value of Student_ID is enough to uniquely determine the corresponding Name and Department, making this a valid and meaningful dependency.

Multivalued Functional Dependency 

A multivalued functional dependency occurs if one attribute or a set of attributes determines a set of values of another attribute, but dependant set values are mutually independent. That is, if the intersection of one attribute for a relation results in multiple values of each attribute in the other relation, each value can exist independently of the other values.

 Consider the same student table again:

Student_ID → Name, Department

Here, Student_ID determines a set of values for both Name and Department. The dependency indicates that for each unique Student_ID, there is a corresponding set of Name and Department values. Importantly, changes in Name or Department do not affect the other attributes. So each Student_ID maps to these values independently.

Transitive Functional Dependency

A transitive functional dependency is said to be when an attribute indirectly determines another attribute through a third attribute. In other words, if A → B and B → C, then A → C becomes a transitive dependency.

In Table, we can identify a potential Transitive Functional Dependency:

 Student_ID → Name

Name → Department

From these two functional dependencies, we can infer the transitive dependency:

Student_ID → Department

Since Student_ID → Name and Name → Department, we can conclude that Student_ID → Department through a transitive relationship.

Fully Functional Dependency

A Fully Functional Dependency occurs when an attribute is functionally dependent on the entire primary key and not just on a part of it. This type of dependency has every attribute in the table rely entirely on the primary key for its value.

In the table, since we have single attributes like Student_ID acting as the determinant, we can observe the following Fully Functional Dependency:

Student_ID → Name, Department

The attribute Name is fully dependent on Student_ID because knowing the Student_ID alone is sufficient to uniquely determine the corresponding Name. Similarly, the Department is also fully dependent on Student_ID, as each Student_ID uniquely determines the associated Department.

Partial Functional Dependency

A Partial Functional Dependency occurs when an attribute is dependent on only a part of a composite primary key and not the entire key. This type of dependency typically arises in tables with composite keys. Here, some non-key attributes rely on only one part of the composite key.

In the table, since we are working with single attributes (like Student_ID) as primary keys, no Partial Functional Dependency exists. However, if the table had a composite key like a combination of Student_ID and Department, a partial dependency could occur.

For example:

Suppose Student_ID and Department together form a composite primary key, and the attribute Name depends only on Student_ID. In this case:

Student_ID → Name

(Student_ID, Department) → Name

Decomposition

Decomposition refers to the process of breaking down a larger table into smaller and more manageable tables. However, the process also preserves the original data and relationships. This is achieved using functional dependencies so that redundancy and anomalies are reduced. Decomposition helps improve database structure without losing any essential information.

Normalisation

Normalisation is the process of organising data in a database to reduce redundancy and improve data integrity. Functional dependencies play a critical role in normalisation by helping identify and eliminate dependencies that can lead to anomalies. Normalisation progresses through various forms, such as 1NF, 2NF, and 3NF. Each step builds on the previous one to refine the database structure.

Non-normalization

Non-normalization occurs when a database structure does not follow normalisation principles. As a result, it leads to issues such as redundancy, inconsistency, and update anomalies. This happens when functional dependencies are not correctly identified or addressed. The end result is a database that may store duplicate or inconsistent data.

Union

The union operation combines multiple attributes or sets of attributes under a single determinant when their functional dependencies have a shared determinant. For example, if A → B and A → C, the union dependency can be expressed as A → B, C.

Functional dependencies are very important in any formation of a database and are very advantageous in several ways.

1. Data Normalization: Functional dependencies assist normalisation as they help eliminate redundancy and reduce anomalies. These enable one to determine the main and potential keys through which data can be sorted and placed in other small, easily manageable tables.
2. Query Optimization: Functional dependencies make it easy to minimise the number of attributes involved and the query derivation form. They allow the system to detect needed attributes and optimal routes for accessing the data, enhancing query general performance.
3. Consistency of Data: Functional dependencies ensure data consistency by preventing redundancy and inconsistency. Updates to one attribute remain accurate across all dependent attributes, maintaining coherence in the database. 
4. Data Quality Improvement: Functional dependencies are important to ensure that data is reliable, current, and contains all relevant information. General dependencies minimise errors or variations and improve the quality of data used for analysis and decision-making.
5. Data Integrity: Defining functional dependencies makes certain rules relating to attributes that make the relationship within the database valid and accurate. This helps in avoiding the entry of wrong or two sets of figures for a particular item.
6. Efficient Storage: With the help of normalisation, the functional dependencies lead to the reduction of redundant data and thus can make efficient use of storage units. Dividing information into many small, ordered tables can also be less complex, and data in these tables are easier to optimise for usage.
7. Ease of Maintenance: It is easy to update and maintain databases constructed with functional dependencies. Updates are characterised and always expectant because they have a predictive effect on any change of schema.

Functional dependencies can be considered as a foundation of an effective and properly organised system of database management. They make the data consistent, help in the improved execution of the queries, and assist in normalisation by eradicating duplication as well as irregularity. Since there are various types of functional dependencies, the designers of databases can easily maintain them, store information and offer accurate, quality information. To learn more concepts, build your knowledge and upgrade your career, enroll into the CCBP Academy 4.0 program.

1. What is meant by a functional dependency in DBMS?

Functional dependency in DBMS is one of the basic concepts under the relational model, which is the relationship between two sets of attributes; one determines the other set decisively. It aids in managing data and keeping databases in order, and at the same time, it has benefits for data quality.

2. What is the significance of functional dependencies in a database?

When dealing with relation schemes and a relational database, functional dependencies are essential for determining key, table normalisation, and distinct schemes. These increase consistency within the data since it defines the relationship of each attribute in an optimal manner that can enhance the performance of queries.

3. How many functional dependencies are there?

There are four forms of functional dependency: trivial, non-trivial, multivalued, and transitive. Every type is useful for finding certain relationships and constraints within a database.

4. What are the three types of dependencies involved in normalisation?

The three types of dependencies in normalization are Functional Dependency, Partial Dependency, and Transitive Dependency.

5. What is the difference between trivial functional dependencies and non-trivial functional dependencies?

In the trivial dependency, the dependant attributes are always subsets of the determinant attributes. In a non-trivial dependency, the dependent attributes are not included in the determinant attributes. It is better to use non-trivial dependencies to find out the possibility of normalisation.