Many thanks for the document, Robert. I've been looking for a while to find a neat example of 1NF to 3NF progress.<br><br>I've taken the liberty of adapting it into a slideshow...<br><br><a href="http://www.vceit.com/slideshows/database-normalisation-example.ppt">http://www.vceit.com/slideshows/database-normalisation-example.ppt</a><br>
<br>Cheers<br>Mark<br><br><div class="gmail_quote">On 17 October 2011 17:13, Timmer-Arends <span dir="ltr"><<a href="mailto:timmer@melbpc.org.au">timmer@melbpc.org.au</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<u></u>
<div vlink="purple" link="blue" bgcolor="#ffffff" lang="EN-AU">
<div><font face="Arial" size="2">Hello all</font></div>
<div><font face="Arial" size="2">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.</font></div>
<div><font face="Arial" size="2"></font> </div>
<div><font face="Arial" size="2">attached is my version of the story</font></div>
<div><font face="Arial" size="2"></font> </div>
<div><font face="Arial" size="2">Regards</font></div>
<div><font face="Arial" size="2">Robert T-A</font></div>
<blockquote style="padding-right:0px;padding-left:5px;margin-left:5px;border-left:#000000 2px solid;margin-right:0px"><div class="im">
<div style="font:10pt arial">----- Original Message ----- </div>
<div style="background:#e4e4e4;font:10pt arial"><b>From:</b>
<a title="msc@luther.vic.edu.au" href="mailto:msc@luther.vic.edu.au" target="_blank">Mark
Scott</a> </div>
<div style="font:10pt arial"><b>To:</b> <a title="itapps@edulists.com.au" href="mailto:itapps@edulists.com.au" target="_blank">Year 12 IT Applications Teachers' Mailing
List</a> </div>
</div><div><div></div><div class="h5"><div style="font:10pt arial"><b>Sent:</b> Monday, October 17, 2011 1:54
PM</div>
<div style="font:10pt arial"><b>Subject:</b> Re: [Year 12 IT Apps] Normal
forms</div>
<div><br></div>
<div>
<p class="MsoNormal"><span style="font-size:11pt">Joseph<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt">Your explanation looks and
sounds simple enough but...<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt">There are at least half a
dozen different definitions of this process. (although the 1NF explanation is
fairly consistent) <u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt">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.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt">I’m expecting a very simple
question on this on the exam (hopefully just a 1NF
question).<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt">Anything too complicated and
this mailing list will be on fire after the exam.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt">thanks<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt">Mark
Scott<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt">Luther
College<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt">“Don’t water the rocks” Gerry
Smith, River Oaks Public School<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt"><u></u> <u></u></span></p>
<p class="MsoNormal" style="margin-left:36pt"><b><span style="font-size:10pt" lang="EN-US">From:</span></b><span style="font-size:10pt" lang="EN-US">
<a href="mailto:itapps-bounces@edulists.com.au" target="_blank">itapps-bounces@edulists.com.au</a> [mailto:<a href="mailto:itapps-bounces@edulists.com.au" target="_blank">itapps-bounces@edulists.com.au</a>] <b>On
Behalf Of </b>Joseph Papaleo<br><b>Sent:</b> Monday, 17 October 2011 1:36
PM<br><b>To:</b> Year 12 IT Applications Teachers' Mailing
List<br><b>Subject:</b> Re: [Year 12 IT Apps] Normal
forms<u></u><u></u></span></p>
<p class="MsoNormal" style="margin-left:36pt"><u></u> <u></u></p>
<p class="MsoNormal" style="margin-bottom:12pt;margin-left:36pt;margin-right:0cm">Mark,<br>In
a nutshell, I've taught it as :<br><br>1NF - tidy the table(s) up
horizontally<br>ie Fields contain only a single value eg Name: Mr Barney
Rubble changes to <br>Title: Mr<br>Fname: Barney <br>Sname: Rubble<br clear="all"><br>Remove repeating Groups (as per Mark's comments)<br><br><br>2NF
- Tidy the table(s) up Vertically<br>Eliminate duplication in records down the
table by using a Primary Key that each record refers to.<br>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.<br><br><br>3NF - Make all fields in a table
Mutually Exclusive<br>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.<br><br>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.<br><br><br><br><br>Joseph Papaleo<br>Ivanhoe
Grammar School,<br>Plenty Campus<br><br><br><br><u></u><u></u></p>
<div>
<p class="MsoNormal" style="margin-left:36pt">On Mon, Oct 17, 2011 at 1:04 PM,
Mark KELLY <<a href="mailto:kel@mckinnonsc.vic.edu.au" target="_blank">kel@mckinnonsc.vic.edu.au</a>>
wrote:<u></u><u></u></p>
<p class="MsoNormal" style="margin-left:36pt">Groan. I just spent another
30 minutes trying to explain the differences between 2NF and 3NF to my
dears. <br>As usual, by the time I finished, I think they knew less than
when I started.<br><br>1NF is pretty clear cut...<br> - no duplicate
rows (i.e. identical records)<br> - no multiple columns that contain the
same *type* of data (e.g. child1, child2, child3)<br> - 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) <br><br>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. <br>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.<br><br>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".<br><br>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.<br><br>Regards<br><span style="color:#888888"><br>-- <br>Mark
Kelly<br>Manager of ICT, Reporting, IT Learning Area<br>McKinnon Secondary
College<br>McKinnon Rd McKinnon 3204, Victoria, Australia<br>Direct line /
Voicemail: <a href="tel:%2B613%208520%209085" target="_blank">+613 8520
9085</a>, Fax <a href="tel:%2B613%209578%209253" target="_blank">+613 9578
9253</a><br><a href="mailto:kel@mckinnonsc.vic.edu.au" target="_blank">kel@mckinnonsc.vic.edu.au</a><br>VCE IT Lecture Notes: <a href="http://vceit.com" target="_blank">http://vceit.com</a><br>Moderator: <a href="http://www.edulists.com.au/" target="_blank">IT Applications
Edulist</a><br><br>Want a good time? Call 0112358. Ask for Mr
Fibonacci.<br></span><br>_______________________________________________<br><a href="http://www.edulists.com.au" target="_blank">http://www.edulists.com.au</a>
- FAQ, resources, subscribe, unsubscribe<br>IT Applications Mailing List
kindly supported by<br><a href="http://www.vcaa.vic.edu.au/vce/studies/infotech/itapplications3-4.html" target="_blank">http://www.vcaa.vic.edu.au/vce/studies/infotech/itapplications3-4.html</a>
- Victorian Curriculum and Assessment Authority and<br><a href="http://www.vitta.org.au" target="_blank">http://www.vitta.org.au</a>
- VITTA Victorian Information Technology Teachers Association
Inc<u></u><u></u></p></div>
<p class="MsoNormal" style="margin-left:36pt"><u></u> <u></u></p></div>
</div></div><p>
</p><hr><div class="im">
<p></p>_______________________________________________<br><a href="http://www.edulists.com.au" target="_blank">http://www.edulists.com.au</a>
- FAQ, resources, subscribe, unsubscribe<br>IT Applications Mailing List
kindly supported
by<br><a href="http://www.vcaa.vic.edu.au/vce/studies/infotech/itapplications3-4.html" target="_blank">http://www.vcaa.vic.edu.au/vce/studies/infotech/itapplications3-4.html</a> -
Victorian Curriculum and Assessment Authority
and<br><a href="http://www.vitta.org.au" target="_blank">http://www.vitta.org.au</a> - VITTA Victorian Information Technology
Teachers Association Inc</div><p></p></blockquote></div>
<br>_______________________________________________<br>
<a href="http://www.edulists.com.au" target="_blank">http://www.edulists.com.au</a> - FAQ, resources, subscribe, unsubscribe<br>
IT Applications Mailing List kindly supported by<br>
<a href="http://www.vcaa.vic.edu.au/vce/studies/infotech/itapplications3-4.html" target="_blank">http://www.vcaa.vic.edu.au/vce/studies/infotech/itapplications3-4.html</a> - Victorian Curriculum and Assessment Authority and<br>
<a href="http://www.vitta.org.au" target="_blank">http://www.vitta.org.au</a> - VITTA Victorian Information Technology Teachers Association Inc<br></blockquote></div><br><br clear="all"><br>-- <br>Mark Kelly<br>Manager of ICT, Reporting, IT Learning Area<br>
McKinnon Secondary College<br>McKinnon Rd McKinnon 3204, Victoria, Australia<br>Direct line / Voicemail: +613 8520 9085, Fax +613 9578 9253<br><a href="mailto:kel@mckinnonsc.vic.edu.au" target="_blank">kel@mckinnonsc.vic.edu.au</a><br>
VCE IT Lecture Notes: <a href="http://vceit.com" target="_blank">http://vceit.com</a><br>Moderator: <a href="http://www.edulists.com.au/" target="_blank">IT Applications Edulist</a><br><br><font>Want a good time? Call 0112358. Ask for Mr </font>Fibonacci.<br>