数据库完整性--断言--触发器实验

一、实验目的： （1）理解和掌握数据库完整性–断言–触发器，能够使用SQL语句对数据库完整性–断言–触发器实验操作。 （2）掌握SQL语句常见语法错误的调试方法。
二、实验内容： 按照下列要求操作，并截取相应的结果图。要求设计测试数据进行实体完整性、参照完整性和用户定义完整性的检查和违约处理。
1、按照教材158页例5.1至例5.13、例5.18至例5.23的要求操作

5.1.1 定义实体完整性
关系模型的实体完整性在CREATE TABLE中用PRIMARY KEY定义码。
对单属性构成的码的两种说明方法: 1.定义为列级约束条件 2.定义为表级约束条件
【例5.1】将Student表中的Sno属性定义为码
在列级定义主码的代码为：

执行成功后，若插入的学生信息的学号有相同的学号，则会出现以下错误提示：

而在没有重复的Sno时可成功插入，所以在列级定义主码已成功。
在表级定义主码的代码：

同样，若插入的Sno有重复则会出现错误提示，而在没有重复的Sno时可成功插入，所以在表级定义主码已成功。
插入信息后查询结果为：

对多属性构成的码只有一种说明方法，即定义为表级约束条件。
【例5.2】 将SC表中的Sno、 Cno属性组定义为码。
在表级定义码的代码：
插入信息后查询结果为：

如果写成列级定义会出现错误：


5.2.1 定义参照完整性
关系模型的参照完整性在CREATE TABLE中用FOREIGNKEY短语定义哪些列为外码，用REFERENCES短语指明这些外码参照哪些表的主码。
【例5.3】 定义SC中的参照完整性。
代码为：

需要注意的是，在定义外码之前，所参照的表需要已经存在，否则会出现错误提示;
插入信息后查询结果为：

此外，根据实体完整性，Sno和Cno都不能取空值，所以Sno和Cno后面可不加NOT NULL，如下：

5.2.2参照完整性检查和违约处理
【例5.4】显式说明 参照完整性的违约处理示例。
一般地，当对参照表和被参照表定义的操作违反了参照完整性时，系统选用默认策略，即拒绝执行。如果想让系统采用其他策略则必须在创建参照表时显式地加以说明。

删除Student11表中的元组时，级联删除SC41表中的元组；
代码为：
删除前：

删除后：

更新Student11表中的元组时，级联更新SC41表中的元组：
代码为：
更新前：
更新后：
删除Course表中的元组造成与SC表不一致时，拒绝删除：
代码为：会出现错误提示，拒绝删除：

因为如果删除Course表中Cno为2的元组，而在SC表中仍有Cno为2的元组，使得Course表中的元组造成与SC表不一致，所以拒绝删除。
更新Course31表中的元组时，级联更新SC41表中的元组:
代码为：更新前：

更新后：

若不加显示说明，当对参照表和被参照表定义的操作违反了参照完整性时，系统选用默认策略，即拒绝执行。如下：

删除Student12表中的元组：

更新Student12表中的元组：

删除Course表中的元组：

更新Course表中的元组：

5.3.1 属性上的约束条件
(1)不允许取空值
【例5.5】在定义SC表时，说明Sno、 Cno、 Grade属性不允许取空值。
代码为：

由于Sno和Cno是SC51的码，根据实体完整性和参照完整性，Sno和Cno都不可能取空值，所以在列级不允许取空值的定义可不写。而Grade是非码属性，所以不能取空值时需要在列级定义注明。
Sno有空值时：


Cno有空值时：


Grade有空值时：


(2)列值唯一
【例5.6】建立部门表DEPT,要求部门名称Dname列取值唯一，部门编号Deptno列为主码。
代码为：

插入信息代码及结果：
若插入一条元组的Dname与原表重复，会拒绝执行：

若插入一条元组的Dname为空值，也会拒绝执行：


(3)用CHECK短语指定列值应该满足的条件
【例5.7】 Student 表的Ssex只允许取“男”或“女”。
代码为：
插入信息及结果：


若插入的元组的Ssex不是男或女，则拒绝执行：


【例5.8】SC表的Grade的值应该在0和100之间。
代码为：
当插入的Grade在0和100之间时，可成功插入：


当插入的Grade大于100时，会拒绝执行：


当插入的Grade小于0时，也会拒绝执行：


5.3.2 元组上的约束条件
同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件。
【例5.9】当学生的性别是男时， 其名字不能以Ms.打头。
代码为：
当Sage为男且不是以Ms.打头可成功插入：


性别是女性的元组都能通过该项CHECK检查，因为Ssex='女成立:当性别是男性时，
要通过检查则名字一定不能以Ms.打头，因为Ssex=男时，条件要想为真值，Sname NOT LIKE 'Ms.%'必须为真值。
若Ssex是男且Sname是以Ms.打头，会拒绝执行报错：


但当性别是女时，其名字以Mr.打头可成功插入：


所以如果当性别为女时，其名字不能以Mr.打头，可再加一个约束条件：

当再次插入性别是女时，其名字以Mr.打头时，会拒绝执行：


需要注意的是，由于没有要求Sage为男时必须要以Mr.打头，及Sage为女时必须要以Ms.打头，所以以别的打头也可以成功插入：


5.4.1 完整性约束命名子句
【例5.10】建立学生登记表Student, 要求学号在90000~ 99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。
代码为:
C1、C2、C3、C4是列级完整性约束条件名，StudentKey为主码约束名。
插入信息代码及结果：


当插入的学号信息不在要求的范围内时，会拒绝执行：


当Sname为空时，会拒绝执行：


当Sage大于等于30时，会拒绝执行：


当Sage不是男或女时，会拒绝执行：


上述代码也可写成以下形式：

但这种形式不能灵活地增加、删除一个完整性约束条件。
【例5.11】建立教师表TEACHER,要求每个教师的应发工资不低于3000元。应发工资是工资列Sal与扣除项Deduct之和。
首先建立DEPT111表：
TEACHER111表代码为:

这里的要求是:Eno为TEACHER111表的主码，Deptno为外码，参照表是DEPT111，并要求Sa1+Deduct>=3000，列级完整性约束名为CT1
插入代码及其执行结果：


当不满足Sa1+Deduct>=3000时，会拒绝执行：


需要注意的是Deduct为扣除工资，依具体情况定正负号，在这里满足的要求是Sa1+Deduct>=3000，所以Deduct应该为负号。

5.4.2 修改表中的完整性限制
【例5.12】去掉例5.10 Student表中对性别的限制。
代码为：
执行成功后再插入以下代码，可成功插入：


【例5.13】修改表Student 中的约束条件，要求学号改为在900 000~999 999之间，年龄由小于30改为小于40。
可以先删除原来的约束条件，再增加新的约束条件
代码为：

当插入的Sno不在900000~999999范围内而在原来的范围内时，会拒绝执行：


当插入的Sno在900000~999999范围内时，可成功插入：

由此验证了Sno的范围已成功修改。
当插入信息的Sage>30时可成功插入：

而Sage超过40时，拒绝执行;


由此验证了Sage的取值范围已成功修改。
【例5.18】限制数据库课程最多60名学生选修。

由于版本不支持断言，所以改为用触发器实现。且60不容易验证，这里改成4，代码为：
SC181表中原有三条记录：
在SC181中插入一条新记录，可成功插入，此时共有4名同学选修数据库课程：

当再插入一条记录时，因为选修数据库的人数就会超过4，此时触发器被激活，删掉此次插入的记录，SC181表中仍为4条记录：


【例5.19】限制每一门课程最多60名学生选修。
断言代码：

触发器代码，这里改为2方便验证：

SC表中选修课程2的学生共有2名

当再插入一条选修课程2的学生的信息时，此时选修2号课程的学生超过了2名，学生将不能再选修2号课程：

但仍可插入选修别的课程的学生信息：


当然选修1号课程的学生数量仍不可超过2人

方法二：

第一个方法用的是FOR INSERT，先把记录插入表中，触发器内再进行判断，如果人数超过了60名，就把刚插入的记录删掉。第二个方法用的是INSTEAD OF INSERT，触发器内先判断表里的每一门记录是否等于60，表里记录不满60，就把新记录插入到表中。当记录等于60后，新记录不再插入表。
【例5.20】限制每个学期每一门课程最多 60名学生选修。首先修改SC表的模式，增加一个“学期(TERM)”的属性。
先修改SC表，增加TERM属性，它的类型是DATE：

断言代码：

触发器代码：

插入以下代码时，可成功执行，且每学期每一门课程的第四条记录无法插入：


而当插入时出现了以下错误提示：

这是因为学号和课程号是SC的码，但学期不是。所以新插入的
和表内已经存在的记录重复。

所以我们需要让TERM也是SC的一个码，可以直接在创建SC表时定义

这样我们再次插入时就可以成功插入：

方法二：
方法二同样需要先将Term定义为码。
【例5.21】当对表SC的Grade属性进行修改时，若分数增加了10%， 则将此次操作记录到另一个表SC_U (Sno、Cno、 Oldgrade、 Newgrade) 中，其中Oldgrade是修改前的分数，Newgrade 是修改后的分数。
首先创建SC_U表：


由于版本不支持书上的代码，所以选择另一种方法实现：

deleted 表和 inserted 表:
触发器语句中使用了两种特殊的表：deleted 表和inserted 表。SQL Server自动创建和管理这些表。可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件，但不能对这两个表中的数据进行更改。
（1）deleted 表 用于存储 DELETE 和 UPDATE 语句所影响的行的备份。在执行 DELETE 或 UPDATE 语句时，行从基本表中删除，并转移到 deleted 表中。deleted 表和基本表通常没有相同的行。
（2）inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的备份。在一个插入或更新事务处理中，新建行被同时添加到 inserted 表和基本表中。inserted表中的行是基本表中新行的备份。
在设置触发器条件时，应恰当使用deleted 表和inserted 表。通常在插入数据时，可以从inserted 表中读取新插入的值；在删除数据时，可以从deleted 表中读取已经删除的值；在更新数据时，可以从deleted 表中读取原有的值，从inserted 表中读取修改后的值。
插入数据后的SC表：

把SC表中201215122同学选修3号课程的成绩80改为91后：
代码为：执行成功会出现以下提示：
此时SC表中的成绩已成功修改：
由于91>801.1,所以在SC_U表中会增加这条记录：
把SC表中201215121同学选修1号课程的成绩92改为95后：


此时SC表中的成绩已成功修改：
但由于95不大于921.1，所以SC_U表中不会增添这条记录：

【例5.22】将每次对表Student 的插入操作所增加的学生个数记录到表Student-InsertLog中。
首先创建Student_InsertLog表：

创建触发器：

最初Student表：
当插入一条记录后：

在Student表中已成功插入：
这是第1次插入1条记录，所以在Student_InsertLog表中显示：

当再次插入三条记录时:

在Student表中已成功插入:

这是第2次插入3条记录，所以在Student_InsertLog表中显示：

【例5.23】定义一个BEFORE行级触发器，为教师表Teacher 定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元”。
代码为：
当插入的Sal<4000时，出现了以下错误提示：


这是因为在TEACHER231表的定义中，完整性约束中要求sal>=4000，不满足要求，便拒绝执行，无法继续执行后面的操作。

所以应该将完整性约束条件删去。
修改完毕后，当教授的sal>4000时可成功插入：

当插入一条sal=3000的记录时：

查询TEACHER232表，可发现李华教授的sal已成功由3000改为40000.

当把张明教授的Sal修改为2500时，执行结果变成了2500：
代码为：
修改前：
修改后:
这说明触发器仅对插入操作起作用，对修改操作不起作用。
修改后代码：

再次对张明教授的Sal修改后的代码及结果：

(图片一张张复制过来太难了，有没有什么好的方法？)