This chapter reviews some concepts of the normalization process. It assumes that any designer will use a CASE tool that has its own set of notational structures and conventions, so the information is as generic as possible.
Note that many of the dependencies described in this chapter can also be used to optimize SQL requests (see SQL Request and Transaction Processing).
Teradata promotes full database normalization for logical modeling. The reasons for normalized databases include the following factors:
- Normalization is a provable, logical, and consistent method for designing provably correct database schemas.
- The Teradata parallel architecture was designed from the outset to support normalized databases.
The normalization process helps you to structure your thinking about the entities you have identified and the relationships they share among one another. It heightens your awareness of the problems that can occur when all the attributes of a schema are not sorted out and stored in one and only one place.
Because database management systems map logical relations directly to physical tables, it can sometimes appear difficult to separate the logical model from its physical realization. Nevertheless, you should always design a normalized logical model, then, only if necessary to achieve performance levels that are otherwise not possible to realize, denormalize the physical design.
There can be a cost to normalization when the logical model is implemented as a 1-to-1 physical mapping. In a very real sense, normalization optimizes update performance at the expense of retrieval performance. Because of this, it sometimes becomes necessary to denormalize physical tables to some extent in order to achieve reasonable overall system performance. Denormalization is actually an implementation issue, not a logical design issue.
This manual often refers to denormalization in the context of physical database design because the logical and physical models of a database are independent things. The physical model applies only to implementation. See Denormalizing the Physical Schema for a description of denormalization.
Before examining the details of the normalization process, you should understand the properties of relations and their logical manipulation described in Properties of Relations and Their Logical Manipulation.