Access数据库第五章.ppt

1,上节回顾,第四章 数据库安全性4.2 数据库安全性控制授权与回收数据库角色强制存取控制方法4.4 审计4.5 数据加密4.6 统计数据库安全性,2,数据库系统概论An Introduction to Database System第五章 数据库完整性,东莞理工学院城市学院计算机与信息科学系,3,第五章 数据库完整性,定义指数据的正确性和相容性目的为了防止不合语义的数据进入数据库。现实世界的实例：学生的性别只能是男或女；本科生的年龄必须是整数，取值范围为1450；学生的学号一定是唯一的；学生所在的系必须是学校开设的系；,4,第五章 数据库完整性,完整性和安全性的区别目的不同完整性是防止数据库中存在不正确的数据;安全性是防止数据库被恶意破坏或非法存取。防范对象不同完整性是防范不合语义、不正确的数据;安全性是防范非法用户和非法操作。,5,数据库完整性,完整性控制机制包括：完整性约束条件定义机制DBMS应提供定义数据库完整性约束条件，并把它们作为模式的一部分存入数据库中完整性检查的方法检查用户发出的操作请求是否违背了完整性约束条件，一般在：INSERT、UPDATE、DELETE语句执行后以及事务提交时检查。,6,数据库完整性,违约处理如果发现用户的操作请求使数据违背了完整性约束条件，则采取一定的动作来保证数据的完整性，包括：拒绝（NO ACTION）执行该操作，或级联（CASCADE）执行其他操作。,7,第五章 数据库完整性,5.1 实体完整性5.2 参照完整性5.3 用户定义的完整性5.4 完整性约束命名子句5.6 触发器5.7 小结,8,5.1.1 实体完整性定义,在CREATE TABLE中用PRIMARY KEY定义，可以是列级约束条件也可以是表级约束条件，但有些时候只能定义表级约束条件。,9,5.1.1 实体完整性定义,【例1】将Student表中的Sno属性定义为码CREATE TABLE Student/*列级定义主码*/(Sno CHAR(9)PRIMARY KEY,Sname CHAR(20)NOT NULL,Ssex CHAR(2),Sage SMALLINT,Sdept CHAR(15);,10,5.1.1 实体完整性定义,CREATE TABLE Student/*表级定义主码*/(Sno CHAR(9),Sname CHAR(20)NOT NULL,Ssex CHAR(2),Sage SMALLINT,Sdept CHAR(15),PRIMARY KEY(Sno)；,11,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);,12,5.1.2 实体完整性检查和违约处理,检查当用户程序对基本表插入一条记录或对主码进行更新操作时进行检查。包括：检查主码的值是否唯一，不唯一则拒绝操作；检查主码的各个属性是否为空，只要有一个为空就拒绝操作。,13,第五章 数据库完整性,5.1 实体完整性5.2 参照完整性5.3 用户定义的完整性5.4 完整性约束命名子句5.6 触发器5.7 小结,14,第五章 数据库完整性,附：创建Course表的SQL语句CREATE TABLE Course(Cno CHAR(4)PRIMARY KEY,Cname CHAR(40),Cpno CHAR(4),Ccredit SMALLINT,FOREIGN KEY(Cpno)REFERENCES Course(Cno);,15,5.2.1 参照完整性定义,在CREATE TABLE中用FOREIGN KEY定义哪些列为外码，用REFERENCES短语指明这些外码参照哪些表的主码。,16,5.2.1 参照完整性定义,【例3】定义SC中的参照完整性CREATE TABLE SC(Sno CHAR(9),Cno CHAR(4),Grade SMALLINT,PRIMARY KEY(Sno,Cno),FOREIGN KEY(Sno)REFERENCES Student(Sno),FOREIGN KEY(Cno)REFERENCES Course(Cno);,17,5.2.2 参照完整性检查和违约处理,检查当用户程序对被参照表和参照表时进行增删改操作时进行检查。处理：对参照表的增、改外码值时可能破坏参照完整性，则以拒绝操作处理。对被参照表进行删、修改主码值时可能破坏参照完整性，则以拒绝/级联删除/设置为空值操作处理。,18,5.2.2 参照完整性检查和违约处理,【注意】系统默认的处理方式是拒绝处理，如果要让系统采用其他策略，则要在创建表时显式定义。,19,5.2.2 参照完整性检查和违约处理,【例4】显式说明参照完整性的违约处理示例CREATE TABLE SC(Sno CHAR(9),Cno CHAR(4),Grade SMALLINT,PRIMARY KEY(Sno,Cno),FOREIGN KEY Sno REFERENCES Student(Sno)ON DELETE CASCADEON UPDATE CASCADE,FOREIGN KEY Cno REFERENCES Course(Cno)ON DELETE NO ACTIONON UPDATE CASCADE,);,当删除Student表中的元组时，级联删除SC表中相应的元组,20,第五章 数据库完整性,5.1 实体完整性5.2 参照完整性5.3 用户定义的完整性5.4 完整性约束命名子句5.6 触发器5.7 小结,21,5.3.1 属性上的约束条件的定义,在CREATE TABLE中根据需要定义，包括：列值非空（NOT NULL）列值唯一（UNIQUE）检查列值是否满足一个布尔表达式（CHECK）,22,5.3.1 属性上的约束条件的定义,用CHECK短语指定列值应该满足的条件【例8】SC表的Grade的值应该在0和100之间。CREATE TABLE SC(SnoCHAR(9)NOT NULL,CnoCHAR(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);,23,5.3.2属性上的约束条件检查和违约处理,检查当往表中插入元组或修改属性的值时进行检查。处理：不满足则以拒绝操作处理。,24,5.3.3 元组上的约束条件的定义,用CHECK短语指定表级应该满足的条件【例9】当学生的性别是男时，其名字不能以Ms.打头。CREATE TABLE Student(SnoCHAR(9),SnameCHAR(20)NOT NULL,SsexCHAR(2),SageSMALLINT,SdeptCHAR(15),PRIMARY KEY(Sno),CHECK(Ssex=女 OR Sname NOT LIKE Ms.%);,25,5.3.4 元组上的约束条件检查和违约处理,检查当往表中插入元组或修改属性的值时进行检查。处理：不满足则以拒绝操作处理。,26,第五章 数据库完整性,5.1 实体完整性5.2 参照完整性5.3 用户定义的完整性5.4 完整性约束命名子句5.6 触发器5.7 小结,27,1 完整性约束命名子句,在CREATE TABLE中提供了完整性约束命名子句：CONSTRAINT。CONSTRAINT 格式：CONSTRAINT PRIMARY KEY 短语|FOREIGN KEY 短语|CHECK 短语,28,1 完整性约束命名子句,【例10】建立学生登记表Student，要求学号在2010000120102999，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。,29,1 完整性约束命名子句,CREATE TABLE Student(SnoNUMERIC(8)CONSTRAINT C1 CHECK(Sno BETWEEN 2010001 AND 20102999),SnameCHAR(20)CONSTRAINT C2 NOT NULL,SsexCHAR(2)CONSTRAINT C3 CHECK(Ssex IN(男,女),SageNUMERIC(3)CONSTRAINT C4 CHECK(Sage30),SdeptCHAR(15)CONSTRAINT StudentKey PRIMARY KEY(Sno);,30,1 完整性约束命名子句,【例11】建立教师表TEACHER，要求每个教师的应发工资不低于3000元。CREATE TABLE TEACHER(EnoNUMERIC(4)PRIMARY KEY,SnameCHAR(10),JobCHAR（10）,SalNUMERIC(7,2),DeductNUMERIC(7,2),DeptnoNUMERIC(2),CONSTRAINT EmpFKey FOREIGN KEY(Deptno)REFERENCES DEPT(Deptno),CONSTRAINT C1 CHECK(Sal+Deduct=3000);,31,2 修改表中的完整性限制,在ALTER TABLE中修改表中完整性限制。可先删除原来的约束条件再增加新的约束条件。删除：ALTER TABLE 表名 DROP CONSTRAINT 增加：ALTER TABLE 表名 ADD CONSTRAINT CHECK例子见课本P159例13。,32,SQL Server 2000中添加默认值约束,格式：ALTER TABLE 用户名.表名 ADD CONSTRAINT 默认值名 DEFAULT 具体值 FOR 字段名删除：ALTER TABLE 用户名.表名 DROP CONSTRAINT 默认值名,33,【例】:ALTER TABLE dbo.Student ADD CONSTRAINT df_Stu DEFAULT 计算机系 FOR Sdept;删除：ALTER TABLE dbo.Student DROP CONSTRAINT df_Stu;,SQL Server 2000中添加默认值约束,34,第五章 数据库完整性,5.1 实体完整性5.2 参照完整性5.3 用户定义的完整性5.4 完整性约束命名子句5.6 触发器5.7 小结,35,5.6 触发器,触发器（Trigger）是一种特殊类型的存储过程；触发器主要是通过事件进行触发而被执行的；当对某一表进行诸如UPDATE、INSERT、DELETE 这些操作时，SQL Server 就会自动执行触发器所定义的SQL 语句，从而确保对数据的处理必须符合由这些SQL 语句所定义的规则；触发器的主要作用就是其能够实现由主码和外码所不能保证的复杂的参照完整性和数据的一致性。,36,5.6.1 定义触发器,SQL2000中创建触发器的一般格式：CREATE TRIGGER 触发器名ON 表名|视图名 FOR|AFTERINSERT,UPDATE,DELETE ASIF UPDATE(列名)AND|ORUPDATE(列名)nSQL语句 n,37,5.6.1 定义触发器,说明：创建表的用户才可以创建此表的触发器，一个表只能创建一定数量的触发器。表名为触发器的目标表的名称。AFTER：指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。,38,5.6.1 定义触发器,触发事件可以是：INSERT、DELETE、UPDATE或上述事件的组合，另外，UPDATE后面还可以有OF，即进一步指明修改哪些列时触发器激活。AS：是触发器要执行的操作。SQL语句：是触发器的条件和操作。触发器条件指定其它准则，以确定 DELETE、INSERT 或 UPDATE 语句是否导致执行触发器操作。,39,5.6.1 定义触发器,触发器被激活时，只有当触发条件为真时，触发动作体才执行；否则触发动作体不执行。如果省略触发条件，则触发动作体在触发器被激活后立即执行。触发器动作体可以是SQL过程块或对已创建的存储过程的调用。,40,5.6.1 定义触发器,IF UPDATE(列名)：测试在指定的列上进行的 INSERT 或 UPDATE 操作，不能用于 DELETE 操作。可以指定多列。因为在 ON 子句中指定了表名，所以在 IF UPDATE 子句中的列名前不要包含表名。,41,5.6.1 定义触发器,【例】定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，则自动改为4000元”。CREATE TRIGGER Insert_Or_Update_SalBEFORE INSERT OR UPDATE ON TeacherFOR EACH ROWAS BEGINIF(new.Job=教授)AND(new.Sal4000)THENnew.Sal:=4000;END IF;END,42,5.6.1 定义触发器,【例18】SQL2000CREATE TRIGGER Insert_Or_Update_SalON TeacherFOR INSERT,UPDATE ASIF EXISTS(SELECT*FROM Teacher WHERE Job=教授 AND Sal4000)UPDATE Teacher SET Sal=4000;,43,5.6.2 激活触发器,【例】Insert触发器CREATE TRIGGER insert_student ON Student AFTER INSERT AS PRINT 数据插入成功!;当执行Insert语句时，会激活此触发器：insert into Studentvalues(201015131,王五,男,20,CS);,44,5.6.2 激活触发器,【例】Insert触发器CREATE TRIGGER insert_student_1ON StudentAFTER INSERTASDECLARE a CHAR(9)Select a=Inserted.SnoFrom InsertedPRINT 学号为+a+的学生数据插入成功!;,45,5.6.2 激活触发器,【例】Update触发器CREATE TRIGGER update_student ON student AFTER UPDATE AS SELECT*FROM student ORDER BY sno;当更新数据时就触发了这个查询语句，就能显示学生表中的学生信息：Update Student Set Sage=Sage+1;,46,5.6.2 激活触发器,【例】如果更改了Student表中某学生的学号要求同时修改他在SC表中的学号。Create Trigger truStudenton Studentfor UpdateASif Update(Sno)beginUpdate SCSet Sno=Inserted.SnoFrom SC,Inserted,DeletedWhere SC.Sno=Deleted.Snoend,47,5.6.2 激活触发器,触发器里面的两个临时的表：Deleted 和 Inserted。一个数据库系统中有两个虚拟表用于存储在表中记录改动的信息，分别是：虚拟表Inserted 虚拟表Deleted在表记录新增时 存放新增的记录 不存储记录 修改时 存放用来更新的新记录 存放更新前的记录 删除时 不存储记录 存放被删除的记录,48,5.6.3 删除触发器,格式：DROP TRIGGER ON;说明：待删的触发器必须是已创建的触发器；只能由相应权限的用户才能操作。例子：删除教师表Teacher上的触发器Insert_SalDROP TRIGGER Insert_Sal ON Teacher;,49,下课了。,休息一会儿。,追求,50,上节回顾,第四章 数据库安全性4.4 审计、4.5 数据加密、4.6 统计数据库安全性第五章 数据库完整性5.1 实体完整性、5.2 参照完整性5.3 用户定义的完整性（CHECK）5.4 完整性约束命名子句（CONSTRAINT）,