<div dir="ltr">It's not often that I'd consider normalisation as a source of interesting reading, but this is rather good. Well done Mark - hopefully this will help others, or at least provide a direct example they can use. <div><br></div><div>Is it OK for it to be re-used elsewhere with acknowledgement?<div><br><div>Ken Price</div></div></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Nov 5, 2014 at 10:20 AM, Mark <span dir="ltr"><<a href="mailto:mark@vceit.com" target="_blank">mark@vceit.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Hello workers.<div><br></div><div>In case anyone was waiting for the other shoe to drop, let's cover 3NF.<div><br></div><div>To achieve 3NF:</div><div>- You must have already achieved 1NF and 2NF.</div><div>- No non-key field may be dependent on another non-key field.</div><div><br></div><div>Another way of saying it is that every non-key field in a table must give some information about the primary key rather than any other key in the table. Any field that does not contribute to the description of the primary key must be removed from the table.</div><div><br></div><div>For example... take a table. StudentID+SubjectID together are the primary key. (BTW - this use of multi-field keys is always to be avoided in the real world, but never mind for now.)</div><div><br></div><div>Honours is a Boolean field that is True if Mark is A or above, and False otherwise.</div><div><br></div><div><font face="courier new, monospace">StudentID SubjectID Mark Honours</font></div><div><font face="courier new, monospace">ABC0001 S01 A True </font></div><div><font face="courier new, monospace">ABC0001 S02 A+ True</font></div><div><font face="courier new, monospace">DEF0002 S01 B False</font></div><div><font face="courier new, monospace"><br></font></div><div><font face="arial, helvetica, sans-serif">The Honours field is dependent on the Mark field (i.e. to find the meaning of the Honours field, you need to refer to the Mark field) - but the Mark field is not the table's primary key. i.e. The Honours field describes the mark, not the student+subject.</font></div><div><font face="arial, helvetica, sans-serif"><br></font></div><div><font face="arial, helvetica, sans-serif">So, a non-key field (Honours) is dependent on another non-key field (Mark). So it fails 3NF.</font></div><div><font face="arial, helvetica, sans-serif">To fix it, do the same as we did before to achieve 2NF... break the offending field away into its own table with its own primary key (Mark) and non-key field (Honours) and relate the new table to the existing one using Mark as the link field.</font></div><div><font face="arial, helvetica, sans-serif"><br></font></div><div><font face="arial, helvetica, sans-serif">This 3NF scenario looks VERY much like the 2NF before, doesn't it? </font></div><div><font face="arial, helvetica, sans-serif">The only difference is that 2NF needed a non-key field to relate to the entire set of fields acting as the primary key.</font></div><div><font face="arial, helvetica, sans-serif">In 3NF, it's actually simpler - a non-key field must not be dependent on another non-key field. In both cases, the fix is the same: table splitting. </font>In my opinion, 3NF should be called 1.5NF because it seems to be more basic that 2NF, but we must all revere the word of Codd.</div><div><br></div><div><font face="arial, helvetica, sans-serif">Always remember the normalisation oath:</font></div><div><span style="font-family:arial,helvetica,sans-serif">The key (1NF), the whole key (2NF) and nothing but the key (3NF), so help me </span><a href="http://en.wikipedia.org/wiki/Edgar_F._Codd" style="font-family:arial,helvetica,sans-serif" target="_blank">Codd</a><span style="font-family:arial,helvetica,sans-serif">.</span><br></div><div><div><br></div><div>(It's taken me 4 years, but I think I'm <i>finally</i> getting the hang of normalisation).</div><span class="HOEnZb"><font color="#888888"><div><br></div>-- <br><div><div dir="ltr"><div><br></div><div>Mark Kelly</div><div>mark AT vceit DOT com</div><div><a href="http://vceit.com" target="_blank">http://vceit.com</a></div><div><br></div><div><i>I love the sound of people's voices after they stop talking.</i></div><div><br></div><div><br></div></div></div>
</font></span></div></div></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 <br><br>
<a href="http://www.vitta.org.au" target="_blank">http://www.vitta.org.au</a> - VITTA Victorian Information Technology Teachers Association Inc <br><br>
<a href="http://www.swinburne.edu.au/ict/schools" target="_blank">http://www.swinburne.edu.au/ict/schools</a> - Swinburne University<br></blockquote></div><br><br clear="all"><div><br></div>-- <br><div class="gmail_signature">-- <br>Dr Ken Price MACS CP ACCE Professional Associate.<br>President, TASITE <a href="http://www.tasite.tas.edu.au" target="_blank">http://www.tasite.tas.edu.au</a></div>
</div>