Denormalization - Physical Database Design - page 343

In this relationship we denormalize report by defining report2 to include abbreviation
and thus eliminate the abbreviation table completely. The new entry in report2 is shown
in boldface.

CREATE TABLE report2
(reportNum INTEGER,
reportName VARCHAR(30),
reportText VARCHAR(256),
abbreviation CHAR(6),
PRIMARY KEY (reportNum));

The functional dependencies for the new report table are:

•

Table report2: reportNum -> reportName, reportText,
abbreviation, abbreviation -> reportNum

The revised table report2 is also in 3NF and BCNF, so there can be no loss of data
integrity due to deletes involving reportNum or abbreviation. If a report is deleted, both
its report number and abbreviation are deleted, so neither one is left orphaned in the
database.

15.2.2 Two Entities in a One-to-many Relationship

Sometimes logical design results in very simple tables with very few attributes, where
the primary key is a foreign key in another table you want to join with. In such cases,
when a query wants data from both tables, it may be more efficient to implement them
as individually named columns as an extension of the parent entity (table).
Let's look at the following example. The table department is the “parent” table and
emp is the “child” table since one department can have potentially many employees and
each employee (emp) is in only one department.

CREATE TABLE department
(deptNum INTEGER,
deptName VARCHAR(30),
PRIMARY KEY (deptNum));

CREATE TABLE emp
(empNum INTEGER,
empName VARCHAR(30),
manager VARCHAR(30),
deptNum INTEGER,