Hello! In our last lesson, we established why normalization is crucial by looking at the data integrity problems—insertion, update, and deletion anomalies—caused by data redundancy. We saw that decomposing large tables into smaller, focused ones is the solution.
Today, we take the first step in learning how to normalize. To properly decompose tables, we need a precise way to describe the relationships between attributes within a single table. This lesson introduces that fundamental tool: functional dependency.
By the end of our session, you'll be able to define what a functional dependency is and, more importantly, practice identifying these dependencies in a given table. Mastering this skill is essential, as it forms the basis for all the normalization rules we will cover next.
1. What is a Functional Dependency?
At its core, a functional dependency is a constraint between two attributes (or sets of attributes) in a relation. It describes a situation where the value of one attribute uniquely determines the value of another.
If we have attributes A and B, we say that "B is functionally dependent on A" if, for any given value of A, you can find exactly one corresponding value of B.
We use the notation: A → B
- A is called the determinant.
- B is called the dependent.
You can think of it like a function in mathematics, f(x) = y, where x is the determinant and y is the dependent. Given an input x, you get one specific output y.
To get a clear and detailed explanation, let's watch a short video that introduces the concept.
The video "Functional Dependency in DBMS" by making IT simple provides a great introduction to this topic.
Please watch the first two parts of the video: Start with the introduction to the concept, which explains what A → B means in a database context. Then, watch the brief section that defines the terms determinant and dependent.
2. Identifying Functional Dependencies in a Table
Now for the practical part: how do we look at a table of data and figure out which functional dependencies hold true?
The rule is simple: For a functional dependency A → B to be valid, every time the same value for A appears in the table, the corresponding value for B must also be the same.
Let's break this down:
- It's perfectly fine for different values of A to determine the same value of B. (e.g.,
InstructorName→Department. Many instructors can belong to the same department). - It is not fine for one value of A to determine multiple different values of B. (e.g.,
StudentID→StudentName. A single student ID cannot correspond to two different names).
The following video segment walks through several examples, clearly showing what makes a functional dependency valid or invalid.
This segment of the same video uses a sample table to demonstrate the four possible scenarios when checking for a functional dependency.
Watch the section that walks through different examples of valid and invalid dependencies. Pay close attention to the final example (Student Name → Department Name), which shows a clear violation.
The logic demonstrated in the video can be summarized with a simple flowchart. When checking if A → B:
Practice Exercise
Let's apply this. Consider the following table instance for a relation R(A, B, C, D).
| A | B | C | D |
|---|---|---|---|
| a1 | b1 | c1 | d1 |
| a1 | b2 | c2 | d1 |
| a2 | b2 | c3 | d2 |
| a3 | b3 | c2 | d1 |
Which of the following functional dependencies holds true for this instance of the data?
A → BB → CC → AD → A
Think through each one before revealing the solution.
Click here to see the solution and explanation
Let's analyze each one:
-
A → B: Invalid. In the first row,a1determinesb1. In the second row, the same valuea1determinesb2. Since one value ofApoints to two different values ofB, this dependency is violated. -
B → C: Invalid. In the second row,b2determinesc2. In the third row, the same valueb2determinesc3. This dependency is violated. -
C → A: Invalid. In the second row,c2determinesa1. In the fourth row, the same valuec2determinesa3. This dependency is violated. -
D → A: Valid.d1appears in rows 1, 2, and 4.- In row 1,
d1→a1. - In row 2,
d1→a1. - In row 4,
d1→a3. - Whoops, let's re-check. In row 1
d1determinesa1, but in row 4d1determinesa3. So this is invalid. My apologies for the misstep, this is a great example of how carefully you must check every row!
Let's try one more: (A, C) → B. This is a functional dependency with a composite determinant.
- Row 1:
(a1, c1)→b1. - Row 2:
(a1, c2)→b2. - Row 3:
(a2, c3)→b2. - Row 4:
(a3, c2)→b3.
Since there are no duplicate pairs of(A, C)values in the table, the condition is not violated. Therefore, the functional dependency(A, C) → Bis valid for this data. This type of dependency, where the determinant is composed of multiple columns, will be very important when we discuss Second Normal Form (2NF).
3. The Rules of Functional Dependencies
Functional dependencies are not just properties you observe; they also follow a set of logical rules, known as Armstrong's Axioms. These rules allow you to infer or discover new FDs based on a set of known FDs. This is particularly useful in complex designs where you can't rely on just looking at sample data.
For your IT background, you might find this formal approach interesting as it mirrors logical inference rules in other areas of computer science.
Chapter 11: Functional Dependencies
This chapter from an open-source database design textbook provides a formal definition of FDs and introduces the key inference rules.
Start by reading the introductory section, from the beginning down to "Rules of Functional Dependencies". This will reinforce the definitions of FD, determinant, and dependent. Next, skim the table example and the observations below it. Finally, read the section on "Inference Rules", focusing on the definitions of the Axioms of Reflexivity, Augmentation, and Transitivity. These three are the most fundamental. The Union and Decomposition rules are also very intuitive and useful.
Of these rules, Transitivity is especially important. It states:
If A → B and B → C, then we can infer that A → C.
This is the exact type of dependency we will need to eliminate when we get to Third Normal Form (3NF). For example:
- If
StudentID → DepartmentID, - and
DepartmentID → DepartmentHeadName, - then a transitive dependency exists:
StudentID → DepartmentHeadName. This indicates a design problem that normalization will fix.
Conclusion
In this lesson, we demystified the concept of functional dependency, the engine that drives normalization. You learned how to define and identify these critical relationships within your data tables.
Key Takeaways:
- A functional dependency (FD), denoted A → B, exists if the value of attribute A uniquely determines the value of attribute B.
- A is the determinant, and B is the dependent.
- To check if A → B is valid, you must verify that for any given value of A, there is always exactly one corresponding value of B throughout the table.
- FDs can be inferred using logical rules, such as Armstrong's Axioms.
You now have the foundational skill needed to start normalizing. In our next lesson, we will put this knowledge to immediate use as we define the requirements for the First Normal Form (1NF) and learn how to convert an unnormalized table to meet them.