     V 

      

      II    ,      
   ,    .    
  .     II,   ,  
     ,   
,  ,   ,      
        
 (    ,    111  IV). 
      ,   
  . 
       ( 17). 
       ( 18). 
      ,     ( 19). 
        ( 20). 
           / ( 21). 
     ,   ,      . 


     17 

     

    17.1.  

        3, ,  ,   
     .   
    (view)   : 

    CREATE VIEW GOOD_SUPPLIERS AS 
    ( S WHERE STATUS > 15 ) [ S#, STATUS, CITY ] ; 

       ,  ,    AS 
    ,     
,  ""       
     GOOD_SUPPLIERS.   ,  
,        
GOOD_SUPPLIERS     ,    
   . 17.1.  ,  GOOD_SUPPLIERS   
 , ..  ,    
 ,  . 

    GOOD SUPPLIERS 

    S#	SNAME	STATUS	CITY 

    SI	Smith	20	London 
    S2	Jones	10	Paris 
    S3	Blake	30	Paris 
    S4	Clark	20	London 
    S5	Adams	30	Athens 
    
    . 17.1. GOOD_SUPPLIERS     S.  
  ,      

      3   ,       
    .  ,    
,        (  
),  ,  ,    ,  
         
.    ,    
   . 
    ,      (, ,   
 ),    ,  
GOOD_SUPPLIERS  .     ,   
 ,     S;    ,  
GOOD_SUPPLIERS   .    , 
    ,      ,    
  .     GOODJSUPPLIERS: 

    1 := GOOD SUPPLIERS WHERE CITY # 'London' 
    I 

    440  V.   

         . 

    1 

    s#	STATUS	CITY 

    S3	30	Paris 
    S5	30	Athens 

              "" 
;     3,      
        (  
).    ,  (  
)     ,   
.      : 

    1 := ( ( S WHERE STATUS > 15 ) [ Stt, STATUS, CITY ] ) 
    WHERE CITY  'London1 ; 
           : 
    1 := ( S WHERE STATUS > 15 AND CITY * 'London1 ) 
    [ S#, STATUS, CITY ] ; 

            
   . 
        ,    (..  
      )   
   ,  (  ) 
,     ,     
 R,      X (, , 
,    X  ,  
 ).  ,   ,   
    .    , 
   . 
         . ,  

    UPDATE GCOD_SUPPLIERS WHERE CITY = 'Paris' STATUS := STATUS + 10 ; 

        

    UPDATE S WHERE STATUS > 15 AND CITY = 'Paris' STATUS := STATUS + 10 ; 

     INSERT  DELETE  . 

    17.2.     

         , , , 
        .   
       ,    
 .     ,  
          
  .     , 
           
    .      
       . 
       . 

    .       .  
 ,      . 

     17. 	441 

        ,   ,  
    .    . 
             
.      ,   
  . ,   DISCOUNT ()  
 ,     . 
    ( ,       . 
    4,         
.    ,       
        ,  
 .) 
        ,     
     . 
                
 ,     . 

      . 

          ,  
     ,    
 .  ,      
 .     . , 
  -  (       ) 
   S   : 

    SNC ( S#, SNAME, CITY ) 
    PRIMARY KEY ( S# ) ST { Stt, STATUS ) 
    PRIMARY KEY ( Stt ) 

      ,    S    
  SNC  ST(a  SNC  ST    
S). ,      S,   
   S: 

    CREATE VIEW S AS SNC JOIN ST ; 

        ,    
  S,     S. , 
          
,      
       1. 
     ,    S,    
,    , SNC  ST,    
 . , ,     
   SP,    .  ,   
   ,     ,  
     (.     ). 
      !  ,   (  SQL)  
       , 
,         
   ,   .  , 
   (  )    
         
   .     
          
,      . 

    442	 V.   

        ,  :    
 SNC  ST  ,      
       .  
,       ()  
         ( 
    ).  ,   
    -.  ,  
   . 
            
(     ,    
 ). 
            
           . 
     ,     
 , ,       , 
  ,    .   
     ,     
  . 
                  , 
    "". 
      "  ,    , 
   ".       
  CITY_PAIRS ( )   SCITY  PCITY 
 ,     {SCITY :x, PCITY:y}   
    ,  ,    , 
 ,     .  CITY_PAIRS 
       : 

    ( CITY_PAIRS WHERE SCITY = 'London' ) [ PCITY ] 

    He  ,     : 

    ( ( ( S RENAME CITY AS SCITY ) JOIN SP JOIN ( P RENAME CITY AS PCITY ) } 
    WHERE SCITY = 'London' ) [ PCITY ] 

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

     17. 	443 

         . 
     " "   ,     
 .  ,    
     ;      
   .       15. 

    17.3.   

         CREATE VIEW  
 : 

    CREATE VIEW  AS  ; 

          ,   
(     )  
 .   CREATE VIEW  
       ( 
 ANSI/SPARC   2),       
       . 
      ,      
 . 

    1. CREATE VIEW REDPARTS AS 
    ( ( P WHERE COLOR = 'Red' ) [ P#, PNAME, WEIGHT, CITY] ) 
    RENAME WEIGHT AS WT ; 

           REDPARTS, 
         .  
   : #, PNAME, WT  CITY,  
  #, PNAME, WEIGHT  CITY   . 

    .  WEIGHT       
 ,      . 

    2. CREATE VIEW PQ AS 
    SUMMARIZE SP BY ( P# ) ADD SUM ( OTY ) AS TOTOTY ; 

         (  REDPARTS), 
         
 (,   ).    
       . 

    3. CREATE VIEW CITY_PAIRS AS 
    ( ( S RENAME CITY AS SCITY ) JOIN SP JOIN 
    ( P RENAME CITY AS PCITY ) ) [ SCITY, PCITY ] ; 

           .   , 
   (, )       , 
 ,    ,  ,    
 . ,  S1   1;  S1  
        .  ,  
 (, )   . 

    4. CREATE VIEW LCNDCN_REDPARTS AS 
    REDPARTS WHERE CITY = 'London' ; 

            ,  
  ,        
   . 

    444	 V.   

     DESTROY VIEW   : 

    DESTROY VIEW  ; 

              
 (..       ).  
    : 

    DESTROY VIEW REDPARTS  ; 

      4 ,   DESTROY BASE RELATION  
,        (  
(snapshot)),     . ,  
DESTROY VIEW  ,   -  
( )    .    
CREATE VIEW ( CREATE SNAPSHOT)    RESTRICTED  
CASCADES.  RESTRICTED ,     
  , a CASCADES       
,   " "     (  
)1.  RESTRICTED   . 

    17.4.   :   

             
       ().  
      . 
       ,      
     .  ,   
      ,    
   .  D     (  
      ),  ӗ  
,    D, .. ,  
   "   D: 

    V = X  ( D ) . 

     R      V.  R   
  : 

    R(V)=R(X(D)). 

     ,        
   X   D (  
V)     R   .  
       , 
    ,   R(X)  
 X  R (   ),      
  D. 
             
.   ,     ,    
      -,        
  
     . 
      -,     ,  
     , , ,   
 . 
    1  SQL [8.1]    RESCTRICTED  CASCADES; 
    DROP TABLE  DROP VIEW (  
DESTROY BASE RELATION  DESTROY VIEW).   CREATE VIEW  
  RESTRICTED  CASCADES.     SQL  
          . 

     17. 	445 

    I   -,       
         , 
   ,         
SQL    ;       
    .    
    SQL,   1992 . ,  
     SQL      
,      . 

    17.5.   :   

            
   .      
 .        
 ,         
?     .  D   
,  V ,   D, .. ,  
   X   D (    ): 

    V = X  ( D ). 

      U       V.   U 
    ,        

    U  ( V )   = U  ( X  ( D )   ) . 

             
   (/',     

    U(X(D))=X(U'(D)). 

          - ,  
" "   D ( )   
     . 

    .    ,   U'   
    ,      
   (    
,        JOIN  
     ). 

       ,   ,   
        
  ,      
     ,     
(Codd)   RM/V2 [4.4] (.  [17.3, 17.7-17.9, 17.11, 17.13]).  
      [17.5, 17.6],   
         
       .  
,         
 . 

       

       ,     
  .    : 
          ,     
   .      
,     . 

    446	 V.   

    (        4 , 
 ,        
    .) 
     ,        
  ,      . 
         . 
    1.        
,   .  ,      
    . ,   
   : 

    CREATE VIEW V AS 
    S WHERE STATUS > 25 OR CITY = 'Paris' ; CREATE VIEW V AS 
    ( S WHERE STATUS > 25 ) UNION ( S WHERE CITY = 'Paris' ) ; 

    ,      . (   
 ,  SQL    
SQL-      .  
  SQL   SQL-    
 ,      .) 
    2.     ,     
         . 
    ,        
   ,    UNION ,  INTERSECT 
,  WHERE true,  MINUS  ( ї     ,   
     )    ,  
      .  ,  
,   ,    
V =  UNION ,   ,    
      . 
    3.      . ,  
DELETE   V = A INTERSECT       
    ,          
    .       
    . ( ,    
.        
,      .) 
    4.       -,    . 

    .        
  ,       . 
  ,        . 

    5.       UPDATE  
      (DELETE-INSERT), 
      .    
,    . 
     " "       
 .  ,    
    UPDATE            DELETE-INSERT-,                
DELETE-
    iNSERT-.    ,   DELETE - 
   ,      

     17. 	447 

    UPDATE  . , ,   R  10 
,     "  F   R,  
   R ,        
10 . 
         " "   . 
(     ,    
 .) 
        " "  
  . 
      ,   UPDATE   
DELETE-INSERT ,        
  ,          . 
    6.          
   .  ,   
(INSERT)   (DELETE)      
        ( 
 ,     UPDATE).   , 
,      (  
,      (UNION)),   
 INSERT    DELETE   .  
    INSERT       
   DELETE!     (.  2),  
          
V =  UNION .        
 (, ,   ..).  ,   
         
,   .  ,   , 
  INSERT  DELETE      
 INSERT  DELETE   . 
    7.         V  
,     ,    
 V.      ,  ,  
  ,   ,  . 
 ,      . 
,         
  ,    ,   
   . 
    8.     "",     
 (..  ).   ,   
    ,     
.          
 .        . 
    9.    ,      
,      (,  
  DELETE    INSERT). 
    10.  INSERT  DELETE   ,   
,   . 
         .       
,        
,  ,    ,   
. 

    448	 V.   

 ,      (, 
         
).           
.        
       , 
  ,      ,   
           
     . 
          : 
, , ,    . 

    17.6.   ,    

        ,     
,      (A UNION ), 
 (A INTERSECT )   (A MINUS ),      
  (   ).      
       (, ). 
          . 

    .  ,   ,   
   . ,  ,  
   .     ,  
    ,      
    ;   
   ,     
,   .     . 

     

           A UNION . 
      INSERT.          
  .     ,   
      (,      
 ,          ). 
     ,       
  ,            
   . 

    .   ,    
 "  ,     ",     
 .   ,     
    ,   .     
   ,    ,      
      .   
   ,    . 

    .        ,   
        A UNION ,  ,  
    (PA) OR (). ( ,  
       ,   ,      
    ,   .) 
,    ,     
        ,      
  (  ). 

     17. 	449 
    15      

    .   UV   

    ( S WHERE STATUS > 25 ) UNION ( S WHERE CITY = 'Paris' ) 

       . 17.2     
    . 

    UV 

    s#	SNAME	STATUS	CITY 

    S2	Jones	10	Paris 
    S3	Blake	30	Paris 
    S5	Adams	30	Athens 
    
    
    .17.2.  UV ( ) 

       (S6, Smith, 50, Rome)  ,     
 UV.     s WHERE STATUS > 25 
(    s WHERE CITY = 'Paris'). , 
    ,   s WHERE STATUS 
> 25.      ,    (. 
), ,        , 
   . 
        ,    UV    (S7, 
Jones, 50, Paris).      

    S WHERE STATUS > 25  S WHERE CITY =   'Paris'. 

     ,          
.   ,  ,        
   ,        
 .  ,        
. 
         , SA  SB.  SA 
   ,    STATUS  25. 
 SB    ,    (. 
17.3). ,   UV    SA UNION 
SB,         . 
  (S6, Smith, 50, Rome)   UV    
    SA,  ,    
.       (S7, Jones, 50, Paris)  
 UV       , SA  SB. 
   ,       
(      " ").  
""       
  .  ,      
,          
.   (, )    [17.12]. 

    SA                                   SB 

    S#	SNAME	STATUS	CITY		S#	SNAME	STATUS	CITY 

    S3	Blake	30	Paris			S2	Jones	10	Paris 
    35	Adams	30	Athens		S3	Blake	30	Paris 
    
    . 17.3.   SA  SB (  ) 

             
A UNION . 

    450  V.   

       DELETE.     ,      
(,       ,   
       ).        
   ,      . 
             
 .  ,         
    DELETE     . 
     ,   . 
       UPDATE.     ,    
        .   
 ,        - ,  
    (   ..).  ,  
         . 
 ,    (DELETE)    , 
        .    
  (, ,  )      ,   
   (          - 
).  ,      
 ,     (     
,          ).  
,       ,   
      ,         
     . 
            ,  
   .     
          
(   ,      
(UPDATE),        ). 
            
(UPDATE),    ,      
       .   , 
  . 17.3,    LJV  (S5, Adams, 
30, Athens)   (S5, Adams, 15, Paris)      
 SA       SB. 

     

         A INTERSECT .   
       - 
  (      
). ,    ,   ,   
  A INTERSECT     (PA) AND (PB).  
         . 
      INSERT.        .  
     ,        (,  
     ,     
   ).  ( )      ,     
 . 
      DELETE. ,     ,    
(,       ,   
      ).  ( )     
,      . 

     17. 	451 

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

     

          A MINUS  ( 
       : (PA) AND NOT (PB)). 
      INSERT.          
  .     . 
      DELETE. ,     ,   . 
       UPDATE.     ,    
        .  
         . 
     . 

    17.7.     

           , 
,   .. 

     

        : 
    A WHERE  
     ,      
,   : (PA) AND (). ,  
  
     S WHERE CITY =   'London'    (PS)  AND   (CITY =   
'London'),  PS    S.   , , 
   /,     ,   
 ,    PS(/)  /.CITY = 'London',  
   INSERT  . 
         A WHERE . 
       INSERT.        . 
    . 
       DELETE. ,   ,   . 
       UPDATE.     ,    
     .       
    .    
 . 

    .   LS   : 

    S WHERE CITY = 'London1 


    452	 V.   


     . 17.4     . 

    LS 

    S#	SNAME	STATUS	CITY 

    SI	Smith	20	London 
    S4	Clark	20	London 

    . 17.4.  LS (  ) 

         (S6, Green, 20, London)   LS 
  .       S , 
,    LS. 
         (SI, Green, 20, London)  , 
        S (, , 
 LS).  ,      
    S.S#. 
         (S6, Green, 20, Athens)  , 
       CITY = 'London'. 
          LS  (S I, Smith, 20, London) 
  .      S , , 
  LS. 
         (SI, Smith, 20, London)  (S6, Green, 
20, London)   .    (SI, Smith, 20, 
London)  (S2, Smith, 20, London)   (SI, Smith, 20, Athens) 
  (,      ?). 

     

           .  
        X Y.  
 "     . ,   
{:}    [\    ,    
     Y,    [:, Y:y]   . 
,      S   S#, 
SNAME  CITY.    (s, n, ),    , 
   t,    (s, n, t, )  
 S. 
        . 
      INSERT.  (*) ,   ,     
 ,      (    
  , ..   "   
",   ).  (,),    
 ,    . 

    .       (  ) 
  "   ",  , 
       ,   
   INSERT. 

      DELETE.      ,    X  
  X    [] . 
    .   ,    ' 
    ,     []  
       .   ,  
        . 
      UPDATE (. ). 

     17.  453 

       UPDATE.  ()  ,   ,  ( *)  
   ().      ,     
  ^.     Y   .    
            
  .         
    (', ),    . 

    .   " ", 
        .  , 
 ,         UPDATE 
    Y      
       Y ,  
 ,        . 

    .   SC     : 

    SC   [  S#,   CITY  ] 

     . 17.5      . 

    SC 

    s#	CITY 

    SI	London 
    S2	Paris 
    S3	Paris 
    S4	London 
    S5	Athens 
    
    
    . 17.5.  SC (  ) 

          SC  (S6, London)   
.       S    (S6, 
, t, London),    /  ,    
   S.SNAME  S.STATUS . 
         (SI, London)   SC  
,         S (, 
,  SC).  ,     
     S.S#. 
          SC  (S I, London)  
 .      S. 
         (SI, London)  (SI, Athens)  
 .       S  (SI, Smith, 20, 
London)  (SI, Smith, 20, Athens),   (SI, , /, Athens), , 
 . 
         SC    (SI, London)  (S2, London) 
  ( ). 
    ,        
 (,   S   STATUS  CITY), 
      . 

     

       ,      

    EXTEND A ADD  AS X, 

      : 

      ( a )  AND . = exp ( a ) 

    454  V.   

         ,        
 .      : 
        ""  , : 1)    
    ,   .,  
  ; 2)  .      
 . 
          = EXTEND A ADD  AS X. 
       INSERT.    ,   .    
  .      ,   
  ,   .. 
      DELETE.    ,   .     
 ,     ,   .. 
       UPDATE.   ,   , ''  
  .           
     ,     
,   ..       ', 
  '  ,   '.. 

    .        : 

    EXTEND P ADD   (  WEIGHT  *   454   )   AS  

     . 17.6     . 

     

    #	PNAME	COLOR	WEIGHT	CITY	GMWT 

    1	Nut	Red	12	London	5448 
    2	Bolt	Green	17	Paris	7718 
    	Screw	Blue	17	Rome	7718 
    4	Screw	Red	14	London	6356 
    5	Cam	Blue	12	Paris	5448 
    	Cog	Red	19	London	8626 

    . 17.6.   (  ) 

        (7, Cog, Red, 12, Paris, 5448)   
      (7, Cog, Red, 12, Paris)   . 
         (7, Cog, Red, 12, Paris, 5449)  
 (?). 
         (PI, Cog, Red, 12, Paris, 5448)  
 (?). 
           1       
    1   . 
           1  (PI, Nut, Red, 10, Paris, 
4540)         (PI, Nut, Red, 12, 
London)     (PI, Nut, Red, 10, Paris). 
                2 
(   )  ,    
 GMWT     WEIGHT,   454 (  
   ). 

     17.  455 

     

        ,  
,           
 , ,       
 ,    ,  ,   
   --, --  --.  
         
,  ,    .  , 
           
 . 
        (    ) 
  ,        
   ,    ,   
      .  
,         
       ,  
 .   : 
        (),      
 ,       ; 
           ,       
 ,       . 
    ,  [10.2]  (Codd) ,     
   ,       
,  "     " (,   
, ,  , ,     
).   ,     
        
   . 
       .    ,    
          
 . ,     
  ,      
,         
     .  ,  
     ,    
    .   ,   
 ,         
  . 
      ,     . 
 ,  .       
   .       
    (--, --, --). 
      J = A JOIN ,   ,   J   
{X, Y}, {Y, 2}  {X, , Z} .        
    .   PJ  J  
 : 

      (  a  )   AND   (  ), 

       "-"   j (.. ,  
  '  ,  /Z),   
"-"  ' (.. ,      
,   j.X).  : 
        ,  -   , 
 5-   . 

    456	 V.   

    ,          
S  SP   S#   : 
      (s, n, t, , , q)   ,   (s, n, t, 
)    S   (s, p, q)  
  SP. 
          J = A JOIN . 
       INSERT.      PJ.  - 
 '    ,  -   *.  -  
  ,  -   . 
       DELETE. -     . -  
   . 
      UPDATE.     ,    
  PJ. -      - 
     , -     
   -      .  
  -      ,  -  
 .  -    ,  -   . 
            . 
     1 (--.). ,      
"--"      
"(--)--(--)".  ,   
   , ,       
          . 
,   ,    ,  
   ,         
 . 
    : 
              
     S (   )  
      ( S#). 
        . ,      
     SR { S#, REST },   S# 
 ,   REST     
 . ,       
 SR.       
    S  SR (  S#).  ,  
     SR       S? 
     2 (--.).  "--"    
  "(--)--(--)".  , 
     , ,  
            
. ,   ,    ,  
  ,          
    . 

    1 ,   INSERT    ,  
 -      ,     
,  .      
  DELETE  UPDATE.       
    . 

     17. 	457 

    .        
      NULLS NOT ALLOWED,   
"  "    " ". 

    .   SSP   : 

    S JOIN SP 

    , ,      . 
   SSP   . 17.7. 

    SSP 

    s#	SNAME	STATUS	CITY	P#	QTY 

    SI	Smith	20	London	PI	300 
    SI	Smith	20	London	P2	200 
    SI	Smith	20	London	P3	400 
    SI	Smith	20	London	P4	200 
    SI	Smith	20	London	P5	100 
    SI	Smith	20	London	P6	100 
    S2	Jones	10	Paris	PI	300 
    S2	Jones	10	Paris	P2	400 
    S3	Blake	30	Paris	P2	200 
    S4	Clark	20	London	P2	200 
    34	Clark	20	London	P4	300 
    S4	Clark	20	London	P5	400 
    
    . 17.7.  SSP (  ) 

         (S4, Clark, 20, London, 6, 100)  
 SSP         (S4, 
6, 100)   SP (,     ). 
         (S5, Adams, 30, Athens, P6, 100)  
 SSP         (S5, 
6, 100)   SP (,     ). 
         (S6, Green, 20, London, P6, 100)  
 SSP         (S6, 
Green, 20, London)   S     (S6, 6, 100)   
SP (,     ). 

    .   ,      
 SP      S.  , 
,   SP       
 S6 (        S6  
 1).         
    ,  :   
   (S6, Green, 20, London)     
S6,      SP. 

          (S4, Clark, 20, Athens, P6, 100)  
 SSP   (?). 
         (S5, Adams, 30, London, P6, 100)  
 SSP   (?). 
         (SI, Smith, 20, London, PI, 400)  
 SSP   (?). 
         (S3, Blake, 30, Paris, P2, 200)   
SSP         (S3, Blake, 30, 
Paris)   S   (S3, 2, 200)   SP. 

    458  V.   

         (S1, Smith, 20, London, P1,300)   
SSP     (.  )     
 (SI, Smith, 20, London)   S   (SI, P1,300)  
 SP. 
    .          
       SP.S#  S.S#.   
 RESTRICT,     .   
 CASCADE,      ,    
   SP  ,     S1. 
         (SI, Smith, 20, London, PI, 300)  
 SSP  (SI, Smith, 20, London, PI, 400)   
      (SI, P1, 300)   SP 
 (SI, P1, 400). 
         (SI, Smith, 20, London, PI, 300)  
 SSP  (SI, Smith, 20, Athens, PI, 400)   
      (SI, Smith, 20, London)   
S  (SI, Smith, 20, Athens)   (SI, P1, 300)   SP 
 (SI, P1,400). 
         (SI, Smith, 20, London, PI, 300)  
 SSP  (S6, Smith, 20, London, PI, 300)   
  (.  )      (SI, 
Smith, 20, London)   S  (S6, Smith, 20, London)   
(SI, P1, 300)   SP  (S6,  1,300). 
    .         
       SP.S#  S.S#.  
     . 
     3 (--).  "--"    
  "(--)--(--)".  ,  
     ,  , 
        ,     . 
    . ,     : 

    S JOIN P 

       S     CITY.    
 .17.8. 
    
    s#					P#
    SNAME	STATUS	CITY		PNAME	COLOR	WEIGHT 

    SI	Smith	20	London	PI	Nut	Red	12 
    SI	Smith	20	London	P4	Screw	Red	14 
    SI	Smith	20	London	P6	Cog	Red	19 
    S2	Jones	10	Paris		P2	Bolt	Green	17 
    S2	Jones	10	Paris		P5	Cam	Blue	12 
    S3	Blake	30	Paris		P2	Bolt	Green	17 
    S3	Blake	30	Paris		P5	Cam	Blue	12 
    S4	Clark	20	London	PI	Nut	Red	12 
    S4	Clark	20	London	P4	Screw	Red	14 
    S4	Clark	20	London	P6	Cog	Red	19 
    
    
    . 17.8.   S     CITY 

        (S7, Bruce, 15, Oslo, P8, Wheel, White, 25)  
       (S7, Bruce, 15, Oslo)  
 S   (8, Wheel, White, 25, Oslo)    
(,     ). 

     17.  459 

        (S1, Smith, 20, London, P7, Washer, Red, 5)  
       (7, Washer, Red, 5, London)  
  (,      (SI, Smith, 
20, London, 7, Washer, Red, 5)  (S4, Clark, 20, London, P7, Washer, Red, 5)). 
        (S6, Green, 20, London, P7, Washer, Red, 5)  
       (S6, Green, 20, London)  
 S   (7, Washer, Red, 5, London)    
(,     ). 
        (SI, Smith, 20, London, PI, Nut, Red, 12)  
       (SI, Smith, 20, London)  
 S   ( 1, Nut, Red, 12, London)    
(,     ). 
           . 

      

            : 
 , 0-, ,    
 . 
      .       
   (A JOIN    A TIMES ,     
   ),      (A TIMES ) 
      (A JOIN ).  
     . 
       -.    . , 
          
 . 
     .  . 
     .  (  6-)    , 
        ( , 
   ,    ).   , 
          
 ,   ,  ,    
 , ,     (  ). 
       .      (SUMMARIZE)  
 ,  .     
      . , 
,      SUMMARIZE,  . 

        

          
. , , ,     
,  .       
     INSERT, DELETE  UPDATE  
.    INSERT INTO SSP  ( SSP 
    ),   INSERT INTO (s JOIN SP)  
  . ,      
  . 

    460	 V.   

      ,   LSSP    
 : 

    ( S WHERE CITY = 'London1 ) JOIN SP 

        ,    (S6, Green, 20, 
London, 6, 100)         (S6, 
Green, 20, London)   S   (S6, 6, 100)   SP. 
,      (S6, Green, 20, London)     
   S,    s WHERE CITY = 'London1,   
      ,      
.   ,     :  
   INSERT    
,     . 

    17.8.  

      ,     ,  
  (snapshots) [17.1]. ,    , 
     (     ).  
   4,        (.. 
 ),   .   ,    
,  ,   :       
       ,   ( 
  )   .   , 
   4. 

    CREATE SNAPSHOT SC AS 
    ( ( S JOIN SP ) WHERE P# = 'P2' ) [ S#, CITY ] REFRESH EVERY DAY; 

           ,   
, , -,         
 (   SC)  ,    ', 
-,  (     (EVERY DAY))   
, ..       , 
         . 
 ,      ,    
   24  . 
         ,   ,   
 ,        
    . ,     
      .   
        (,  
  ),      ,  
   ,  .  ,    
 1,         
         
 . 

    .        
   (.  21). 

       CREATE SNAPSHOT: 

    CREATE SNAPSHOT  AS  REFRESH EVERY _ ; 

         _  , , 
MONTH (), WEEK (), HOUR (),  MINUTES (), MONDAY 
(), WEEKDAY ( )  ..    
  

    DESTROY: 
    DESTROY SNAPSHOT  ; 

     17, 	461 

            (..   
      ). 
    .  DESTROY SNAPSHOT  ,  - 
           
.         
RESTRICTED  CASCADES.        
 . 

    17.9.  SQL 

            SQL  
 ( ,    SQL    
  - ).      
CREATE VIEW: 

    CREATE VIEW  AS _  [ WITH CHECK OPTION ] ; 

    : 
    1.   _  .  
      . 
    2.   WITH CHECK OPTION (  ) ,   
INSERT  UPDATE    ,     
,  .  ,    
   (    WITH CHECK OPTION), 
         . 
    ,      
.       
   WITH CHECK OPTION. (,   
     SQL (. ). 
,  WITH CHECK OPTION    , 
  .) 

    .  WITH CHECK OPTION   , 
      .  ,   
    [8.5],       [17.4]. 

    : 

    1. CREATE VIEW REDPARTS 
    AS SELECT P.P#, P.NAME, P.WEIGHT, P.CITY 
    FRCM  P 
    WHERE P.COLOR = 'Red' WITH CHECK OPTION ; 

    2. CREATE VIEW LREDPARTS 
    AS SELECT REDPARTS.P#, REDPARTS.WEIGHT 
    FRCM  REDPARTS 
    WHERE REDPARTS. CITY = 'London' WITH CHECK OPTION ; 

    3. CREATE VIEW CITYPAIRS 
    AS SELECT DISTINCT S.CITY AS SCITY, P.CITY AS PCITY FRCM  S, SP, P WHERE 
S.S# = SP.S# AND   SP.P# = P.P# ; 

    4. CREATE VIEW PQ 
    AS SELECT SP.P#,   SUM   (  SP.QTY )   AS TOTCTY FRCM      SP GROUP    BY 
SP.P#  ; 

    462	 V.   

            DROP 
VIEW,    , 

    DROP VIEW   ; 

       (    DROP TABLE  DROP DOMAIN)   
 RESTRICT  CASCADE.    RESTRICT   
         
  ,   DROP  .  
  CASCADE,   DROP     
        , 
     . 
       .       , 
          
  SQL ( SQL/92).  ,    
     , ,    
   SQL. 
      .  SQL   
    .    
    ,    
          
  .  ,       
.    ,    SQL  
     ,  ,  
 SQL    . 
             
SQL (    [8.5]).  SQL ,   
     ,      1-8. 
    1.   ,     
,    . ,    
    JOIN, UNION, INTERSECT  EXCEPT. 
    2.   SELECT       
   DISTINCT. 
    3.       SELECT   
   (,   AS), 
        (.  5). 
    4.   FROM        . 
    5.          ,  
 . . ,    , 
      . 
    6.        WHERE,   
 ,    FROM,     
,    FROM,    4. 
    7.       GROUP BY. 
    8.       HAVING. 

     J.    SQL    " 
 ".  ,    , INSERT, UPDATE  DELETE,  
   ,       
 . ,    SQL  , 
      DELETE    
 INSERT (      
 ). 

     17. 	463 


     2.   SQL  UPDATE     
,  .    SQL  ,  
    ,     ( 
         ). 

     ,    ,     
 SQL      (    
),     . 

    17.10.  

         .   
    ( ).   
      ,  
     ,  . 
      .   
         ( 
  ,     ).   
    ,   
    (  ,   
  ). 
          ,   
      
 .      , , 
    ( ,  )  
      .   
  ,      
 .  ,     
,     , , 
, , ,   .  ,  
   ,    
  , 6-,   . 
          .  
   :     .  
   (         
).      :  
   , ,    
,         
"". 
          .  ,  
      SQL. 
       ,    ,   
   .       
,         
     .   : 
        ,     ,   
,   . 
          , ,   
   S        SNC ( 
 S#, SNAME  CITY)  ST (  S#  STATUS)   
.      ,    
 S (  SNC  ST).   , , , 
         , 
     

    464	 V.   

    ,      1.     
 S     ,   S    . 

     

    17.1.        
 ,    17.3. 
    17.2.  ,      
    ,      . 
    17.3.  ,   ,  
  . 
    17.4.    ,      SPJ 
  SP,   SP    
  . 
    17.5.    ,      
      ,    ,  
    S1    1. 
    17.6.  ,     : 

    CREATE VIEW HEAVYWEIGHTS AS 
    ( ( ( P RENAME WEIGHT AS WT, COLOR AS COL ) ) 
    WHERE WT > 14 ) [ P#, WT, COL ] ; 

     ,     ( ) 
     : 

    )  := HEAVYWEIGHT WHERE COL = 'Green1 ; 

    )  := ( EXTEND HEAVYWEIGHTS ADD WT + 5 AS WTP ) [ P#, WTP ] ; B) UPDATE 
HEAVYWEIGHTS WHERE WT 18 COL := 'White' ; ) DELETE HEAVYWEIGHTS WHERE WT < 10 
; ) INSERT ( ( P#:'P99', WT:12, COL:'Purple1 ) ) INTO HEAVYWEIGHTS ; 

    17.7. ,     HEAVYWEIGHTS 
  : 

    CREATE VIEW HEAVYWEIGHTS AS 
    ( ( ( EXTEND P ADD WEIGHT * 454 AS WT ) RENAME COLOR AS COL ) 
    WHERE WT > 14 ) [ Pft, WT, COL ] ; 

       . 17.6. 
    17.8.   5 ,      
   (  ), ..   
CANDIDATE  PRIMARY KEY   CREATE VIEW.    
 ? 
    17.9.     (   3  4), 
   ?      ? 

    1      ,      
       ( ,  
      ,   [17.12]). 

     17. 	465 

    17.10. ,     R   
   , ,   A UNION    R,  
 A INTERSECT   .     ? 
    17.11. )  A INTERSECT    A JOIN  
( --,     ,      
 ,        ,  
).         
     ? 
    )  A INTERSECT     A MINUS (A MINUS 
)    MINUS ( MINUS ).       
       ? 
    17.12.   ,    ,   ,   
    INSERT  DELETE     .  
      ,   ? 
    17.13.    (     
)        
     S     
 SNC  ST.  ,      
.  . 
    17.14.   SQL-: )      
     ,  ; ) 
        
(,            ). 

      

    17.1. Adiba  M.E.,   Lindsay  B.G.   Database   Snapshots  //   IBM   
Research   Report RJ2772. 1980. 
        ,      
.    ,     
   " "    
          
  . 
    17.2. Buff H.W. Why Codd's Rule  6 Must Be Reformulated // ACM SIGMOD.  
1988.17, 4. 
     1985   (Codd)     ,  
  "   ,   , 
   ,    " [21.10].  
  6 ,   ,  ,  
    .      (Buff) 
,      , .. 
      (  )  
    .   ,  , 
  ,    ,  
  ,  .    ,  
        
  . 

    466	 V.   

    17.3. Chamberlin D.D., Gray J.N., Traiger I.L. Views, Authorization, and 
Locking in a Relational Data Base System // Proc. NCC 44. Calif. Montvale, 
N.J.: AFIPS Press, 1975. 
        ,   
     System R (, ,  
 SQL/DS  DB2,   SQL  ..).     
[17.13],        INGRES. 
    17.4. Darwen H. Without Check Option. Relational Database Writings 
1989-1991. Reading, Mass.: Addison-Wesley, 1992. 
    17.5. Date C.J., McGoveran D. Updating Union, Intersection, and Difference 
Views // Database Programming & Design.  1994.  7,  6. 
    17.6. Date C.J., McGoveran D. Updating Joins and Other Views // Ibid.  8. 
    17.7. Date C.J. Updating Views. Relational Database: Selected Writings. 
Reading, Mass.: Addison-Wesley, 1986. 
           . 
     ,   .  
     ,  ,    
    ,    . 
    17.8. Dayal U., Bernstein P.A. On the Correct Translation of Update 
Operations on Relational Views // ACM TODS.  1982.  7,  3. 
           
( ,   ). 
    17.9. Furtado A.L., Casanova M.A. Updating Relational Views // Query 
Processing in Database Systems.  New York, N.Y.: Springer Verlag, 1985. 
             
.    (      ) 
    ,     
   .      
  .  ,  , 
,        , 
       .   
   ,    
    .     
  .  ,      
,   1985 . 
    17.10. Goodman N. View Update Is Practical // InfoDB.  1990.  5,  2. 
          
.    ( ): " 
(Dayal)   (Bernstein) [17.8] , ,  ,  
    .  (Buff) [17.2] , 
       
. , ,   .      
  .     ". 
    17.11. Keller A.M. Algorithms for Translating View Updates to Database 
Updates for Views   Involving   Selections,   Projections,   and   Joins   //  
 Proc.   4th   ACM 

     17. 	467 

    , 
    SIGACT-SIGMOD Symposium on Principles of Database Systems.  Portland, 
Ore., 1985. 
        ,     
 :   ,   
,   ,      
  DELETE-INSERT  UPDATE.      
,    .   , 
         
  . ,  DELETE     
   UPDATE     (.. 
         
    CITY   Paris).   ( 
    (Keller)):  DELETE   V ( V 
   A MINUS )       
 ,       . ,       
           
 . 
    17.12. McGoveran  D.,  Date  C.J.  A New  Database  Design  Principle // 
Database Programming & Design.  1994.  7,  7. 
                
. ,         ,  
    /,     , PA(t)  
PB(t).      : 
     '  '           
  '  '    ,    
   . 
     "  "     
   , : )     
     ; )      
      . 
              
,       ,   
,           ,   
  : 

    A	UNICN 	  
    A	INTERSECT 	   
    A	MINUS 	  

         ,     
      ,   
       
.         
 .  ,    
         ,  
  .  ,      
         , 
  . 

    468	 V.   

    17.13. Stonebraker M.R. Implementation of Views and Integrity Constraints 
by Query Modification // Proc. ACM SIGMOD Intern. Conf. on Management of Data. 
 San Jose, Calif., 1975. 
    .   [17.3]. 

        

    17.1.      17.1.,    
   17.3. 

    17.1.1. CREATE VIEW REDPARTS AS 
    (  P.P#,   P.PNAME,   P.WEIGHT AS WT,   P.CITY  ) WHERE P.COLOR =   'Red'; 
    17.1.2. CREATE VIEW PQ AS (  SP.P#, SIM   (  SPX WHERE SPX.Ptt = SP.P#,   
OTY  )   AS TOTQTY  )   ; 

    ,        SP.P#  
.#,     ,     
  . 

    17.1.3. CREATE VIEW CITY_PAIRS AS 
    ( S.CITY AS SCITY, P.CITY AS PCITY ) WHERE EXISTS ( SP WHERE SP.Stt = S.S# 
    AND SP.P# = P.P# ) ; 

    17.1.4. CREATE VIEW LONDON_REDPARTS AS 
    ( REDPARTS WHERE REDPARTS.CITY = 'London' ) ; 

    .   . 17.2-17.5     
 . 

    17.2. CREATE VIEW NON_COLCCATED AS 
    ( S.S#, P.P# ) WHERE S.CITY  P.CITY ; 
    17.3. CREATE VIEW LONDON_SUPPLIERS AS 
    ( S.S#, S.SNAME, S.STATUS ) WHERE S.CITY = 'London' ; 

        CITY,   ,   
    London.   ,  :  
  ,    INSERT    
   (    S.CITY    
 ,  London).  ,     
   INSERT. (     
      CITY  ,  
  .  , ,   London. 
   ,   ,   
 . 
    17.4.    .    SP.QTY? 
   ,     S#-P#  SP.QTY  
     SPJ.QTY,     
 J#    S#-P#. 

    CREATE VIEW SP AS 
    ( SPJX.Sft, SPJX.P#, 

     17. 	469 

    SUM ( SPJY WHERE SPJY.S# = SPJX.Stf 
    AND  SPJY.P# = SPJX.Ptf, QTY ) AS QTY ) ; 

    17.5. CREATE VIEW JC AS 
    ( J.J#, J.CITY ) WHERE EXISTS SPJ ( SPJ.S# = 'SI' AND 
    SPJ.P* = 'PI' AND SPJ.J# = J.J# ) ; 

    17.6.      . 
  , ,   ()  ,    
    . 
    17.7.    ()   ,     
 . -,       
""   WEIGHT (, ,    ),  
        ,   
 w. -,   WT,   ,  
   w*454,     WT  14. 
,       . 
    17.8.      [4.15]. 
            , 
 ,          
 .        , 
   ,       
   .      
      ,   
        
  .     
     . 
             (  
    ).  ,   
     (   ) 
        . 
             , 
 ,   ,    ,  
       ,   
  . 
           , 
    , , ,  ,  
.  [4.15]    . 
     [10.3]   , ,  ,   , 
   (   )  
       ,   
     . 
    17.9. ,        . 
   . 
               
RELATIONS   ("View"  "Snapshot")   RELTYPE. 

    470	 V.   

             ,  
     .    VIEWS.  
     ,   . 
     ,         
(SNAPSHOTS),      .    
  ,   ,     
 . 
               
  . ,      
  PART_STRUCTURE (. . 4.4   4)     
  ,         
   . 
    17.10. !   . ,   S    
   SA  SB,  SA      
, a SB    ,   .  
  SA  SB      S.  
        
  -        
,     ,     UPDATE 
    DELETE       INSERT  
.          , 
   UPDATE    DELETE-INSERT.   ,  
    ,     
      UPDATE.   
,    UPDATE     
UPDATE          . 
,  ,     ,  
   . 
    17.11. ) ; ) . 
    17.12.      INSERT  DELETE  
 ,        . 
,        ,    t  
 V = A INTERSECT       /   
  (      ).   ,  
   ,      
 [17.12]. 
    17.13.    .  ,   
     . (  
   .) 

    CREATE BASE RELATION SNC 
    ( S#   DOMAIN ( S# ) , SNAME DOMAIN ( NAME ), CITY DOMAIN ( CITY ) ) 
PRIMARY KEY ( S# ) ; CREATE BASE RELATION ST 
    ( S#    DOMAIN ( S# ), STATUS DOMAIN ( STATUS ) ) 

     17. 	471 

    PRIMARY KEY ( S# ) ; 
    INSERT S [ S#, SNAME, CITY ] INTO SNC ; INSERT S [ S#, STATUS ] INTO ST ; 
DESTROY BASE RELATION S ; 

        : 

    CREATE VIEW S AS SNC JOIN ST ; 

    ,     SNC.S#  ST.S#    
 ,    . 
     ,   -     
SP.S#   SP.        S.  
 ,          
 S.     (      
 ),          
  S.    : 

    CREATE BASE RELATION SS 
    ( S# DOMAIN ( S# ) ) 
    PRIMARY KEY ( S# ) ; INSERT S [ S# ] INTO SS ; 

    (  [12.9]       
 .)    S   

    CREATE VIEW S AS SS JOIN SNC JOIN ST ; 

     ,    SNC  ST   
 : 

    FOREIGN KEY ( S# } REFERENCES SS 
    DELETE CASCADES UPDATE CASCADES 

     ,      SP.S#   
SP,        S,    SS. 

    . ,       
  ,    . 

    17.14.       ,  
     ,   
  . 

    CREATE VIEW PQ AS 
    SELECT SP.Ptf,   SUM   (SP.QTY  )   AS TOTQTY 
    FROM      SP 
    GROUP    BY SP.P#  ; 

       : 

    SELECT AVG ( PQ.TOTOTY ) AS PT FROM  PQ ; 

            (..  
      ,  
 ),   

    SELECT AVG ( SUM ( SP.QTY ) ) AS PT 

    472	 V.   

    FRCM      SP 
    GROUP    BY SP.P#  ; 

      SELECT-,   SQL    
    . 
             PQ,  
   (    ). 

    SELECT	PQ.P# 
    FROM	PQ 
    WHERE	PQ.TOTOTY > 500 ; 

      ,   ,  
  ( , IBM DB2)    
(       )   
      .  , 
,    ,   . 
( ,    DB2       
  ,   .) 

     17. 	473 

