SQL and Relational Theory, 2nd Edition

APPENDIX C

C.1 Here’s an SQL version of constraint EQD2 (only; constraint EQD3 is essentially similar, of course).

     CREATE ASSERTION EQD2 CHECK
          ( NOT EXISTS ( SELECT SNO
                         FROM   ST
                         WHERE  SNO IN ( SELECT SNO
                                         FROM   SUT ) )
            AND
            NOT EXISTS ( SELECT SNO
                         FROM   SUT
                         WHERE  SNO IN ( SELECT SNO
                                         FROM   ST ) )
            AND
            NOT EXISTS ( SELECT SNO
                         FROM   SN
                         WHERE  SNO NOT IN ( SELECT SNO
                                             FROM   ST
                                        UNION  CORRESPONDING
                                             SELECT SNO
                                             FROM   SUT ) )
            AND
            NOT EXISTS ( SELECT SNO
                         FROM ( SELECT SNO
                                FROM   ST
                                UNION  CORRESPONDING
                                SELECT SNO
                                FROM   SUT ) AS POINTLESS
                         WHERE  SNO NOT IN ( SELECT SNO
                                             FROM   SN ) ) ) ;

C.2

   WITH T1 AS ( SELECT SNO , STATUS ,
                   CAST ( STATUS AS CHAR ( 3 ) ) AS XSTATUS
            FROM   ST ) ,
    T2 AS ( SELECT SNO , XSTATUS
            FROM   T1 ) ,
    T3 AS ( SELECT SNO , 'd/k' AS XSTATUS
            FROM   SUT ) ,
    T4 AS ( SELECT SNO , XSTATUS
            FROM   T1
            UNION  CORRESPONDING
            SELECT SNO , XSTATUS
            FROM   T3 ) ,
    T5 AS ( SELECT SNO , CITY AS XCITY
            FROM   SC ) ,
    T6 AS ( SELECT SNO , 'd/k' AS XCITY
            FROM   SUC ) ,
    T7 AS ( SELECT SNO , 'n/a' AS XCITY
            FROM   SNC ) ,
    T8 AS ( SELECT SNO , XCITY
            FROM   T5
            UNION  CORRESPONDING
            SELECT SNO , XCITY
            FROM   T6
            UNION  CORRESPONDING
            SELECT SNO , XCITY
            FROM   T7 ) ,
    S  AS ( SELECT SNO , SNAME , XSTATUS , XCITY
             FROM   SN NATURAL JOIN T4 NATURAL JOIN T8 )
SELECT SNO , SNAME , XSTATUS , XCITY
FROM   S

C.3 Because CORRESPONDING means “match on column names” and the single column in the table produced by the expression VALUES(‘S1’)s doesn’t have a name.