SQL and Relational Theory, 2nd Edition

BASE vs. DERIVED RELATIONS

As I explained earlier, the operators of the relational algebra allow us to start with some given relations, such as the ones depicted in Figure 1-3, and obtain further relations from those given ones (for example, by doing queries). The given relations are referred to as base relations, the others are derived relations. In order to get us started, therefore, a relational system has to provide a means for defining those base relations in the first place. In SQL, this task is performed by the CREATE TABLE statement (the SQL counterpart to a base relation being, of course, a base table, which is what CREATE TABLE creates). And base relations obviously need to be named—for example:

     CREATE TABLE S ... ;

But certain derived relations, including in particular what are called views, are named too. A view (also known as a virtual relation) is a named relation whose value at any given time t is the result of evaluating a certain relational expression at that time t. Here’s an SQL example:

     CREATE VIEW SST_PARIS AS
          ( SELECT SNO , STATUS
            FROM   S
            WHERE  CITY = 'Paris' ) ;

In principle, you can operate on views just as if they were base relations,^[14] but they aren’t base relations. Instead, you can think of a view as being “materialized”—in effect, you can think of a base relation being constructed whose value is obtained by evaluating the specified relational expression—at the time the view in question is referenced. But I must emphasize that thinking of views being materialized ...