Functional dependency

As explained in 100 SQL Server Mistakes and How to Avoid Them

A functional dependency is a relationship in which one set of attributes determines the value of another set of attributes.

A functional dependency means an attribute’s value is determined by a key or another attribute. It is a practical test for whether a relation stores facts at the right level: in second normal form (2NF), every non-key attribute must depend on the whole key. section 4.1.3

Watch for two common dependency mistakes:

• Partial dependency: in a relation with a composite key, an attribute depends on only part of that key. Product Type Description depends on Product Type Name, not on the complete Product key. section 4.1.3
• Transitive dependency: an attribute depends on a nonprime attribute rather than directly on the candidate key. This violates third normal form (3NF). section 4.1.3

Separate the fact that determines the description

Move Product Type Name and Product Type Description into their own relation, make Product Type Name the prime attribute, and retain Product Type Name in Product as a foreign key. This removes the partial dependency and brings the design to 2NF. section 4.1.3

Figure 4.5 ERD from normalized design

Inspect the normalized ERD to see dependencies represented as separate relations linked by keys. section 4.1.3

How 100 SQL Server Mistakes and How to Avoid Them covers it

How deep it goes

Explains partial and transitive dependencies through schema changes, without formal dependency notation or algorithms.

Examples

Uses Product Type Name and Product Type Description to expose a partial dependency.

What it assumes

Assumes relations already meet 1NF before evaluating functional dependency for 2NF.

What you can do after

Apply 2NF and 3NF checks by separating attributes determined by only part of a key or by nonprime attributes.

How it connects

Connects functional dependency to composite keys, prime attributes, foreign keys, 1NF, 2NF, and 3NF.

liveBookmanning.com

Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Also explained in

• 

  liveBookmanning.com
  Grokking Relational Database DesignA functional dependency is a constraint in which a value or set of values in one or more columns uniquely determines the value of another column.

Compare how 2 books explain functional dependency →

Related concepts

• Boyce-Codd normal formused by · Boyce-Codd normal form is a relational normalization condition requiring every nontrivial functional dependency to have a superkey as its determinant.
• primary keyused by · A primary key is a designated column or attribute, or combination of them, that uniquely identifies each row or item in a table or data store.
• dependencykind of · A software dependency is a component, resource, or prerequisite on which another software component or operation relies to function or proceed.

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.