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.

Any more explanation and preferably an example would help in
understanding the concept behind this normal form.

Sameeksha wrote:
The join dependency definition is the definition for 5th normal form.
6th normal form was introduced in Date's, Darwen's and Lorentzos'
/Temporal Data and the Relational Model/

Basically, 6th normal form puts each non-key attribute in a separate
relation.

On 30 jul, 12:45, Sameeksha <sameeksha.ch... (AT) gmail (DOT) com> wrote:
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?

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

On Jul 30, 9:30 pm, Jan Hidders <hidd... (AT) gmail (DOT) com> wrote:
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.

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.

3. Regarding RDBMS support - what you say is correct. The statement is
there at http://en.wikipedia.org/wiki/Database_normalization, and it
is about temporal dimension, not about the sixth normal form.

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? If
this is not a suitable example for applying the sixth normal form,
please give another example which will make the concept clearer.

Thanks again for your help

Sameeksha

On 31 jul, 06:33, Sameeksha <sameeksha.ch... (AT) gmail (DOT) com> wrote:
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.

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

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

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

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

On Jul 31, 5:24 pm, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:
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.

On 1 aug, 11:50, Sameeksha <sameeksha.ch... (AT) gmail (DOT) com> wrote:
That depends on the candidate keys. If {b} is a candidate key then it
is in 5NF if this is the only non-trivial JD that holds. If {b} is not
a candidate key then it is not in 5NF.

That's a rather complicated and confusing way of formulating it and
depending on how you define independency of multivalued dependencies
probably wrong. I suggest we stick to the more standard definition:

A relattion is said to be in 4NF if for every non-trival MVD A->>B it
holds that A is a superset of a candidate key.

How do you define "semantically related"? Also here it is really much
simpeler if you just stick to the standard definition:

A relation is said to be in 5NF if all join dependencies are implied
by the candidate keys.

Any attempt to reformulate it to something easier or more intuitive in
my experience almost always ends up with something that is either
wrong or actually harder to understand.

The only somewhat mysterious part may be the "JD is implied by the
CKs" but this can be tested by the following simple procedure:

1. Let jd be the join dependency we want to test
2. While jd has two elements (being sets of attributes) Si and Sj such
that the intersection of Si and Sj contains a candidate key do:
2.1 replace Si and Sj with the union of Si and Sj
3. If jd contains the header of the relation (which is also a set of
attributes) then return "yes" else "false"

For some reason most textbooks don't give this algorithm.

Unless you tell me how you define "independent" and "semantically
related" the above might be complete nonsense. Again, your intuition
doesn't seem to be far off, but we need to be exact here.

-- Jan Hidders

"Jan Hidders" <hidders (AT) gmail (DOT) com> wrote

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.

Below script models above using dbd.

(; Create skills)
(new 'child_psychology 'skill)
(new 'english 'skill)
(new 'algebra 'skill)
(new 'logic 'skill)
(new 'judo 'skill)
(new 'sing 'skill)

(; Create courses)
(new 'history_101 'course)
(new 'physics_101 'course)
(new 'chemistry_101 'course)

(; Set course skill requirements)
(set history_101 skill english)

(set physics_101 skill english)
(set physics_101 skill logic)

(set chemistry_101 skill english)
(set chemistry_101 skill algebra)


(; Create teachers)
(new 'john 'teacher)
(new 'mary 'teacher)
(new 'sue 'teacher)

(; Create verb teach)
(new 'teach 'verb)

(; Set john's skills)
(set john skill child_psychology)
(set john skill english)
(set john skill algebra)
(set john skill logic)
(set john skill judo)

(; Set courses that john teaches)
(set john teach history_101)
(set john teach physics_101)


(; Set mary's skills)
(set mary skill child_psychology)
(set mary skill english)
(set mary skill algebra)
(set mary skill sing)

(; Set courses that mary teaches)
(set mary teach history_101)
(set mary teach chemistry_101)


(; Set sue's skills)
(set sue skill child_psychology)
(set sue skill sing)


(; Get all teachers who have skills for chemistry_101)
(; Gets john and mary)
(and (get teacher instance *)
(getAll * skill (get chemistry_101 skill *)))

(; Get courses taught by teachers that can sing)
(; Gets history101 and chemistry101)
(and (get course instance *)
(get (get * skill sing) teach * ))