Re: Sixth normal form

From: Sameeksha <>
Date: Wed, 01 Aug 2007 02:50:43 -0700
Message-ID: <>

On Jul 31, 5:24 pm, Bob Badour <> wrote:
> 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
> >>>>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.
> 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.- Hide quoted text -
> - Show quoted text -

Thanks for the explanations.
The concepts of sixth normal form (along with join dependency and trivial join dependency) are clearer now.

However, a next question (first of all sorry for taking up your time): Will a table {a,b,c} with join dependencies {(a,b), (b,c)} be in fourth / fifth normal form?

The fourth normal form states there should not be more than 1 independent multivalued dependencies; i.e. the table should not contain more than 1 independent many-to-many relationships. e.g. {teachername, teacherid, courseid}
The fifth normal form states that there should not be more than 1 semantically related multi-valued dependencies. e.g. {skillid, teacherid, courseid}

So in case (a,b) and (b,c) are independent from each other they will be split out into tables {a,b} and {b,c} when one is evaluating whether the table is in 4NF. In case these two are semantically related they will still be split when one is evaluating whether the table is in 5NF. Received on Wed Aug 01 2007 - 11:50:43 CEST

Original text of this message