[prog] normalization and databases

[Jacinta Richardson]

G'day Wolf,

I don't remember my normal forms, but the principals I use are all based 
on the same aim - to avoid redundancy.  So I'll offer a solution with 
this in mind.  Do note that this might ruin the execise for you as I do 
offer a full solution.  :)


> Donations(Donor_Number, Donor_Name,Donor_Phone, Classification_Code,
>           Classification_Name, Exempt_Status,(Check_Number, Date_Received,
>           Amount, Fund_Number, Fund_Name, Fund_Directory)
>           
>           
> This is a table concerning information about donors and their
> contributions to a non-profit
> organization. Each donation is made to a particular fund (Food Bank,
> Medical Care, etc_).
> In addition, each donor is assigned to a particular class of donors (
> personal, corporate, etc).
> All donations are made by check. The following dependencies exist in Donations..
> 
> Donor_Number -> Donor_Name, Donor_Phone, CLassification_Code,
> Classification_Name, Exempt_Statis
> 
> 
> Classification_Code -> Classification_Name
> 
> Donor_Number, Check_Number -> Date_Received, Amount, Fund_Number,
> Fund_Name, Fund_Director
> 
> Fund_Number -> Fund_Name, Fund_Director

First of all.  It seems logical that a donor could make more than one 
donation in the course of their lives.  Thus, Donor information should 
be in a table of its own:

Donors ( Donor_Number, Donor_Name, Donor_Phone  ... )

Classification seems to be tied in with donors and probably exempt 
status too, giving us:

*      Donors ( Donor_Number, Donor_Name, Donor_Phone,
*               Classification_Code, Exempt_Status )
*          Primary key: Donor_Number
*          Foreign key: Classification_Code references Classifications

The primary key uniquely identifies the donor entry.  This means that 
there could be two Joan Smiths, but we'd know they were different 
because of the Donor_Number.  If we want to allow donors to have 
multiple phone numbers at some point, then we'd need to pull the 
Donor_Phone bit out and have a table which associated the phone number 
with the Donor_Number.

I've left off including Classification_Name in the table, because we're 
now going to create a table to map Classification names and numbers 
together.:

*     Classifications( Classificiation_Code, Classification_Name )
*           Primary Key: Classification_Code.

If we were to include both classification code and classification name 
in the donor entry then we run the risk that one donor entry could have 
classification code 8 and classification name "personal" whereas a 
different entry could have classification code 8 and classification name 
"corporate".  Ideally the code should uniquely identify the name.

To look at the actual donations we need a donations table which has 
information about the cheques in it.

*      Donations( Donor_Number,    # who the donation is from
*                 Cheque_Number,   # which donation from this donor
*                 Date_Received,   # multiple donations in one day are
*                                  # allowed
*                 Amount,
*                 Fund_Number
*       );
*           Primary Key: Donor_Number, Cheque_Number
*           Foreign Key: Donor_Number references Donors
*           Foreign Key: Fund_Number references Funds

Once again, since the Fund_Number should be able to tell us information 
about the Fund_Name and Director, we can leave that out of this table. 
I've added some comments in so as to explain the primary key.  Assuming 
people stay with one bank, then their cheque numbers are always unique 
(and incrementing).  Thus if I give you cheque number 7 then I'll not be 
giving either you or someone else cheque number 7 in the future. 
However, you may have a cheque number 7 all of your very own.  So in 
order to distinguish my cheque 7 from your cheque 7, and my cheque 7 
from my cheque 8, we need a dual primary key.  My Donor_Number AND my 
Cheque_Number uniquely distinguish this record.

We omit the Date_Received from the primary key, as the cheque_number and 
donor_number are sufficient.

So funds...

*       Funds ( Fund_Number,
*               Fund_Name,
*               Fund_Director )
*          Primary Key: Fund_Number

This should be pretty straight forward.  If the director ever changes 
then it only gets updated in this table.  Easy stuff.  Note however that 
we're not allowing funds to have multiple directors.  IF that is 
required then we'll need to take the director out of this table and 
create a table that links fund directors and funds.

You'll notice that most of my tables specified above are based almost 
wholly on your restrictions.  This is normal.  I've also mentioned some 
points where there might in the future be further restrictions, such as 
multiple phone numbers for donors, or multiple directors for funds.

I've found a great discussion on normal forms over here:
    http://www.microsoft-accesssolutions.co.uk/1norm_form.htm

And it would appear that I've sorted these into 3rd Normal form 
(hooray!).  3rd Normal form is what most people use, so its pretty easy 
to get used to.

If you have any further questions please ask.

All the best,

     Jacinta

-- 
    ("`-''-/").___..--''"`-._          |  Jacinta Richardson         |
     `6_ 6  )   `-.  (     ).`-.__.`)  |  Perl Training Australia    |
     (_Y_.)'  ._   )  `._ `. ``-..-'   |      +61 3 9354 6001        |
   _..`--'_..-_/  /--'_.' ,'           | contact at perltraining.com.au |
  (il),-''  (li),'  ((!.-'             |   www.perltraining.com.au   |