Home › Databases › Functional Dependency

Introduction to Functional Dependency

What a functional dependency actually says

A functional dependency X → Y ("X functionally determines Y") holds in a table when, for any two rows, if they agree on X they must also agree on Y. Formally: for tuples t₁, t₂, if t₁[X] = t₂[X] then t₁[Y] = t₂[Y]. Read it as: the value of X pins down the value of Y.

The common misstatement to avoid: an FD does not require X to be unique, nor Y. It is perfectly normal for X to repeat across many rows — the FD only forbids the same X from being paired with two different Y values.

Dept→DeptLocation holds when all rows sharing a Dept share a DeptLocation, and is violated when two same-Dept rows disagree; X need not be unique

Traced on real rows

In Employees(EmpID, Dept, DeptLocation):

EmpID → Dept, EmpID → DeptLocation — the key determines everything.
Dept → DeptLocation — every employee in "Sales" sits in the same office. Note Dept is not unique (dozens of people are in Sales) yet the FD holds.
Dept → EmpID does not hold — one department has many employees, so the same Dept maps to different EmpIDs.

Here are those dependencies drawn as arrows on the table itself — determinant at the tail, determined column at the head. The blue arrows out of EmpID are just "it is the key"; the red arrow is the interesting one:

Employees table with EmpID, Dept, DeptLocation columns and three rows (E1 Sales NYC, E2 Sales NYC, E3 Eng LON); blue arrows over the header show EmpID determines Dept and DeptLocation, a red arrow under the table shows Dept determines DeptLocation, the non-key determinant 3NF removes; note that Dept does not determine EmpID since Sales maps to E1 and E2

📬 Worked Trace — ZipCode → City:
Consider a table mapping client addresses:

Address_ID (PK)	Street	ZipCode	City
1	120 Main St	90210	Beverly Hills
2	450 Oak Ave	90210	Beverly Hills
3	789 Pine Rd	10001	New York
4	12 Wall St	10001	New York

The functional dependency ZipCode → City holds because:

Row 1 and Row 2 share the same ZipCode (90210) and agree on the same City (Beverly Hills).
Row 3 and Row 4 share the same ZipCode (10001) and agree on the same City (New York).

This proves that ZipCode functionally determines City. However, ZipCode is not a primary key (it repeats across multiple rows). The FD simply dictates that the same ZipCode can never be mapped to two different cities in this table.

That last non-dependency (Dept → DeptLocation holds but the table's key is EmpID) is exactly a transitive dependency — a non-key column determining another non-key column — and it is the thing 3NF exists to remove. The entire normalization track is built on this definition, which is why getting it right matters.

Pitfalls

"X → Y means X is unique" — false. Dept → DeptLocation with a repeating Dept is the canonical counterexample.
FDs are a statement about all allowed states of the table (the business rule), not about one snapshot. A single snapshot can't prove an FD, but it can disprove one.

Takeaways

X → Y: rows agreeing on X must agree on Y — X determines Y.
Neither X nor Y need be unique; only "same X, different Y" is forbidden.
A non-key → non-key FD is a transitive dependency — the target of 3NF.

Re-authored for correctness for this guide (the prior version defined an FD as requiring unique values). Definition per Silberschatz, "Database System Concepts" & Codd. See also: Closure of Attribute Sets, Normalization (3NF), Keys.

🎯 STRICT STANDOUT: Why / worked / when-not / failure / drills — Introduction to Functional Dependency

Why this concept exists (judgment chain)

An FD X→Y is a constraint on all legal states: same X forces same Y. It does not require X unique. Normalization is just systematic removal of bad FDs (partial, transitive) that cause anomalies — so a wrong FD definition poisons every later NF decision.

Worked example with numbers or traced steps

Employees: E1 Sales NYC; E2 Sales NYC; E3 Eng LON.
Dept → DeptLocation HOLDS (Sales always NYC) even though Dept repeats.
Dept → EmpID FAILS (Sales maps to E1 and E2).
Zip table: 90210→Beverly Hills twice, 10001→New York twice → Zip→City holds;
Zip is not a key.
Violation test: one snapshot can disprove (two same-X different-Y rows);
proving holds requires the business rule / all allowed states.

When NOT to use / named alternative

Do not invent FDs from one CSV sample (“looks unique”). Do not treat candidate keys as the only determinants — non-key determinants are exactly what 3NF/BCNF hunt. Skip FD modeling only for throwaway staging dumps with no integrity needs.

Failure / ops fingerprint

Fingerprint: “Dept determines location so Dept must be unique” confusion; schemas that store DeptLocation on every employee and suffer update anomalies. Ops: encode FDs as UNIQUE constraints or separate tables; document business FDs in schema review.

Hostile-panel drills (defend the decision)

Q1. Does X→Y imply X is unique?
Model answer: No. Many rows may share X; they must share Y. Uniqueness of X is a key property, a special FD X→all attributes.

Q2. Give a transitive dependency from the employee example.
Model answer: EmpID→Dept and Dept→DeptLocation imply EmpID→DeptLocation; Dept→DeptLocation is non-key→non-key — 3NF target.

Q3. How do you disprove an FD on data?
Model answer: Exhibit two tuples equal on X and unequal on Y. Absence of counterexample in one dump does not prove the FD.

🤖 Don't fully get this? Learn it with Claude

Stuck on Introduction to Functional Dependency? Open Claude, copy a block below, and it'll teach you this exact concept — visually and interactively.

🎨 Explain it visually

Build the mental picture, not memorization.

I just read a lesson on **Introduction to Functional Dependency** (Databases) and want to truly understand it. Explain Introduction to Functional Dependency from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

🤔 Walk me through it (interactive)

Socratic — adapts to where you're stuck.

Teach me **Introduction to Functional Dependency** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

🧪 Quiz me & fix my gaps

Active recall exposes what you missed.

Quiz me on **Introduction to Functional Dependency** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

🧠 Make it stick

Intuition + hook + flashcards for long-term memory.

Help me remember **Introduction to Functional Dependency** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

📝 My notes

Types of Functional Dependencies →