Relational Theory for Computer Professionals

Chapter 5. Relational Operators II

Men of judgment ... so enclose

Infinite riches in a little room

—Christopher Marlowe: The Jew of Malta (c. 1592)

As noted in Chapter 4, any number of operators can be defined that fit the simple definition of “one or more relations in, exactly one relation out.” That previous chapter described Codd’s original operators (join, project, etc.); in this chapter, by contrast, I want to describe some of the many additional operators that have been defined since the relational model was first invented. Specifically, I want to discuss (a) MATCHING and NOT MATCHING; (b) EXTEND; (c) image relations; and (d) aggregation, summarization, and related matters.

MATCHING and NOT MATCHING

It turns out in practice that most relational expressions—not all—that seem to need the join operator for their formulation really need a related but logically distinct operator called semijoin (MATCHING, in Tutorial D). Here’s an example (the query is “Get supplier details for suppliers who supply at least one part”):

S MATCHING SP

Here’s the result:

SNO	SNAME	STATUS	CITY
S1	Smith	20	London
S2	Jones	10	Paris
S3	Blake	30	Paris
S4	Clark	20	London

This result contains just the tuples for suppliers that “match” at least one shipment.

Here now is the semijoin definition (note how it appeals to the concept of joinability, which was defined in Chapter 4):

• Definition: Let relations r1 and r2 be joinable, and let r1 have attributes called A1, A2, ..., An (only). Then the expression r1 MATCHING r2 denotes the semijoin ...