Denormalization - Physical Database Design - page 350

CREATE TABLE department (deptNum CHAR(2),
PRIMARY KEY (deptNum));
CREATE TABLE item (itemNum CHAR(6),
deptNum CHAR(2),
PRIMARY KEY (itemNum),
FOREIGN KEY (deptNum) REFERENCES department);
CREATE TABLE orderItemSales (orderNum CHAR(9),
itemNum CHAR(6),
salesName varCHAR(256) not null,
PRIMARY KEY (orderNum, itemNum),
FOREIGN KEY (orderNum) REFERENCES order,
FOREIGN KEY (itemNum) REFERENCES item,
FOREIGN KEY (salesName) REFERENCES salesperson);
CREATE TABLE orderDeptSales (orderNum CHAR(9),
deptNum CHAR(2),
salesName VARCHAR(256) not null,
PRIMARY KEY (orderNum, deptNum),
FOREIGN KEY (orderNum) REFERENCES order,
FOREIGN KEY (deptNum) REFERENCES department,
FOREIGN KEY (salesName) REFERENCES salesperson);
Further logical design can be done to minimize the number of 3NF tables [Teorey
2005]. However, we will assume the tables defined here are complete and focus on the
database refinement using denormalization to increase the efficiency for executing que-
ries and updates.
15.4.3 Schema Refinement Using Denormalizaton
We now look at the quantitative tradeoffs of the refinement of tables to improve pro-
cessing efficiency. Assume that each of the following transactions are to be executed
once per fixed time unit.
Example Query 15.3
Select all order numbers assigned to customers who are computer engineers.
SELECT o.orderNum, c.custNum, c.jobTitle
FROM order AS o, customer AS c
WHERE c.custNum = o.custNum
AND c.jobTitle = 'computer engineer';