数据库——约束、断言、触发器

断言 (整个关系或关系间的约束)。外键是断言。
触发器(编程者指定触发时刻 一组命令)

一、键
1、键约束
1）键约束(主键约束)：用 PRIMARY KEY 表示 (唯一 非空)
2）单值约束(可用于标识候选键)：用 UNIQUE 表示 (唯一 可空)——primary key = unique + not null
主键——两种方式：

CREATE  TABLE  MovieStars(
                 name  CHAR(30)  [  PRIMARY   KEY ],
                 address  VARCHAR(255),     [UNIQUE]  
或     PRIMARY   KEY (name)           
     );

2、外键声明——两种方式
1）单一属性作外键： 在属性定义后加 REFERENCES <表名> (<属性名>)
2）单独说明一个或多个属性为外键：FOREIGN KEY (<属性名列表>) REFERENCE<表名> (<属性名列表>)
有外键时，表中对外键做了修改，相关约束的地方也要修改。有如下三种方式：

(a) 缺省原则——拒绝非法插入、删除和修改
(b) 级联原则——同时对子表进行相同操作（删除、更新）
(c) 置空值原则——删除、更新主记录，同时将子记录外键改为NULL

方法：在ON DELETE或ON UPDATE后面加上SET NULL（置空）或CASCADE（级联）选项。
例：

CREATE TABLE Studio(                   
    name CHAR(30) PRIMARY KEY，           
    address VARCHAR(255)，               
    presC# INT REFERENCES MovieExec(cert#)
                    ON DELETE SET NULL                   
                        ON UPDATE CASCADE
  )；

级联：利于修改。置空：利于删除。

3、延迟约束检查
两个外键相互约束（循环约束），要推迟约束检查。
1）NOT DEFERRABLE (缺省值)：立即检查该约束。
2）DEFERRABLE：延迟检查。也有两个选项：

INITIALLY DEFERRED：检查被推迟到事务提交前执行
INITIALLY IMMEDIATE：检查在每个语句后立即被执行

例：

CREATE TABLE Studio (             
           name CHAR(30)PRIMARY KEY，            
           presC# INT UNIQUE   REFERENCES MovieExec(cert#)
                               DEFERRABLE INITIALLY DEFERRED 
        )；

延迟检查可以修改：修改名为 MyConstraint的外键约束检查，改为立即检查

SET CONSTRAINT MyConstraint IMMEDIATE ;

二、属性和元组上的约束
1. 非空值约束——置空原则此处不适用

CREATE TABLE Studio (
        name CHAR(30) PRIMARY KEY，
        address VARCHAR(255)，
        presC# INT REFERENCES MovieExec(cert#) NOT NULL
)；

2、基于属性的CHECK约束
建表时，某个属性定义后，CHECK(<条件>)。<条件>是该属性的每个值都应满足的。条件可以是子查询。
例：约束证书号必须至少有6位数字，约束gender属性的值只能取‘F’和‘M’

CREATE TABLE Studio (
        name CHAR(30) PRIMARY KEY，
        presC# INT REFERENCES MovieExec(cert#) 
                              CHECK(presC#>=100000)，
               gender  CHAR(1) CHECK (gender IN (‘F’,’M’)),
)；

CHECK约束是在元组修改/插入时被检查。在修改的情况下，是对新值而非旧值进行约束检查。如果新值违反约束，则该修改被拒绝。

3、基于元组的CHECK约束
涉及整个元组而不仅针对某一个属性，每次向R插入元组以及当R的元组被修改时，都要检查基于元组的CHECK约束条件。违规的插入或修改语句都将被拒绝。
例：约束男影星姓名前不能加 ‘Ms.’

CREATE  TABLE  MovieStars(
         name  CHAR(30)   PRIMARY KEY,
         gender  CHAR(1)   CHECK (gender IN (‘F’,’M’) ) ,
         CHECK (gender=‘F’ OR name NOT LIKE ‘Ms.%’)
)；

基于元组的约束将比基于属性的约束更频繁地被检查：只要该元组的任一个属性被改变时都要检查。

三、修改约束
1、为了修改或删除一个已存在的约束，约束必须有名字

CREATE  TABLE  MovieStars(
        name  CHAR(30)  CONSTRAINT NameIsKey PRIMARY KEY,
        gender  CHAR(1) CONSTRAINT NoAndro  CHECK (gender IN (‘F’,’M’)),
        CONSTRAINT RightTitle  
                                CHECK(GENDER=‘F’ OR name NOT LIKE ‘Ms.%’)
);

2、修改表上的约束
1）修改延迟约束检查:SET CONSTRAINT MyConstraint DEFERRED / IMMEDIATE
2）删除约束:ALTER TABLE MovieStar DROP CONSTRAINT NamelsKey；

3、添加约束

ALTER TABLE MovieStar ADD CONSTRAINT NamelsKey  PRIMARY KEY(name);
ALTER TABLE MovieStar ADD CONSTRAINT NoAndro   CHECK(gender IU('F','M'));
ALTER TABLE MovieStar ADD CONSTRAINT RightTitle  CHECK(gender='F'  OR name NOT LIKE 'Ms.%'));

这些约束都是基于元组而不是基于属性的检查，不能将其恢复到基于属性的约束。

四、断言
1、创建断言
断言的形式：CREATE ASSERTlON <断言名> CHECK(<条件>)
断言建立时，断言的条件必须是真，且要永远保持是真。任何引起断言条件为假的数据库更新都被拒绝。约束的检查可以一直延期到事务提交前。如果对断言也这样做，到事务结束前它可能暂时变成假值。已经介绍过的其他类型CHECK约束，如果涉及子查询，可以在某些条件下避免操作被拒绝。

2、使用断言
基于元组的CHECK约束和断言约束在书写方式上有差别。基于元组的检查能直接引用在它声明中出现的关系的属性。断言没有如此特权。断言条件中引用的任何属性都必须要介绍，特别是要提及在select-from-where表达式中的关系。

assert用在那些你知道绝对不会发生的事情上，但是因为人总是会犯错误，保不准你写出来的东西跟你想的不一样。所以assert用来捕捉的是程序员自己的错误。
同理，exception捕捉的是用户或者环境的错误。

基于元组的约束与断言的区别：
例：希望其净资产值少于S10 000 000的人不能成为电影公司经理。即声明经理净资产值少于$10000000的电影公司集合是空。
1）基于元组：

CREATE  TABLE Studios(
     name CHAR(30) PRIMARY KEY,
     presC#   INT REFERENCES MovieExec(cert#),
     CHECK ( presC# NOT IN (SELECT cert#  FROM  MovieExecs WHERE netWorth<10000000)）
);

2）断言：

CREATE ASSERTION RichPres CHECK            
    (NOT EXISTS                           
         ( SELECT Studio.name  FROM Studio,MovieExec  WHERE presC#=cert# AND netWorth<10000000 )）
)；

例：声明对一个给定电影公司，其所有电影的总长度不能超过10000分钟。
1）断言：

CREATE ASSERTION SumLength CHECK(10000 >= ALL (SELECT SUM(length) FROM Movies GROUP BY studioName) )；

2）元组：在创建表时加入。

CHECK(10000 >= ALL (SELECT SUM(1ength) FROM Movies GROUP DY studioName));

CHECK 约束与断言的区别

约束类型        声明的位置             动作的时间               确保成立?
基于属性的CHECK   属性            插入元组或属性修改时    如果是子查询，则不能确保
基于元组的CHECK   关系模式-元素     插入元组或属性修改时    如果是子查询，则不能确保
断言             数据库模式-元素   对任何涉及的关系做改变时     是

3、删除断言
DROP ASSERTION <断言名>

五、触发器
满足条件时触发。
1、触发器主要特征

1）触发器的条件检查和动作可在触发事件执行前的数据库状态或触发动作被执行后的状态上执行。
2）条件和动作可引用元组的旧值／触发事件中更新的元组的新值。
3）更新事件可被局限到某个特定的属性或某些属性。
4）程序员可选择动作执行的方式：
    a)一次只对一个更新元组(行级触发器)
    b)一次针对在数据库操作中被改变的所有元组 (语句级触发器)

例：

CREATE TRIGGER NetWorthTrigger  //触发声明
  AFTER UPDATE OF netWorth ON MovieExec  //告诉触发器在触发事件之前还是之后使用数          据库状态
  REFERENCING            　　         //允许触发器的条件和动作引用正被修改的元组。
            OLD ROW AS OldTuple，                //本例该子句允许给在改变之前和之后的元组命名
            NEW ROW AS NewTuDle
  FOR EACH ROW                       //触发器是每修改一个元组执行一次的方式——行级触发器
  WHEN (OldTuple.netWorth>NewTuple.netWorth)              //触发条件
            UPDATE MovieExec                                                 ——动作部分
            SET netWorth=OldTuple.netWorth
           WHERE cert#=NewTuple.cert#；

2、触发器语句
1）AFTER：条件测试和动作将在触发事件之后的数据库状态上被执行。

2）BEFORE：WHEN条件将在触发事件执行之前的数据库状态上测试。
如果条件是真，则在该状态上执行触发器的动作
最后执行唤醒触发器的事件，不管条件是否仍为真

3）INSTEAD OF：它与视图的修改有关。

4）UPDATE
OF <属性(组)> 短语是UPDATE事件的可选项
若给出该选项，那么它定义的事件仅仅是OF保留字后列出的属性(组)的修改。

5）INSERT

6）DELETE
OF短语在INSERT或DELETE事件中不可使用，因为这两个事件都是作用在整个元组上。

7）WHEN短语是可选项
如果该短语缺省，则只要触发器被唤醒，都要执行动作。
若有该短语，则仅当WHEN后的条件为真时执行动作。

8）触发器的动作部分可以由单个或任意多个SQL语句组成。这些语句需由BEGIN…END括起，并且语句之间用分号分隔。

9）FOR EACH ROW：行级触发器
行级触发器触发事件——修改

旧元组：修改之前的元组，用OLD ROW AS短语命名
新元组：修改之后的元组，用NEW ROW AS短语命名

行级触发器触发事件——插入

旧元组： OLD ROW AS不可使用
新元组：使用NEW ROW AS短语命名被插入的元组

行级触发器触发事件——删除

旧元组： OLD ROW AS被用于命名被删除的元组
新元组： NEW ROW AS不可使用

10）FOR EACH STATEMENT：语句级触发器[默认]
一旦有合适类型的语句被执行，语句级触发器就被执行，而不问它实际上会影响多少元组(0个、1个或多个)。
例如：如果用SQL更新语句更新整个表，语句级的修改触发器将只执行一次，元组级触发器将对要修改的元组一次一个地执行。

11）语句级触发器中不能直接引用旧的和新的元组
任何触发器(元组级或语句级)都可引用旧元组 (删除的元组或更新的元组的旧版本) 的关系和新元组(插入元组或更新元组的新版本) 的关系
声明方式：

OLD TABLE AS OldStuff
NEW TABLE AS NewSuff

关于OldStuff和NewSuff的说明：
语句级触发器触发事件是修改：NewStuff和OldStuff中分别是被更新元组的新版本和旧版本
语句级触发器触发事件是删除：删除元组是OldStuff，不能声明NewStuff
语句级触发器触发事件是插入：插入的元组是NewStuff，不能声明OldStuff

例：要阻止电影制作人的平均净资产值降到$500000（修改期间平均净资产值可暂低于$500 000 ;当所有变更结束时，其净资产值必须超过$500 000。约束要做的工作：若语句执行结束后，净资产值仍低于$500 000，则整组更新操作被拒绝。）

  1)   CREATE TRIGGER AvgNetWorthTrigger                    
  2)   AFTER UPDATE OF netWorth ON MovieExec
  3)   REFERENCING
  4)          OLD TABLE AS OldStuff，
  5)          NEW TABLE AS NewStuff
  6)   FOR EACH STATEMENT
  7)   WHEN(500000>(SELECT AVG(netWorth) FROM MovieExec))
  8)   BEGIN
  9)          DELETE FROM MovieExec
10)          WHERE (name, address, cert#, netWorth) IN NewStuff ;
11)          INSERT INTO MovieExec
12)                                  (SELECT * FROM OldStuff) ;
13)   END;

例：假设对关系Movies插入电影元组，但有时候不知道该电影的年份。由于year是主键的一部分，该属性不能为NULL。下面的例子一个用1915替代NULL的触发器。

1)  CREATE TRIGGER FixYearTrigger
2)  BEFORE INSERT ON Movies                              
3)  REFERENCING                                          
4)            NEW ROW AS NewRow                                  
5)            NEW TABLE AS NewStuff                              
6)  FOR EACH ROW                                         
7)  WHEN NewRow.year IS NULL                            
8)  UPDATE NewStuff SET year=1915;

3、替换触发器——与视图一起用。
例如：对paramount电影公司所拥有的所有电影的视图定义如下：该视图可以更新，但当插入元组时，系统系统不能保证sutdioname属性值一定是Paramount，插入后在moive中该元组的sutdioname属性值是NULL。

1)CREATE VIEW  ParamountMoive AS
2)     SELECT title，year
3)      FROM  Moive
4)     WHERE studioName=‘Para,mount’;

创建替换触发器：

1）CREATE TRIGGER ParamountInsert
2)  INSTEAD OF INSERT ON ParamountMovies                              
3)  REFERENCING                                          
4)            NEW ROW AS NewRow                                  
5)  FOR EACH ROW                                         
6)  INSERT INTO Moive(title,year,studioName)                            
7)  VALUES(NewRow.title,NewRow.year,’Paramount’);

---

视图与索引
一、虚拟视图
1、表和视图的区别：
表(基本关系/基本表)

以物理组织的方式实际存储在数据库中
表是持久的，除非删除、更新操作。

虚拟视图

不以物理的形式存在
通过类似查询的表达方式定义
可以将视图当作物理存在进行查询
在某些情况下，视图也可以更新

2、视图定义
CREATE VIEW <视图名> AS <视图定义>；视图定义是一个SQL查询
例如：

1)  CREATE VIEW ParamountMovies AS
2)         SELECT title, year
3)         FROM Movies 
4)         WHERE studioName=‘Paramount’；

3、视图查询
与表查询一样，区别是把表名换成视图名。

4、属性重命名

CREATE VIEW MovieProd (movieTitle, prodName) AS  
        SELECT title, name  FROM Movies, MovieExec
    WHERE producerC#=cert# ;

视图的列头由原来的tltle和name变为movieTitle和prodName

二、视图更新
多数视图是不可更新的。
对于一些充分简单的视图(可更新视图)，可以把对视图的更新转变成一个等价的对基本表的更新，更新操作最终作用在基本表上。
此外，“替换”触发器可以将视图上的更新转变为基本表上的更新。

1、视图删除：
1）DROP VIEW ParamountMovies ; ——这条语句删除了视图的定义，因此不再能对该视图进行查询、修改。但是删除视图并不影响表Movies中的任何元组。
2）DROP TABLE Movies ; ——使得表Movies从此消失，也使得视图ParamountMovies不可用，因为使用该视图的查询会间接地引用一个不存在的关系Movies。

2、更新视图
1）若一个视图是从单个的基表导出的，并且只是去掉了基表的某些行和列，且保留了基表的主码，这样的视图被称为行列子集视图。
2）SQL92中对可更新视图的要求——略
3）要判定其可更新——三个要点：
WHER子句在子查询中不能使用关系R。
FROM语句只能包含一个关系R
SELECT语句中的属性列表必须包括足够多的属性，以保证对该视图进行元组插入时，能够用NULL或者适当的默认值来填充所有其他不属于该视图的属性。

(1) 对可更新视图插入元组——像插基本表一样
INSERT INTO ParamountMovies VALUES (‘Paramouat’,‘Star Trek’, 1979);
相当于对基本表：INSERT INTO Movies (studioName，title, year) VALUES (‘Paramouat’,‘Star Trek’, 1979) ;（其他值为空要指明属性）
(2) 从可更新视图删除元组
 DELETE FROM ParamountMovies    WHERE title LIKE ‘%Trek%’ ;
等价于 DELETE FROM Movies  WHERE title LIKE '%Trek%' AND studioName = ‘paramount’ ;（要把视图的WHERE语句中的条件(用AND)添加到删除操作的WHERE子句中。）
(3) 对可更新视图的修改——类似删除，也要用AND连接。略。、

3 视图中的替换触发器
当视图上定义了一个触发器时，可以用INSTEAD OF代替BEFORE或AFTER。

三、索引
索引是一种数据结构，类似目录。提高查询效率。
1、创建索引
CREATE INDEX <索引名> ON <关系名>(<属性列表>);
例：

CREATE INDEX Keylndex ON Movies (title, year);——多重索引中的属性排列顺序决定查询依赖顺序

2、删除索引
DROP INDEX <索引名> ;例：DROP INDEX Keylndex ;

3、索引的优缺点：
(1)优： 提高查询效率。
(2)缺：某个属性/属性集建立的索引会使得对关系的插入、删除和修改变得更复杂和更费时。

4、设计索引的两个重要因素
1）简单代价模型
假设关系的元组被正常地分配在磁盘的多个页面上。每个磁盘页可以存储多个元组。为了检查哪怕只是一个元组，需要将包含它的整个磁盘页从硬盘调入到主存中。另一方面，检查一个磁盘页上所有元组所花费的时间通常和检查一个元组所花费的时间几乎没有什么差别。

2）一些有用的索引
通常关系上最有用的索引是其键上的索引

  ● 在查询中为主键指定值比较普遍。因此，键上的索引使用频繁。
  ● 因为键值唯一，故与给定键值匹配的元组最多只有一个，因此索引返回的要么是这个元组的位置，要么什么也不返回。即为了取得这个元组，最多只有一个磁盘页需要被读入到主存(尽管有时为了使用索引本身需要读入存储索引的其他磁盘页)。

非键上索引在查询时可能不会加速元组的检索速度。但在两种情况下仍然有效：

  ● 1．相对来说基本上没有多少元组在该属性上具有给定值。所以，即使每个具有给定值的元组分别位于不同的磁盘页上，也不需要检索大量的磁盘页。
  ● 2．元组在该属性上是“聚集”的，即通过将具有该属性上公共值的元组分组到尽可能少的磁盘页里来将一个关系聚合到一个属性上。即使符合要求的元组很多，却不必检索与符合要求的元组数目相同的磁盘页。

3）计算最佳索引
关系和索引都被存储在磁盘页上，查询或更新的主要代价通常来自于将所需磁盘页读入到主存的数目。

查询：根据索引找到所需元组，将元组所在的若干个磁盘页读入主存。
更新：更新操作需要一次磁盘访问以读取磁盘页，而另一次磁盘访问用于将修改后的页写回磁盘，所以它的开销是查询中访问索引或数据的两倍。
注意：如果更新是最频繁发生的操作，则对于索引的创建应该采取非常保守的策略。

计算：略。