Denormalization - Physical Database Design - page 342

15.2

Common Types of Denormalization

Denormalization is often used to suggest alternative logical structures (schemas) during
physical design and thus provides the designers with other feasible solutions to choose
from. More efficient databases are the likely outcome of evaluating alternative struc-
tures. This process is referred to as denormalization because the schema transformation
can cause the degree of normalization in the resulting table to be less than the degree of
at least one of the original tables.
The two most common types of denormalization are two entities in a one-to-one
relationship and two entities in a one-to-many relationship.

15.2.1 Two Entities in a One-to-One Relationship

The tables for these entities could be implemented as a single table, thus avoiding fre-
quent joins required by certain applications. As an example, consider the following two
tables in 3NF and BCNF:

CREATE TABLE report
(reportNum INTEGER,
reportName VARCHAR(64),
reportText VARCHAR(256),
PRIMARY KEY (reportNum));

CREATE TABLE reportAbbreviation
(abbreviation CHAR(6),
reportNum INTEGER NOT NULL UNIQUE,
PRIMARY KEY (abbreviation),
FOREIGN KEY (reportNum) REFERENCES report);

The functional dependencies for these tables are:

•

Table report: reportNum -> reportName, reportText

•

Table reportAbbreviation: abbreviation -> reportNum

• reportNum -> abbreviation

Example Query 15.1

SELECT r.reportName, ra.abbreviation
FROM report AS r, reportAbbreviation AS ra
WHERE r.reportNum = ra.reportNum;