Dependencies: Partial and Transitive

Tutorials Shared by the Internet Community


  Top Tutorials     New Tutorials     Submit     Login     Register  

Dependencies: Partial and Transitive

Description of Dependencies which violate normalization rules.
  • Total Hits: 46079

  • Average Rating :
    URL RatingURL RatingURL RatingURL RatingURL Rating


  • Total Votes: 332 votes

  • Category: Basic SQL/Basic SQL Misc

  • Submitted on: 2012-05-11 20:32:04

  • Submitted By: David Cohen

  • Set as Favorite


Partial Key and Transitive Dependencies
by David Cohen

Let's start by nailing down the concept of dependency. We will construct a primitive database using pencil and paper. Our table will be called "Cars". It will have 3 fields: a key, called car-name; and fields called color and brand. Let's walk through the process of creating a record to represent Fred's car. This in fact will be the key: the car-name is "Fred's car". With pencil in hand I ask you what I should write for "color". "I don't know", you reply. "It depends on Fred's car. What color is it?". This "depends" is what we mean by dependency: the value of a column depends on which item in the real world is being described. What is it that identifies this item? The primary key ("Fred's car"). So we say that the column is dependent on the primary key.

Now suppose a table has a 2-segment-key, say a transaction table, with customer number and transaction number. If this table were to have a field called customer name, it would be dependent only on the customer number; that is, it would be dependent on part of the key. This is called a partial-key dependency.

Say that our table is for a grocery store; and every transaction is for one item. Suppose a given record had a item value of banana. Suppose another field were item-family, with a value of fruit. The item-family is dependent on item; item itself is dependent on the primary key; so the dependency makes a transit from key to field, and field to another field: a transitive dependency.

Both types of dependency are considerd violations of normalization rules. Both are resolved by breaking out the dependent value and storing it somewhere else, once only, so it doesn't have to be repeated on multiple rows. We record the customers name in the customer table, not the transaction table; and we record bananas' membership in the fruit family by creating one record in, say, a grocery-type table.    More detail...

Rating Detail

 Stars  Percentage  Total Vote
 One star  Vote LeftVote MiddleVote Right 38.55%  128
 Two starsTwo star  Vote LeftVote MiddleVote Right 14.16%  47
 Three starsThree starsThree stars  Vote LeftVote MiddleVote Right 11.14%  37
 Four starsFour starsFour starsFour stars  Vote LeftVote MiddleVote Right 10.84%  36
 Five starsFive starsFive starsFive starsFive stars  Vote LeftVote MiddleVote Right 25.3%  84

Caste Your Vote

 Rating  Selection  Guidelines
 Poor:     Very disappointing, useless.
 Fair:     Below average, disappointing but not useless.
 Good:     About average in its class.
 Very Good:     Above average for its category.
 Excellent:     One of the best Tutorials in its category.