[Year 12 IT Apps] Normalisation

[Mark]

Hi Michael. This is how I summarised it last year...

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

Cheers
Mark

On 13 February 2015 at 10:56, Poke, Michael C <
poke.michael.c at edumail.vic.gov.au> wrote:

>  Hi all,
> 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
> (and yes, I am a frequent visitor to vceit.com).
>
>  Cheers,
> Michael
>
>  PS Don’t forget to join and contribute to our growing little ITApps
> community over at Google+…
> https://plus.google.com/u/0/communities/108389555192787196436
>
>
>  *Important - *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.
>
> _______________________________________________
> 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
>



-- 

*"**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*

Mark Kelly
mark AT vceit DOT com
http://vceit.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.edulists.com.au/pipermail/itapps/attachments/20150213/67c80a22/attachment.html