[Year 12 IT Apps] Third Normal Form

[Mark]

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.*
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.edulists.com.au/pipermail/itapps/attachments/20141105/2fba5c9f/attachment-0001.html