[prog] Re: Programming Digest, Vol 20, Issue 7

[Gareth Anderson]

Hi,

> ---------- Forwarded message ----------
> From: Wolf Rising <wolfrising at gmail.com>
> To: Jacinta Richardson <jarich at perltraining.com.au>
> Date: Sat, 23 Oct 2004 23:44:35 -0400
> Subject: Re: [prog] normalization and databases
> 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.

1st normal form that requires that a column of any tuple (or attribute
of any tuple) can only be a single value.
By definition all relations (tables) are in 1st normal form.

So in other words you cannot do:

StudentID | Results
33333       | 3,4,5,6

You can do:
StudentID | Results
33333       | 3
33333       | 4
33333       | 5
33333       | 6

Ok? Quite simple really.

2nd normal form requires that everything is funtionally dependent on
the primary key. You can probably find a good definition of that
somewhere...
Anyway, it means that say you have:
A table like:
StaffID,  FirstName, LastName, PhoneNumber, OfficeNumber

Where StaffId is the primary key.

Since, StaffID determines FirstName, StaffID determines LastName, then
these 2 are functionally depdent on StaffID.

However, if we have the fact that, OfficeNumber determines Phone
number and StaffID determines OfficeNumber, then this is called a
transitive dependency.

The above table is ok in 2nd normal form, but not 3rd normal form
because in 3rd normal form you cannot have transistive dependency.

Theres also, 4th and 5th normal form...

However here's an article which explains the first 3 quite well, note
that the idea is to reduce redundancy so the solution is nearly always
to break thing into more tables.

http://www.microsoft-accesssolutions.co.uk/1norm_form.htm

Heres some other recommended ones:
1NF
http://databases.about.com/library/weekly/aa081901a.htm

2NF
http://databases.about.com/library/weekly/aa090201a.htm

3NF
http://databases.about.com/library/glossary/bldef-3nf.htm