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 −
| A | B |
|---|---|
| 1 | X |
| 2 | Y |
| 3 | Z |
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).
| Roll Number | Student Name | Department Name | Department Building |
|---|---|---|---|
| 1 | Alice | CS | A4 |
| 2 | Bob | IT | B2 |
| 3 | Carol | CS | A4 |
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 −
| Department Name | Department Building |
|---|---|
| CS | A4 |
| IT | B2 |
| ME | B2 |
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 −
| Student Name | Department Name |
|---|---|
| Bob | CS |
| Bob | IT |
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.
Leave a Reply