So, now our data can go in table format, but there are still
some problems with it. We store the information that code 1023
refers to the Madagascar travel site 3 times! Besides the waste
of space, there is another serious problem. Look carefully at
the data below.
Click here for Table 3.
Did you notice anything strange in the data above? Congratulations
if you did! Madagascar is misspelt in the 3rd record. Now imagine
trying to spot this error in a table with thousands of records!
By using the structure above, the chances of the data being
corrupted increases drastically.
The solution is simply to take out the duplication. What we are
doing formally is looking for partial dependencies, ie fields that
are dependent on a part of a key, and not the entire key. Since
both project number and employee number make up the key, we look
for fields that are dependent only on project number, or on
We identify two fields. Project name is dependent on project
number only (employee_number is irrelevant in determining project
name), and the same applies to employee name, hourly rate and
rate category, which are dependent on employee number. So, we
take out these fields, as follows:
Clearly we can't simply take out the data and leave it out of
our database. We take it out, and put it into a new table,
consisting of the field that has the partial dependency, and
the field it is dependent on. So, we identified employee name,
hourly rate and rate category as being dependent on employee
number. The new table will consist of employee number as a key,
and employee name, rate category and hourly rate, as follows:
And the same for the project data.
||Madagascar travel site|
||Online estate agency
Note the reduction of duplication. The text "Madagascar travel
site" is stored once only, not for each occurrence of an employee
working on that project. The link is made through the key, the
project number. Obviously there is no way to remove the
duplication of this number without losing the relation altogether,
but it is far more efficient storing a short number repeatedly,
than a large piece of text.