8.1 A type constraint is a definition of the set of values that constitute a given type. The type constraint for type T is checked whenever some selector for type T is invoked; if the check fails, the selector invocation fails on a type constraint violation. Subsidiary exercise: What do you think should happen if the type constraint for type T evaluates to FALSE at the time type T is defined? (Answer: This state of affairs isn’t necessarily an error, but the type in question will be empty. See the answer to Exercise 2.18 elsewhere in this appendix.)

A database constraint is a constraint on the values that can appear in a given database. Database constraints are checked “at semicolons”—more specifically, at the end of any statement that assigns a value to any of the pertinent relvars. If the check fails, the assignment fails on a database constraint violation. Note: Database constraints must also be checked when they’re defined. If that check fails, the constraint definition must be rejected.

8.2 The Golden Rule states (in effect) that no update operation must ever cause any database constraint to evaluate to FALSE, and hence a fortiori that no update operation must ever cause any relvar constraint to evaluate to FALSE either. However, a (total) relvar constraint might evaluate to FALSE, not because some single relvar constraint is violated, but rather because some multirelvar constraint is violated. The point is hardly significant, however, given that—as mentioned in the body of the chapter and explained in more detail in Chapter 9—which relvar constraints are single relvar and which multirelvar is somewhat arbitrary anyway.

8.3 Assertion is SQL’s term for a constraint specified via CREATE ASSERTION. An attribute constraint is a specification to the effect that a certain attribute is of a certain type. A base table constraint is an SQL constraint that’s specified as part of a base table definition (and not as part of a column definition within such a base table definition). A column constraint is an SQL constraint that’s specified as part of a column definition within a base table definition. A multirelvar constraint is a database constraint that mentions two or more distinct relvars. A referential constraint is a constraint to the effect that if B references A, then A must exist. A relvar constraint for relvar R is a database constraint that mentions R. A row constraint is an SQL constraint with the property that it can be checked for a given row by examining just that row in isolation. A single relvar constraint is a database constraint that mentions just one relvar. A state constraint is a database constraint that isn’t a transition constraint. “The” (total) database constraint for database DB is the logical AND of all of the relvar constraints for relvars in DB and TRUE. “The” (total) relvar constraint for relvar R is the logical AND of all of the database constraints that mention R and TRUE. A transition constraint is a constraint on the legal transitions a database can make from one “state” (i.e., value) to another. A tuple constraint is a relvar constraint with the property that it can be checked for a given tuple by examining just that tuple in isolation. Which of these categories if any do (a) key constraints, (b) foreign key constraints, fall into? No answers provided.

8.4 See the body of the chapter.

8.5 a. The integer 345. b. The value of QTY (where, let’s assume, QTY is a variable of type QTY).

8.6 See the body of the chapter.

8.7   TYPE CITY POSSREP { C CHAR CONSTRAINT C = 'London'
                                         OR C = 'Paris'
                                         OR C = 'Rome'
                                         OR C = 'Athens'
                                         OR C = 'Oslo'
                                         OR C = 'Stockholm'
                                         OR C = 'Madrid'
                                         OR C = 'Amsterdam' } ;

Now we can define the CITY attribute in relvars S and P to be of type CITY instead of just type CHAR.

8.8 By definition, there’s no way to impose a constraint in SQL that’s exactly equivalent to the one given in the previous answer, even if we define an explicit type, because SQL doesn’t support type constraints as such. But we could impose the constraint that supplier cities in particular are limited to the same eight values by means of a suitable database constraint, and similarly for part cities. For example, we could define a base table as follows:

     CREATE TABLE C ( CITY VARCHAR(20) , UNIQUE ( CITY ) ) ;

We could then “populate” this table with the eight city values:

     INSERT INTO C ( CITY ) VALUES 'London'    ,
                                   'Paris'     ,
                                   'Rome'      ,
                                   'Athens'    ,
                                   'Oslo'      ,
                                   'Stockholm' ,
                                   'Madrid'    ,
                                   'Amsterdam' ;

Now we could define some foreign keys:

     CREATE TABLE S ( ... ,
                      FOREIGN KEY ( CITY ) REFERENCES C ( CITY ) ) ;

     CREATE TABLE P ( ... ,
                      FOREIGN KEY ( CITY ) REFERENCES C ( CITY ) ) ;

This approach has the advantage that it makes it easier to change the set of valid cities, if the requirement should arise.

Another approach would be to define an appropriate set of base table (or column) constraints as part of the definitions of base tables S and P. Note: SQL’s “domains”—see Chapter 2—could help with this approach (if they’re supported, of course!), because they could allow the pertinent constraint to be written just once and shared by all pertinent columns. For example (in outline):

     CREATE DOMAIN CITY AS VARCHAR(20)
            CONSTRAINT ... CHECK ( VALUE IN ( 'London'    ,
                                              'Paris'     ,
                                              'Rome'      ,
                                              'Athens'    ,
                                              'Oslo'      ,
                                              'Stockholm' ,
                                              'Madrid'    ,
                                              'Amsterdam' ) ) ;

Now we can define the CITY columns in tables S and P to be of “domain CITY” instead of type VARCHAR(20), and they’ll then “automatically” be subject to the required constraint.

Another approach would be to use an appropriate set of CREATE ASSERTION statements. Yet another would be to define some appropriate triggered procedures.

All of these approaches are somewhat tedious, with the first perhaps being the least unsatisfactory.

8.9

   TYPE SNO POSSREP
        { C CHAR CONSTRAINT
                 CHAR_LENGTH ( C ) ≥ 2 AND CHAR_LENGTH ( C ) ≤ 5
                 AND SUBSTR ( C , 1 , 1 ) = 'S'
                 AND CAST_AS_INTEGER ( SUBSTR ( C , 2 ) ) ≥ 0
                  AND CAST_AS_INTEGER ( SUBSTR ( C , 2 ) ) ≤ 9999 } ;

I’m assuming that operators CHAR_LENGTH, SUBSTR, and CAST_AS_INTEGER are available and have the obvious semantics.

8.10

TYPE LINESEG POSSREP { BEGIN POINT , END POINT } ;

I’m assuming the existence of a user defined type called POINT as defined in the body of the chapter. Note, incidentally, that an SQL analog of the foregoing type definition wouldn’t be able to use BEGIN and END as names of the corresponding attributes—attributes being (most unfortunately!) SQL’s term for components of what it calls a “structured type”—because BEGIN and END are reserved words in SQL. (It would, however, be able to use the delimited identifiers “BEGIN” and “END” for the purpose. A delimited identifier in SQL is an arbitrary string of characters—including, possibly, the string of characters that forms an SQL reserved word—enclosed in what SQL calls double quotes, or in other words conventional quotation marks.)

8.11 Type POINT is an example, but there are many others—for example, you might like to think about type PARALLELOGRAM, which can “possibly be represented” in numerous different ways (how many can you think of?). As for type constraints for such a type: Conceptually, each possrep specification must include a type constraint; however, those constraints must all be logically equivalent. For example:

     TYPE POINT
          POSSREP CARTESIAN { X RATIONAL , Y RATIONAL
                              CONSTRAINT SQRT ( X ** 2 + Y ** 2 ) ≤ 100.0 }
          POSSREP POLAR { R RATIONAL , THETA RATIONAL
                          CONSTRAINT R ≤ 100.0 } ;

Whether some shorthand could be provided that would effectively allow us to specify the constraint just once instead of once per possrep is a separate issue, beyond the scope of this book.

8.12 A line segment can possibly be represented by its begin and end points or by its midpoint, length, and slope (angle of inclination).

8.13 I’ll give answers in terms of the INSERT, DELETE, and UPDATE shorthands, not relational assignment as such:

CX1: INSERT into S, UPDATE of STATUS in S

CX2: INSERT into S, UPDATE of CITY or STATUS in S

CX3: INSERT into S, UPDATE of SNO in S

CX4: INSERT into S, UPDATE of SNO or CITY in S

CX5: UPDATE of STATUS in S, INSERT into SP, UPDATE of SNO or PNO in SP (I’m assuming here that constraint CX6, the foreign key constraint from SP to S, is being enforced)

CX6: DELETE from S, UPDATE of SNO in S, INSERT into SP, UPDATE of SNO in SP

CX7: INSERT into LS or NLS, UPDATE of SNO in LS or NLS

CX8: INSERT into S or P, UPDATE of SNO or CITY in S, UPDATE of PNO or CITY in P

CX9: UPDATE of SNO or STATUS in S

8.14 This exercise is a little unfair, since you aren’t supposed to be an expert in Tutorial D! Be that as it may, the answer is yes for KEY and FOREIGN KEY constraints, no for other constraints. Note: There’s no particular reason why the answer shouldn’t be yes in the latter case too, if it were thought desirable; however, any temptation to intermingle (and thereby muddle, à la SQL) specification of the pertinent relation type and specification of such constraints should be firmly resisted. Also, we’d have to be careful over what it might mean for such a “base relvar” constraint if the base relvar to whose definition it’s attached happens to be empty (see the answer to Exercise 8.16 below).

8.15 (The following answer is a little simplified but captures the essence of what’s going on.) Let c be a base table constraint on table T; then the CREATE ASSERTION counterpart to c is logically of the form FORALL r (c)—or, in terms a little closer to concrete SQL syntax, NOT EXISTS r (NOT c)—where r stands for a row in T. In other words, the logically necessary universal quantification is implicit in a base table constraint but has to be explicit in an assertion. See Chapter 10 for further explanation.

8.16 The formal reason has to do with the definition of FORALL when the applicable “range” is an empty set; again, see Chapter 10 for further explanation. Tutorial D has nothing directly analogous to base table constraints in general and thus doesn’t display analogous behavior.

8.17

  CREATE TABLE S
    ( ... ,
      CONSTRAINT CX5
         CHECK ( STATUS >= 20 OR SNO NOT IN ( SELECT SNO
                                                 FROM   SP
                                                 WHERE  PNO = 'P6' ) ) ) ;

  CREATE TABLE P
    ( ... ,
      CONSTRAINT CX5
         CHECK ( NOT EXISTS ( SELECT *
                              FROM   S NATURAL JOIN SP
                              WHERE  STATUS < 20
                              AND    PNO = 'P6' ) ) ) ;

Observe that in this latter formulation, the constraint specification makes no reference to the base table whose definition it forms part of. Thus, the same specification could form part of the definition of absolutely any base table whatsoever. (It’s essentially identical to the CREATE ASSERTION version, anyway.)

8.18 The boolean expression in constraint CX1 is a simple restriction condition; the one in constraint CX5 is more complex. One implication is that a tuple presented for insertion into S can be checked against constraint CX1 without even looking at any of the values currently existing in the database, whereas the same is not true for constraint CX5.

8.19 Yes, of course it’s possible; constraint CX3 does the trick. But note that, in general, neither a constraint like CX3 nor an explicit KEY specification can guarantee that the specified attribute combination satisfies the irreducibility requirement on candidate keys—though it would at least be possible to impose a syntax rule to the effect that if two distinct keys are specified for the same relvar, then neither is allowed to be a proper subset of the other. Such a rule would help, but it still wouldn’t do the whole job.^[206]

8.20

  CREATE ASSERTION CX8 CHECK
        ( ( SELECT COUNT ( * )
            FROM ( SELECT CITY
                   FROM   S
                   WHERE  SNO = 'S1'
                   UNION  CORRESPONDING
                   SELECT CITY
                   FROM   P
                   WHERE  PNO = 'P1' ) AS POINTLESS ) < 2 ) ;

Note the need for an AS clause to accompany the subquery in the outer FROM clause here, even though the name it introduces is never referenced. See the discussion in the section on EXTEND in Chapter 7 if you need to refresh your memory regarding this point.

8.21 Space reasons make it too difficult to show Tutorial D and SQL formulations side by side here, so in each case I’ll show the former first and the latter second. I omit details of which operations might cause the constraints to be violated.

8.22 Constraint CX22a certainly suffices (it’s directly analogous to the formulation I gave for CX4 in the body of the chapter). As for constraint CX22b: Well, let’s see if we can prove it does the job. First of all, to simplify the discussion, let’s agree to ignore supplier names, since they’re irrelevant to the matter at hand. Then we need to show, first, that if the FD {CITY} → {STATUS} holds, then S is equal to the join of its projections on {SNO,CITY} and {CITY,STATUS}; second, if S is equal to the join of its projections on {SNO,CITY} and {CITY,STATUS}, then the FD {SNO} ® {CITY} holds. Denote S{SNO,CITY} and S{CITY,STATUS} by SC and CT, respectively, and denote JOIN{SC,CT} by J. Adopting an obvious shorthand notation for tuples, then, we have for the first part of the proof:

Turning to the second part:

It follows that constraint CX22b does indeed represent the FD, as required. Note carefully, however, that it does so only because we were able to appeal (in the second part of the proof) to the fact that {SNO} is a key for relvar S; it would not correctly represent the desired FD, absent that key constraint.

8.23 It guarantees that the constraint is satisfied by an empty database (i.e., one containing no relvars).

8.24 Suppose we were to define a relvar SC with attributes SNO and CITY and predicate Supplier SNO has no office in city CITY. Suppose further that supplier S1 has an office in just ten cities. Then The Closed World Assumption would imply that relvar SC must have n−10 tuples for supplier S1, where n is the total number of valid cities (possibly in the entire world)!

8.25 We need a multiple assignment (if we are to do the delete in a single statement as requested). Let the supplier number of the specified supplier be x. Then:

     DELETE S WHERE SNO = x , DELETE SP WHERE SNO = x ;

The individual assignments (DELETEs) can be specified in either order.

8.26 These constraints can’t be expressed declaratively in either SQL or Tutorial D, since neither of those languages currently has any direct support for transition constraints. Triggered procedures can be used, but details of triggered procedures are beyond the scope of this book. However, here are possible formulations using the “primed relvar name” convention discussed briefly in the section MISCELLANEOUS ISSUES:

8.27 No answer provided.

8.28 SQL fails to support type constraints for a rather complicated reason having to do with type inheritance. The specifics are beyond the scope of this book; if you’re interested, you can find a detailed discussion in the book Databases, Types, and the Relational Model: The Third Manifesto, by Hugh Darwen and myself (see Appendix G). As for consequences, one is that when you define a type in SQL, you can’t even specify the values that make up that type!—except for the a priori constraint imposed by the representation—and so, absent further controls, you can wind up with incorrect data in the database (even nonsensical data, like a shoe size of 1000, or even −1000).

8.29 In principle they all apply—though Tutorial D in particular deliberately provides no way of specifying constraints, other than a priori ones, for either nonscalar or system defined types.

8.30 The generic expansion of an arbitrary UPDATE in terms of DELETE and INSERT can be inferred by straightforward generalization from the following simple, albeit somewhat abstract, example. Let relvar R have just two attributes, X and Y, and consider the following UPDATE on R:

     UPDATE R WHERE X = x : { Y ≔ y } ;

Let the current (“old”) value of R be r. Define d and i as follows:

     d  =  { t : t ∈ r AND t.X = x }
     i  =  { t′ : EXISTS t ∈ d ( t.X = t′.X ) AND t′.Y = y }

Then the original UPDATE is logically equivalent to the following assignment:

     R  ≔  r MINUS ( d UNION i ) ;

Or equivalently to the following multiple assignment:

     DELETE R d , INSERT R i ;