Identifying Functional Dependencies from Data
You'll learn: How to discover functional dependencies by examining sample data and understand the critical difference between instance-level patterns and true schema-level constraints.
The Detective Work of Database Design
When designing or analyzing a database, you often need to infer functional dependencies from existing data rather than having them handed to you. This is detective work: you look at sample rows and try to determine which columns functionally determine others.
Analyzing Sample Data
Imagine you have this employee data:
EmployeeID | DepartmentID | DepartmentName | Salary
101 | 5 | Sales | 50000
102 | 3 | IT | 60000
103 | 5 | Sales | 55000
By inspection, you might notice:
- Whenever
EmployeeIDis the same, all other values are the same →EmployeeIDdetermines everything - Whenever
DepartmentIDis 5,DepartmentNameis "Sales" → possible FD:DepartmentID → DepartmentName
The Critical Distinction: Instance vs Schema
This is where beginners stumble. Just because a pattern holds in your current data (the instance) doesn't guarantee it's a true constraint at the schema level.
In our example, you see Salary varies even when DepartmentID is the same (both 103 and 101 are in department 5). So DepartmentID → Salary is clearly not an FD.
But what about DepartmentID → DepartmentName? It looks good... but you need domain knowledge! If the business rule states "departments always have one name," it's a true FD. If departments could theoretically be renamed over time (and you're keeping history), maybe it's not.
Key Takeaway: Sample data reveals candidate functional dependencies, but only business rules and schema-level constraints confirm true FDs. Always validate patterns against real-world semantics, not just current instances.