     16 

     

    16.1.  

          ,   
      ,    
.      ,      
  .   , ,    
 ,        
    ,     .  
       (   
)          . 
 ,  -       
     .   
      ,   
    .  ,      
,       ,   
  ! 
            . 
      .   
 ,     .  
         
 .       
  .       5  
   ,   ,     
     .   
    SQL,      
  . 
    .   ,    ,   
  ,   [16.5].    
      
 ,    [16.5].  ,   
    ,     
- (David McGoveran),       . 
            
 .         
, ..   ON UPDATE R,     , 
  ,      ,   
  R.       [16.4]. 

    16.2.   

            . 
    1.     ,        
     ,     
       .  
         
   ,    5,      
       . 

    416  IV.   

    2.      ,    5. 
            
,     " " , 
,    ,    
,   ,  .     
      . 
,     S, ,   
   , ,      
   . 
         ,   
     ,  
  .    ,   
 ,       
 . 
      , ,      
 ,        
. (     "",    
 ,      .)  
        
 . 
    3.  ,        
 .  ,      
      ,    
  .    , 
,    ,   
. (      5,   
         .)      
   ,      
 90 %      .  , 
     ,      
   .         
  .     
  ,       . 
    4.   ,   ""     
    . ,       
, ,     ,  ,    
  .  ,      
 ,       
.  , ,   ,    
  ,     
 .   13 ,     
   "" .  ,   14 
   (  ),  
,        
 " "     " ".  
   ,      
 ,  " "  "". 
,       . 
    5.   ,     
     ,      . 
 ,         

     16, 	417 
    14      

    ,      . 
    ,  " "   
     ,     
     ,     
      . 
    6.   ,  ,  (   ) 
 ,    ,    
.       ,  
      (INSERT, 
UPDATE, DELETE)     ,    
  . 
    .  ,  (    )  
   ,   , 
      .  
    ,   ,  
   .        . 

    16.3.   

         ( )   
 ,      .   
    . 

    CREATE INTEGRITY ROLE PR4 
    TORALL PX ( PX.WEIGHT > 0 ) 
    ON ATTEMPTED VIOLATION REJECT ; 

             
      [16.5]       
    ,       
.        ,  
,         . 
    1.          
  (    PR4).    
          
  . 
    2.   ,    , 
    , ..   
    ,    ,  
    ,    . 
    .          
 FORALL  ,        . 
(          SX, SY, 
...     S;  PX, PY, ...   
     ..  ,     
 ,         
    .) ,   
        ,     
.         
,   ,     "" 
.        . 
     , ,  ,      
       . 
  " "  " "  
  . 

    418	 IV.   

    3.     ,     ON ATTEMPTED 
VIOLATION,    ,     
    .   
          
 . ( , ,    
       
.)        ,  
     .       
     . 
       CREATE INTEGRITY RULE    
,         
.    ,       
,       (..   )  
     .     
          
    P.WEIGHT. 
      .       
   ,   ,   ,   
  .    ()   
  .   ,     
 (        
 )         
,       : 

    CREATE INTEGRITY RULE PR4 P.WEIGHT > 0 ; 

    ,         : 

    DESTROY INTEGRITY RULE  ; 

         . 

    DESTROY  INTEGRITY RULE PR4   ; 

           
.      ,  : 
 , ,    . 
    1.         
 . 
    2.          
 . 
    3.         
 . 
    4.          
  . 
          . 

    16.4.    

          , 
   .  ,     
   .        
    CREATE DOMAIN INTEGRITY RULE,  
        CREATE DOMAIN, : 

    CREATE DOMAIN QTY NUMERIC (9) 
    FORALL QTY ( QTY > 0 AND 
    QTY < 5000 AND 
    MOD ( QTY, 50 ) = 0 ) ; 

     16, 	419 

          . 
             . 
            (   
 ),         
(    ,   .  
 ). 
                . 
          ,     
     ,     
  (     ).   
       ,  
 ()     .   
   ,         
50    ,   5000.       . 
              
      DESTROY DOMAIN. 
     VALUES (___)   
      . , 
,  CREATE DOMAIN: 

    CREATE DCMAIN COLOR CHAR   (6) 
    VALUES   (   'Red1,   'Yellow',   'Blue',   'Green'   )   ; 

       : 

    CREATE DCMAIN COLOR CHAR (6) FORALL COLOR 
    ( COLOR = 'Red' OR COLOR = 'Yellow' OR COLOR = 'Blue' OR COLOR = 'Green' ) ; 

             
 (,   ),  (, PIC '999-99-9999')  .. 

    .  ,       SQL 
      .   
     . 

    16.5.    

          ,   
    .  ,   
     CREATE ATTRIBUTE INTEGRITY 
RULE,           
  . (, ,    
 , ""    .)  
    ,    
 CREATE BASE RELATION    S. 

    SNAME DCMAIN   ( _ ) 

           ,  
,         . 
           . 
             
  ,    . 
           , .. 
  (    INSERT   UPDATE) 

    420	 IV.   

      ,   
 ,   (     
 ""). 
        ,      
    REJECT   ,   . 
          ,     
     ,     
  . (     
          .) 
               
   (       
   ). 

    16.6.    

         ,  
            
.      ,  
,  " ,      
         (  " 
 "      ,  
NUMERlC(w),    ,  " "). 
     ,      
 20". 

    CREATE INTEGRITY RULE SR7 
    FDRALL S ( IF S.CITY = 'London' THEN S.STATUS = 20 ) ON ATTEMPTED 
VIOLATION REJECT ; 

           ( 
       ). 

    CREATE INTEGRITY RULE   [ ON ATTEMPTED VIOLATION  ] ; 

    : 
          . 
          , .. 
       
         . 
        (       
)    WFF ( WFF    7) 
   (    ,  
     "").  ,  
 ,        
    ,   .  ,  
 (.. )        
    , .. ,     
.     SR7     
,   S,        
 . 
       ( )    
,    ,    
  .    ,    
   ,    . 
   

     16. 	421 

       : ,  
         
    .    
     ,     
 5,      ( , 
    !). 

    .    REJECT  -  
,         
 ,     .  , 
          
. (,         
 ,   .) 

           . 

    1. CREATE INTEGRITY RULE RX1 
    S.STATUS > 0 AND S.STATUS < 100 ; 

       ,  "     
  1-100".  ,   STATUS  , 
      .    
STATUS          
  . 
         :  
       ,     
   .   PR4,   
   ("   "),    
.         
  . ,      
      (,   " 
   "    "null-  
"),      .  ,   
       
  ( ).   ,   
,     ,  ,     
       . 
    2.          
 SCP   9 (. . 9.1). ,   SCP   
    S#, P#  QTY  CITY,  
,     . 

    CREATE INTEGRITY RULE RX2 
    FORALL SCPX ( FDRALL SCPY 
    ( IF  SCPX.S* = SCPY.S# THEN SCPX.CITY = SCPY.CITY ) ) ; 

          SCPX  SCPY     
  SCP.    ,      
 SCP     S#,       
     CITY.  ,    
    CITY   S#,  
   "S#-CITY"     
  . 

    422	 IV.   

          ,   
   . 

    CREATE INTEGRITY RULE RX2 
    IF SCPX.S* = SCPY.S* THEN SCPX.CITY = SCPY.CITY ; 
    3. CREATE INTEGRITY RULE RX3 
    IF SX.S# = SY.S# THEN SX.SNAME = SY.SNAME AND SX.STATUS = SY.STATUS AND 
SX.CITY = SY.CITY ; 

        S     S#,   
      ;  ,   
      .  ,   
,   S#     S.  
     CANDIDATE KEY (s#)  
      (   
 ). 

    4. CREATE INTEGRITY RULE RX4 
    COUNT ( P WHERE P.COLOR = 'Red' ) > 1 ; 

    (   ,  "      
  ".)   7 ,    (COUNT  
.)       ,   .  , 
          
    p.COLOR = 'Red1 (     
).    ,    COUNT 
 EXISTS: 

    CREATE INTEGRITY RULE RX4 
    EXISTS PX   (  EXISTS  PY   (   PX.Ptt  PY.P# AND 
    PX.COLOR =   'Red'  AND PY.COLOR =  'Red'   )   )   ; 

     ,   RX4     ,  
        ( 
?).  ,        
,     ,     DELETE 
    (,   ,  
INSERT   ). 
       ,  (     4)  
     ,     
     .    
   .       
        
 ,     . 
           .  
,     ,    
,        
 ,  ,     . 

    16.7.     

         ,   
       .    
 ,  ,  "    20  
      500". 

    CREATE INTEGRITY RULE C95 
    FORALL SX ( FORALL SPX 
    ( IF SX.STATUS < 20 AND SX.S# = SPX.S* THEN SPX.QTY < 500 } ) ; 

     16. 	423 

              
   : 

    CREATE INTEGRITY RULE C95 
    IF SX.STATUS < 20 AND SX.S# = SPX.S* THEN SPX.QTY < 500 ; 

            SX (  S)  SPX 
(  SP). .        
    ? 
              
,      : 

    CREATE INTEGRITY RULE  
     [ ON ATTEMPTED VIOLATION  ] 

            . 
               
 " ",     , 
      (   
    "SP.S#=S.S#").  ,  
   ,       . 
            .  
   ,    , 
     ( COMMIT).   
 ,      .   
    (  )    
   .     (, 
,   )   ""  
(  ,      ),  
      ,     
  . 
                
 REJECT,    ROLLBACK.      
    (       
 ). 

    .     ROLLBACK  -  
,         
,    .  ,  
       
   (   ,     
      ,   ). 

           . 

    1. CREATE INTEGRITY RULE DBX1 
    FORALL SP ( EXISTS S ( S.S# = SP.S# ) AND EXISTS P ( P.P# = SP.P# ) ) ; 

    (   ,  "    
  ".)  ,   S#    
 ,   #     ,   
     ,   
.  ,       
  ,     !   - 
   . 

    424  IV.   

    2. CREATE INTEGRITY RULE DBX2 
    FORALL P EXISTS SP ( SP.P# = P.P# ) ; 

    (   ,  "     
   ".)      
      ,    , 
,    .     
  ,      
         . 

    3.    CREATE INTEGRITY RULE DBX3 
    FORALL P   (   SUM   (SP WHERE SP.Pft = P.P#,   QTY)   < 2000   )   ; 

    (   ,  "       
  2000".)  ,    WHERE  
SP  "" SUM.   ,  (    
)   FORALL P   ,    
         . 
       :      
    (    ),    
          , 
        
     ,      
. (     ,      
.  ,      
,         
 ,  ,      ) 

    16.8.     

              
,         . 
          
. ,         
         .   
    : 
    	        ; 
    	    ; 
    	    ; 
    	    . 
          : 
            ; 
            ; 
        ; 
        . 
           ,   
 , ,  "    ": 
    CREATE INTEGRITY RULE SR9 FORALL S' FORALL S ( IF S'.S# = S.S# THEN 
S'.STATUS < S.STATUS ) ; 

     16. 	425 

    .    ,     
      ,    
     . (   
 ,       
  ,    .)     
   : 
     S'  S        
          
(..       ),      
S'         S. 
            ,   
  : 

    CREATE INTEGRITY RULE SR9 
    IF S'.S# = S.S# THEN S'.STATUS < S.STATUS ; 

           (   
  S),   .    
     . 

    CREATE INTEGRITY RULE AR43 FORALL S 
    ( SUM ( SP WHERE SP.S# = S.S#, QTY ) > ( SUM ( SP' WHERE SP'.St = S.S#, 
QTY ) ) ; 

    (  ,  "   , 
   ,   ".)   
     (     S  SP), 
   ,    SP'   
 SP  ,      . 
              
 . 

    16.9.     

         (  ) 
       ,   
  ,      , ..  
    CREATE INTEGRITY RULE,    
.          
 ,       
   ; ..   ""   
   ,    
    . (    ,   
          
 (         
,  ),        
   .         
    ,   .) 
      ,     ,  , 
            
 .       
,     16.6: 

    CREATE INTEGRITY RULE RX3 
    IF SX.Stt = SY.Sff THEN SX.SNAME = SY.SNAME AND SX.STATUS = SY.STATUS AND 
SX.CITY = SY.CITY ; 

    426	 IV.   

        16.6,       
,   S#      S. 
      ,    
   S, 

    CANDIDATE KEY ( S# ) 

    ,        . , 
 ,        
   ,  . 
                
. ,       
,   ,   S#   SP  
    S. 

    CREATE INTEGRITY RULE SPSFK 
    FORALL SP ( EXISTS S ( S.S# = SP.S# ) ) ; 

        (    
  SP) : 

    FOREIGN KEY   (   S#   )   REFERENCES S   ... 

        ,    
,         
.        : 

    CREATE INTEGRITY RULE SPSFK 
    FORALL SP ( EXISTS S ( S.S# = SP.Stt ) ) ON ATTEMPTED VIOLATION 
    DELETE SP WHERE NOT EXISTS S ( S.Stt = SP.S# ) ; 

      (..      ) 
      : 
    1.     ( INSERT)  SP,    
    S,  SP  .  : 
       . 
    2.     ( UPDATE)    
  SP ,       
 S,  SP  .  :   
(UPDATE)     (DELETE). 
    3.     ( UPDATE)    
  S   SP,       
 S,  .  :   (UPDATE)  
    (DELETE),     (UPDATE). 
    4.     ( DELETE)   S  
 SP,        S,  . 
 :   (DELETE)    
  (DELETE). 
     , , ,     
 , , .  ,   
    .     
,        ,    . 
      ,       
  (  ,   ) 
      . , 
   ,      
      : 
, "      "  " 
   -

     16. 	427 

      ". ( ,    5  
    (UPDATE CASCADES),  
 (DELETE RESTRICTED)  ..     
 .)  ,    
,          
 . 
            
[16.8],     .    
,       
 .        
  (     ),   
        
      .     
         ,  
         . 
(        ,  
           
,   [16.6]. ,    ,  
  .)       
   ;         [16.8]. 

    16.10.       SQL 

          SQL  
     .   SQL   
    . 
    1.	 . 
    2.	  . 
    3.	   "". 
                
.  ,   "  "  
   ,    . 
        8.     
  ,   . 
           SQL   (    
)     .     
    . 
            SQL   (  
  )      .     
    . 
            SQL  
,     ,    
 : "     "! 
            
       . 
            .  
    CREATE ASSERTION,     
    CREATE INTEGRITY RULE    : 

    CREATE ASSERTION  CHECK   (   1   ; 

    428	 IV.   

           ,    
-_     (  
 SQL    8).      
 DROP ASSERTION    : 

    DROP ASSERTION  ; 

     ,         , 
   ,  DROP ASSERTION    
RESTRICT  CASCADE. 
        CREATE ASSERTION. 
    1.        5. 

    CREATE ASSERTION IC13 CHECK 
    ( ( SELECT MIN ( S.STATUS ) FROM S ) > 4 ) ; 

    2.      . 

    CREATE ASSERTION IC18 CHECK 
    ( NOT EXISTS ( SELECT * FROM P 
    WHERE NOT ( P.WEIGHT > 0 ) ) ) ; 

    3.        . 

    CREATE ASSERTION IC99 CHECK 
    ( NOT EXISTS ( SELECT * FROM P 
    WHERE P.COLOR = 'Red1 
    AND  P.CITY <> 'London' ) ) ; 

    4.  He      (   
  )   20 000. 

    CREATE ASSERTION IC46 CHECK 
    ( NOT EXISTS ( SELECT * FROM P, SP WHERE P.P# = SP.P# AND ( P.WEIGHT * 
SP.QTY ) > 20000 ) ) ; 

    5.      20     
  500. 

    CREATE ASSERTION IC95 CHECK 
    ( NOT EXISTS ( SELECT * FROM S, SP WHERE S.STATUS < 20 AND  S.S# = SP.S# 
AND  SP.QTY > 500 ) ) ; 


      

          SQL    
 DEFERRABLE (),  NOT DEFERRABLE (). 
  DEFERRABLE      INITIALLY 
DEFERRED ( )  INITIALLY IMMEDIATE ( 
),        . 
         
,       IMMEDIATE,  DEFERRED. 

    SET CONSTRAINTS   ; 

        : 

    SET CONSTRAINTS IC46, IC95 DEFERRED ; 

         (COMMIT)   
     IMMEDIATE. ,  
 DEFERRABLE   ,     
 IMMEDIATE,   NOT DEFERRABLE   . 

     16. 	429 


    16.11.  

              
   .    . 
              . 
              . 
     ,  ,     
 ,  ,      
  ,     , 
     .   
     : 
           . 
       ,       ,  
      . 
        ,   ,     ,       
   . 
        ,  ,    
   . 
        ,       
   ,      . 
        ,     
     ,      
 . 
        ,      
    , ..       
,        . 
 ,         
 ,       
    .       
          
 .         , 
        
 ,        . 
        
   . 
        SQL,      
 ,       
().        
(         ).  
 ,        
, .. ,  ,      
      [16.6].  ,     
       SQL  
  SET CONSTRAINTS. 
       ,     ,   
   ,      
,           
.   ,       
 . 

    430  IV.   

     

     . 16.1-16.16        
        
,    ,    , 
  . 
    16.1.     (London),  (Paris),  
(Rome),  (Athens),  (Oslo),  (Stockholm),  (Madrid)  
 (Amsterdam). 
    16.2.      50 . 
    16.3.             . 
    16.4.            . 
    16.5.           
    . 
    16.6.            
   . 
    16.7.      ,      
 . 
    16.8.      ,      
   . 
    16.9.         . 
    16.10.        15. 
    16.11.        2. 
    16.12.         50 . 
    16.13.          
        . 
    16.14.       ,  
  . 
    16.15.         
,    . 
    16.16.          ,  
      . 
    16.17.      . 16.1-16.16 ,    
     . 
    16.18.      . 16.1-16.16  ,  
     ,     
. ,         
  "", ..       ! 
    16.19.   ,       ,   
,        
,      ,   
   .      ? 
    16.20.   . 16.116.16   SQL. 

     16. 	431 

         15 ( [15.16]),      
      INGRES.    
    ,     . 
       DEFINE INTEGRITY 
  : 
    DEFINE INTEGRITY ON  IS  
            
    DEFINE INTEGRITY ON S IS S.STATUS > 0 
    ,   U     REPLACE: 
    REPLACE S ( STATUS = S.STATUS - 10 ) WHERE S.CITY = "London" 
      INGRES      
    REPLACE S ( STATUS = S.STATUS - 10 ) WHERE S.CITY = "London" AND ( 
S.STATUS - 10 ) > 0 
    ,         
. (    ,    15, 
   "".)      
   ,       
    QUEL   15.  ,    
          
. ,   QUEL    , 
      .    
        . 
    16.12. Walker A., Salveter S. . Automatic Modification of Transactions to 
Preserve Data Base Integrity Without Undoing Updates: Technical Report 
81/026. Stony Brook, N.Y.: State University of New York, 1981. 
           " 
" (..   )    
,      ,     
,     ,   
   .      
        ,   
    ,    
   .         
  ,        . 

        

             
,    . 

    16.1. CREATE DOMAIN CITY CHAR (15) 
    VALUES ( 'London', 'Paris', 'Rome', 
    'Athens', 'Oslo', 'Stockholm', 'Madrid', 'Amsterdam' ) ; 
    16.2. CREATE INTEGRITY RULE IR2 
    IF P.COLOR = 'Red' THEN P.WEIGHT < 50 ; 

    434	 IV.   

    16.3. CREATE INTEGRITY RULE IR3 
    FORALL JX FORALL JY ( IF JX.J# * JY.Jtt THEN JX.CITY  JY.CITY } ; 
    16.4. CREATE INTEGRITY ROLE IR4 
    COUNT ( S WHERE S.CITY = 'Athens' ) < 2 ; 
    16.5. CREATE INTEGRITY RULE IR5 
    SPJX.QTY < 2 * AVG ( SPJY.QTY ) ; 
    16.6. CREATE INTEGRITY RULE IR6 
    IF SX.SATUS = MAX ( S.STATUS ) AND SY.SATUS = MIN ( S.STATUS ) AND 
SX.SATUS * SY.STATUS THEN SX.CITY * SY.CITY ; 
       "   "  " 
  "    ,    
  .    ,   SX 
 SY     " "  " " 
,  SX  SY      .  
  ,  " "  " "   
 ,      . 
    16.7. CREATE INTEGRITY RULE IR7 
    FORALL J EXISTS S ( S.CITY = J.CITY ); 
    16.8. CREATE INTEGRITY RULE IR8 
    FORALL J EXISTS S EXISTS SPJ ( J.J# = SPJ.J# AND 
    SPJ.S# = S.S# AND S.CITY = J.CITY ); 
    16.9. CREATE INTEGRITY RULE IR9 IF EXISTS P { true ) THEN EXISTS P ( 
P.COLOR = 'Red' ) ; 
      ("        
")  ,    ,    
    . 
    16.10. CREATE INTEGRITY RULE IR10 
    ( IF EXISTS S { true ) THEN AVG (S, STATUS) ELSE 20 ) > 15 ; 
          ,   
   ,       
 20, .. ,  15. .     
  ,   AVG   
     . 
    16.11. CREATE INTEGRITY RULE IR11 IF S.CITY = 'London' THEN EXISTS SPJ ( 
SPJ.S# = S.S# AND SPJ.P# = 'P2' ) ; 
    16.12. CREATE INTEGRITY RULE IR12 
    IF EXISTS P ( P.COLOR = 'Red' ) 
    THEN EXISTS P ( P.COLOR = 'Red' AND P.WEIGHT < 50 ) ; 
    16.13. CREATE INTEGRITY RULE IR13 
    SP.QTY > 0.5 * SP'.CTY 
    16.14. CREATE INTEGRITY RULE IR14 
    COUNT ( P WHERE EXISTS SPJ EXISTS S ( P.P# = SPJ.P# AND SPJ.S* = S.S# AND 
S.CITY = 'London' ) ) > COUNT { P WHERE EXISTS SPJ EXISTS S ( P.P# = SPJ.Pt 
AND SPJ.S# = S.S# AND S.CITY = 'Paris' ) \ ; 

     16. 	435 

    16.15. CREATE INTEGRITY RULE IR15 
    SUM ( SPJ WHERE EXISTS S 
    ( SPJ.SS = S.S# AND 
    S.CITY = 'London' ), GTY ) > SUM ( SPJ WHERE EXISTS S 
    { SPJ.Sff = S.S# AND S.CITY = 'Paris' ), QTY ) ; 
    16.16. CREATE INTEGRITY RULE IR16 IF S'.CITY = 'Athens' 
    THEN S.CITY IN ( 'Athens', 'London', 'Paris' ) AND 
    IF S'.CITY = 'London' THEN S.CITY IN ( 'London', 'Paris' ) ; 

    16.17.  IR1    ,   
   .  IR7, IR8, IR11, IR14  IR15  
   ,      
 . 
        :  ,   
 ..     " ",     
(, "   X       Y 
  ").    ,  ,  
  . ( .) 
    16.18.        16.18.,  16. 
  .      
  ,         
    .     
    16.18.1.   (INSERT)   S, P  J,   
  (UPDATE)   S.CITY, P.CITY  J.CITY. 
    16.18.2.   (INSERT)   ,    
 (UPDATE)   P.COLOR  P.WEIGHT. 
    16.18.3.   (INSERT)   J,   
(UPDATE)   J.J#  J.CITY.   ,   
   J.J#,  (    
 IR3)     (UPDATE)   J.CITY. 
    16.18.4.   (INSERT)   S,    
 (UPDATE)   S.CITY. 
    16.18.5.   (INSERT)   (DELETE)   SPJ, 
a    (UPDATE)   SPJ.QTY. 
    16.18.6.   (INSERT)   (DELETE)   S,  
   (UPDATE)   S.STATUS  S.CITY. 
    16.18.7.   (INSERT)   J,   
(DELETE)   S,     (UPDATE)   
S.CITY  J.CITY. 
    16.18.8.   (INSERT)   J,   
(DELETE)   S  SPJ,     (UPDATE)  
 S.S#, S.CITY, J.J#, J.CITY, SPJ.S#  SPJ.J#. 
    16.18.9.   (INSERT)   (DELETE)   ,  
   (UPDATE)   P.COLOR. 
    16.18.10.   (INSERT)   (DELETE)   S,  
   (UPDATE)   S.STATUS. 

    436	 IV.   

    16.18.11.   (INSERT)   S,   
(DELETE)   SPJ,     (UPDATE)   
S.S#, S.CITY, SPJ.S#  SPJ.P#. 
    16.18.12.   (INSERT)   (DELETE)   ,  
   (UPDATE)   P.COLOR  P.WEIGHT. 
    16.18.13.   (UPDATE)   SP.QTY. 
    16.18.14.   (INSERT)   (DELETE)   S, P 
 SPJ,     (UPDATE)   S.S#, S.CITY, .#, 
SPJ.S#  SPJ.P#. 
    16.18.15.   (INSERT)   (DELETE)   S  
SPJ,     (UPDATE)   S.S#, S.CITY, SPJ.S#  SPJ.QTY. 
    16.18.16.   (UPDATE)   S.CITY. 
    16.20.       16.20.,  16.  
 . 
    16.20.1. CREATE DOMAIN CITY CHAR (15) 
    CONSTRAINT VALID_CITIES CHECK ( VALUE IN 
    ( 'London', 'Paris', 'Rome', 'Athens', 'Oslo1, 'Stockholm', 'Madrid', 
'Amsterdam' ) ) ; 
    16.20.2. CREATE ASSERTION IR2 CHECK 
    ( P.COLOR 0 'Red' OR P.WEIGHT < 50 ) ; 
    16.20.3. CREATE ASSERTION IR3 CHECK 
    ( NOT EXISTS ( SELECT * FROM J JX WHERE EXISTS ( SELECT * FROM J JY WHERE 
( JX.J# 0 JY.J# AND JX.CITY <> JY.CITY ) ) ) ) ; 
    16.20.4. CREATE ASSERTION IR4 CHECK 
    ( ( SELECT COUNT(*) FRCM S 
    WHERE S.CITY = 'Athens' ) < 2 ) ; 
    16.20.5. CREATE ASSERTION IR5 CHECK 
    ( NOT EXISTS ( SELECT * 
    FRCM SPJ, SPJX WHERE SPJX.OTY > 2 * 
    ( SELECT AVG ( SPJY.OTY ) FROM SPJ SPJY ) ) ) ; 
    16.20.6. CREATE ASSERTION IR6 CHECK 
    ( NOT EXISTS ( SELECT * FRCM S SX WHERE EXISTS ( SELECT * FRCM S SY WHERE 
    SX.SATUS = ( SELECT MAX ( S.STATUS ) 
    FRCM S ) AND SY.SATUS = ( SELECT MIN ( S.STATUS ) 
    FROM S ) AND 
    SX.SATUS  SY.STATUS AND SX.CITY <> SY.CITY ) ) ) ; 
    16.20.7. CREATE ASSERTION IR7 CHECK 
    ( NOT EXISTS ( SELECT * FROM J WHERE NOT EXISTS ( SELECT * FROM S WHERE 
S.CITY = J.CITY ) ) ) ; 
    16.20.8. CREATE ASSERTION IR8 CHECK 
    ( NOT EXISTS ( SELECT * FRCM J WHERE NOT EXISTS ( SELECT * FROM S WHERE 
S.CITY = J.CITY AND EXISTS ( SELECT * FRCM SPJ 
    WHERE SPJ.S# = S.S# 
    AND  SPJ.J# = J.J# ) ) ) ) ; 

     16. 	437 

    16.20.9. CREATE ASSERTION IR9 CHECK 
    ( NOT EXISTS ( SELECT * FRCM P ) OR EXISTS ( SELECT * FRCM P 
    WHERE P.COLOR = 'Red' ) ) ; 
    16.20.10. CREATE ASSERTION IR10 CHECK 
    ( ( SELECT AVG (S.STATUS) FRCM S ) > 15 ) ; 
       ,   AVG  SQL  
null-,      ,  
      . 
    16.20.11. CREATE ASSERTION IR11 CHECK 
    ( NOT EXISTS ( SELECT * FROM S 
    WHERE S.CITY = 'London' AND  NOT EXISTS 
    ( SELECT * FRCM SPJ WHERE SPJ.S# = S.S# AND  SPJ.P# = 'P21 ) ) ) ; 
    16.20.12. CREATE ASSERTION IR12 CHECK 
    ( NOT EXISTS ( SELECT * FRCM P 
    WHERE P.COLOR = 'Red' ) OR EXISTS ( SELECT * FRCM P 
    WHERE P.COLOR = 'Red' AND  P.WEIGHT < 50 ) ) ; 
    16.20.13. He   ,   SQL   
  . 
    16.20.14. CREATE ASSERTION IR14 CHECK 
    (( SELECT COUNT;*) FROM P 
    WHERE EXISTS ( SELECT * FRCM SPJ WHERE EXISTS ( SELECT * FRCM S WHERE ( 
P.P# = SPJ.P# AND SPJ.S# = S.S# AND 
    S.CITY = 'London' ) ) ) ) > ( SELECT COUNT(*) FRCM P 
    WHERE EXISTS ( SELECT * FRCM SPJ WHERE EXISTS ( SELECT * FRCM S WHERE ( 
P.P# = SPJ.P* AND SPJ.Stt = S.S# AND S.CITY = 'Paris' ))))); 
    16.20.15. CREATE ASSERTION IR15 CHECK 
    ( ( SELECT SUM ( SPJ.OTY ) FRCM SPJ WHERE ( SELECT S.CITY FRCM S 
    WHERE ( S.S# = SPJ.Stt ) = 'London' ) > ( SELECT SUM ( SPJ.QTY ) FRCM SPJ 
WHERE ( SELECT S.CITY FRCM S 
    WHERE ( S.S# = SPJ.SI ) = 'Paris' ) ) ; 
    16.20.16. He   ,   SQL   
  . 

    438	 IV.   

