[Year 12 IT Apps] Normal forms

Tue Oct 18 16:45:22 EST 2011

No problems Mark - I like the show, although Marty Feldman's appearance worries me a little - are you suggesting that all this RDBMS stuff is a monster comedy or a perhaps a comedy of monsters!?

Regards
Robert T-A
  ----- Original Message ----- 
  From: Mark KELLY 
  To: Year 12 IT Applications Teachers' Mailing List 
  Sent: Tuesday, October 18, 2011 10:48 AM
  Subject: Re: [Year 12 IT Apps] Normal forms

  Many thanks for the document, Robert. I've been looking for a while to find a neat example of 1NF to 3NF progress.

  I've taken the liberty of adapting it into a slideshow...

  http://www.vceit.com/slideshows/database-normalisation-example.ppt

  Cheers
  Mark

  On 17 October 2011 17:13, Timmer-Arends <timmer at melbpc.org.au> wrote:

    Hello all
    just to chip in my 2-cents worth, yes, there might be different processes that lead to a normalised db, but the definitions of 1NF, 2NF, and 3NF are pretty solid and have been for at least 30 years.

    attached is my version of the story

    Regards
    Robert T-A
      ----- Original Message ----- 
      From: Mark Scott 
      To: Year 12 IT Applications Teachers' Mailing List 
      Sent: Monday, October 17, 2011 1:54 PM
      Subject: Re: [Year 12 IT Apps] Normal forms

      Joseph

      Your explanation looks and sounds simple enough but...

      There are at least half a dozen different definitions of this process. (although the 1NF explanation is fairly consistent) 

      And, most importantly, we still do not know how the VCAA (Hi Paula) defines the process of Normalisation and in particular the first three forms.

      I’m expecting a very simple question on this on the exam (hopefully just a 1NF question).

      Anything too complicated and this mailing list will be on fire after the exam.

      thanks

      Mark Scott

      Luther College

      “Don’t water the rocks” Gerry Smith, River Oaks Public School

      From: itapps-bounces at edulists.com.au [mailto:itapps-bounces at edulists.com.au] On Behalf Of Joseph Papaleo
      Sent: Monday, 17 October 2011 1:36 PM
      To: Year 12 IT Applications Teachers' Mailing List
      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

    _______________________________________________
    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

  -- 
  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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.edulists.com.au/pipermail/itapps/attachments/20111018/30779650/attachment.html 