Partial and Transitive Dependencies

Learning outcomes

Two dependency patterns explain much of the redundancy removed by second and third normal form: partial dependencies and transitive dependencies. Understanding them before memorizing normal-form definitions makes normalization much easier.

Partial dependency

A partial dependency occurs when a non-key attribute depends on only part of a composite candidate key.

Suppose:

Enrollment(  student_id,  course_id,  student_name,  course_title,  final_grade)PRIMARY KEY (student_id, course_id)

Then:

\[ student\_id \rightarrow student\_name \]

and:

\[ course\_id \rightarrow course\_title \]

while:

\[ (student\_id, course\_id) \rightarrow final\_grade \]

Why this is partial

student_name depends on one component of the key rather than the full key. It therefore repeats in every enrollment row for that student.

student_id | course_id | student_name | course_title-----------+-----------+--------------+-------------S1         | C1        | Amina Rahimi | DatabasesS1         | C2        | Amina Rahimi | NetworkingS1         | C3        | Amina Rahimi | Statistics

Composite keys expose the pattern

Partial dependencies matter when a candidate key contains multiple attributes. If the key is a single attribute, no proper subset of that key exists, so a partial dependency on that key is impossible.

Resolve partial dependencies

Separate the facts according to their true determinants:

Student(  student_id,  student_name)Course(  course_id,  course_title)Enrollment(  student_id,  course_id,  final_grade)

Now each relation stores facts at one grain.

Transitive dependency

A transitive dependency occurs when a non-key attribute depends on the key through another non-key attribute.

WorkOrder(  work_order_id,  asset_id,  status_code,  status_display_name,  opened_at)

Suppose:

\[ work\_order\_id \rightarrow status\_code \]

and:

\[ status\_code \rightarrow status\_display\_name \]

Then:

\[ work\_order\_id \rightarrow status\_display\_name \]

transitively.

Why transitive dependencies create redundancy

Every WorkOrder with status closed repeats the same status_display_name. If the label changes from “Closed” to “Completed,” many rows must be updated.

Resolve transitive dependencies

WorkOrder(  work_order_id,  asset_id,  status_code,  opened_at)WorkOrderStatus(  status_code,  status_display_name)

The descriptive status fact is stored once, at the grain determined by status_code.

Partial versus transitive dependency

WorkshopHub partial-dependency example

PartUsageBad(  work_order_id,  part_id,  part_description,  quantity)PRIMARY KEY (work_order_id, part_id)

If:

\[ part\_id \rightarrow part\_description \]

then part_description depends on only part of the composite key. It belongs in Part, not PartUsage.

WorkshopHub transitive-dependency example

Asset(  asset_id,  manufacturer_id,  manufacturer_name,  serial_number)

If:

\[ asset\_id \rightarrow manufacturer\_id \]

and:

\[ manufacturer\_id \rightarrow manufacturer\_name \]

then manufacturer name is transitively dependent on asset_id.

Not every transitive-looking path is a problem

If the intermediate attribute is itself a candidate key, the dependency structure may still satisfy stronger normal forms. The formal normal-form definitions matter. For now, the key intuition is: store each independently governed fact with the determinant that actually identifies it.

Surrogate keys can hide partial-dependency intuition

If the bad Enrollment table gets a surrogate:

enrollment_id PRIMARY KEY

you may no longer see a composite primary key, but the business dependency:

\[ (student\_id, course\_id) \rightarrow final\_grade \]

still exists. Normalization must consider candidate keys and business dependencies, not just the chosen surrogate primary key.

Dependency diagrams

Practice: classify the dependency

OrderLine(  order_id,  line_number,  customer_id,  customer_name,  product_id,  product_name,  quantity)

Summary and next lesson

Partial dependencies attach facts to only part of a composite key; transitive dependencies attach facts to another non-key determinant. Both patterns duplicate information and prepare the ground for anomalies. The next lesson examines those anomalies directly.

References

• E. F. Codd, “Further Normalization of the Data Base Relational Model,” 1971.
• C. J. Date, Database Design and Relational Theory.
• Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.