Denormalized Physical Schemas and Ambiguity - Teradata Database

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.

com_id	com_name	grp_id	grp_name	fam_id	fam_name	sequence
PK

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 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.