数据库第七周实验——数据库完整性+存储过程——例题实现

数据库的完整性

  数据的正确性:数据是符合现实世界语义，反映了当前实际状况的
  数据的相容性:同一对象在不同关系表中的数据是符合逻辑的

【例如】
(1)学生的学号必须唯一
(2)性别只能是男或女
(3)本科学生年龄的取值范围为14~50的整数
(4)学生所选的课程必须是学校开设的课程，学生所在的院系必须是学校已成立的院系

数据的“完整性”和“安全性”是两个不同概念

  数据的完整性:防止数据库中存在不符合语义的数据，也就是防止数据库中存在不正确的数据
    防范对象：不合语义的、不正确的数据

  数据的安全性:保护数据库 防止恶意的破坏和非法的存取
    防范对象：非法用户和非法操作

5.1实体完整性

5.1.1定义实体完整性
  关系模型的实体完整性在CREATE TABLE中用PRIMARY KEY定义

  对单属性构成的码有两种说明方法：
  （1）定义为列级约束条件
  （2）定义为表级约束条件

  对多个属性构成的码只有一种说明方法：定义为表级约束条件

【例5.1】 将Student表中的Sno属性定义为码

CREATE TABLE Student
	(Sno  CHAR(9)  PRIMARY KEY,    --在列级定义主码          
	 Sname  CHAR(20) NOT NULL,     
	 Ssex  CHAR(2),
	 Sage  SMALLINT,
	 Sdept  CHAR(20)
 	);
或者
CREATE TABLE Student
	(Sno  CHAR(9),  
 	 Sname  CHAR(20) NOT NULL,
 	 Ssex  CHAR(2),
 	 Sage  SMALLINT,
	 Sdept  CHAR(20),
	 PRIMARY KEY (Sno)     --在表级定义主码
 	 ); 

【例5.2】将SC表中的Sno，Cno属性组定义为码

CREATE TABLE SC
           (  Sno   CHAR(9)  NOT NULL, 
              Cno  CHAR(4)  NOT NULL,  
              Grade    SMALLINT,
              PRIMARY KEY (Sno,Cno)    /*只能在表级定义主码*/
           ); 

5.1.2 实体完整性检查和违约处理

  用PRIMARYKEY短语定义了关系的主码后，每当用户程序对基本表插入一条记录或对主码列进行更新操作时，DBMS按照实体完整性规则自动进行检查:
  （1）检查主码值是否唯一，如果不唯一则拒绝插入或修改
  （2）检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改

5.2 参照完整性

5.2.1 定义参照完整性
  关系模型的参照完整性
    (1)在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外码
    (2)用REFERENCES短语指明这些外码参照哪些表的主码

【例5.3】定义SC中的参照完整性

关系SC中一个元组表示一个学生选修的某门课程的成绩，（Sno，Cno）是主码。Sno，Cno分别参照引用Student表的主码和Course表的主码

CREATE TABLE SC
         (  Sno    CHAR(9)  NOT NULL, 
            Cno     CHAR(4)  NOT NULL,  
            Grade    SMALLINT,
            PRIMARY KEY (Sno, Cno),   /*在表级定义实体完整性*/
            FOREIGN KEY (Sno) REFERENCES Student(Sno),   /*在表级定义参照完整性*/
            FOREIGN KEY (Cno) REFERENCES Course(Cno)        /*在表级定义参照完整性*/
          );

5.2.2 参照完整性检查和违约处理

对被参照表与参照表进行增、删、改操作时有可能破坏参照完整性，必须进行检查以保证两个表的相容性。

例如：对表SC和Student有4种可能破坏参照完整性的情况：

(1)SC表中增加一个元组，该元组的Sno属性值在表Student中找不到一个元组，其Sno属性值与之相等
（2）修改SC表中的一个元组，修改后该元组的Sno属性值在表Student中找不到一个元组，，其Sno属性值与之相等
（3）从Student表中删除一个元组，造成SC表中某些元组的Sno属性值在表Student中找不到一个元组，，其Sno属性值与之相等
（4）修改Student表中一个元组的Sno属性，造成SC表中某些元些元组的Sno属性值在表Student中找不到一个元组，，其Sno属性值与之相等

当上述的不一致发生时，系统可以采用以下策略加以处理

（1）拒绝（NO ACTION）执行
    不允许该操作执行。该策略一般设置为默认策略
（2）级联（CASCADE）操作
    当删除或修改被参照表（Student）的一个元组导致与参照表（SC）的不一致时，删除或修改参照表中的所有导致不一致的元组。
（3）设置为空值
    当删除或修改被参照表的一个元组时造成了不一致，则将参照表中的所有造成不一致的元组的对应属性设置为空值。

例如，有下面2个关系
     学生（学号，姓名，性别，专业号，年龄）
     专业（专业号，专业名）
  （1）假设专业表中某个元组被删除，专业号为12
  （2）按照设置为空值的策略，就要把学生表中专业号=12的所有元组的专业号设置为空值
  （3）对应语义：某个专业删除了，该专业的所有学生专业未定，等待重新分配专业

外码能否接受空值:见书162页

对于参照完整性，除了应该定义外码，还应该定义外码列是否允许空值

一般地，当对参照表和被参照表的操作违反了参照完整性时，系统选用默认策略，即拒绝执行。如果想让系统采用其他策略则必须在创建参照表时显式地加以说明。

【例5.4】显式说明参照完整性的违约处理示例

CREATE TABLE SC
        (  Sno   CHAR(9)  NOT NULL，
           Cno   CHAR(4)  NOT NULL，
           Grade  SMALLINT,
           PRIMARY KEY(Sno,Cno), 			              /*在表级定义实体完整性，Sno、Cno都不能取空值*/
           FOREIGN KEY (Sno) REFERENCES Student(Sno)      /*在表级定义参照完整性*/              
		   		ON DELETE CASCADE                          /*当删除Student表中的元组时，级联删除SC表中相应的元组*/
           		ON UPDATE CASCADE,                         /*当更新Student表中的sno时，级联更新SC表中相应的元组*/
           FOREIGN KEY (Cno) REFERENCES Course(Cno)	      /*在表级定义参照完整性*/              
            	ON DELETE NO ACTION 	                   /*当删除course 表中的元组造成了与SC表不一致时拒绝删除*/
            	ON UPDATE CASCADE                          /*当更新course表中的cno时，级联更新SC表中相应的元组*/
           );

5.3 用户定义的完整性

用户定义的完整性是：针对某一具体应用的数据必须满足的语义要求

5.3.1属性上的约束条件
  在CREATE TABLE中定义属性的同时，可以根据应用要求定义属性上的约束条件，即属性值限制，包括：
    （1）列值非空（NOT NULL）
    （2）列值唯一（UNIQUE）
    （3）检查列值是否满足一个条件表达式（CHECK短语）

1.不允许取空值

【例5.5】在定义SC表时，说明Sno、Cno、Grade属性不允许取空值。

CREATE TABLE SC
        (  Sno CHAR(9)  NOT NULL,	       --Sno属性不允许取空值
           Cno CHAR(4)  NOT NULL,	       --Cno属性不允许取空值
           Grade  SMALLINT NOT NULL,	   --Grade属性不允许取空值
           PRIMARY KEY (Sno, Cno),         --在表级定义实体完整性，隐含Sno，Cno不允许取空值，在列级不允许取空值的定义可以不写 
           ...
         ); 

2.列值唯一

【例5.6】建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码

  CREATE TABLE DEPT
        (   Deptno  NUMERIC(2),
            Dname  CHAR(9)  UNIQUE NOT NULL,     /*要求Dname列值唯一, 并且不能取空值*/
            Location  CHAR(10),
            PRIMARY KEY (Deptno)
         );

3.用CHECK短语指定列值应该满足的条件

【例5.7】Student表的Ssex只允许取“男”或“女”。

CREATE TABLE Student
          ( Sno  CHAR(9) PRIMARY KEY,                          --在列级定义主码
            Sname CHAR(8) NOT NULL,                            --Sname属性不允许取空值
            Ssex  CHAR(2)  CHECK （Ssex IN （'男','女'））,     --性别属性Ssex只允许取'男'或'女' */
            Sage  SMALLINT,
            Sdept  CHAR(20)
          )