数据库系统概论第5章数据库完整性.ppt

1、1,第5章 数据库完整性,5.1完整性约束条件 5.2完整性控制,2,数据库的完整性,正确性 相容性,数据库完整性的实现: (1)定义完整性约束条件 (2)完整性检查 (3)违约反应,10.1完整性约束条件,完整性约束条件作用的对象:关系 元组 列,列约束:是指列的类型、取值范围、精度、排序等约束. 元组约束:是指元组中各个字段间的联系约束. 关系约束:是指若干元组间、关系集合上以及关系之间的联系约束.,3,完整性约束条件作用对象的状态: 静态 动态,静态约束:是指数据库每一确定状态时的数据对象所应满 足的约束条件,它是反映数据库状态合理性的约束.,动态约束:是指数据库从一种状态转变为另一种状

2、态时,新、 旧值之间所满足的约束条件,它是反映数据库状 态变迁的约束.,完整性约束条件分类:,1:静态列级约束 2:静态元组约束 3:静态关系约束 4:动态列级约束 5:动态元组约束 6:动态关系约束,4,一、静态列级约束,静态列级约束：是指对一个列的取值域的说明。,包括以下几个方面： (1)对数据类型的约束(数据的类型、长度、单位、精度等) (2)对数据格式的约束 (3)对取值范围或取值集合的约束 (4)对空值的约束 (5)其他约束,二、动态列级约束,动态列级约束:是指修改列定义或列值时应满足的约束条件.,包括以下几个方面： (1)修改列定义时的约束 (2)修改列值时的约束,5,三、静态元组

3、约束,静态元组约束:是指元组的各个列之间的约束关系.,四、动态元组级约束,动态元组级约束:是指修改元组的值时元组中各个列之间 的约束条件.,五、静态关系约束,静态关系约束:是指一个关系的各个元组之间或者若干关系 之间存在的约束条件.,包括以下几个方面： (1)实体完整性约束 (2)参照完整性约束 (3)函数依赖约束 (4)统计约束,6,六、动态关系约束,动态关系约束:是指加在关系变化前后状态上的约束条件.,总结:,7,10.2完整性控制,完整性控制机制的功能： (1)定义功能 (2)检查功能 (3)如果发现用户的操作请求使数据违背了完整性约束 条件,则采取一定的动作来保证数据的完整性.,完整性

4、规则的表示 :,五元组 (D, O, A, C, P),D(Data):指约束作用的数据对象 O(Operation):触发完整性检查的数据库操作 A(Assertion):数据对象必须满足的断言或语义约束 C(Condition):选择A作用的数据对象值的谓词 P(Procedure):违反完整性规则时触发的过程,立即执行检查 延迟执行检查,8,例子1:对表Student的“学号不能为空”的约束规定.,五元组 (D, O, A, C, P),D:约束作用的对象为Sno属性 O:插入或修改Student元组时 A:Sno不能为空 C:无(A作用于所有记录的Sno属性) P:拒绝执行该操作,例子

5、2:对教师基本情况表的“教授工资不得低于1000元”的约束规定.,五元组 (D, O, A, C, P),D:约束作用的对象为工资Sal属性 O:插入或修改职工元组时 A:Sal不能小于1000 C:职称=教授 P:拒绝执行该操作,9,实现参照完整性要考虑的几个问题:,1.外码能否接受空值的问题,学生(学号,姓名,性别,专业号,年龄) 专业(专业号,专业名),外码,例子1:,外码取空值表示该学生尚未分配到任何具体的专业学习.,与应用环境的语义相符,因此外码可取空值.,例子2:,学生(学号,姓名,性别,专业号,年龄) 课程(课程号,课程名,学分) 选修(学号,课程号,成绩),外码(学号)取空值表

6、示一个不存在的学生选了某门课程,成绩记录在成绩列中.,与应用环境的语义不相符,因此外码不能取空值.,10,2.在被参照关系中删除元组的问题,例子:学生(学号,姓名,性别,专业号,年龄) 选修(学号,课程号,成绩),被参照关系,三种策略: (1)级联删除 (2)受限删除 (3)置空值删除,在学生关系中删除元组(95001,李勇,男,CS,20),11,例子： A（A#，AN） S（S#，SN，A#，SA）,在A关系中删除元组(001,物理)？,三种策略: (1)级联删除 (2)受限删除 (3)置空值删除,12,3.在参照关系中插入元组时的问题,例子:学生(学号,姓名,性别,专业号,年龄) 选修(

7、学号,课程号,成绩),参照关系,两种策略: (1)受限插入 (2)递归插入,在选修关系中插入元组(99001,1,90),13,例子： S（S#，SN，A#，SA） A（A#，AN）,在S关系中插入元组(95001,李勇,031,20)？,两种策略: (1)受限插入 (2)递归插入,14,4.修改关系中的主码,不允许修改主码 允许修改主码,(1)修改被参照关系的主码,例子:学生(学号,姓名,性别,专业号,年龄) 选修(学号,课程号,成绩),被参照关系,在学生关系中修改元组(95001,李勇,男,CS,20)的学号为960123,三种策略: (1)级联修改 (2)受限修改 (3)置空值修改,15

8、,例子： S（S#，SN，A#，SA） A（A#，AN）,在A关系中修改元组(001,物理)中的主码001为005？,三种策略: (1)级联修改 (2)受限修改 (3)置空值修改,16,(2)修改参照关系的外码,例子:学生(学号,姓名,性别,专业号,年龄) 选修(学号,课程号,成绩),参照关系,在选修关系中元组(95001, 1, 90)修改为(99001,1,90),两种策略: (1)受限修改 (2)递归修改,17,例子： S（S#，SN，A#，SA） A（A#，AN）,在S关系中修改元组(95001,李勇,001,20)的A#改为030？,两种策略: (1)受限修改 (2)递归修改,18,

9、10.3 SQL Server的完整性,一、Oracle中的实体完整性,例1：在学生选课数据库中，要定义Student表的Sno属性为主码 CREATE TABLE Student (Sno NUMBER(8), Sname VARCHAR(20), Sage NUMBER(20), CONSTRAINT PK_SNO PRIMARY KEY (Sno); 注意: (PK_SNO是此主码约束名 ) 或: CREATE TABLE Student (Sno NUMBER(8) PRIMARY KEY , Sname VARCHAR(20), Sage NUMBER(20);,PRIMARY KE

10、Y子句，供用户在建表时指定关系的主码列。,19,例2：要在SC表中定义(Sno, Cno)为主码 CREATE TABLE SC (Sno NUMBER(8), Cno NUMBER(2), Grade NUMBER(2), CONSTRAINT PK_SC PRIMARY KEY (Sno, Cno);,二、 SQL Server中的参照完整性,定义参照完整性 FOREIGN KEY子句：定义外码列 REFERENCES子句：外码相应于哪个表的主码 ON DELETE|UPDATE CASCADE子语： 在删除修改被参照关系的元组时，同时删除修改参照关系中 外码值等于被参照关系的元组中主码值

11、的元组,20,例1：建立表EMP表 CREATE TABLE EMP (Empno NUMBER(4), Ename VARCHAR(10), Job VERCHAR2(9), Mgr NUMBER(4), Sal NUMBER(7,2), Deptno NUMBER(2), CONSTRAINT FK_DEPTNO FOREIGN KEY (Deptno) REFERENCES DEPT(Deptno);,Deptno NUMBER(2) CONSTRAINT FK_DEPTNO FOREIGN KEY REFERENCES DEPT(Deptno),CONSTRAINT FK_DEPTNO

12、 FOREIGN KEY (Deptno) REFERENCES DEPT(Deptno) ON DELETE CASCADE,21,CREATE TABLE SC ( Sno char(7) , Cno char(10) , Grade tinyint, CHECK (Grade = 0 and Grade = 100), PRIMARY KEY ( Sno, Cno ), FOREIGN KEY ( Sno ) REFERENCES Student ( Sno ), FOREIGN KEY ( Cno ) REFERENCES Course ( Cno ) ),例子2:sc表结构,22,三

13、、 SQL Server中用户定义的完整性,可定义三类完整性约束: 列值非空（NOT NULL短语） 列值唯一（UNIQUE短语） 检查列值是否满足一个布尔表达式（CHECK短语）,例1：建立部门表DEPT，要求部门名称Dname列 取值唯一，部门编号Deptno列为主码 CREATE TABLE DEPT (Deptno NUMBER, Dname VARCHAR(9) CONSTRAINT U1 UNIQUE, Loc VARCHAR(10), CONSTRAINT PK_DEPT PRIMARY KEY (Deptno);,23,例2： 建立学生登记表Student，要求学号在90000

14、至99999之 间，年龄29，性别只能是男或女，姓名非空. CREATE TABLE Student (Sno NUMBER(5) CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999), Sname VARCHAR(20) CONSTRAINT C2 NOT NULL, Sage NUMBER(3) CONSTRAINT C3 CHECK (Sage 29), Ssex VARCHAR(2) CONSTRAINT C4 CHECK (Ssex IN (男, 女);,24,例3 ：建立职工表EMP，要求每个职工的应发工资不得超过3000元。 应发工资

15、实际上就是实发工资列Sal与扣除项Deduct之和。 CREATE TABLE EMP (Eno NUMBER(4) Ename VARCHAR(10), Job VARCHAR(8), Sal NUMBER(7,2), Deduct NUMBER(7,2) Deptno NUMBER(2), CONSTRAINTS C1 CHECK (Sal + Deduct =3000);,25,四、 SQL Server中的触发器。,1.定义触发器,Create trigger on for|after|instead of as ,例子1:定义一个for型触发器,为教师表teacher定义完整性规则教授的工资=4000,如果4000,自动改为4000.,Create trigger in-up-sal On teacher for insert,update as if (inserted.zc=教授) and (inserted.sal4000) inserted.sal=4000,26,例2:定义一个for型触发器,记录修改student表sno的用户和时间信息.,Create table who-change (