In database normalization, 1NF (no multivalued attributes), 2NF (all non-PK attr

Question

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?

Explanation / Answer

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 R1 to Rn, the decomposition will be a lossless-join decomposition if you restrict the legal relations on R to a join dependency on R called ?(R1,R2,...Rn).

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)

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.