Definition: Let r be a relation and let A, B, ..., C be attributes of r. Then the projection of r on (or over) those attributes, r{A,B,...,C}, is a relation with (a) heading {A,B,...,C} and (b) body the set of all tuples x such that there exists some tuple t in r with A value equal to the A value in x, B value equal to the B value in x, ..., and C value equal to the C value in x.
For example:
To repeat, the result is a relation; thus, “duplicates are eliminated,” to use the common phrase, and that DISTINCT in the SQL formulation is really needed, therefore.[76] The result heading has attributes (or columns) COLOR and CITY—in that left to right order, in SQL.
Let r be a relation. Then:
The projection r{H}, where {H} is all of the attributes—in other words, the heading—of r, just returns r. Such a projection is known as an identity projection.
The projection r{}—in other words, the projection of r on no attributes at all—returns TABLE_DEE if r is nonempty, TABLE_DUM otherwise. Such a projection is sometimes called a nullary projection; however, the term nullary is best avoided because of the potential confusion with SQL-style nulls. (Just to remind you, TABLE_DEE is the unique relation with no attributes and just one tuple—the 0-tuple, of course—and TABLE_DUM is the unique relation with no attributes and no tuples at all. The fact that projecting r on no attributes always yields one of these two relations is a direct consequence of the fact that every tuple has the same value for the empty set of attributes: namely, the 0-tuple. See the answer to Exercise 3.16 in Appendix F if you need to refresh your memory regarding this point.)
Tutorial D also allows a projection to be expressed in terms of the attributes to be removed instead of the ones to be kept. Thus, for example, the Tutorial D expressions
P { COLOR , CITY } and P { ALL BUT PNO , PNAME , WEIGHT }are equivalent. This feature can save a lot of writing (think of projecting a relation of degree 100 on 99 of its attributes).[77] Analogous remarks apply, where they make sense, to all of the operators in Tutorial D.
In concrete syntax, it turns out to be convenient to assign high precedence to the projection operator. In Tutorial D, for example, we take the expression
P JOIN S { CITY }to mean
P JOIN ( S { CITY } )and not
( P JOIN S ) { CITY }Exercise: Show the difference between these two interpretations, given our usual sample data.
[76] I remark in passing out that the phrase “duplicate elimination,” which is used almost universally (not just in SQL contexts), would more accurately be duplication elimination.
[77] A relvar, as opposed to a relation, of such a high degree is unlikely, since it would almost certainly be in violation of the principles of normalization. But such violations aren’t exactly unknown in practice.