Database Reference
In-Depth Information
partial dependency
means that attributes in the entity don't rely entirely
on the primary key. Transitive dependency means that attributes in the
entity don't rely on the primary key at all, but they do rely on another
non-key attribute in the table. In either case, removing the offending at-
tribute (and related attributes, in the 3NF case) to another entity solves the
problem.
One of the simplest ways to remember the basics of 3NF is the popu-
lar phrase, “The key, the whole key, and nothing but the key.” Because the
normal forms are nested, the phrase means that 1NF is met because there
is a primary key (“the key”), 2NF is met because all attributes in the table
rely on all the attributes in the primary key (“the whole key”), and 3NF is
met because none of the non-key attributes in the entity relies on any other
non-key attributes (“nothing but the key”). Often, people append the
phrase, “So help me Codd.” Whatever helps you keep it straight.
Boyce-Codd Normal Form (BCNF)
In certain situations, you may discover that an entity has more than one po-
tential, or candidate, primary key (single or composite).
Boyce-Codd nor-
mal form
simply adds a requirement, on top of 3NF, that states that if any
entity has more than one possible primary key, then the entity should be
split into multiple entities to separate the primary key attributes. For the
vast majority of databases, solving the problem of 3NF actually solves this
problem as well, because identifying the attribute that has a transitive de-
pendency also tends to reveal the candidate key for the new entity being
created. However, strictly speaking, the original 3NF definition did not
specify this requirement, so BCNF was added to the list of normal forms
to ensure that this was covered.
Fourth Normal Form (4NF) and Fifth Normal Form (5NF)
You've seen that 3NF generally solves most logical problems within data-
bases. However, there are more-complicated relationships that often ben-
efit from 4NF and 5NF. Consider Table 4.8, which describes an
alternative, expanded version of the Agents entity.
Search WWH ::
Custom Search