DBMS - Functional Dependency

Functional Dependency is one of the basic but important concepts in databases. It is highly useful in understanding normalization and how attributes in a table relate to one another. We have come across this term "functional dependency" while studying the concepts of Database Design and topics like Keys and Normal Forms.

In this chapter, we will cover the concepts of functional dependencies in detail with simple explanations and practical examples for a better understanding.

What is Functional Dependency?

In short, functional dependency is all about relationships between attributes (columns) in a table. We can say that the value of one attribute (or a group of attributes) can be used to determine the value of another attribute in the same table.

Think of it this way: If we know the value of A, can we always find the corresponding value of B? If yes, then we say A determines B, or we can represent this with an arrow, A → B. We can also say that B is dependent on A.

To get a better understanding, let us compare this concept with a function in mathematics. Imagine we have a function f where we input a number 'a', and it calculates the square of that number as the output 'b'.

For example − Input a = 5, the function calculates b = 25. Here, "a uniquely determines b".

In databases, it is a little bit different. Instead of calculating a value, we simply use one attribute (or a set of attributes) to find or identify another. Let us see this with a table example.

Functional Dependency in Action

Consider a table T with two columns, A and B −

Here, if someone gives us the value of A, like A = 2, we can look at the table and find that the corresponding value of B is Y. This tells us that A determines B, or A → B.

Key Components of Functional Dependency

In functional dependencies we must keep in mind several terms or components −

• Determinant − The attribute(s) used to determine the other attribute (Left hand attribute)
• Dependent − The attribute whose value is determined (Right hand attribute)

In the example above −

• A is the determinant
• B is the dependent

Examples of Functional Dependency

Let us see more detailed examples and analyze if the dependencies are valid or not.

Example 1: Roll Number Determines Student Information

Imagine a table with the following attributes: (Roll Number, Student Name, Department Name, and Department Building).

If we say Roll Number → Student Name, Department Name, Department Building, is this a valid functional dependency?

Yes, because, each roll number is unique, meaning it can help us identify the student's name, department, and building. For example −

If Roll Number = 3, we can easily find that the student is Carol, in the CS department, and her building is A4.

Example 2: Department Name Determines Department Building

Using the same table, let us see another example. Check if Department Name → Department Building is a valid functional dependency or not. Observe carefully, the Department Name column has duplicate values: "CS" appears twice. However, for both instances, the corresponding Department Building is the same (A4).

So, even though there's redundancy, this is still a valid dependency because CS always maps to A4, no matter how many times it appears.

Example 3: Different Determinants with the Same Dependent

Now, consider another functional dependency, say Department Name → Department Building, but we add another department to the table −

Here, two departments (IT and ME) share the same building (B2). Is this valid?

Yes, it is also valid. Multiple determinants (IT and ME) can point to the same dependent (B2). This does not violate the rules of functional dependency.

Example 4: Student Name Determines Department Name

Consider another example. Here the functional dependency Student Name → Department Name.

In our table, two students (Bob and another student) could share the same name but belong to different departments. For instance −

If we ask, "Which department is Bob in?", there are two possible answers: CS and IT. This makes the dependency invalid. This is because a single determinant (Student Name) does not lead to a unique dependent (Department Name).

Analyzing Valid and Invalid Cases

Now let us categorize functional dependency scenarios −

• Unique Determinant to Unique Dependent − Example: Roll Number → Student Information. This is valid because each roll number is unique and maps to a specific set of values.
• Same Determinant with Same Dependent − Example: Department Name → Department Building. Valid even with duplicate department names. And as they always map to the same building.
• Different Determinants with Same Dependent − Example: IT → B2, ME → B2. This is valid because multiple attributes can lead to the same dependent value.
• Same Determinant with Different Dependents − Example: Student Name → Department Name (with duplicates). This is invalid because one determinant (Student Name) leads to multiple, conflicting values.

Importance of Functional Dependency

The functional dependencies are important for database normalization. They help us identify several factors as listed below −

• Redundancies in data
• Which attributes should serve as keys
• How to design efficient, error-free database structures

Without properly handling functional dependencies, we might design database tables that are prone to anomalies and inefficiencies.

Conclusion

In this chapter, we explained the concept of functional dependency with real-world examples. We have seen the basics. We explored different scenarios to determine whether a functional dependency is valid or invalid.

Through examples like Roll Number → Student Information and Student Name → Department Name, we understood how unique and redundant data affect dependency. We also understood the difference between valid and invalid cases, such as same determinants with different dependents leading to invalid dependencies.