Re: Sixth normal form

From: Brian Selzer <>
Date: Wed, 01 Aug 2007 13:51:16 GMT
Message-ID: <od0si.54391$>

"Jan Hidders" <> wrote in message
> On 30 jul, 12:45, Sameeksha <> wrote:
>> Googling out for definition and explanation for sixth normal form only
>> resulted in the following information - "6th normal form states that a
>> relation R should not contain any non-trivial join dependencies". Also
>> everywhere it is stated that this normal form takes into account the
>> temporal (time) dimension to the relational model, and that current
>> implementations like SQL server 2005 do not implement this normal
>> form.
> It would help if you first explained what you already know, so we
> don't spend time on explaining what you already know. Do you know what
> at join dependency is? Do you know when it is trivial?
> Btw. where and in what context did you read that SQL server did not
> support this normal form? That is a rather odd statement since the
> normal form is just about how much to split your relations into
> projections, so strictly speaking it needs no support at all form the
> DBMS. But perhaps support for temporal features was meant?
>> Any more explanation and preferably an example would help in
>> understanding the concept behind this normal form.
> Informally put it says that every distinct fact gets its own relation
> or "if you can split, then you should". So if you have a relation
> Student(student_id, name, address) then the fact that the student with
> a certain id has a certain name is split form the fact the this
> student lives at a certain address. This is different from 5NF since
> there you only split when there is a risk of redundancy or update
> anomalies.

I think it is important to emphasize the fact that vertically splitting a 5NF relation into a set of 6NF relations has consequences, specifically the need to enforce mutual foreign keys or a circular inclusion dependency, depending upon the number of resulting 6NF relations. This is important because support in commercial RDBMSs for enforcing such constraints is severely limited, if not nonexistent, since it requires an implementation of multiple assignment. Therefore splitting should only be done when absolutely necessary, for example, to support a temporal dimension.

It should be easy to see that unless the referential constraints are present, the 6NF relations are not equivalent to a 5NF relation. It would be possible for a student to have an address without a name, for example, something that is not possible in the 5NF relation.

It's somewhat unrelated, but important, nonetheless, to point out that if a relation has a temporal dimension, then either every key that it references in a foreign key constraint must be immune to updates, or the referenced relation must also have the same temporal dimension. Otherwise, history would have to be rewritten every time an update targets that referenced key.

> -- Jan Hidders
Received on Wed Aug 01 2007 - 15:51:16 CEST

Original text of this message