<div dir="ltr">Hi Michael. This is how I summarised it last year...<div><br></div><div><div style="font-size:12.8000001907349px">1NF is easy:</div><div style="font-size:12.8000001907349px"> - there must be no repeated columns (fields) e.g. Contact person 1, Contact person 2, Contact person 3.</div><div style="font-size:12.8000001907349px">- there must be no repeated (duplicate) records.</div><div style="font-size:12.8000001907349px"> - 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 style="font-size:12.8000001907349px"><br></div><div style="font-size:12.8000001907349px">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 style="font-size:12.8000001907349px">2NF problems never even arise if each table has its own dedicated key field (e.g. ID, account number). </div><div style="font-size:12.8000001907349px"><br></div><div style="font-size:12.8000001907349px">But anyway, kids are expected to know 2NF.</div><div style="font-size:12.8000001907349px"><br></div><div style="font-size:12.8000001907349px">2NF requires:</div><div style="font-size:12.8000001907349px">- 1NF has already been achieved.</div><div style="font-size:12.8000001907349px">- Any non-key field in a table is dependent on ALL of the fields used as the primary key.</div><div style="font-size:12.8000001907349px"><br></div><div style="font-size:12.8000001907349px">As usual, this means as little as American football, so an example is needed.</div><div style="font-size:12.8000001907349px"><br></div><div style="font-size:12.8000001907349px">There's a table with the following fields. 1NF has already been achieved.</div><div style="font-size:12.8000001907349px"><br></div><div style="font-size:12.8000001907349px">StudentID</div><div style="font-size:12.8000001907349px">SubjectID</div><div style="font-size:12.8000001907349px">Mark</div><div style="font-size:12.8000001907349px">SubjectName</div><div style="font-size:12.8000001907349px"><br></div><div style="font-size:12.8000001907349px">e.g. (Hope the formatting is not mangled - wish I could insert a table)</div><div style="font-size:12.8000001907349px"><font face="courier new, monospace"><b>STUDENTID SUBJECTID Mark SubjectName</b></font></div><div style="font-size:12.8000001907349px"><font face="courier new, monospace">SMI0001 ENG A+ English</font></div><div style="font-size:12.8000001907349px"><font face="courier new, monospace">SMI0001 MA B Maths</font></div><div style="font-size:12.8000001907349px"><font face="courier new, monospace">FRE0002 ENG C English</font></div><div style="font-size:12.8000001907349px"><br></div><div style="font-size:12.8000001907349px">The table's key is STUDENTID and SUBJECTID (together) to uniquely identify each record in the table.</div><div style="font-size:12.8000001907349px"><br></div><div style="font-size:12.8000001907349px">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 style="font-size:12.8000001907349px"><br></div><div style="font-size:12.8000001907349px">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 style="font-size:12.8000001907349px"><br></div><div style="font-size:12.8000001907349px">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 style="font-size:12.8000001907349px"><br></div><div style="font-size:12.8000001907349px">So it fails 2NF.</div><div style="font-size:12.8000001907349px"><br></div><div style="font-size:12.8000001907349px">To fix the problem, the table must be broken into two - </div><div style="font-size:12.8000001907349px">- MARKS_TABLE with STUDENTID+SUBJECTID as its key. It also contains the MARK non-key field.</div><div style="font-size:12.8000001907349px">- SUBJECTS_TABLE with SUBJECTID (primary key) and non-key SUBJECTNAME.</div><div style="font-size:12.8000001907349px"><br></div><div style="font-size:12.8000001907349px">You then create a relationship between the MARKS table and the SUBJECTS table using their primary keys as the related fields.</div><div style="font-size:12.8000001907349px">Now, in the MARKS table, a Mark is dependent upon the entire key in its table (STUDENTID+SUBJECTID).</div><div style="font-size:12.8000001907349px">In the SUBJECTS table, a subject name is dependent on the entire key in its table (SUBJECTID).</div><div style="font-size:12.8000001907349px"><br></div><div style="font-size:12.8000001907349px">Bingo. 2NF.</div><div style="font-size:12.8000001907349px"><br></div><div style="font-size:12.8000001907349px">Hope that helps.</div><div style="font-size:12.8000001907349px"><br></div></div><div class="gmail_extra">Cheers</div><div class="gmail_extra">Mark</div><div class="gmail_extra"><br><div class="gmail_quote">On 13 February 2015 at 10:56, Poke, Michael C <span dir="ltr"><<a href="mailto:poke.michael.c@edumail.vic.gov.au" target="_blank">poke.michael.c@edumail.vic.gov.au</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div style="word-wrap:break-word">
Hi all,
<div><span style="color:rgb(64,64,64);font-family:Roboto,arial,sans-serif;font-size:13px;line-height:18.2000007629395px;background-color:rgb(255,255,255)">Normalisation...... It's a swear word to me..... Is anyone willing to
share their best resources to make it really simple for my students (and for me!) - I'm doing U3O2 first</span></div>
<div><span style="color:rgb(64,64,64);font-family:Roboto,arial,sans-serif;font-size:13px;line-height:18.2000007629395px;background-color:rgb(255,255,255)">(and yes, I am a frequent visitor to
<a href="http://vceit.com" target="_blank">vceit.com</a>).</span></div>
<div><span style="color:rgb(64,64,64);font-family:Roboto,arial,sans-serif;font-size:13px;line-height:18.2000007629395px;background-color:rgb(255,255,255)"><br>
</span></div>
<div><span style="color:rgb(64,64,64);font-family:Roboto,arial,sans-serif;font-size:13px;line-height:18.2000007629395px;background-color:rgb(255,255,255)">Cheers,</span></div>
<div><span style="color:rgb(64,64,64);font-family:Roboto,arial,sans-serif;font-size:13px;line-height:18.2000007629395px;background-color:rgb(255,255,255)">Michael</span></div>
<div><span style="color:rgb(64,64,64);font-family:Roboto,arial,sans-serif;font-size:13px;line-height:18.2000007629395px;background-color:rgb(255,255,255)"><br>
</span></div>
<div><span style="background-color:rgb(255,255,255)"><font color="#404040" face="Roboto, arial, sans-serif"><span style="line-height:18.2000007629395px">PS Don</span><span style="line-height:18.200000762939453px">’</span><span style="line-height:18.2000007629395px">t
forget to join and contribute to our growing little ITApps community over at Google+</span><span style="line-height:18.200000762939453px">…</span><span style="line-height:18.2000007629395px"> </span></font></span><a href="https://plus.google.com/u/0/communities/108389555192787196436" target="_blank">https://plus.google.com/u/0/communities/108389555192787196436</a></div>
<div><br>
</div>
<div><br>
</div>
<p></p><p><b>Important - </b>This email and any attachments may be confidential. If received in error, please contact us and delete all copies. Before opening or using attachments check them for viruses and defects. Regardless of any loss, damage or consequence, whether caused by the negligence of the sender or not, resulting directly or indirectly from the use of any attached files our liability is limited to resupplying any affected attachments. Any representations or opinions expressed are those of the individual sender, and not necessarily those of the Department of Education and Training.</p>
</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><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div><i><br></i></div><div><i>"</i><i style="font-size:12.7272720336914px">Love is a snowmobile racing across the tundra and then suddenly, it flips over, pinning you underneath. At night, the ice weasels come." Matt Groening</i></div><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><div dir="ltr"><br></div></div></div></div></div></div></div></div></div></div></div>
</div></div>