15.00 - Denormalized Physical Schemas and Ambiguity - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

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 80, “The Referential Integrity Rule” on page 95, and “Domains and Referential Integrity” on page 100 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

com_id

commodity_id

com_name

commodity_name

grp_id

group_id

grp_name

group_name

fam_id

family_id

fam_name

family_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 86).

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.