某大学计算机科学系课件

单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,数据库系统原理,厦门大学计算机系 林子雨,ziyulin 2016,版,厦门大学计算机科学系,2016,版,第五章 数据库完整性（,2016,版）,厦门大学计算机科学系本科生课程,数据库系统原理,林子雨,厦门大学计算机科学系,E-mail: ziyulin,主页：, 数据库完整性,5.1,实体完整性,5.2,参照完整性,5.3,用户自定义,完整性,5.4,完整性约束命名子句,5.6,触发器,5.7,小结,综合统一（操纵三级模式）,SQL,视图,1,视图,2,基表,1,基表,2,基表,3,基表,4,存储文件,1,存储文件,2,外模式,模式,内模式,数据安全,数据完整性,什么是数据库的完整性？,数据的正确性和相容性,防止不合语义的数据进入数据库。,例,:,学生的年龄必须是整数，取值范围为,14-29,；,学生的性别只能是男或女；,学生的学号一定是唯一的；,学生所在的系必须是学校开设的系；,完整性：是否真实地反映现实世界,什么是完整性控制机制？,1.,完整性约束条件定义机制,2.,完整性检查机制,3.,违约处理,1,、完整性约束条件定义,完整性约束条件：数据模型的组成部分,约束数据库中数据的语义,DBMS,应提供定义数据库完整性约束条件，并把它们作为模式的一部分存入数据库中,由,SQL,的,DDL,语句实现,2,、完整性检查机制,检查用户发出的操作请求是否违背了完整性约束条件,在,INSERT,、,UPDATE,、,DELETE,语句执行时进行检查,3,、违约处理,如果发现用户的操作请求使数据违背了完整性约束条件，则采取一定的动作来保证数据的完整性。,拒绝,(NO ACTION),、级连,(CASCADE),1.,完整性约束条件作用的对象,列,：对属性的取值类型、范围、精度等的约束条件,元组,：对元组中各个属性列间的联系的约束,关系,：对若干元组间、关系集合上以及关系之间的联系的约束,2.,完整性约束条件分类,静态,对静态对象的约束是反映数据库状态合理性的约束,动态,对动态对象的约束是反映数据库状态变迁的约束,第五章 数据库完整性,5.1,实体完整性,5.2,参照完整性,5.3,用户自定义,完整性,5.4,完整性约束命名子句,5.6,触发器,5.7,小结,5.1,实体完整性定义,实体完整性：,实体完整性规则（,Entity Integrity,）,若属性,A,是基本关系,R,的主属性，则属性,A,不能取空值,在,CREATE 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) ,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,的主码,K,s,相对应（基本关系,R,和,S,不一定是不同的关系），则对于,R,中每个元组在,F,上的值必须为：,或者取空值（,F,的每个属性值均为空值）,或者等于,S,中某个元组的主码值。,5.2,参照完整性规则,例：,职工部门数据库包含职工表,EMP,和部门表,DEPT,1) DEPT,关系的主码为部门号,Deptno,2) EMP,关系的主码为职工号,Empno,，外码为部门号,Deptno,称,DEPT,为被参照关系或目标关系，,EMP,为参照关系,RDBMS,执行参照完整性时需要考虑以下,4,方面：,5.2,参照完整性规则,1,、外码是否可以接受空值的问题,外码是否能够取空值：依赖于应用环境的语义,实现参照完整性：,系统提供定义外码的机制,定义外码列是否允许空值的机制,1,、外码是否可以接受空值的问题（续）,例,1,：在职工部门数据库中，,EMP,关系包含有外码,Deptno,某元组的这一列若为空值，表示这个职工尚未分配到任何具体的部门工作和应用环境的语义是相符,1,、外码是否可以接受空值的问题（续）,例,2,：学生选课数据库,Student,关系为被参照关系，其主码为,Sno,。,SC,为参照关系，外码为,Sno,。,若,SC,的,Sno,为空值：表明尚不存在的某个学生，或者某个不知学号的学生，选修了某门课程，其成绩记录在,Grade,中，与学校的应用环境是不相符的，因此,SC,的,Sno,列不能取空值。,2,、在被参照关系中删除元组时的问题,出现违约操作的情形：,删除被参照关系的某个元组（,student,）,而参照关系有若干元组,(SC),的外码值与被删除的被参照关系的主码值相同,2,、在被参照关系中删除元组时的问题（续）,违约反应：可有三种策略,受限删除（,NO ACTION,）,级联删除（,CASCADE,）,置空值删除（,NULLIFIES,）,这三种处理方法，哪一种是正确的，要依应用环境的语义来定,2,、在被参照关系中删除元组时的问题（续）,受限删除,当参照关系中没有任何元组的外码值与要删除,的被参照关系的元组的主码值相对应时，系统,才执行删除操作，否则拒绝此删除操作,CREATE TABLE SC,(Sno CHAR(5)foreign key,references Student(Sno),Cno CHAR(1) ,Grade INT,);,2,、在被参照关系中删除元组时的问题（续）,级联删除,将参照关系中外码值与被参照关系中要删除元,组主码值相对应的元组一起删除,CREATE TABLE SC,(Sno CHAR(5),foreign key references Student(Sno),ON DELETE CASCADE,Cno CHAR(1) ,Grade INT,);,2,、在被参照关系中删除元组时的问题（续）,置空值删除,删除被参照关系的元组，并将参照关系,中与被参照关系中被删除元组主码值相,等的外码值置为空值。,3,、在参照关系中插入元组时的问题,出现违约操作的情形,需要在参照关系中插入元组，而被参照关系不存在相应的元组,违约反应,受限插入,递归插入,3,、在参照关系中插入元组时的问题（续）,受限插入,仅当被参照关系中存在相应的元组，其主码值与参照关系插入元组的外码值相同时，系统才执行插入操作，否则拒绝此操作。,递归插入,首先向被参照关系中插入相应的元组，其主码值等于参照关系插入元组的外码值，然后向参照关系插入元组。,3,、在参照关系中插入元组时的问题（续）,例：向,SC,关系插入（,99001,，,1,，,90,）元组，而,Student,关系中尚没有,Sno=99001,的学生,受限插入,：系统将拒绝向,SC,关系插入（,99001,，,1,，,90,）元组,递归插入,：系统将首先向,Student,关系插入,Sno=99001,的元组，然后向,SC,关系插入（,99001,，,1,，,90,）元组。,结论：参照完整性的执行,RDBMS,在执行参照完整性时,:,需要向用户提供定义主码、外码的机制,向用户提供按照自己的应用要求选择处理依赖关系中对应的元组的方法,第五章 数据库完整性,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,修改约束,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 TABLE,语句修改表中的完整性限制,第五章 数据库完整性,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,触发器,理解触发器里面的两个临时的表：,Deleted , Inserted,Deleted,与,Inserted,分别表示触发事件的表“旧的一条记录”和“新的一条记录”,一个数据库系统中有两个虚拟表用于存储在表中记录改动的信息,5.6,触发器,虚拟表,Inserted,虚拟表,Deleted,在表记录新增时 存放新增的记录 不存储记录,修改时 存放用来更新的新记录存放更新前的记录,删除时 不存储记录 存放被删除的记录,例,.,学生成绩不低于,60,分,低于,60,分自动赋为,60,分,Create Trigger chggrade on SC for insert,as,update SC set grade=60 where exists (select * from inserted,where inserted.Sno=SC.Sno and inserted.Cno=SC.Cno,and inserted.grade 60 ),5.6,触发器,课堂作业,计算机（,sdept,为,CS,）学生成绩不低于,60,分,低于,60,分自动赋为,60,分,课堂作业,create trigger T1,on sc,for insert,as,update sc set grade=60 where exists,(,select * from student,where sdept=CSand student.sno=sc.sno and exists,(,select * from inserted,where inserted.sno=sc.sno and o=o,and inserted.grade60,),计算机（,sdept,为,CS,）学生成绩不低于,60,分,低于,60,分自动赋为,60,分,例,.,数学是必修课，所有学生都必须选，学生退学则将成绩置,0,create Trigger selcou,on student,for insert,as,insert into SC(Sno,Cno),select Sno, 2 from inserted,create Trigger sleft,on student for delete,as,update SC set Grade = 0,where Sno in,( select Sno from deleted),5.6,触发器,5.6,触发器,例子：创建触发器，当更新,student,表中的学号时，也同时更新,sc,表中的学号,5.6,触发器,create trigger T2,on student,for update,as,if update(Sno),begin,update sc set sc.sno=i.sno,from sc, inserted i,deleted d,where sc.sno=d.sno,end,创建触发器，当更新,student,表中的学号时，也同时更新,sc,表中的学号,5.6,触发器,例子：创建一个触发器，当删除,student,表中的某条记录时，也同时删除,sc,表中的记录,5.6,触发器,创建一个触发器，当删除,student,表中的某条记录时，也同时删除,sc,表中的记录,create trigger T3,on student,for delete,as,delete sc from sc,deleted d,where sc.sno=d.sno,5.7,断言,使用断言（,assertion,）来指定更具一般性地约束,可以定义涉及多个表或聚集操作的比较复杂的完整性约束,任何对断言中所涉及关系的操作都会出发关系数据库管理系统对断言的检查,任何使得断言不为真的操作都会被拒绝执行,5.7,断言,语句格式：,create assertion ,5.7,断言,例：限制数据库课程最多,60,名学生选修,create assertion asse_sc_db_num,check (,60=(,select count(*) from course,sc,Where,So=o and ame=,数据库,),5.7,断言,例：限制每一门课程最多,60,名学生选修,create assertion asse_sc_cnum1,Check(,60=ALL(select count(*) from sc group by cno),),5.7,断言,例：限制每个学期每一门课最多,60,名学生选修,修改,sc,表，增加一个“学期,(TERM),”属性,Create assertion asse_sc_cnum2,Check(,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,小结,完整性机制的实施会极大地影响系统性能,不同的数据库产品对完整性的支持策略和支持程度是不同的,许多数据库管理系统对完整性机制的支持比对安全性的支持要晚得多也弱得多,数据库厂商对完整性的支持越来越好，不仅在能保证实体完整性和参照完整性而且能在,DBMS,核心定义、检查和保证用户定义的完整性约束条件,第,5,版教材第,173,页 第,6,题,作业,附录：本章常用,SQL,语句,alter table student alter column sno char(5) not null,alter table student add primary key (Sno),alter table sc alter column sno char(5) not null,alter table sc add foreign key (Sno) references student(sno),alter table course alter column Cno char(1) not null,alter table course add primary key (Cno),alter table sc add foreign key (Cno) references course(Cno),exec sp_helpconstraint course,sp_helpconstraint student,alter table sc drop constraint FK_SC_Sno_07020F21,alter table sc add constraint 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: ziyulin,个人网页：, xrdxmu,助教：谢荣东,单位：厦门大学计算机科学系数据库实验室,2015,级硕士研究生,E-mail: xueqian_victoria,助教：薛倩,附录：班级网站,林子雨主讲,数据库系统原理,2016,班级主页,扫一扫访问班级网站,支持手机浏览, of Computer Science, Xiamen University, 2016,