[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