数据完整性实现技术（DDL）

1、4.2 数据完整性实现技术（DDL）信息科学与工程学院杨金民2020.10数据完整性的四大保障措施：主键约束；外键约束；域约束；业务规则约束；数据完整性问题DDL介绍表模式（Schema）的定义中，包含了主键，外键，域约束的定义；业务规则约束用触发器（Trigger）来定义;SQL 标识符（Identifiers）数据库中的对象，有如下类别：表，列，视图，存储过程，函数，触发器，用户，角色，索引，用户自定义数据类型。例如，在数据库中创建一个表student，准确的说法是创建表类型的一个实例对象。该实例对象的名称字段的取值为student；这样一来，表的模式(Schema), 在DBMS内部也是

2、使用表来存储，设这个表为sys_table表。用户每创建一个表，其实就是往sys_table中添加一行数据；对于某个类别，在数据库中，其实例对象都是用名称来标识。名称命名规则：以字母开头，只含字母，数字，下划线，长度不超过128；SQL基本数据类型Data Type Description BOOLEAN 布尔类型 TRUE/FALSECHAR 固定长度的字符串，如 CHAR(10)VARCHAR 变长字符串，如 VARCHAR(50)NUMERIC or DECIMAL 数值型 NUMERIC(7,2)INTEGER IntegerSMALLINT 小整型 FLOAT or REAL 浮点数

3、. DATE 日类型 TIME 时间类型 TIMESTAMP 时间戳CHARACTER LARGE OBJECT SQL3 文本大对象BINARY LARGE OBJECT SQL3 二进制大对象表模式定义语句例子CREATE TABLE Emp (eno CHAR(5),ename VARCHAR(30) NOT NULL,birthday DATE,title CHAR(2) CHECK (title IN (NULL,EE,SA,PR,ME),salary DECIMAL(9,2),superno CHAR(5),dno CHAR(5)PRIMARY KEY (eno)FOREIGN K

4、EY (dno) REFERENCES Dept(dno)ON DELETE SET NULL ON UPDATE CASCADE FOREIGN KEY (superno) REFERENCESEmp(eno);引用完整性约束所指的一个方面StudentNamestudentNosexbirthdateMike2008043101male1990/12/14Tom2008043214female1992/02/21Mary2008043332female1988/07/09EnrollstudentNocourseNosemestergrade20080433324308032010/019

5、020080431014303172009/015620080431014303172008/025620080433324300072004/027720080433324306012000/018720080431014303172002/0256在Student表中的某一行记录：1）其主关键字studentNo被修改；2）被删除；3）被插入；在Enroll表中对应的记录怎么办？引用完整性约束StudentNamestudentNosexbirthdateMike2008043101male1990/12/14Tom2008043214female1992/02/21Mary2008043

6、332female1988/07/09EnrollstudentNocourseNosemestergrade20080433324308032010/019020080431014303172009/015620080431014303172008/025620080433324300072004/027720080433324306012000/018720080431014303172002/0256在Student表中的某一行记录：1）修改的其主关键字studentNo；2）删除某一行记录；3）插入一行新记录；1) CASCADE2) SET NULL3) NO ACTION引用完整性

7、约束所指的另一个方面StudentNamestudentNosexbirthdateMike2008043101male1990/12/14Tom2008043214female1992/02/21Mary2008043332female1988/07/09EnrollstudentNocourseNosemestergrade20080433324308032010/019020080431014303172009/015620080431014303172008/025620080433324300072004/027720080433324306012000/01872008043101

8、4303172002/0256在Enroll表中:添加一行记录；某行记录的外建字段studentNo的值被修改enroll表中行记录的外建字段studentNo的值在Student中要存在；表模式创建例子CREATE TABLE Enroll (sno CHAR(12) NOT NULL,cno CHAR(8) NOT NULL,semester CHAR(7), tno CHAR(7),classNo CHAR（2）；grade SMALLINTPRIMARY KEY (sno, cno, semester)FOREIGN KEY (sno) REFERENCES student(sno)

9、ON DELETE CASCADE ON UPDATE CASCADE,FOREIGN KEY (cno) REFERENCES course(cno) ON DELETE NO ACTION ON UPDATE CASCADE FOREIGN KEY (tno) REFERENCES teacher(to) ON DELETE SET NULL ON UPDATE CASCADE );课堂练习CourseNamecourseNocredithourstextbooksyllabusdtuy_tnodnodatabaseA240608464aintroduction200421324opera

10、ting system430803464ball200100724java430317348csectionnull24写出Course表的模式定义语句 用户对某个表执行行更新元组约束 1）域约束：当前数据行，每个字段取值受其域约束：data type，取值范围，NOT NULL，UNIQUE等；2）业务规则约束当前行数据，不同字段的取值彼此之间存在关联约束；当前行数据，它受该表中已有行的约束；当前行数据，它受其它表中已有行的约束；业务规则约束的表达1）当前行数据，每个字段取值受其域约束：data type，domain，NOT NULL 等；2）当前行数据，不同字段的取值彼此之间存在关联约束

11、；例如：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）当前行数据，它受其它表中已有行的约束；使用触发器实现比较后两类业务规则约束（相对复杂）触发器表：TableNameTableTypeOperationTypeFieldNameTriggerTypeTriggerNameTriggerCodeenrolltableINSERTnull

12、BEFOREenroll_insert.teachertableUPDATEsalaryAFTERsalary_update.staffviewINSERTnullINSTEAD OFtransfer_insertstaffviewDELETEnullINSTEAD OFtransfer_delete理解业务规则约束的触发器实现机制Thread：while （1） waitForClientRequest（Request); try 解析请求：操作类型，表，字段；对更新操作，查触发器表： if (有BEFORE触发器) 分别调用它们； if (有INSTEAD OF触发器) 分别调用它们 el

13、se 执行请求操作； if (有AFTER触发器) 分别调用它们； catch exception(e) . 使用触发器实现每学期选课不超过25学分CREATE TRIGGER trigger_insertEnrollBEFORE INSERT ON enrollREFERENCING NEW ROW AS newFOR EACH ROWBEGINcredit integerSELECT SUM(credit) INTO credit FROM course WHERE courseId IN (SELECT courseId FROM enroll WHERE studentId = new

14、.studentId AND semester =new.semester) OR courseId = new.courseId;WHEN ( credit 25 ) raise_application_error(20000, 这个学期已经选修学分 + credit);END;实现每个房间的预订不重叠冲突CREATE TRIGGER trigger_insertBookingBEFORE INSERT ON bookingREFERENCING NEW ROW AS newFOR EACH ROWBEGINWHEN( EXIST SELECT hotelNo, roomNo FROM bo

15、oking WHERE hotelNo = newrow.hotelNo AND roomNo =new.roomNo AND dateTo = new.dateFrom AND dateFrom old.salary*1.1) INSERT INTO auditEmpVALUES (new.eno, date(now(), new.salary, old.salary);事件条件动作随堂测试基于触发器放入业务规则定义1）对老师表，rank的取值有：教授，副教授，助理教授。要求：职称级别低的工资不能高于职称级别高的老师的工资；2) 当员工表中的某个员工被删除时，该员工在workOn表中相应的记

16、录也要求一同删除；删除或者修改中的一个对象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视图屏蔽联接业务数据数据库中的对

17、象应用程序(视图和存储过程)存储过程视图表触发器(完整性约束）用户(权限管理）索引数据库管理系统（DBMS)SQL DDL创建 CREATE；删除 DROP；修改ALTER；数据库设计人员SQL DMLSELECT；DELETE；UPDATE；INSERT；Statistics；CALL；数据库使用人员什么时候定义成视图？什么时候定义成存储过程？阶段考试题希尔顿是一跨国旅馆经营公司，其数据库系统中有如下4个表：Hotel (hotelNo, name, city)Room (roomNo, hotelNo, type, price)Booking (hotelNo, guestId, dateFrom, dateTo, roomNo)