[Year 12 IT Apps] Third Normal Form

[Mark]

Use it with my blessing, Ken.
I'm glad it may be useful.

On 5 November 2014 10:40, ken price <kenjprice at gmail.com> wrote:

> 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.*
>>
>>
>> --
> Dr Ken Price MACS CP ACCE Professional Associate.
> President, TASITE http://www.tasite.tas.edu.au
>
>
-- 

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/d23c481a/attachment.html