(2版)数据库实验14 数据完整性及答案.doc

实验十四 数据完整性实验一、实验目的及要求通过本实验目的是掌握保障数据完整性的各种措施及其使用方式，能根据现实需要为数据库中数据设置各种完整性约束，保障数据库的完整性和一致性。l 理解数据的完整性的概念及实施数据完整性的重要性。l 掌握约束的概念及实施方法。l 掌握规则的概念及实施方法。l 掌握默认值的概念及实施方法。l 掌握参照完整性的含义及其实施方法。二、实验原理及背景知识（一）了解数据完整性概念数据完整性是指数据的精确性和可靠性，是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作的措施。完整性分为：实体完整性、参照完整性和用户自定义完整性。（二）SQL Server对完整性的保障措施1、主键：保障实体完整性。2、外键：通过外键关系保障参照完整性。3、自定义完整性（1）约束：对字段进行域进行限制的，主要有：UNIQUE、check约束、NOT NULL约束。（2）规则对象：可以实现比约束复杂的约束，需要创建并绑定列使用。（3）默认值对象：便于数据输入，需要创建并绑定使用。4、触发器是数据库对象，可以用来保障数据库完整性，本实验不涉及，可参照实验十一。（三）数据库关系图用来体现数据库中各种表之间关系的对象，也用来设置并体现所有的参照完整性。三、实验内容及步骤（一）实体完整性设置SQL Server 2005中实体完整性通过设置主键来实现。具体设置方法参照实验二中内容。主键默认建立聚簇索引，关于主键索引问题参照实验七中的相关内容。（二）SQL Server参照完整性设置1、定位需要建立外键关系的外键所在表，然后进入表设计界面。在需要建立外键关系的字段上点击右键，选择“关系”，即可进入外键关系编辑器。点击“添加”即可添加一个外键关系，然后进行相应属性设置即可，如实验图14-1所示。实验图14-1 创建外键界面 实验图14-2 设置主外键关系2、在外键关系编辑窗口的右侧属性中，主要设置“标识”中的“名称”和“表和列规范”中信息。在设置“表和列规范”时不能直接在下方修改，需要点击“表和列规范”行后面的浏览按钮，进入表和列窗口进行选择设置，如上图14-2所示。所有设置完成后点击“关闭”按钮既可以完成。3、最后还需要设置外键规则，在实验图14-3所示的窗口中展开“INSERT和UPDATE规范”，然后进行设置。层叠表示级联操作，当主键值删除之后，对应的外键记录也删除；当主键值更新之后，对应的外键值也自动更新。实验图14-3 设置外键关系属性界面4、使用SQL语句设置外键约束（三）数据表级自定义完整性设置1、设置惟一约束（以student表中sName字段为例）（1）表设计器中，右击需要设置为唯一约束的字段sName，选择“索引/键”，也可以直接单击工具栏中的“管理索引/键”按钮进入“索引/键”属性对话框，如实验图14-4所示。（2）在索引/键设计器中点击“添加”按钮，建立新的设置，并在右侧的属性框中分别输入信息：“名称”为IX_student_sname；“列”选择sName；“是唯一的”选择是。其中列的选择利用右边的浏览按钮在弹出的“索引列”窗口中进行选择，如实验图14-5所示。完成所有设置后点击关闭即可完成唯一约束设置。 实验图14-4 创建唯一性约束界面 实验图14-5 选择唯一性索引列（3）在查询编辑窗口中依次执行下列两条SQL 语句，检验新设置的惟一约束，观察并思考结果。答：第一句的执行结果：违反了PRIMARY KEY 约束PK_student。不能在对象dbo.student 中插入重复键。 原因是：在student表中插入该数据时原表中已有次数据； 第二句的执行结果是成功插入数据；（4）使用SQL语句设置惟一约束。2、设置检查约束（以student_course表中mark字段为例）（1）在student_course表设计器中右击“mark”字段，在弹出的菜单中选择“CHECK 约束”，弹出的空白窗口中点击添加按钮，添加一个新CHECK约束。然后在右侧属性窗口中分别设置名称和表达式两项，如实验图14-6所示。完成属性编辑后点击“关闭”按钮即可完成检查约束的设置。（2）在查询分析器中检验上面设置的检查约束，可执行下列SQL语句，观察结果。答：INSERT 语句与CHECK 约束CK_student_course冲突。该冲突发生于数据库stuManage，表dbo.student_course, column Mark。 实验图14-6 创建CHECK约束 实验图14-7 设置默认值（3）使用SQL 语句设置检查约束。3、设置默认约束（以student表中sSex字段为例）（1）在表设计器中选择字段sSex，然后在下面的属性编辑窗口中，找到“默认值或绑定”属性，在后面输入“男”即可完成默认值约束设置，如上图14-7所示。（2）在查询分析器中利用下列语句验证默认值。（3）使用SQL语句设置默认约束。（四）数据库级自定义完整性设置1、使用SQL语句管理默认值对象（1）创建默认值（2）绑定默认值若将默认对象default_sex 绑定到数据表student 的sSex 字段上，语句如下：（3）解除默认值的绑定（4）删除默认值注意：在删除默认对象前，必须先解除默认值的绑定。2、使用SQL 语句管理规则（1）创建规则创建一个学生性别只能为“男”或“女”的规则，其SQL 语句如下：（2）绑定规则（3）解除规则的绑定（4）删除规则（五）创建数据库关系图1、展开stuManage数据库，用鼠标右键单击“数据库关系图”。在弹出的菜单中选择“新建数据库关系图”项，就会弹出“添加表”对话框，如实验图14-8所示。实验图14-8 添加表 实验图14-9 数据库关系图2、在“添加表”对话框中逐一添加表或者利用Ctrl和Shift键多选后一次性添加，添加完成后关闭“添加表”对话框，即可进入关系图编辑器，如实验图14-9所示，此时大多数表之间是没有联系的。3、要建立表之间的关联，通过拉线和填写关联对话框的方法解决。比如：先选中student的主键字段“sNO”项（点击最左侧按钮选择），然后按下鼠标左键拖动鼠标向student_course表拉出一条线。这时屏幕上就会出现如实验图14-10所示的建立表间关联对话框。在建立关联对话框中，选择外码和被参照表的主码，单击“确定”按钮。这时就会进入如实验图14-11所示的外键关系编辑器窗口，修改名称后点击“确定”按钮即可回到关系图编辑器，此时会发现两个表之间已经多了一条连线。同理，再建立其他表间的关联。 实验图14-10 主外键对应字段 实验图14-11 外键关系属性4、用鼠标单击表中的列，则弹出一个菜单。通过选择选项可以进行对当前选中表的增加列、建立主码、建立表间关系、从关系图移除等操作。5、用鼠标右键单击对话框的空白处，则会弹出一个弹出菜单。通过选择弹出菜单中的增加表、删除表和显示关系标签等选项，可完成相应的操作功能。6、当关系表编辑完毕后，关闭编辑窗口，在随后出现的关系表命名的对话框中输入图表名。7、用鼠标单击联系，可以通过左侧属性窗口修改连线所代表的外键关系，如果右键单击连线则可以删除该关系，同时数据表直接的参照完整性也被删除了。8、全部连线建立后就可以关闭并保存该关系图。四、实验测验1、为stuManage数据库创建一个规则，限制所输入的数据为8位09的数字。（1） 创建一个规则phone_rule，限制所输入的数据为8位09的数字。实现该规则的代码为答：rule（2）使用系统存储过程sp_bindrule将phone_rule规则绑定到department表的“dTel”列上。实现该操作的代码为答：sp_bindrule（3）输入以下代码，进行一次插入操作： 观察结果并思考原因！ 答：消息513，级别16，状态0，第1 行列的插入或更新与先前的CREATE RULE 语句所指定的规则发生冲突。该语句已终止。冲突发生于数据库stuManage，表dbo.department，列dTel。2、请创建一个grade_rule的规则，用来限定成绩只能在0100之间，然后绑定到student_course表的四种成绩字段上。答：创建规则;Create rule grade_rule As mark like 0-1000-1000-100 绑定规则：sp_bindrule grade_rule, student_course.mark ,student_course.normark,student_course.exammark3、为数据库中所有表创建外键关系，并生成数据库关系图。答： alter table student add constraint fk_sno_studentforeig