[Year 12 IT Apps] Third Normal Form

[ken price]

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.

Is it OK for it to be re-used elsewhere with acknowledgement?

Ken Price

On Wed, Nov 5, 2014 at 10:20 AM, Mark <mark at vceit.com> wrote:

> Hello workers.
>
> In case anyone was waiting for the other shoe to drop, let's cover 3NF.
>
> To achieve 3NF:
> - You must have already achieved 1NF and 2NF.
> - No non-key field may be dependent on another non-key field.
>
> 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.
>
> 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.)
>
> Honours is a Boolean field that is True if Mark is A or above, and False
> otherwise.
>
> StudentID   SubjectID   Mark   Honours
> ABC0001     S01         A      True
> ABC0001     S02         A+     True
> DEF0002     S01         B      False
>
> 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.
>
> So, a non-key field (Honours) is dependent on another non-key field
> (Mark). So it fails 3NF.
> 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.
>
> This 3NF scenario looks VERY much like the 2NF before, doesn't it?
> The only difference is that 2NF needed a non-key field to relate to the
> entire set of fields acting as the primary key.
> 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. 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.
>
> Always remember the normalisation oath:
> The key (1NF), the whole key (2NF) and nothing but the key (3NF), so help
> me Codd <http://en.wikipedia.org/wiki/Edgar_F._Codd>.
>
> (It's taken me 4 years, but I think I'm *finally* getting the hang of
> normalisation).
>
> --
>
> Mark Kelly
> mark AT vceit DOT com
> http://vceit.com
>
> *I love the sound of people's voices after they stop talking.*
>
>
>
> _______________________________________________
> 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
>



-- 
-- 
Dr Ken Price MACS CP ACCE Professional Associate.
President, TASITE http://www.tasite.tas.edu.au
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.edulists.com.au/pipermail/itapps/attachments/20141105/05cf2f1b/attachment.html