Database Reference
In-Depth Information
they may exist because of information not available to the original
designer. Specifically, identify the key entities, foreign key rela-
tionships, and any entities and tables that exist only in the physical
model that are purely for relationship support (such as many-to-
many relationships). You can then review the key and non-key at-
tributes of every entity, evaluating for each normal form. Ask
yourself whether or not each entity and its attributes follow the
“The key, the whole key, and nothing but the key” ideal. For each
entity that seems to be in 3NF, evaluate for BCNF and 4NF. This
analysis will help you understand to what depth the original design
was originally done. If there are many-to-many relationships, en-
sure that 5NF is met unless there is a specific reason that 5NF is
not necessary.
Identifying the normal form of each entity in a database should be
fairly easy once you understand the normal forms. Make sure to consider
every attribute: does it depend entirely on the primary key? Does it de-
pend only on the primary key? Is there only one candidate primary key in
the entity? Whenever you find that the answer to these questions is no, be
sure to look at creating a separate entity from the existing entity. This prac-
tice helps reduce redundancy and moves data to each element that is spe-
cific only to the entity that contains it.
If you follow these basic steps, you'll understand what forms the data-
base meets, and you can identify areas of improvement. This will help you
complete a thorough review—understanding where the existing design
came from, where it's going, and how to get it there. As always, document
your work. After you have finished, future designers and developers will
thank you for leaving them a scalable, logical design.
Generally, most
online transactional processing
(OLTP) systems will
perform well if they've been normalized to either 3NF or BCNF. However,
certain conditions may require that data be intentionally duplicated or that
unrelated attributes be combined into single entities to expedite certain
operations. Additionally,
online analytical processing
(OLAP) systems,
because of the way they are used, quite often require that data be denor-
malized to increase performance.
Denormalization,
as the term implies,
Search WWH ::
Custom Search