We're still not perfect. There is still room for anomalies in the
data. Look carefully at the data below.
Employee table
Employee number
Employee name
Rate category
Hourly rate
11
Vincent Radebe
A
$60
12
Pauline James
B
$50
16
Charles Ramoraz
C
$40
17
Monique Williams
B
$40
The problem above is that Monique Williams has been awarded an
hourly rate of $40, when she is actually category B, and should
be earning $50 (In the case of this company, the rate category -
hourly rate relationship is fixed. This may not always be the
case). Once again we are storing data redundantly: the hourly
rate - rate category relationship is being stored in its entirety
for each employee. The solution, as before, is to remove this
excess data into its own table. Formally, what we are doing is
looking for transitive relationships, or relationships where a
non-key attribute is dependent on another non-key relationship.
Hourly rate, while being in one sense dependent on Employee number
(we probably identified this dependency earlier, when looking for
partial dependencies) is actually dependent on Rate category.
So, we remove it, and place it in a new table, with its actual key,
as follows.
Employee table
Employee number
Employee name
Rate category
11
Vincent Radebe
A
12
Pauline James
B
16
Charles Ramoraz
C
17
Monique Williams
B
Rate table
Rate category
Hourly rate
A
$60
B
$50
C
$40
We've cut down once again. It is now impossible to mistakenly
assume rate category "B" is associated with an hourly rate of
anything but $50.
These relationships are only stored in once place - our new table,
where it can be ensured they are accurate.
Add databasejournal.com to your favorites Add databasejournal.com to your browser search box IE 7 | Firefox 2.0 | Firefox 1.5.xReceive news via our XML/RSS feed
IE 7
Firefox 2.0