Re: Sixth normal form

From: Bob Badour <>
Date: Tue, 31 Jul 2007 09:24:00 -0300
Message-ID: <46af29b0$0$4063$>

Jan Hidders wrote:
> 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
>>>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
>>>-- 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

> 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.
>>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.

To offer a simple illustration, suppose one of the relations above were: {TeacherID, TeacherName, CourseID} where the only candidate key in the relation is TeacherID.

It would be in 5th normal form but not 6th normal form. Received on Tue Jul 31 2007 - 14:24:00 CEST

Original text of this message