The Normal Forms | Database Design | Teradata Vantage - 17.10 - The Normal Forms - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Design

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update

Normalization theory is constructed around normal forms that define a system of constraints. If the form of a relation meets the constraints of a particular normal form, it is said to be in that form.

While there many normal forms, the third normal form (3NF) discussed in this section is adequate for most logical database designs.

The Objective of Normalization

The intent of normalizing a relational database can be reduced to one simple aphorism: One Fact In One Place. By decomposing your relations into fully normalized forms, you can eliminate the majority of update anomalies that can occur when data is stored in unnormalized tables. Decomposition is attained through a series of projections of a relational schema into a set of normalized relations that optimize the conciseness of attributes.

A slightly more detailed statement of this principle would be the definition of a relation (or table) in a normalized relational database: A relation consists of a primary key (more accurately, a candidate key.), which uniquely identifies any tuple, and zero or more additional attributes, each of which represents a single-valued (atomic) property of the entity type identified by the primary key (once again, it is more accurate to say candidate key in place of primary key.

Types of Decomposition

Relations can be decomposed in one of two ways: horizontally or vertically.

A horizontal decomposition is one in which a relation is partitioned along its cardinality dimension. In other words, entire tuples are divided into two or more tuple sets. Conceptually, this is pure relational restriction without projection, as indicated by the following graphic:

Horizontal decomposition example

A vertical decomposition is one in which a relation is partitioned along its arity dimension. In other words, the attributes of a relation are decomposed into two or more sets of projections.

Conceptually, this is pure relational projection without restriction, as indicated by the following graphic:

Vertical decomposition example

Notice that the vertical decomposition in this example is not a simple splitting of attributes between relvars (see Relations, Relation Values, and Relation Variables), but an actual normalization step (to BCNF), so the SuppNum attribute is duplicated, once as a PK in Supplier and again as an FK in Parts.

A more pure example of vertical composition is the projection of a subset of the columns of a large table into a single-table join index (see Single-Table Join Indexes).

For more information about decomposing relations, see Decomposing Relations.