Denormalizing Through Prejoins - Teradata Database

Denormalizing Through Prejoins

A prejoin moves frequently joined attributes to the same base relation in order to eliminate join processing. Some vendors refer to prejoins as materialized views.

Example  

The following example first indicates two normalized relations, Job and Employee, and then shows how the attributes of the minor relation Job can be carried to the parent relation Employee in order to enhance join processing:

Job			Employee
JobCode	JobDesc		EmpNum	EmpName	JobCode
PK	NN, ND		PK, SA		FK
UPI			UPI
1015	Programmer		22416	Jones	1023
1023	Analyst		30547	Smith	1015

This is the denormalized, prejoin form of the same data. This relation violates 2NF:

Employee
EmpNum	EmpName	JobCode	JobDesc
PK, SA		FK
UPI
22416	Jones	1023	Analyst
30547	Smith	1015	Programmer