Dependencies: Partial and Transitive
|Top Tutorials||New Tutorials||Submit||Login||Register|
Total Hits: 19696
Total Votes: 158 votes
Category: Basic SQL/Basic SQL Misc
Submitted on: 2012-05-11 20:32:04
Submitted By: David Cohen
Description: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...