Denormalization - Physical Database Design - page 344

PRIMARY KEY (empNum),
FOREIGN KEY (deptNum) REFERENCES department);

The functional dependencies for these two tables are:

•

Table department: deptNum -> deptName

•

Table emp: empNum -> empName, manager, deptNum

Example Query 15.2

SELECT e.empName, d.deptName
FROM emp AS e, department AS d
WHERE d.deptNum = e.deptNum;

In this relationship we denormalize emp by defining emp2 to include deptName from
the department table. The new attribute deptName in emp2 is shown in boldface.

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

CREATE TABLE emp2
(empNum INTEGER,
empName VARCHAR(30),
manager VARCHAR(30),
deptNum INTEGER,
deptName VARCHAR(30),
PRIMARY KEY (empNum),
FOREIGN KEY (deptNum) REFERENCES department);

The functional dependencies for these two tables are:

•

Table department: deptNum -> deptName

•

Table emp2: empNum -> empName, manager, deptNum

•

deptNum -> deptName

Table department is still in 3NF, but table emp2 has lost normalization to below
3NF. To compensate for the lost normalization in emp2, we could keep department as a
redundant table. The cost of this redundancy is in storage space and increased update
time since updates involving deptName will have to be made to both tables. A third