[Year 12 IT Apps] First Normal Form

[Mark]

Hi Stephanie. Now you know why I bailed out when I did  :-)

1NF is easy:
 - there must be no repeated columns (fields) e.g. Contact person 1,
Contact person 2, Contact person 3.
 - 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".

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).
2NF problems never even arise if each table has its own dedicated key field
(e.g. ID, account number).

But anyway, kids are expected to know 2NF.

2NF requires:
- 1NF has already been achieved.
- Any non-key field in a table is dependent on ALL of the fields used as
the primary key.

As usual, this means as little as American football, so an example is
needed.

There's a table with the following fields. 1NF has already been achieved.

StudentID
SubjectID
Mark
SubjectName

e.g. (Hope the formatting is not mangled - wish I could insert a table)
*STUDENTID     SUBJECTID    Mark    SubjectName*
SMI0001       ENG          A+      English
SMI0001       MA           B       Maths
FRE0002       ENG          C       English

The table's key is STUDENTID and SUBJECTID (together) to uniquely identify
each record in the table.

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.

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.

So the (non-key) SUBJECTNAME field is dependent on *part of* the key
(SUBJECTID) but not the *whole* key (STUDENTID+SUBJECTID).

So it fails 2NF.

To fix the problem, the table must be broken into two  -
- MARKS_TABLE with STUDENTID+SUBJECTID as its key. It also contains the
MARK non-key field.
- SUBJECTS_TABLE with SUBJECTID (primary key) and non-key SUBJECTNAME.

You then create a relationship between the MARKS table and the SUBJECTS
table using their primary keys as the related fields.
Now, in the MARKS table, a Mark is dependent upon the entire key in its
table (STUDENTID+SUBJECTID).
In the SUBJECTS table, a subject name is dependent on the entire key in its
table (SUBJECTID).

Bingo. 2NF.

Hope that helps.






On 5 November 2014 08:53, Stephanie Polan <pos at mckinnonsc.vic.edu.au> wrote:

> Good Morning everyone,
>
> 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!!!
>
> Thanks in advance
> Stephanie Polan
>
> --
> Miss Polan
> McKinnon SC
>
> _______________________________________________
> 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 <br>
> http://www.vitta.org.au  - VITTA Victorian Information Technology
> Teachers Association Inc <br>
> http://www.swinburne.edu.au/ict/schools - Swinburne University
>



-- 

Mark Kelly
mark AT vceit DOT com
http://vceit.com

*I love the sound of people's voices after they stop talking.*
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.edulists.com.au/pipermail/itapps/attachments/20141105/70bbe0e5/attachment.html