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.
Get SQL and Relational Theory, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.