Re: Sixth normal form

From: Jan Hidders <>
Date: Tue, 31 Jul 2007 08:41:21 -0000
Message-ID: <>

On 31 jul, 06:33, Sameeksha <> wrote:
> On Jul 30, 9:30 pm, Jan Hidders <> wrote:
> > 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.
> > -- Jan Hidders
> Thanks for replying. Some explanations regarding your questions :-
> 1. Just to explain my concept of join dependency - consider this
> example - a table contains TeacherId, SkillId and CourseId as fields.
> These are related by the rule that teacher with certain skills can
> teach certain courses, however a teacher may possess skills required
> for a course, but he may not be teaching that course. Here there are 3
> join dependencies - (TeacherId, SkillId), (SkillId, CourseId) and
> (TeacherId, CourseId) which should be separate tables as per the 5th
> normal form. Please verify whether this concept is nearly correct.

Your terminology is not entirely correct, although your intuition is in the right direction. In the case you describe there is one join dependency namely {{TeacherId, SkillId}, {SkillId, CourseId}, {TeacherId, CoursId}}. Note that a join dependency is described by a set of set of attrbutes, {S1, .., Sn} and holds for a relation R if R always equal R[S1] JOIN ... JOIN R[Sn] where JOIN is the natural join nd R[Si] denotes the projection of R on Si.

> 2. My concept of Trivial dependency is this - in case in a table we
> have id and name and both are unique the dependency of name on id is
> trivial.

Hmm, not really right. A JD (join dependency) {S1, ..., Sn} is trivial if one of the Si contains all the attributes of R.

> Now another question after getting more idea about the sixth normal
> form - If we consider the above example of a table teacherid, skillid
> and courseid, how will we split it to fit in sixth normal form?

It already is in 6NF since the only JDs left are all trivial.

> If
> this is not a suitable example for applying the sixth normal form,
> please give another example which will make the concept clearer.

Actually you need a simpeler example. Because I lack time I'l make it abstract. Asume that the following JD holds: {{a,b}, {b,c}} and that {b} is a candidate key. In that case the JD follows from the CK, so you are in 5NF, but this is a nontrivial JD, so according to 6NF you should still split.

  • Jan Hidders
Received on Tue Jul 31 2007 - 10:41:21 CEST

Original text of this message