数据库系统概论

第五章数据库完整性 孟小峰 xfmeng@ruc.edu.cn 信息学院 2014/4/8

Replay Time 身份认证 自主访问控制 GRANT & REVOKE 强制访问控制 敏感度标记 主体和客体 视图机制, 审计 数据安全与隐私

数据库完整性 什么是数据库的完整性 数据库的完整性是指数据的正确性和相容性, 防止不合语义的数据进入数据库 例如, 学生的年龄必须是整数, 取值范围为 14--29; 学生的性别只能是男或女 ; 学生的学号一定是唯一的 ; 学生所在的系必须是学校开设的系 ; 数据库系统是否具备完整性关系到数据库系统能否真实地反映现实世界, 因此维护数据库的完整性是非常重要的

数据库完整性 完整性控制机制 1. 完整性约束条件定义机制完整性约束条件是数据模型的一个重要组成部分, 它约束了数据库中数据的语义 DBMS 应提供手段让用户根据现实世界的语义定义数据库的完整性约束条件, 并把它们作为模式的一部分存入数据库中 2. 完整性检查机制检查用户发出的操作请求是否违背了完整性约束条件 3. 违约反应 如果发现用户的操作请求使数据违背了完整性约束条件, 则采取一定的动作来保证数据的完整性

完整性的处理 用户定义, 系统自动检查 完整性定义 增删改数据库 数据索引 数据字典

SQL 处理流程 DAC + MAC 安全检查示意图 SQL 语法分析 & 语义检查 安全检查 DAC 检查 MAC 检查 继续

SQL 处理流程完整性检查示意图 SQL 语法分析 & 语义检查 安全检查 DAC 检查 MAC 检查 完整性检查

关系模型三类完整性 实体完整性 参照完整性 用户定义完整性

完整性定义 Integrity constraints are rules or regulations imposed on data to ensure their integrity and correctness. Constraints on individual columns. Constraints on a table. Constraints on multiple tables.

Constraints on Individual Columns (1) type: values must be of the given type not null: no null value can be taken unique: no identical non-null values primary key: no null value and no identical values references to another attribute S(B): any non-null value must be currently under S(B). check constraint definition: values must satisfy the check condition.

Constraints on Individual Columns (2) Syntax for column definition: col_name data_type [default value] [constraints] Syntax for column constraints: [constraint constraint_name] [not] null check condition unique primary key references table_name [(column)] [on delete cascade]

Constraints on Individual Columns (3) create table Books (ISBN char(15) primary key, Title varchar2(50) unique, Price number(5,2) check (Price > 0), Edition number(2) default 1 check (Edition > 0), Publisher_name varchar2(30) references Publisher(Name));

Table Constraints (1) Syntax for table constraints: [constraint constraint_name] check condition unique (column {, column}) primary key (column {, column}) foreign key (column {, column}) references table_name[(column {, column})] [on delete cascade]

Table Constraints (2) create table Enrollments (SSN char(9) not null references Students, Course_no varchar2(7) not null, Section_no number(2) not null, Grade char, primary key (SSN, Course_no, Section_no), foreign key (Course_no, Section_no) references Section);

Table Constraints (3) create table Graduates (SSN char(9) not null primary key, Name varchar2(30), Address varchar2(80), Degree varchar2(8) check (Degree in { Bachelor, Master, Ph.D. }), GPA number (3,2) not null, unique (Name, Address), constraint ic12 check (GPA >= 2.0 and (Degree = Bachelor or GPA >= 3.0)));

Disabling and Enabling a Constraint Constraints are created enabled. Disabling a constraint alter table Graduates disable constraint ic12; Enabling a constraint alter table Graduates enable constraint ic12;

完整性检查 数据库状态发生改变时, 保证满足完整性约束, 否则拒绝状态的改变 引起数据库状态改变的操作 INSERT UPDATE DELETE 完整性约束 实体完整性 PRIMARY KEY 参照完整性 FOREIGN KEY REFERENCE 用户定义完整性 CHECK,TYPE

用户定义完整性检查 使数据满足 CHECK 条件, 符合定义的数据类型等 INSERT 插入元组使 CHECK 条件为真 UPDATE 更新元组使 CHECK 条件为真 DELETE 不受影响

实体完整性检查 保证主码属性值唯一且不能为空 INSERT 语句 检查主码属性值是否唯一, 如果不唯一则拒绝插入 检查主码属性值是否为空, 若有一个为空则拒绝插入 UPDATE 检查更新后主码属性值是否唯一, 如果不唯一则拒绝插入 主码索引加快完整性检查

参照完整性检查 外码取值要么取所参照的主码值, 要么取空值 外码表 ( 参照表 ) 主码表 ( 被参照表 ) INSERT 检查通过 UPDATE 检查检查 DELETE 检查检查

Modification and Referential Integrity (1) Consider the following table definitions: create table Departments (Name varchar2(20) not null,... ); create table Employees (... Dept_name varchar2(20), foreign key (Dept_name) references Departments(Name));

Modification and Referential Integrity (1) Eno Age Lead Dept# by E1 34 - D2 E3 25 E1 D3 Dname Dept# Loc Account D2 F 1 Sale D3 F 2 E4 28 E1 D2..

Modification and Referential Integrity (2) Insert a new tuple t into Employees allowed if t[dept_name] is currently under Departments.Name or null rejected otherwise

Modification and Referential Integrity (3) Delete an existing tuple t from Departments Options: rejected if t[name] is referenced by some tuple in Employees allowed but also delete all tuples of Employees which reference t 辞退 allowed but also change t1[dept_name] to null for every t1 in Employees which references t 下岗

Modification and Referential Integrity (4) Update t[name] of a tuple t in Departments Options: rejected if t[name] is referenced by some tuple in Employees allowed but also update all tuples of Employees which reference t 转岗 allowed but also change t1[dept_name] to null for every t1 in Employees which references t 下岗

Modification and Referential Integrity (5) Specify options using SQL Format: foreign key (Dept_name) references Departments(Name) [on delete { no action cascade set null }]

Database Modification and Referential Integrity (6) Explanations (for on delete only): no action: reject any request that would violate the referential integrity rule. cascade: delete and then cascade to delete all tuples that reference the deleted tuple. Oracle supports only the above choices with no action as the default.

Database Modification and Referential Integrity (7) set null: delete and then set the foreign key values of the tuples referencing the deleted tuple to null. Reference chain: T1 T2 T3 T1 With cascade option if one has no action, then a chain of cascade may not succeed.

Modification and Referential Integrity (8) Redefine the Employees table. create table Employees (... foreign key (Dept_Name) references Departments(Name) on delete no action on update cascade);

触发器 Trigger (1) A trigger is an event-driven block of PL/SQL code. An event could be an insertion of a tuple into a table, a deletion of an existing tuple from a table, and an update of a table. A trigger fires (executes, activates) when an event occurs. Triggers are useful for enforcing various integrity constraints and business rules.

Trigger (2) ECA Model: an event yes Is the event yes take detected? significant? actions insert need to satisfy execute delete additional SQL/PL update condition block Event Condition Action

Trigger (3) create or replace trigger raise_sal before update of salary on employees for each row when (new.salary > old.salary * 1.2) begin dbms_output.put_line( Old salary is :old.salary, New salary is :new.salary); dbms_output.put_line( The raise is too high! ); end;

Trigger Applications (1) Use trigger for monitoring changes Example: add a log entry each time the price of a product is changed log table for product: create table products_log (pid char(4), username char(10), update_date date, old_price number(6, 2), new_price number(6, 2));

Trigger Applications (2) create or replace trigger update_p_price after update of price on products for each row begin insert into products_log values (:old.pid, user, sysdate, :old.price, :new.price); end;

Trigger Applications (3) Use trigger to enforce integrity constraints Example: If a student is removed, delete all enrollments by the student. create or replace trigger stud_enroll after delete on students for each row begin delete from enrollments where sid = :old.sid; end;

Constraints On Multiple Tables Example: Create the constraint that no students is allowed to take more than six courses. create assertion Course_Constraint check (not exists (select * from Students s where 6 < (select count(*) from Enrollment where SSN = s.ssn)));

习题与实验 第 4 章 : 习题 8~13 第 5 章 习题 5,6,7 交作业 :4 月 10 日, 讲评 :4 月 15 日 实验 4 月 22 日 : 安全性, 完整性

期中复习 第一章 : 概述, 以教材为主 文件系统与数据库系统的本质区别 数据模型三要素 概念模型概念 最常见的数据模型 层次模型 网状模型 关系模型 数据库系统的三级模式与两级映射 : 数据抽象 数据独立性 : 物理独立性, 逻辑独立性 数据库系统的组成

期中复习 第二章 : 关系模型, 关系完整性, 关系运算 关系模式与关系 实体完整性, 超码, 候选码, 主码, 外码 参照完整性 关系代数 关系演算 关系代数 关系演算 SQL 的等价性

期中复习 第三章 : SQL DDL 与数据字典 SELECT: 简单查询, 连接查询, 集合查询, 嵌套查询, 分组查询与聚集函数, 排序 INSERT/DELETE/UPDATE 视图 视图定义 视图查询 视图更新 视图作用

期中复习 第八章编程 SQL SQL 与主语言的通讯 游标机制 动态 SQL ODBC PL/SQL

期中复习 第四章 : 数据库安全 安全控制的一般方法 自主存取控制 授权与回收 角色 强制存取控制 视图机制

期中复习 第五章 : 数据库完整性 完整性定义 完整性检查 实体完整性检查 参照完整性检查 触发器 create or replace trigger create assertion