Full, Partial & Transitive Dependency in DBMS

Section 01

The Story Behind Functional Dependencies

📖 Real World Analogy

The Supermarket Barcode

At the checkout, the cashier scans a barcode and the screen instantly shows the product name and price. Scan the same barcode tomorrow, in any store — same name, same product. The barcode determines the product details: one value always fixes the others.

Inside a database, that promise is a functional dependency. If a value of X always pins down a value of Y, we say X → Y ("X determines Y"). Some of these dependencies are healthy; others quietly cause redundancy. This tutorial sorts them into the three types that decide a table's design quality: full, partial, and transitive.

💡

The Core Idea

In X → Y, X is the determinant and Y the dependent. The test is simple: if two rows ever share the same X but differ on Y, the dependency does not hold.

Section 02

The Vocabulary You Need

Three quick terms unlock every definition that follows.

🔑

Prime attribute

Part of a candidate key

An attribute that belongs to some candidate key — e.g. Order_ID and Item_ID in an order-line table.

📋

Non-prime attribute

Not in any key

Everything else — the descriptive columns like Qty, Item_Name, or Dept_Location.

✅

Trivial vs non-trivial

Does it tell us anything?

If Y is already inside X, the FD is trivial ({A,B}→A). Normalization only studies non-trivial ones.

Section 03

How to Classify Any Dependency

Faced with a dependency X → Y, ask four questions in order. The animated flowchart leads you straight to its type.

🧮 The Classification Flowchart

Four yes/no questions classify every non-trivial dependency — full and trivial are healthy; partial and transitive signal redundancy.

Section 04

Two Tables We'll Use

One relation has a composite key (for full and partial), the other a single key (for transitive).

ORDER_ITEM — primary key = {Order_ID, Item_ID}
Order_ID	Item_ID	Qty	Item_Name	Order_Date
O1	P1	2	Pen	2026-01-10
O1	P2	5	Notebook	2026-01-10
O2	P1	3	Pen	2026-02-05

EMPLOYEE — primary key = Emp_ID
Emp_ID	EName	Dept_ID	Dept_Location
E1	Raj	D1	Block A
E2	Sara	D2	Block B
E3	Amit	D1	Block A

Section 05

Full Functional Dependency

A full functional dependency means a non-prime attribute depends on the entire composite key — drop any part of the key and the determination fails.

👑 Qty needs both Order_ID and Item_ID

Order O1 alone can't fix Qty (it has 2 and 5). Item P1 alone can't either (2 and 3). Only the pair determines Qty.

✅

This Is the Healthy Kind

Non-prime attributes should depend on the whole key. {Order_ID, Item_ID} → Qty is exactly what 2NF wants to see.

Section 06

Partial Functional Dependency

A partial functional dependency means a non-prime attribute depends on only part of a composite key. It is only possible when the key has more than one attribute.

⚠️ Item_Name needs only Item_ID

Item_ID P1 is always "Pen", regardless of the order. Item_Name is fixed by half the key — that is a partial dependency.

⚠️

Partial Dependencies Break 2NF

Both Item_ID → Item_Name and Order_ID → Order_Date are partial, so "Pen" and the order date get repeated on every matching row. The table is not in 2NF until these are split out.

💡

Single-Key Tables Are Automatically Safe

If the primary key is a single attribute, there is no "part" to depend on — partial dependency is impossible, so the table is always at least in 2NF.

Section 07

Transitive Functional Dependency

📖 Story

The Address You Reach Through the Department

To find where employee Raj sits, you first look up his department (D1), and the department tells you the location (Block A). The location isn't stored because of Raj — it's stored because of the department. That indirect hop, Emp_ID → Dept_ID → Dept_Location, is a transitive dependency.

🔗 Emp_ID → Dept_ID → Dept_Location

A non-key attribute (Dept_Location) depends on another non-key attribute (Dept_ID) — the hallmark of a transitive dependency.

⚠️

Transitive Dependencies Break 3NF

Because Dept_Location is duplicated for every employee in the same department, the EMPLOYEE table is not in 3NF. (A transitive dependency needs at least three attributes.)

Section 08

Why They Matter — The Impact on Normalization

🧱 Each Bad Dependency Blocks a Normal Form

Full dependencies are fine; partial dependencies stop a table reaching 2NF; transitive dependencies stop it reaching 3NF.

Dependency	Problem	Fix to reach
Full	None — desirable	already fine
Partial	Part-key data repeated	2NF
Transitive	Non-key data repeated via a middle attribute	3NF

Section 09

Quick Classification Drills

✅ Classify Each FD (key shown in braces)

{Order_ID, Item_ID} → Qty → Full (whole composite key).

Item_ID → Item_Name, key {Order_ID, Item_ID} → Partial (part of key) → breaks 2NF.

Emp_ID → Dept_Location via Dept_ID, key Emp_ID → Transitive → breaks 3NF.

{Emp_ID, Date} → Emp_ID → Trivial (dependent is inside the determinant).

🧮

The One-Question Shortcut

Ask "what is on the left?" A whole key → full; part of a key → partial; a non-key attribute → transitive. That single glance settles most cases.

Section 10

Full vs Partial vs Transitive — Side by Side

Feature	Full	Partial	Transitive
Left side is	whole key	part of key	non-key attribute
Needs composite key	Yes	Yes	No
Goes through a middle attr	No	No	Yes
Desirable?	Yes	No	No
Blocks	—	2NF	3NF
Example	{Order_ID,Item_ID}→Qty	Item_ID→Item_Name	Emp_ID→Dept_Location

Section 11

Common Mistakes to Avoid

⚠️

Mistake 1 — Hunting for partial dependency with a single key

Partial dependency requires a composite key. A single-attribute key has no "part" to depend on, so it is automatically 2NF.

⚠️

Mistake 2 — Calling a key-to-key link transitive

Transitive means non-key → non-key. If the middle attribute is itself a candidate key, it is not the 3NF-breaking kind.

⚠️

Mistake 3 — Proving an FD from a few rows

Sample rows can disprove a dependency but never prove one. FDs come from the real-world rules of the data, not a snapshot.

Section 12

Golden Rules of Functional Dependencies

🏆 Functional Dependencies — Non-Negotiable Rules

X → Y means same X always gives same Y. One mismatched row disproves it.

Classify by the left side: whole key → full, part of key → partial, non-key → transitive.

Partial and transitive need different keys. Partial needs a composite key; transitive needs three or more attributes.

Remove partial for 2NF, transitive for 3NF. Each bad type maps to exactly one step.

Identify prime vs non-prime attributes first. Every definition hinges on that distinction.

Full dependencies are the goal. A well-designed table has non-prime attributes depending on the whole key and nothing else.