Functional Dependency in DBMS – Different Types Explained

Functional dependency is a big deal in the world of database management systems (DBMS). It’s like the hidden strings that keep all the data organised and in line. Think of it as a conductor silently directing how different parts of data relate to each other, making sure everything stays coherent and reliable. 

In simple terms, functional dependency keeps everything tidy and logical in the digital world, ensuring that data is in the right place and databases work smoothly. Functional dependency in DBMS is the behind-the-scenes architecture that makes sure our databases are strong and efficient.

What is Functional Dependency in DBMS?

In database management systems (DBMS), a Functional Relationship is a core concept that illustrates the connection between attributes (fields) within a table. It elucidates how the values in one or more attributes dictate the value in another. In essence, it clarifies how information in a single column or a cluster of columns corresponds with data in another column, thus assisting in maintaining the integrity of data within the DBMS.

This correlation is commonly illustrated through an equational representation featuring a collection of attributes (A, B, C, etc.) and an arrow (->) denoting the association. For example, in a table containing employee information with columns such as “EmployeeID,” “FirstName,” and “LastName,” a functional dependency can be expressed as:

EmployeeID -> FirstName, LastName.

In DBSM, you denote functional dependencies with a notation. The denotation has two major components: the RHS (right-hand side) and the LHS (left-hand side) of an arrow (->).

For example, let’s imagine a situation where a table comprises attributes like “A,” “B,” and “C,” and attribute “A” influences the values of both “B” and “C.” This association is represented as:

A -> B, C

This notation indicates that the value(s) in attribute “A” determines the value(s) in attributes “B” and “C.” Put simply, if you know the value of “A,” you can determine the values of “B” and “C”.

Rules/Properties of Functional Dependency 

In relational databases, there exist three pivotal rules governing functional dependencies, famously known as Armstrong’s axioms. Understanding these axioms is essential for those interested in pursuing a career in database management:

• Reflexive Rule: This principle asserts that if X represents a set of attributes and Y is a subset of X, then X inherently implies Y.

• Augmentation rule: Under this guideline, additional attributes may be introduced without fundamentally altering the underlying dependencies. In essence, if X→Y is true and C represents an attribute set, then XC→YC also holds true.

• Transitivity rule: Analogous to the transitive property in algebra, this rule dictates that if X→Y and Y→Z are valid, then X→Z must also hold. In this scenario, X→Y serves as the foundational dependency determining Y.

Also Read: Relational Model in DBMS

Types of Functional Dependencies in DBMS

There are four main types of functional dependencies in a DBMS. Following are the types of functional dependencies in DBMS:

• Multivalued dependency
• Trivial functional dependency
• Non-trivial functional dependency
• Transitive dependency

Multivalued Dependency

In certain instances, multivalued dependency arises when there are numerous independent multivalued attributes within a singular table. It represents a comprehensive constraint between two attribute sets in a relation, necessitating the presence of specific tuples within the relation. Let’s look into an example of multivalued dependency to gain a clearer understanding.

Example:  

In this instance, the attributes “maf_year” and “colour” exhibit independence from each other but display dependency on the “bike_model.” In such a scenario, these two columns are characterised as multivalue dependent on the “bike_model.”

This dependence can be represented like this:

bike_model -> maf_year

bike_model-> colour

Trivial Functional Dependency

Trivial dependency refers to a set of attributes that are considered trivial if the attributes within that set encompass the entirety of another attribute. 

Therefore, X -> Y constitutes a trivial functional dependency if Y represents a subset of X. Let’s elucidate this concept with an example of a trivial functional dependency.

Example:

Consider this table 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

A non-trivial dependency arises when A->B is valid and B is not a subset of A. In a relational context, if attribute B does not constitute a subset of attribute A, it signifies a non-trivial dependency.

Example:

(Company} -> {CEO} (if we know the Company, we know the CEO name)

However, the CEO is not a subset of the Company, and hence it’s a non-trivial functional dependency.

Transitive Dependency in DBMS

A transitive dependency occurs within functional dependencies when “t” is indirectly determined by two other functional dependencies. Let’s illustrate this concept with the following example of transitive dependency.

Example:

{Company} -> {CEO} (if we know the company, we know its CEO’s name)

{CEO } -> {Age} If we know the CEO, we know the Age

Therefore, according to the 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.

Advantages of Functional Dependency

• By identifying and eliminating unnecessary or duplicate data, they contribute to minimising data redundancy within databases. 
• Ensuring data accuracy and consistency across the database enhances data integrity.
• They streamline the process of adding, modifying, and deleting data, thereby facilitating effective database management.

Disadvantages of Functional Dependency

• Identifying functional dependencies can prove challenging and time-intensive, particularly within extensive databases featuring numerous tables and connections. 
• Excessively rigid functional dependencies might lead to sluggish query execution or inconsistencies in data, as interconnected data may not be adequately linked. 
• Furthermore, functional dependencies may overlook the semantic significance of data, potentially failing to accurately represent the genuine relationships among data elements.

In a Nutshell,

Functional dependency is like the backbone of a strong and reliable database system. It makes sure that the data stays organised and accurate. By showing how different pieces of data relate to each other, it helps in setting up the database effectively. Knowing about functional dependency is important for anyone working with databases because it helps in building databases that work well and give useful information. 

If you’re interested in learning more about data analytics and business intelligence, checking out an “Accelerator Program in Business Analytics and Data Science” could be a great idea. It offers valuable skills and opportunities to grow in your career. Don’t miss out on this chance to take your career to the next level!