[Year 12 IT Apps] Normal forms

[Joseph Papaleo]

Mark,
In a nutshell, I've taught it as :

1NF - tidy the table(s) up horizontally
ie Fields contain only a single value eg Name: Mr Barney Rubble changes to
Title: Mr
Fname: Barney
Sname: Rubble

Remove repeating Groups (as per Mark's comments)


2NF - Tidy the table(s) up Vertically
Eliminate duplication in records down the table by using a Primary Key that
each record refers to.
Continue to do this as often as required until duplication is removed from
all tables- students should see the need for a new table if there is
duplication with their own unique Primary Key.


3NF - Make all fields in a table Mutually Exclusive
ie, no field can rely on another.  eg table may contain "Quantity" and
"Price", but it cannot contain "Total Cost" as Total cost = Quantity x
Price.

I'm concerned that my students understand this, but they found it difficult
to do. Also concerned that I may have used an old or inferior resource.




Joseph Papaleo
Ivanhoe Grammar School,
Plenty Campus




On Mon, Oct 17, 2011 at 1:04 PM, Mark KELLY <kel at mckinnonsc.vic.edu.au>wrote:

> Groan.  I just spent another 30 minutes trying to explain the differences
> between 2NF and 3NF to my dears.
> As usual, by the time I finished, I think they knew less than when I
> started.
>
> 1NF is pretty clear cut...
>   - no duplicate rows (i.e. identical records)
>   - no multiple columns that contain the same *type* of data (e.g. child1,
> child2, child3)
>   - only one piece of data per field (e.g. don't store 2 phone numbers for
> a person in the 'phone' field; put street address/suburb/postcode in
> separate fields; in Filemaker, don't use repeating fields)
>
> But 2NF is really only a problem if you use *multiple* fields as a key
> (e.g. using firstname + lastname + phone as a unique key) instead of using a
> unique and arbitrary key field, like an ID number.
> I realise now that my kids have trouble absorbing 2NF because at no time in
> their database education have I ever *mentioned* the possibility of using
> multiple fields as the key. So explaining 2NF to them was a bit like warning
> them not to walk using their ears - the concept had never even dawned on
> them before. To them, having a non-key field that did not give information
> about *all* of the key fields was a strange and scary possibility.
>
> 3NF is, I think, relatively straight forward. Each non-key field must give
> information about the key and not to another non-key field.  Violation of
> 3NF is usually pretty obvious because it looks "strange".
>
> After 2 slideshows, I'm still trying to work out a way to make it sound
> really clear...  next time, maybe. If anyone has hit on a sure-fire summary
> of differentiating normal forms, I'd love to hear of it.
>
> Regards
>
> --
> Mark Kelly
> Manager of ICT, Reporting, IT Learning Area
> McKinnon Secondary College
> McKinnon Rd McKinnon 3204, Victoria, Australia
> Direct line / Voicemail: +613 8520 9085, Fax +613 9578 9253
> kel at mckinnonsc.vic.edu.au
> VCE IT Lecture Notes: http://vceit.com
> Moderator: IT Applications Edulist <http://www.edulists.com.au/>
>
> Want a good time? Call 0112358. Ask for Mr Fibonacci.
>
> _______________________________________________
> http://www.edulists.com.au - FAQ, resources, subscribe, unsubscribe
> IT Applications Mailing List kindly supported by
> http://www.vcaa.vic.edu.au/vce/studies/infotech/itapplications3-4.html -
> Victorian Curriculum and Assessment Authority and
> http://www.vitta.org.au  - VITTA Victorian Information Technology Teachers
> Association Inc
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.edulists.com.au/pipermail/itapps/attachments/20111017/94be6de0/attachment.html