Functional Dependencies

A functional dependency is a rule about a table's columns: for a given value of one column (or set of columns), there is exactly one corresponding value of another column, in every row, always. Functional dependencies are the formal vocabulary that every normal form (1NF through 5NF) is defined in terms of — understanding them makes the normal forms a matter of applying a definition rather than memorizing a checklist.

Notation

X → Y

"X determines Y", or "Y is functionally dependent on X". Read: for every value of X, there is exactly one associated value of Y. X is called the determinant.

In ASCII text (e.g. code comments) this is often written X -> Y. For example, in an employees table:

employee_id → first_name, last_name, department_id

Every employee_id maps to exactly one first_name, one last_name, and one department_id. employee_id is a candidate key.

A functional dependency does not require the reverse to hold. employee_id → department_id does not imply department_id → employee_id, since one department has many employees.

Full vs. Partial Functional Dependency

These terms only apply when the determinant is a composite key (more than one column).

Full functional dependency	Y depends on the entire composite key, not on any subset of it. Removing any single column from the key breaks the dependency.
Partial functional dependency	Y depends on only part of a composite key — a subset of the key columns is already enough to determine Y.

Example: an order_items table with composite key (order_id, product_id):

(order_id, product_id) → quantity

Full dependency — quantity genuinely depends on which product was ordered on which order; neither column alone determines it.

(order_id, product_id) → product_name

Partial dependency — product_name depends only on product_id, not on order_id at all. This is exactly the redundancy Second Normal Form removes.

Transitive Dependency

A transitive dependency exists when a non-key column determines another non-key column, chained through the key: X → Y and Y → Z, so indirectly X → Z, where Y is not a candidate key itself.

employee_id → department_id → department_name

department_name depends on employee_id only transitively, through department_id. Storing department_name directly in the employees table duplicates it once per employee in that department. This is exactly the redundancy Third Normal Form removes.

Why These Concepts Underpin the Normal Forms

Each normal form is, at its core, a restriction on which functional dependencies a table is allowed to contain:

1NF	Concerned with atomicity, not functional dependencies directly — a prerequisite before dependency analysis is meaningful at all.
2NF	No partial functional dependencies on a composite candidate key.
3NF	No transitive functional dependencies on the candidate key.
BCNF	Every determinant of a functional dependency must itself be a candidate key — a stricter restatement of 3NF.

Identifying the functional dependencies in a table — usually by listing every column and asking "what value(s) determine this one, uniquely?" — is the practical first step of normalizing any real schema, well before writing a single CREATE TABLE statement.

-- A table exhibiting all three kinds of dependency at once.
-- Composite key: (order_id, product_id)

CREATE TABLE order_items_unnormalized (
    order_id      INT UNSIGNED NOT NULL,
    product_id    INT UNSIGNED NOT NULL,
    customer_id   INT UNSIGNED NOT NULL,
    customer_city VARCHAR(80)  NOT NULL,  -- transitive: order_id -> customer_id -> customer_city
    product_name  VARCHAR(120) NOT NULL,  -- partial: depends only on product_id
    unit_price    DECIMAL(10,2) NOT NULL, -- partial: depends only on product_id
    quantity      INT UNSIGNED NOT NULL,  -- full: depends on (order_id, product_id) together
    PRIMARY KEY (order_id, product_id)
) ENGINE=InnoDB;

-- Functional dependencies present in this table:
--   order_id                     -> customer_id
--   customer_id                  -> customer_city        (transitive on the key)
--   product_id                   -> product_name          (partial on the key)
--   product_id                   -> unit_price             (partial on the key)
--   (order_id, product_id)       -> quantity               (full on the key)
--
-- See First-Normal-Form, Second-Normal-Form and Third-Normal-Form for the
-- step-by-step decomposition that removes the partial and transitive
-- dependencies shown above.

-- A table exhibiting all three kinds of dependency at once.
-- Composite key: (order_id, product_id)

CREATE TABLE order_items_unnormalized (
    order_id      INT UNSIGNED NOT NULL,
    product_id    INT UNSIGNED NOT NULL,
    customer_id   INT UNSIGNED NOT NULL,
    customer_city VARCHAR(80)  NOT NULL,  -- transitive: order_id -> customer_id -> customer_city
    product_name  VARCHAR(120) NOT NULL,  -- partial: depends only on product_id
    unit_price    DECIMAL(10,2) NOT NULL, -- partial: depends only on product_id
    quantity      INT UNSIGNED NOT NULL,  -- full: depends on (order_id, product_id) together
    PRIMARY KEY (order_id, product_id)
) ENGINE=InnoDB;

-- Functional dependencies present in this table:
--   order_id                     -> customer_id
--   customer_id                  -> customer_city        (transitive on the key)
--   product_id                   -> product_name          (partial on the key)
--   product_id                   -> unit_price             (partial on the key)
--   (order_id, product_id)       -> quantity               (full on the key)
--
-- See First-Normal-Form, Second-Normal-Form and Third-Normal-Form for the
-- step-by-step decomposition that removes the partial and transitive
-- dependencies shown above.

MENU

Functional Dependencies

Notation

Full vs. Partial Functional Dependency

Transitive Dependency

Why These Concepts Underpin the Normal Forms