[prog] normalizing tables
Sue Stones
suzo at spin.net.au
Tue Oct 18 19:29:29 EST 2005
Wolf Rising wrote:
>Not a student, not homework :-)
>
>
I think its OK to help students understand things too.
>Here it is in un-normalized form:
>
>Donations(*donorNumber, donorName, donorPhone, classificationCode,
> classificationName, exemptStatus, (checkNumber,
> dateReceived, Amount, fundNumber, fundName, fundDirector))
>
>
>
In this example your primary key should not be the donorNumber. At
least unless a donor is prohibited from donating more than one check.
The checkNumber would have been a suitable primaryKey.
2NF requires that no attribute is dependent on part of the primary key.
So this only applies if you have a composite primary key eg a composite
key comprising of the donorNumber, checkNumber. You would then split
this into 2 tables, one with donorNumber as the key and one with
checkNumber as the key. Or you might consider that a donor may
contribute to more than one fund in which case the composite key in 1NF
would include fundNumber. And in 2NF you would have 3 tables.
3NF requires 2NF AND the only determinates must be candidate keys.
Basically you have applied this rule to get 3NF directly. We didn't
have any reason to make classification a separate table because it was
just dependent upon donorNumber. But notice that classificationName is
dependent on classificationNumber, but classificationNumber is not a
candidate key for the donor table, so this now needs to go into a
separate table.
The difference between 2NF and 3NF is something that doesn't cause much
problem in reality. Its actually easier to understand and remember 3NF
than 2NF.
I hope that its clear.
sue
More information about the Programming
mailing list