DB05数据库教学公开课获奖课件.pptx

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,第,5,章,数据库完整性,本章重点：,掌握数据库完整性约束定义、检查措施及违约处理,掌握触发器定义及寄存过程定义,本章难点：,触发器定义及寄存过程定义,理解内容：,域中完整性控制,第1页,第,5,章,数据库完整性,数据库完整性,数据对旳性和相容性,数据完整性和安全性是两个不一样样概念,数据完整性,防止数据库中存在不符合语义数据，也就是防止数据库中存在不对旳数据,防备对象：不合语义、不对旳数据,数据安全性,保护数据库防止恶意破坏和非法存取,防备对象：非法顾客和非法操作,第2页,第,5,章,数据库完整性,为维护数据库完整性，DBMS必须：,1.提供定义完整性约束条件机制,2.提供完整性检查措施,3.违约处理,第3页,实体完整性,参照完整性,顾客定义完整性,完整性约束命名字句,触发器,寄存过程,第,5,章,数据库完整性,第4页,5.1,实体完整性,实体完整性定义,实体完整性检查和违约处理,第5页,5.1.1,实体完整性定义,关系模型实体完整性,CREATE TABLE中用PRIMARY KEY定义,单属性构成码有两种阐明措施,定义为列级约束条件,定义为表级约束条件,对多种属性构成码只有一种阐明措施,定义为表级约束条件,第6页,5.1.1,实体完整性定义,例,1,将,Student,表中,Sno,属性定义为码,(1),在列级定义主码,CREATE TABLE Student,(,Sno CHAR(9)PRIMARY KEY,，,Sname CHAR(20)NOT NULL,，,Ssex CHAR(2),，,Sage SMALLINT,，,Sdept CHAR(20);,第7页,(2),在表级定义主码,CREATE TABLE Student,(Sno CHAR(9),，,Sname CHAR(20)NOT NULL,，,Ssex CHAR(2),，,Sage SMALLINT,，,Sdept CHAR(20),，PRIMARY KEY(Sno),);,5.1.1,实体完整性定义,第8页,5.1.1,实体完整性定义,例,2,将,SC,表中,Sno,，,Cno,属性组定义为码,CREATE TABLE SC,(Sno CHAR(9)NOT NULL,，,Cno CHAR(4)NOT NULL,，,Grade SMALLINT,，,PRIMARY KEY(Sno,，,Cno)/*,只能在表级定义主码*,/,);,第9页,5.1.2 实体完整性检查和违约处理,插入或对主码列进行更新操作时，RDBMS按照实体完整性规则自动进行检查。包括：,1.检查主码值与否唯一，假如不唯一则拒绝插入或修改,2.检查主码各个属性与否为空，只要有一种为空就拒绝插入或修改,第10页,检查记录中主码值与否唯一一种措施是进行全表扫描,5.1.2 实体完整性检查和违约处理,第11页,5.1.2 实体完整性检查和违约处理,索引,第12页,5.2 参照完整性,参照完整性定义,参照完整性检查和违约处理,第13页,5.2.1 参照完整性定义,关系模型参照完整性定义,在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外码,用REFERENCES短语指明这些外码参照哪些表主码,第14页,例3 定义SC中参照完整性,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.1 参照完整性定义,第15页,5.2.2 参照完整性检查和违约处理,也许破坏参照完整性状况及违约处理,被参考表（比如Student）,参考表（比如SC）,违约处理,可能破坏参考完整性,插入元组,拒绝,可能破坏参考完整性,修改外码值,拒绝,删除元组,可能破坏参考完整性,拒绝,/,级连删除,/,设置为空值,修改主码值,可能破坏参考完整性,拒绝,/,级连修改,/,设置为空值,第16页,参照完整性违约处理,1.拒绝(NO ACTION)执行,默认方略,2.级联(CASCADE)操作,3.设置为空值（SET-NULL）,对于参照完整性，除了应当定义外码，还应定义外码列与否容许空值,5.2.2 参照完整性检查和违约处理,第17页,例3 显式阐明参照完整性违约处理示例,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),ON DELETE CASCADE /*级联删除SC表中对应元组*/,ON UPDATE CASCADE，/*级联更新SC表中对应元组*/,FOREIGN KEY(Cno)REFERENCES Course(Cno),ON DELETE NO ACTION/*当删除course 表中元组导致了与SC表不一致时拒绝删除*/,ON UPDATE CASCADE /*当更新course表中 o时，级联更新SC表中对应元组*/,)；,5.2.2 参照完整性检查和违约处理,第18页,5.3 顾客定义完整性,顾客定义完整性就是针对某一详细应用数据必须满足语义规定,RDBMS提供，而不必由应用程序承担,第19页,5.3.1,属性上约束条件定义,CREATE TABLE时定义,列值非空（NOT NULL）,列值唯一（UNIQUE）,检查列值与否满足一种布尔表达式（CHECK）,第20页,5.3.1,属性上约束条件定义,1.不容许取空值,例5 在定义SC表时，阐明Sno、Cno、Grade属性不容许取空值。,CREATE TABLE SC,（Sno CHAR(9)NOT NULL，,Cno CHAR(4)NOT NULL，,Grade SMALLINT NOT NULL，,PRIMARY KEY(Sno，Cno)/*假如在表级定义实体完整性，隐含了Sno，Cno不容许取空值，则在列级不容许取空值定义就不必写了*/,）；,第21页,2.列值唯一,例6 建立部门表DEPT，规定部门名称Dname列取值唯一，部门编号Deptno列为主码,CREATE TABLE DEPT,(Deptno NUMERIC(2)，,Dname CHAR(9)UNIQUE，/*规定Dname列值唯一*/,Location CHAR(10)，,PRIMARY KEY(Deptno),)；,5.3.1,属性上约束条件定义,第22页,3.用CHECK短语指定列值应当满足条件,例7 Student表Ssex只容许取“男”或“女”。,CREATE TABLE Student,(Sno CHAR(9)PRIMARY KEY，,Sname CHAR(8)NOT NULL，,Ssex CHAR(2)CHECK(Ssex IN(男,女)，,/*性别属性Ssex只容许取男或女*/,Sage SMALLINT，,Sdept CHAR(20),);,5.3.1,属性上约束条件定义,第23页,5.3.1,属性上约束条件定义,例,8,SC,表,Grade,值应在,0,到,100,之间,CREATE TABLE SC,(Sno CHAR(9)NOT NULL,，,Cno CHAR(4)NOT NULL,，,Grade SMALLINT,Check(Grade=0 and Grade=100),PRIMARY KEY(Sno,，,Cno),/*,在表级定义实体完整性*,/,FOREIGN KEY(Sno)REFERENCES Student(Sno),，,FOREIGN KEY(Cno)REFERENCES Course(Cno),);,第24页,5.3.2 属性上约束条件检查和违约处理,插入元组或修改属性值时，RDBMS检查属性上约束条件与否被满足,假如不满足则操作被拒绝执行,第25页,5.3.3,元组上约束条件定义,例9 当学生性别是男时，其名字不能以Ms.打头。,CREATE TABLE Student,(Sno CHAR(9)，,Sname CHAR(8)NOT NULL，,Ssex CHAR(2)，,Sage SMALLINT，,Sdept CHAR(20)，,PRIMARY KEY(Sno)，,CHECK(Ssex=女 OR Sname NOT LIKE Ms.%),/*定义了元组中Sname和 Ssex两个属性值之间约束条件*/,)；,性别是女性元组都能通过该项检查，由于Ssex=女成立；,当性别是男性时，要通过检查则名字一定不能以Ms.打头,第26页,5.3.4 元组上约束条件检查和违约处理,插入元组或修改属性值时，RDBMS检查元组上约束条件与否被满足,假如不满足则操作被拒绝执行,第27页,5.4,完整性约束命名子句,1.CONSTRAINT,约束,子句,CONSTRAINT,PRIMARY KEY,短语,|FOREIGN KEY,短语,|CHECK,短语,第28页,5.4,完整性约束命名子句,例10 建立学生登记表Student，规定学号在9000099999之间，姓名不能取空值，年龄不不小于30，性别只能是“男”或“女”。,CREATE TABLE Student,(Sno NUMERIC(6),CONSTRAINT C1 CHECK(Sno BETWEEN 90000 AND 99999)，,Sname CHAR(20),CONSTRAINT C2 NOT NULL，,Sage NUMERIC(3),CONSTRAINT C3 CHECK(Sage 30)，,Ssex CHAR(2),CONSTRAINT C4 CHECK(Ssex IN(男，女)，,CONSTRAINT StudentKey PRIMARY KEY(Sno),)；,在Student表上建立了5个约束条件，包括主码约束（命名为StudentKey）以及C1、C2、C3、C4四个列级约束。,第29页,2.,修改表中完整性限制,使用,ALTER TABLE,语句修改表中完整性限制,5.4,完整性约束命名子句,例,12,去掉,例,10 student,表中对性别限制,ALTER TABLE Student,DROP CONSTRAINT C4,第30页,例13 修改表Student中约束条件，规定学号改为在900000999999之间，年龄由不不小于30改为不不小于40,可以先删除本来约束条件，再增长新约束条件,ALTER TABLE Student,DROP CONSTRAINT C1;,ALTER TABLE Student,ADD CONSTRAINT C1 CHECK(Sno BETWEEN 900000 AND 999999)，,ALTER TABLE Student,DROP CONSTRAINT C3;,ALTER TABLE Student,ADD CONSTRAINT C3 CHECK(Sage 40)；,5.4,完整性约束命名子句,第31页,5.5,触发器,触发器（Trigger）是顾客定义在关系表上一类由事件驱动特殊过程,由服务器自动激活,可以进行更为复杂检查和操作，具有更精细和更强大数据控制能力,第32页,Sno,Sname,Ssex,95001,张勇,男,95002,赵楠,男,95003,宋佳,女,Student,表,Cno,Cname,Cpno,1,计算机基础,2,C,语言,1,3,软件工程,4,Course,表,Sno,Cno,Score,95001,1,90,95002,2,85,SC,表,应用实例,5.5,触发器,第33页,5.5.1,定义触发器,CREATE TRIGGER,语法格式,CREATE TRIGGER,触发器名称,ON,表名（触发器定义在哪个表上）,FOR DELETE,INSERT,UPDATE,AS,BEGIN,触发体,END,第34页,定义触发器语法阐明:,1.触发器名,2.表名：触发器目旳表,3.触发事件：INSERT、DELETE、UPDATE,5.5.1,定义触发器,第35页,例如,假设在Student表上创立了一种UPDATE触发器。,CREATE TRIGGER update_sno ON Student,FOR,update,AS,if update(sno),begin,update sc set sno=c.sno,from deleted b,inserted c where sc.sno=b.sno,end,5.5.1,定义触发器,第36页,4.触发条件,触发条件为真,省略WHERE触发条件,5.触发动作体,触发动作体可以是一种匿名PL/SQL过程块,也可以是对已创立寄存过程调用,5.5.1,定义触发器,第37页,插入视图和删除视图(SQL SERVER),当在定义了触发器表上发生修改操作时，会派生出两个视图，一种是插入视图，一种是删除视图。,当在表上发生插入操作时，新插入行将出目前插入视图中；,当在表上发生删除操作时，被删除行将出目前删除视图中；,当在表上发生更新操作时，旧行出目前删除视图中而新行出目前插入视图中。（更新实现过程是先删除旧行，然后插入新行）,这里插入视图和删除视图只在触发器内可用，一旦触发器完毕任务，这两个视图将不再存在。,这两个视图名称分别是insterted和deleted，他们和原表具有完全相似构造。,5.5.1,定义触发器,第38页,假如从student表中删除学生基本信息，则从SC中删除所有该学生成绩信息。,CREATE TRIGGER delete_sno ON Student,FOR,delete,AS,begin,delete sc from sc,deleted a where sc.sno=b.sno,end,第39页,假如从,student表中插入学生基本信息，则从,student1,中插入该学生信息。,CREATE TRIGGER insert_sno ON Student,FOR,insert,AS,begin,insert student1(sno)select c.sno from inserted c,end,第40页,5.2.2,激活触发器,触发器执行，是由触发事件激活，并由数据库服务器自动执行,一种数据表上也许定义了多种触发器,第41页,5.5.3,删除触发器,删除触发器SQL语法：,DROP TRIGGER ON;,触发器必须是一种已经创立触发器，并且只能由具有对应权限顾客删除。,例21 删除教师表student上触发器Insert_sno,DROP TRIGGER Insert_sno ON student,第42页,5.6 寄存过程,CREATE PROCEDURE P_Sno,ls_sno varchar(6),AS,BEGIN TRANSACTION,BEGIN,select sno,sname,ssex from student where sno=:ls_sno;,END,MIT TRANSACTION;,5.6.1 寄存过程定义,第43页,5.6.2 寄存过程执行,exec P_Sno 95001,5.6.3 寄存过程删除,drop procedure p_sno,第44页,5.7,小结,数据库完整性是为了保证数据库中寄存数据是对旳,RDBMS完整性实现机制,完整性约束定义机制,完整性检查机制,违反完整性约束条件时RDBMS应采用动作,第45页,作业：,P164 3,、,6,、,7,第46页,