[Year 12 IT Apps] Normal forms

[Litsa Tzelepis]

I found this site to help as it hs many examples:

http://www.teach-ict.com/as_as_computing/ocr/H447/F453/3_3_9/normalisation/miniweb/index.htm

Follow the links on the left frame.

However, as much as this confuses me, it looks like the more you break down a table, to eliminate as much duplication as possible, it goes up an NF level.

Good luck, Litsa

  ----- Original Message ----- 
  From: Joseph Papaleo 
  To: Year 12 IT Applications Teachers' Mailing List 
  Sent: Monday, October 17, 2011 1:35 PM
  Subject: Re: [Year 12 IT Apps] Normal forms


  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

    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





------------------------------------------------------------------------------


  _______________________________________________
  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/20111018/28e1c3b5/attachment.html