Course contentsShow
← Computer Science
Lesson 1623 of 2,87237. Database Design and NormalizationFree lesson

Identifying Functional Dependencies from Data

Techniques for discovering functional dependencies by analyzing sample data and understanding instance vs schema constraints.

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 EmployeeID is the same, all other values are the same → EmployeeID determines everything
  • Whenever DepartmentID is 5, DepartmentName is "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.