Concepts and theorems · Intermediate

Functional dependencies: a determinant fixes a value

Distinguish keys, semantic dependencies and accidental patterns with an employee-and-department example.

Ask Logeia about this articleTry the three questionsReview your answers

Updated

On this page
  1. Define a constraint over pairs of rows
  2. Work through employee and department rules
  3. Keys determine all attributes
  4. Separate schema rules from planner statistics
  5. Ask Logeia about this articleCheck your understanding

Define a constraint over pairs of rows

A functional dependency X → Y requires that any two rows agreeing on attribute set X also agree on Y. The determinant X fixes a value of Y within every valid instance of the relation. This is different from a pattern that happens to hold in the current sample. Similar column names or matching data types do not establish the rule either. Dependencies come from the intended meaning and constraints of the data. Database design then asks how to represent and enforce those constraints so that future inserts and updates cannot silently invalidate the assumed relationships.

Work through employee and department rules

Consider Employee(employee_id, department_id, department_name). Assume each employee belongs to one department and each department ID has one name. Then employee_id determines department_id, and department_id determines department_name; transitivity gives employee_id → department_name. The reverse dependency from department_name to department_id does not follow unless names are also unique by rule. Reversing an arrow needs justification. If the table instead stores an employee’s department history, the determinant may need a time or assignment identifier. A dependency is always relative to the meaning of the relation being designed.

Keys determine all attributes

The closure of X collects attributes derivable from X using the given dependencies. If it contains every attribute of the relation, X is a superkey. A candidate key is a minimal such set: removing an attribute would lose that property. In the example, employee_id determines every attribute, but department_id does not determine one employee ID because many employees may share a department. These ideas relate to SQL primary-key and uniqueness constraints, but implementation details such as null treatment and composite-key semantics still need attention. A logical dependency should not be confused with an unchecked naming convention.

Separate schema rules from planner statistics

Repeating a department name on every employee row creates a need to keep many copies consistent when a department is renamed. Analysing dependencies helps justify decompositions that avoid that update problem. In contrast, dependency statistics collected for a query planner describe relationships useful for estimation; they do not automatically enforce a business constraint. A strong observed association and an exception-free schema rule are different kinds of information. If the meaning of an entity changes, both data and constraints may need revision. Normalization begins with the intended rules, not solely with whichever correlations a recent sample contains.

Reading and practice are recorded separately in this browser, for each language.

Learn this in order

Understand the systems behind the web · STEP 11

Choose a learning path