4
$\begingroup$

In database normalization, 1NF (no multivalued attributes), 2NF (all non-PK attributes depending only on PK attributes) and 3NF (all non-PK attributes depending on all of the PK attributes) are widely known. The 4NF (no part of the PK depending on other part of the PK) is less known, but still reasonably known.

Much less known are the 5NF, 6NF and the intermediates EKNF (Elementary Key normal form), BCNF (Boyce-Codd normal form - 3.5) and DKNF (Domain/Key normal form - 5.5). What exactly are that? Given a database schema, how do I determine if any table violates one of these much less knows normal forms?

Improve this question
$\endgroup$
6
  • $\begingroup$ Wikipedia, I believe, gives definitions for at least BCNF and DKNF. What about those definitions doesn't make sense? $\endgroup$
    – Patrick87
    Mar 9, 2012 at 13:12
  • 1
    $\begingroup$ Wikipedia gives definitions and explanations to all except EKNF. However the explanation given looks hard to understand for me. Maybe this is because I am simply too stupid for understand them, but possibly because these articles on wikipedia are too technical and detalistics. Further, even if I am simply too stupid to understand, possibly other people are too, so a nice simpler, easier and enlightning answer would be nice. Actually I wrote that question with the intention that I might myself eventually answer it if nobody else do that before. $\endgroup$
    – Victor Stafusa - BozoNaCadeia
    Mar 9, 2012 at 13:23
  • $\begingroup$ I asked on the meta about stocking the site with "easy" questions (i.e., ones I could answer off the top of my head) and the consensus was that we shouldn't do it. That being said, I don't see a need to elaborate on my answer, if you don't elaborate on your question to point out what about this is "confusing". And just because Wikipedia doesn't include EKNF doesn't mean I couldn't get an explanation in a Google search. Without more specifics, I don't see this question as being a particularly good one... just saying. $\endgroup$
    – Patrick87
    Mar 9, 2012 at 13:33
  • $\begingroup$ @Victor The problem with asking questions that way is that if you don't even mention the Wikipedia articles and why you didn't understand them, people will give you answers that are as technical and detailistic as the wikipedia articles because they don't know that they were hard to understand for you. $\endgroup$
    – sepp2k
    Mar 9, 2012 at 14:56
  • $\begingroup$ This tutorial seems to be simple http://www2.yk.psu.edu/~lxn/IST_210/normal_form_definitions.html $\endgroup$
    – Sid
    Dec 9, 2012 at 8:47

1 Answer 1

Reset to default
3
$\begingroup$

From Wikipedia:

1NF: The table faithfully represents a relation and has no repeating groups.

2NF: Non-prime attributes are non functionally dependent on a proper subset of a candidate key.

3NF: Non-prime attributes are non-transitively dependent on every candidate key in the table.

BCNF: Every non-trivial functional dependency is a dependency on a superkey.

4NF: Every non-trivial multivalued dependency in the table is a dependency on a superkey.

5NF: Every non-trivial join dependency in the table is implied by the superkeys of the table.

DKNF: Every constraint in the table is a logical consequence of the table's domain and key constraints.

Please let me know which, if any, of these requires further explanation. Each provides a means of checking whether a table is in the normal form.

EDIT: Again from Wikipedia:

A join dependency is a constraint on the set of legal relations over a database scheme. A table $T$ is subject to a join dependency if $T$ can always be recreated by joining multiple tables each having a subset of the attributes of $T$. If one of the tables in the join has all the attributes of the table $T$, the join dependency is called trivial.

The join dependency plays an important role in the Fifth normal form, also known as project-join normal form, because it can be proven that if you decompose a scheme $R$ in tables $R_1$ to $R_n$, the decomposition will be a lossless-join decomposition if you restrict the legal relations on $R$ to a join dependency on $R$ called $*(R_1,R_2,...R_n)$.

In other words, if a constraint on your table $T$ is that T = R JOIN S for some tables $R$ and $S$, then $T$ has a join dependency $*(R, S)$.

Improve this answer
$\endgroup$
2
  • $\begingroup$ Can you explain better. What is a "non-trivial join dependency" for example? $\endgroup$
    – Victor Stafusa - BozoNaCadeia
    Mar 12, 2012 at 20:44
  • $\begingroup$ I will give you some more to improve your answer: Why are the 3NF and the BCNF descriptions that you gave not equivalent? What about 6NF? Given a set of tables in some schema, what should I look for to discover something violating 5NF? $\endgroup$
    – Victor Stafusa - BozoNaCadeia
    Mar 12, 2012 at 21:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.