<div dir="ltr">Hi Stephanie. Now you know why I bailed out when I did :-)<div><br></div><div>1NF is easy:</div><div> - there must be no repeated columns (fields) e.g. Contact person 1, Contact person 2, Contact person 3.</div><div> - there may only be one datum in any field - i.e. no "16kg" "3 minutes 56 seconds", "Large Size $4, Small $2.50", "16 Fred St, Melbourne, 3000".</div><div><br></div><div>2NF problems ONLY arise if you're silly enough to use a multi-field key (e.g. using firstname & familyname to uniquely identify people in a table). </div><div>2NF problems never even arise if each table has its own dedicated key field (e.g. ID, account number). </div><div><br></div><div>But anyway, kids are expected to know 2NF.</div><div><br></div><div>2NF requires:</div><div>- 1NF has already been achieved.</div><div>- Any non-key field in a table is dependent on ALL of the fields used as the primary key.</div><div><br></div><div>As usual, this means as little as American football, so an example is needed.</div><div><br></div><div>There's a table with the following fields. 1NF has already been achieved.</div><div><br></div><div>StudentID</div><div>SubjectID</div><div>Mark</div><div>SubjectName</div><div><br></div><div>e.g. (Hope the formatting is not mangled - wish I could insert a table)</div><div><font face="courier new, monospace"><b>STUDENTID SUBJECTID Mark SubjectName</b></font></div><div><font face="courier new, monospace">SMI0001 ENG A+ English</font></div><div><font face="courier new, monospace">SMI0001 MA B Maths</font></div><div><font face="courier new, monospace">FRE0002 ENG C English</font></div><div><br></div><div>The table's key is STUDENTID and SUBJECTID (together) to uniquely identify each record in the table.</div><div><br></div><div>The (non-key) MARK field is dependent on both STUDENTID and SUBJECTID - i.e. to find out what a mark refers to, you need to know both the student and subject.</div><div><br></div><div>However, the (non-key) SUBJECTNAME field is dependent only the SUBJECTID - i.e. to find out what a subject name refers to, you only need the SUBJECTID. You don't need the STUDENTID.</div><div><br></div><div>So the (non-key) SUBJECTNAME field is dependent on <b>part of</b> the key (SUBJECTID) but not the <b>whole</b> key (STUDENTID+SUBJECTID).</div><div><br></div><div>So it fails 2NF.</div><div><br></div><div>To fix the problem, the table must be broken into two - </div><div>- MARKS_TABLE with STUDENTID+SUBJECTID as its key. It also contains the MARK non-key field.</div><div>- SUBJECTS_TABLE with SUBJECTID (primary key) and non-key SUBJECTNAME.</div><div><br></div><div>You then create a relationship between the MARKS table and the SUBJECTS table using their primary keys as the related fields.</div><div>Now, in the MARKS table, a Mark is dependent upon the entire key in its table (STUDENTID+SUBJECTID).</div><div>In the SUBJECTS table, a subject name is dependent on the entire key in its table (SUBJECTID).</div><div><br></div><div>Bingo. 2NF.</div><div><br></div><div>Hope that helps.</div><div><br></div><div><br></div><div><br></div><div><br></div><div><br></div></div><div class="gmail_extra"><br><div class="gmail_quote">On 5 November 2014 08:53, Stephanie Polan <span dir="ltr"><<a href="mailto:pos@mckinnonsc.vic.edu.au" target="_blank">pos@mckinnonsc.vic.edu.au</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">Good Morning everyone,<div><br></div><div>I know this is crazy... but my 12's are making me crazy, does anyone have any simple blunt help for Normal Form's ie 1NF 2NF. I've gone through my explanations and Mark Kelly's slide show and my patience and my students are still asking for clarification because of course they can learn and remember it for Friday!!! </div><div><br></div><div>Thanks in advance</div><span class="HOEnZb"><font color="#888888"><div>Stephanie Polan<br clear="all"><div><br></div>-- <br><div><div dir="ltr">Miss Polan<div>McKinnon SC</div></div></div>
</div></font></span></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"><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>
</div>