[prog] normalization and databases

[Wolf Rising]

Thank you as always for taking the time to answer :-) I've been
looking over this for a week and reading
some  discussion forums and web stuff, if I'm starting to understand 1
normal form is basically break
everything down into as small of bits as possible. Third normal is
where redundancy has been removed
as much as possible. I'm stuck on what exactly is second normal form.

Again, thanks to everyone who either responded on the list or emailed,
I really do appreciate it :-)


On Mon, 18 Oct 2004 12:00:12 +1000, Jacinta Richardson
<jarich at perltraining.com.au> wrote:
> 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   |
> 
> _______________________________________________
> Programming mailing list
> Programming at linuxchix.org
> http://mailman.linuxchix.org/mailman/listinfo/programming
>