HiveBrain v1.2.0
Get Started
← Back to all entries
snippetMinor

How to determine if a database schema violates one of the less known normal forms?

Submitted by: @import:stackexchange-cs··
0
Viewed 0 times
theviolatesnormaldatabaseonelessknowndeterminehowforms

Problem

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?

Solution

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

Context

StackExchange Computer Science Q#151, answer score: 3

Revisions (0)

No revisions yet.