SQL and Relational Theory, 3rd Edition

Chapter 6

SQL and Relational Algebra I: The Original Operators

Join the union!

—Susan B. Anthony (1869)

This is the first of two chapters on the operators of the relational algebra; it discusses the original operators (i.e., the ones briefly described in Chapter 1) in some depth, and it also examines certain ancillary but important issues—e.g., the significance of proper attribute (or column) naming once again. It also explains the implications of such matters for our overall goal of using SQL relationally.

SOME PRELIMINARIES

I’ll begin by reviewing a few points from Chapter 1. First, recall that each algebraic operator takes at least one relation as input and produces another relation as output.¹ Second, recall too that the fact that the output is the same kind of thing as the input(s)—they’re all relations— constitutes the closure property of the algebra, and it’s that property that lets us write nested relational expressions. Third, I gave outline descriptions in Chapter 1 of what I there called “the original operators” (restrict, project, product, union, intersect, difference, and join); however, now I’m in a position to define those operators, and others, much more carefully. Before I can do that, however, I need to make a few more general points:

• The operators of the algebra are generic, meaning they apply (in effect) to all possible relations. For example, we don’t need one specific join operator to join departments and employees and another, different, join operator ...