[Year 12 IT Apps] Normal forms

Tue Oct 18 10:48:28 EST 2011

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 <msc at luther.vic.edu.au>
> *To:* Year 12 IT Applications Teachers' Mailing List<itapps at edulists.com.au>
> *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 <%2B613%208520%209085>, Fax +613
> 9578 9253 <%2B613%209578%209253>
> 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****
>
> ** **
>
> ------------------------------
>
> _______________________________________________
> 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 <http://www.edulists.com.au/>

Want a good time? Call 0112358. Ask for Mr Fibonacci.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.edulists.com.au/pipermail/itapps/attachments/20111018/c5a1b56a/attachment-0002.html 