厦门大学-林子雨-数据库系统原理-2016版-第5章-数据库完整性ppt.ppt

1、,厦门大学计算机科学系 2016版,第五章 数据库完整性（2016版）,厦门大学计算机科学系本科生课程数据库系统原理,林子雨厦门大学计算机科学系E-mail: 主页：http:/ 数据库完整性,5.1 实体完整性5.2 参照完整性5.3 用户自定义完整性5.4 完整性约束命名子句5.6 触发器5.7 小结,综合统一（操纵三级模式）,SQL,视图1,视图2,基表1,基表2,基表3,基表4,存储文件1,存储文件2,外模式,模式,内模式,什么是数据库的完整性？,数据的正确性和相容性防止不合语义的数据进入数据库。例: 学生的年龄必须是整数，取值范围为14-29； 学生的性别只能是男或女； 学生的学号一

2、定是唯一的； 学生所在的系必须是学校开设的系；完整性：是否真实地反映现实世界,什么是完整性控制机制？,1. 完整性约束条件定义机制2. 完整性检查机制3. 违约处理,1、完整性约束条件定义,完整性约束条件：数据模型的组成部分,约束数据库中数据的语义DBMS应提供定义数据库完整性约束条件，并把它们作为模式的一部分存入数据库中由SQL的DDL语句实现,2、完整性检查机制,检查用户发出的操作请求是否违背了完整性约束条件在INSERT、UPDATE、DELETE语句执行时进行检查,3、违约处理,如果发现用户的操作请求使数据违背了完整性约束条件，则采取一定的动作来保证数据的完整性。拒绝(NO ACTIO

3、N)、级连(CASCADE),1.完整性约束条件作用的对象,列：对属性的取值类型、范围、精度等的约束条件元组：对元组中各个属性列间的联系的约束关系：对若干元组间、关系集合上以及关系之间的联系的约束,2.完整性约束条件分类,静态对静态对象的约束是反映数据库状态合理性的约束动态对动态对象的约束是反映数据库状态变迁的约束,第五章 数据库完整性,5.1 实体完整性5.2 参照完整性5.3 用户自定义完整性5.4 完整性约束命名子句5.6 触发器5.7 小结,5.1 实体完整性定义,实体完整性：实体完整性规则（Entity Integrity）若属性A是基本关系R的主属性，则属性A不能取空值,在CREA

4、TE TABLE语句中提供了PRIMARY KEY子句，供用户在建表时指定关系的主码列。在列级使用PRIMARY KEY子句在表级使用PRIMARY KEY子句,5.1 实体完整性定义,例1：在学生选课数据库中，要定义Student表的Sno属性为主码,CREATE TABLE Student (Sno CHAR(5) primary key , Sname VARCHAR(10) , Ssex CHAR(2) , Sage INT, Sdept CHAR(2);,5.1 实体完整性定义,例2：要在SC表中定义(Sno, Cno)为主码,CREATE TABLE SC (Sno CHAR(5)

5、 , Cno CHAR(1) , Grade INT, primary key (Sno,Cno) );,5.1 实体完整性定义,用户程序对主码列进行更新操作时，系统自动进行完整性检查违约操作使主属性值为空值的操作使主码值在表中不唯一的操作违约反应系统拒绝此操作，从而保证了实体完整性,5.1 实体完整性定义,第五章 数据库完整性,5.1 实体完整性5.2 参照完整性5.3 用户自定义完整性5.4 完整性约束命名子句5.6 触发器5.7 小结,参照完整性： 若属性（或属性组）F是基本关系R的外码，它与基本关系S的主码Ks相对应（基本关系R和S不一定是不同的关系），则对于R中每个元组在F上的值必须

6、为： 或者取空值（F的每个属性值均为空值） 或者等于S中某个元组的主码值。,5.2 参照完整性规则,例：职工部门数据库包含职工表EMP和部门表DEPT 1) DEPT关系的主码为部门号Deptno 2) EMP关系的主码为职工号Empno，外码为部门号Deptno 称 DEPT 为被参照关系或目标关系，EMP 为参照关系 RDBMS执行参照完整性时需要考虑以下4方面：,5.2 参照完整性规则,1、外码是否可以接受空值的问题,外码是否能够取空值：依赖于应用环境的语义实现参照完整性：系统提供定义外码的机制定义外码列是否允许空值的机制,1、外码是否可以接受空值的问题（续）,例1：在职工部门数据库中，

7、 EMP关系包含有外码Deptno 某元组的这一列若为空值，表示这个职工尚未分配到任何具体的部门工作和应用环境的语义是相符,1、外码是否可以接受空值的问题（续）,例2：学生选课数据库 Student关系为被参照关系，其主码为Sno。 SC为参照关系，外码为Sno。 若SC的Sno为空值：表明尚不存在的某个学生，或者某个不知学号的学生，选修了某门课程，其成绩记录在Grade中，与学校的应用环境是不相符的，因此SC的Sno列不能取空值。,2、在被参照关系中删除元组时的问题,出现违约操作的情形： 删除被参照关系的某个元组（student） 而参照关系有若干元组(SC)的外码值与被删除的被参照关系的主

8、码值相同,2、在被参照关系中删除元组时的问题（续）,违约反应：可有三种策略受限删除（NO ACTION）级联删除（CASCADE）置空值删除（NULLIFIES）这三种处理方法，哪一种是正确的，要依应用环境的语义来定,2、在被参照关系中删除元组时的问题（续）,受限删除当参照关系中没有任何元组的外码值与要删除的被参照关系的元组的主码值相对应时，系统才执行删除操作，否则拒绝此删除操作,CREATE TABLE SC (Sno CHAR(5)foreign key references Student(Sno), Cno CHAR(1) , Grade INT );,2、在被参照关系中删除元组时的问

9、题（续）,级联删除将参照关系中外码值与被参照关系中要删除元组主码值相对应的元组一起删除,CREATE TABLE SC (Sno CHAR(5)foreign key references Student(Sno) ON DELETE CASCADE, Cno CHAR(1) , Grade INT );,2、在被参照关系中删除元组时的问题（续）,置空值删除删除被参照关系的元组，并将参照关系中与被参照关系中被删除元组主码值相等的外码值置为空值。,3、在参照关系中插入元组时的问题,出现违约操作的情形需要在参照关系中插入元组，而被参照关系不存在相应的元组违约反应受限插入递归插入,3、在参照关系中插

10、入元组时的问题（续）,受限插入仅当被参照关系中存在相应的元组，其主码值与参照关系插入元组的外码值相同时，系统才执行插入操作，否则拒绝此操作。递归插入首先向被参照关系中插入相应的元组，其主码值等于参照关系插入元组的外码值，然后向参照关系插入元组。,3、在参照关系中插入元组时的问题（续）,例：向SC关系插入（99001，1，90）元组，而Student关系中尚没有Sno=99001的学生受限插入：系统将拒绝向SC关系插入（99001，1，90）元组递归插入：系统将首先向Student关系插入Sno=99001的元组，然后向SC关系插入（99001，1，90）元组。,结论：参照完整性的执行,RDBM

11、S在执行参照完整性时:需要向用户提供定义主码、外码的机制向用户提供按照自己的应用要求选择处理依赖关系中对应的元组的方法,第五章 数据库完整性,5.1 实体完整性5.2 参照完整性5.3 用户自定义完整性5.4 完整性约束命名子句5.6 触发器5.7 小结,1. 用CREATE TABLE语句在建表时定义用户完整性约束可定义三类完整性约束列值非空（NOT NULL短语）列值唯一（UNIQUE短语）检查列值是否满足一个布尔表达式（CHECK短语）,5.3 用户定义的完整性,5.3 用户定义的完整性,约束命名Constraint Primary key | Foreign Key | Check ,

12、修改约束Alter Table Add| Drop Constraint ,5.3 用户定义的完整性,第五章 数据库完整性,5.1 实体完整性5.2 参照完整性5.3 用户自定义完整性5.4 完整性约束命名子句5.6 触发器5.7 小结,5.4 完整性约束命名子句,SQL还在CREATE TABLE 语句中提供了完整性约束命名子句CONSTRAINT，用来对完整性约束条件命名。从而可以灵活地增加、删除一个完整性约束条件。1、完整性约束命名子句CONSTRAINT PRIMARY KEY 短语 | FOREIGN KEY 短语 | CHECK 短语2、修改表中的完整性限制使用ALTER TABL

13、E语句修改表中的完整性限制,第五章 数据库完整性,5.1 实体完整性5.2 参照完整性5.3 用户自定义完整性5.4 完整性约束命名子句5.6 触发器5.7 小结,5.6 触发器,通过触发器来定义复杂的完整性规则定义其它的完整性约束时，需要用数据库触发器（Trigger）来实现。数据库触发器：一类靠事务驱动的特殊过程一旦由某个用户定义，任何用户对该数据的增、删、改操作均由服务器自动激活相应的触发子，在核心层进行集中的完整性控制定义数据库触发器的语句,CREATE TRIGGER ON FOR INSERT| DELETE|UPDATE AS删除DROP TRIGGER ,5.6 触发器,5.6

14、 触发器,理解触发器里面的两个临时的表：Deleted , Inserted Deleted 与Inserted分别表示触发事件的表“旧的一条记录”和“新的一条记录”一个数据库系统中有两个虚拟表用于存储在表中记录改动的信息,5.6 触发器,虚拟表Inserted 虚拟表Deleted在表记录新增时 存放新增的记录 不存储记录 修改时 存放用来更新的新记录存放更新前的记录 删除时 不存储记录 存放被删除的记录,例. 学生成绩不低于60分,低于60分自动赋为60分,Create Trigger chggrade on SC for insertasupdate SC set grade=60 wh

15、ere exists (select * from insertedwhere inserted.Sno=SC.Sno and inserted.Cno=SC.Cno and inserted.grade 60 ),5.6 触发器,课堂作业,计算机（sdept为CS）学生成绩不低于60分,低于60分自动赋为60分,课堂作业,create trigger T1on scfor insertasupdate sc set grade=60 where exists(select * from student where sdept=CSand student.sno=sc.sno and exis

16、ts(select * from insertedwhere inserted.sno=sc.sno and o=oand inserted.grade=(select count(*) from course,scWhereSo=o and ame=数据库),5.7 断言,例：限制每一门课程最多60名学生选修create assertion asse_sc_cnum1Check(60=ALL(select count(*) from sc group by cno),5.7 断言,例：限制每个学期每一门课最多60名学生选修修改sc表，增加一个“学期(TERM)”属性Create assert

17、ion asse_sc_cnum2Check(60=ALL(select count(*) from sc group by cno,term),第五章 数据库完整性,5.1 实体完整性5.2 参照完整性5.3 用户自定义完整性5.4 完整性约束命名子句5.6 触发器5.7 小结,5.7 小结,数据库的完整性是为了保证数据库中存储的数据是正确的，所谓正确的是指符合现实世界语义的。DBMS完整性实现的机制完整性约束定义机制完整性检查机制违背完整性约束条件时DBMS应采取的动作,5.7 小结,完整性机制的实施会极大地影响系统性能不同的数据库产品对完整性的支持策略和支持程度是不同的许多数据库管理系统

18、对完整性机制的支持比对安全性的支持要晚得多也弱得多数据库厂商对完整性的支持越来越好，不仅在能保证实体完整性和参照完整性而且能在DBMS核心定义、检查和保证用户定义的完整性约束条件,第5版教材第173页 第6题,作业,附录：本章常用SQL语句,alter table student alter column sno char(5) not nullalter table student add primary key (Sno)alter table sc alter column sno char(5) not nullalter table sc add foreign key (Sno)

19、references student(sno)alter table course alter column Cno char(1) not nullalter table course add primary key (Cno)alter table sc add foreign key (Cno) references course(Cno)exec sp_helpconstraint coursesp_helpconstraint studentalter table sc drop constraint FK_SC_Sno_07020F21alter table sc add cons

20、traint FK1 foreign key (Sno) references student(Sno)alter table sc add constraint FK2 foreign key (Cno) references course(Cno)alter table student add constraint PK1 primary key (Sno)alter table student drop constraint PK1,附录：主讲教师,单位：厦门大学计算机科学系E-mail: 个人网页：http:/ ,助教：谢荣东,单位：厦门大学计算机科学系数据库实验室2015级硕士研究生E-mail: xueqian_,助教：薛倩,附录：班级网站,林子雨主讲数据库系统原理2016班级主页,扫一扫访问班级网站支持手机浏览,http:/ of Computer Science, Xiamen University, 2016,