Database Systems 主题三 : 数据完整性实现技术 DDL 信息科学与工程学院 Jin-Min Yang 2018.09
数据完整性问题 数据完整性的四大保障措施 : 主键约束 ; 外键约束 ; 域约束 ; 业务规则约束 ;
DDL layout Definition of Schema of Table Trigger( 触发器 ): 业务规则约束 ;
SQL Identifiers Identifiers are used to identify objects in the database such as tables, views, and columns. The identifer is the name of the database object. An SQL identifier (name) must follow these rules: 1) only contain upper or lower case characters, digits, and underscore ("_") character 2) be no longer than 128 characters 3) must start with a letter 4)cannot contain spaces.
Attributes of relation In SQL, Attribute (column, field ) name (identifier); each column (attribute) has a data type that limits the values that it may store. Attribute value constraint: domain;
SQL Data Types Data Type Description BOOLEAN TRUE or FALSE CHAR Fixed length string (padded with blanks) e.g. CHAR(10) VARCHAR Variable length string e.g. VARCHAR(50) NUMERIC or DECIMAL e.g NUMERIC(7,2) INTEGER Integer data only SMALLINT Smaller space than INTEGER FLOAT or REAL Approximate numeric data types. DATE Stores YEAR, MONTH, DAY TIME Stores HOUR, MINUTE, SECOND TIMESTAMP Stores date and time data. CHARACTER LARGE OBJECT SQL3 BINARY LARGE OBJECT SQL3
Example CREATE TABLE Emp ( eno CHAR(5), ename VARCHAR(30) NOT NULL, bdate DATE, title CHAR(2) CHECK (title IN (NULL,'EE','SA','PR','ME')), salary DECIMAL(9,2), supereno CHAR(5), dno CHAR(5) PRIMARY KEY (eno) FOREIGN KEY (dno) REFERENCES Dept(dno) ON DELETE SET NULL ON UPDATE CASCADE );
引用完整性约束 Student Name studentno sex birthdate Mike 2008043101 male 1990/12/14 Tom 2008043214 female 1992/02/21 Mary 2008043332 female 1988/07/09 Enroll 在 Student 表中的某一行记录 : 1) 修改的其主关键字 studentno; 2) 删除某一行记录 ; 3) 插入一行新记录 ; studentno courseno semester grade 2008043332 430803 2010/01 90 2008043101 430317 2009/01 56 2008043101 430317 2008/02 56 2008043332 430007 2004/02 77 2008043332 430601 2000/01 87 2008043101 430317 2002/02 56 在 Enroll 表中对应 的记录怎么办?
引用完整性约束 Student Name studentno sex birthdate Mike 2008043101 male 1990/12/14 Tom 2008043214 female 1992/02/21 Mary 2008043332 female 1988/07/09 在 Student 表中的某一行记录 : 1) 修改的其主关键字 studentno; 2) 删除某一行记录 ; 3) 插入一行新记录 ; Enroll studentno courseno semester grade 2008043332 430803 2010/01 90 2008043101 430317 2009/01 56 2008043101 430317 2008/02 56 2008043332 430007 2004/02 77 2008043332 430601 2000/01 87 2008043101 430317 2002/02 56 1) CASCADE 2) SET NULL 3) NO ACTION
表模式的创建 CREATE TABLE Enroll ( studentno CHAR(5) NOT NULL, courseno CHAR(5) NOT NULL, semester CHAR(7), grade SMALLINT PRIMARY KEY (studentno, courseno, semester) FOREIGN KEY (studentno) REFERENCES student(studentno) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (courseno) REFERENCES course(courseno) ON DELETE NO ACTION ON UPDATE CASCADE );
Table definition practice question Student Name studentno sex birthdate Mike 2008043101 male 1990/12/14 Tom 2008043214 female 1992/02/21 Mary 2008043332 female 1988/07/09 Course Name courseno textbook syllabus database 430601 a introduction operating system 430803 b all java 430317 c section Enroll studentno courseno semester grade 2008043332 430803 2010/01 90 2008043101 430317 2009/01 56 2008043101 430317 2008/02 56 2008043332 430007 2004/02 77 2008043332 430601 2000/01 87 2008043101 430317 2002/02 56 Write DDL for table: Enroll, student
业务规则约束 : 用户对某个表进行行更新 ( 修改, 添加, 删除 ) 时, 所做的更新要符合业务规则 元组约束 1) 当前行数据, 每个字段取值受其域约束 :data type, domain,not NULL 等 ; 2) 当前行数据, 不同字段的取值彼此之间存在关联约束 ; 例如 : CREATE TABLE student ( studentno CHAR(10) NOT NULL, honors CHAR(1), grade DECIMAL(4,1) CHECK ( (honors = 'Y' AND grade > 85) OR honors = 'N')); 3) 当前行数据, 它受该表中已有行的约束 ; 4) 当前行数据, 它受其它表中已有行的约束 ;
业务规则约束的表达 1) 当前行数据, 每个字段取值受其域约束 :data type, domain,not NULL 等 ; 2) 当前行数据, 不同字段的取值彼此之间存在关联约束 ; 例如 : CREATE TABLE student ( studentno CHAR(10) NOT NULL, honors CHAR(1), grade DECIMAL(4,1) CHECK ( (honors = 'Y' AND grade > 85) OR honors = 'N')); 3) 当前行数据, 它受该表中已有行的约束 ; 4) 当前行数据, 它受其它表中已有行的约束 ;
使用触发器实现比较后两张业务规则约束 ( 相对复杂 ) 触发器表 : Table Name Table Type Operation Type Field Name Trigger Type Trigger Name Trigger Code enroll table INSERT null BEFORE enroll_i nsert teacher table UPDATE salary AFTER salary_ update...... staff view INSERT null INSTEAD OF transfer _insert staff view DELETE null INSTEAD OF transfer _delete
用触发器来实现后两类相对复杂的业务规则 Thread: while (1) { 约束 waitforclientrequest(request); try { 解析请求 : 操作类型, 表, 字段 ; 对更新操作, 查触发器表 : if ( 有 BEFORE 触发器 ) 分别调用它们 ; if ( 有 INSTEAD OF 触发器 ) 分别调用它们 else 执行请求操作 ; } } if ( 有 AFTER 触发器 ) 分别调用它们 ; catch exception(e) {... }
使用触发器实现每学期选课不超过 25 学分
实现每个房间的预订不重叠冲突
实现对视图执行更新操作 CREATE VIEW staff (deptname, e_no,name,job, birthday) AS SELECT dname,e_no,emp.name, title, bdate FROM emp, dept WHERE emp.d_no = dept.dno AND DeptName = Accounting';
实现对视图执行更新操作
使用触发器来执行审计 CREATE TRIGGER cheatingemployee AFTER UPDATE OF salary ON Emp REFERENCING OLD ROW AS old NEW ROW AS new FOR EACH ROW WHEN (@new.salary > @old.salary*1.1) INSERT INTO auditemp VALUES (@new.eno, date(now()), @new.salary, @old.salary); 事件 条件 动作
业务规则的表达测试 1) 老师中, 职称级别低的工资不能高于职级高的老师的工资. 2) 当员工表中的额某个员工被删除时, 该员工在 workon 表中 相应的记录也要求一同删除.
删除或者修改中的一个对象 DROP TABLE emp; ALTER TABLE emp..; DROP VIEW staff; ALTER VIEW staff..; DROP PROCEDURE add_enroll; ALTER PROCEDURE add_enroll; DROP TRIGGER staff_insert; ALTER TRIGGER staff_insert;
域约束外键约束约束完整性约束主务规则触发器业约束关系数据库模型的知识图谱 权限管理 对象权限用户授权收权 审计踪迹记录 应用程序屏蔽编程可视化数据 存储过程 屏蔽 SQL 个性化数据 视图屏蔽联接业务数据 键SQL 表达
数据库中的对象 数据库设计人员 SQL DDL 创建 CREATE; 删除 DROP; 修改 ALTER; 应用程序 ( 视图和存储过程 ) 1 存储过程 2 视图 3 表 4 触发器 ( 完整性约束 ) 5 用户 ( 权限管理 ) 6 索引数据库管理系统 (DBMS) 数据库使用人员 SQL DML SELECT; DELETE; UPDATE; INSERT; Statistics; CALL; 什么时候定义成视图? 什么时候定义成存储过 程?
第六次作业 希尔顿是一跨国旅馆经营公司, 其数据库系统中有如下 4 个表 : Hotel (hotelno, name, city) Room (roomno, hotelno, type, price) Booking (hotelno, guestid, datefrom, dateto, roomno) Guest (guestid, name, guestcity, creditcard) 1) 使用 SQL 的 DDL 写出 Booking 表的定义, 要求至少包含一条业务 规则约束 ; 书上 :7.11;8.11;8.12;
测试题 希尔顿是一跨国旅馆经营公司, 其数据库系统中有如下 4 个表 : Hotel (hotelno, name, city) Room (roomno, hotelno, type, price) Booking (hotelno, guestid, datefrom, dateto, roomno) Guest (guestid, name, guestcity, creditcard) 1) 使用 SQL 的 DDL 写出 Booking 表的定义, 要求至少包含一条业务 规则约束 ; 2) 在 Booking 表中, 某一旅馆的某一个房间在某一天不能出现重复预 定的情况 今天是 2014/04/09, 某个客户想要在 hotelno 为 01 的旅馆预定从 2014/4/10 至 2014/4/12 的一个 双人间 房 间, 请列出可供他选择的房间号 (roomno); 3) 今天是 2012/04/09, 问昨天每个旅馆的空房损失分别是多少? 4) 用触发器实现订房不能出现重叠的业务规则约束 ;