Denormalized Physical Schemas and Ambiguity - Teradata Database

Teradata Database Design

Teradata Database
Release Number
English (United States)
Last Update
Product Category

Denormalized Physical Schemas and Ambiguity

A denormalized physical schema creates ambiguity because within a denormalized table, it is not possible to determine which columns are related to the key, to parent tables, or to one another (see “Functional, Transitive, and Multivalued Dependencies” on page 82, “The Referential Integrity Rule” on page 97, and “Domains and Referential Integrity” on page 102 for explanations of how fully normalized tables avoid this ambiguity). Normalized models maintain all relationships through the association of primary keys with their functionally dependent attributes and with foreign keys.

Consider the following non‑normalized table, where all but one of the column names have been abbreviated to fit on the page.


The expanded column heading names are given in the following table.


                   Abbreviated Column Name

                      Actual Column Name













This table meets the criteria for 2NF, but not for 3NF, because not only does every non‑key attribute not depend on the primary key, but several non‑key attributes are functionally dependent on other non‑key attributes (see “Definition of Third Normal Form” on page 88).

You cannot perceive the relationships among the commodity_id, group_id, and family_id attributes by looking at this table. While it is fairly obvious that commodity_name is a functional dependency of the primary key, it could also be true that the commodity attribute has separate relationships with groups and families, or it could be true that commodities are related to groups, which are, in turn, related to families. Moreover, the relationship of the Sequence attribute to the primary key, or to anything else, cannot be resolved. Is it used to order commodities within groups, commodities within families, or groups within families? It is surprising how often such dangling relationships are found in denormalized logical models.

In contrast, normalized models maintain all relationships through the association of primary keys with their attributes and with foreign keys. As a result, you can clearly see the relationships among the various tables in the database schema simply by looking at the data model.