河南中医药大学：《数据库原理》课程教学资源（实验指导）实验八 数据库完整性约束

实验8数据库完整性约束 、实验目的 1.掌握主键约束、外键约束、唯一值约束的用法; 2.掌握 Check约束、默认值约束的用法 3.了解存储过程的使用方法 4.了解触发器的使用方法 二、实验环境 1.硬件 每位学生配备计算机一台。 2.软件 Windows Server2003操作系统,安装 SQL Server2008企业版软件。 3.网络 局域网环境 4.工具 无 三、实验理论与预备知识 1.数据库完整性约束规则; 2.实体完整性的实现方法 3.参照完整性的实现方法 4.域完整性的实现方法; 5.存储过程的类型及创建方法,执行存储过程的语法格式 6.触发器的类型,创建触发器的语法格式。 四、实验任务 1.在创建数据表的同时实现完整性约束 2.通过修改数据表实现完整性约束

实验 8 数据库完整性约束 一、实验目的 1. 掌握主键约束、外键约束、唯一值约束的用法； 2. 掌握 Check 约束、默认值约束的用法； 3. 了解存储过程的使用方法； 4. 了解触发器的使用方法。 二、实验环境 1. 硬件 每位学生配备计算机一台。 2. 软件 Windows Server 2003 操作系统，安装 SQL Server 2008 企业版软件。 3. 网络 局域网环境 4. 工具 无 三、实验理论与预备知识 1. 数据库完整性约束规则； 2. 实体完整性的实现方法； 3. 参照完整性的实现方法； 4. 域完整性的实现方法； 5. 存储过程的类型及创建方法，执行存储过程的语法格式； 6. 触发器的类型，创建触发器的语法格式。 四、实验任务 1. 在创建数据表的同时实现完整性约束； 2. 通过修改数据表实现完整性约束

五、实验内容及步骤 (一)在创建数据表的同时实现完整性约束 创建学生课程数据库SC,并按表1、表2、表3中的要求使用SQL语句在数据库中分别创建学 生信息表(XS)、课程信息表(KC)、选课信息表(ⅪK),充分考虑数据完整性约束条件,并使用 CONSTRAINTˆ 语句对完整性约束条件命名。 表1:学生信息表(XS) 字段名称字段类型及长度说明 备注 Char (8) 学生学号主键,且不能以0开头 Char (6) 学生姓名非空 Ssex Char (2) 学生性别可为空,只能输入“男”或“女” Int 学生年龄可为空,年龄只能在16-28岁之间 Sdept VarChar (20) 所在院系可为空 表2:课程信息表(KC) 字段名称字段类型及长度 说明 备注 Char (4) 课程编号主键,约束名为Cno_pk Cname VarChar(20)课程名称非空,唯一值 Char (4) 先行课可为空,必须包含在Cno的取值中 Ccredit 学分非空,默认值为2 表3:选课信息表(XK) 字段名称字段类型及长度说明 备注 Char (8) 学生学号主键,约束名XK_pk,外码 Cno Char(4) 课程编号|主键,约束名XKpk,外码 Grade 成绩 可为空 外码:对于选课信息表(XK)中的Sno、Cno字段定义为外码,使之与学生信息表(XS)中的 主码Sno及课程信息表(KC)中的主码Cno对应,实现如下参照完整性: 1.删除XS表中记录的同时删除XK表中与该记录Sno字段值相同的记录:

五、实验内容及步骤 （一）在创建数据表的同时实现完整性约束 创建学生课程数据库 S_C,并按表 1、表 2、表 3 中的要求使用 SQL 语句在数据库中分别创建学 生信息表（XS）、课程信息表（KC）、选课信息表（XK），充分考虑数据完整性约束条件，并使用 CONSTRAINT 语句对完整性约束条件命名。 表 1：学生信息表（XS） 字段名称 字段类型及长度 说 明 备注 Sno Char（8） 学生学号 主键，且不能以 0 开头 Sname Char（6） 学生姓名 非空 Ssex Char（2） 学生性别 可为空，只能输入“男”或“女” Sage Int 学生年龄 可为空，年龄只能在 16—28 岁之间 Sdept VarChar（20） 所在院系 可为空 表 2：课程信息表（KC） 字段名称 字段类型及长度 说 明 备注 Cno Char（4） 课程编号 主键，约束名为 Cno_pk Cname VarChar（20） 课程名称 非空，唯一值 Cpno Char（4） 先行课 可为空，必须包含在 Cno 的取值中 Ccredit Int 学分 非空，默认值为 2 表 3：选课信息表（XK） 字段名称 字段类型及长度 说 明 备注 Sno Char（8） 学生学号 主键，约束名 XK_pk，外码 * Cno Char（4） 课程编号 主键，约束名 XK_pk，外码 * Grade Int 成绩 可为空 外码*：对于选课信息表（XK）中的 Sno、Cno 字段定义为外码，使之与学生信息表（XS）中的 主码 Sno 及课程信息表（KC）中的主码 Cno 对应，实现如下参照完整性： 1. 删除 XS 表中记录的同时删除 XK 表中与该记录 Sno 字段值相同的记录；

2.修改XS表中某学生的Sno时,若XK表中有与该字段值对应的记录,则拒绝修改; 3.修改KC表中Cno字段值时,该字段在XK表中的对应值也应修改 4.删除KC表一条记录时,若该字段在XK表中存在,则删除该字段对应的记录 (二)通过修改数据表实现完整性约束 在已创建好的学生信息表(XS)、课程信息表(KC)、选课信息表(Ⅺ)中,使用 ALTER TABLE 语句,通过修改数据表的方式实现以下完整性约束 1.使用 ALTER TABLE语句为学生信息表(XS)添加一个新列SID(身份证号),并为该列定 义 UNIQUE约束,并测试该约束的有效性; 2.使用 ALTER TABLE语句对课程信息表(KC)进行修改,为其增加 Ccredit-字段的 CHECK 约束,将其取值范围限制在1至5学分之间,并测试该约束的有效性 3.使用 ALTER TABLE语句对选课信息表(XK)进行修改,为其增加 Grade字段的 CHECK 约束,将其取值范围限制在0-100之间,并测试该约束的有效性 4.删除学生信息表(XS)中Sage字段的CHCK约束 5.删除课程信息表(KC)中 Cname字段的 UNIQUE约束。 六、实验分析 1.向学生信息表(XS)中插入数据,Ssex列插入“男”和“女”以外的字符,会发生什么情 况? 2.若要修改 PRIMARY KEY、 UNIQUE约束,是否需要先删除现有的 PRIMARY KEY UNIQUE约束后重新创建? 3.定义表间参照关系后,当主码与外码发生不一致时,系统可采取哪些策略避免数据不一致? 七、课外自主实验 (一)创建存储过程 存储过程是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户可以通过指 定存储过程的名字并给出参数来执行它 以STU数据库为例,按照以下要求,创建相应的存储过程,并执行。 1.从 Student、 course、sc三个表中査询某学生指定课程的成绩和学分,并执行该存储过程 2.査询指定学生的学号、姓名、所选课程名称及该课程的成绩,并执行该存储过程 3.修改第1题中创建的存储过程,将第一个参数改成学生的学号

2. 修改 XS 表中某学生的 Sno 时，若 XK 表中有与该字段值对应的记录，则拒绝修改； 3. 修改 KC 表中 Cno 字段值时，该字段在 XK 表中的对应值也应修改； 4. 删除 KC 表一条记录时，若该字段在 XK 表中存在，则删除该字段对应的记录。 （二）通过修改数据表实现完整性约束 在已创建好的学生信息表（XS）、课程信息表（KC）、选课信息表（XK）中，使用 ALTER TABLE 语句，通过修改数据表的方式实现以下完整性约束。 1. 使用 ALTER TABLE 语句为学生信息表（XS）添加一个新列 SID（身份证号），并为该列定 义 UNIQUE 约束，并测试该约束的有效性； 2. 使用 ALTER TABLE 语句对课程信息表（KC）进行修改，为其增加 Ccredit 字段的 CHECK 约束，将其取值范围限制在 1 至 5 学分之间，并测试该约束的有效性； 3. 使用 ALTER TABLE 语句对选课信息表（XK）进行修改，为其增加 Grade 字段的 CHECK 约束，将其取值范围限制在 0-100 之间，并测试该约束的有效性； 4. 删除学生信息表（XS）中 Sage 字段的 CHECK 约束； 5. 删除课程信息表（KC）中 Cname 字段的 UNIQUE 约束。 六、实验分析 1. 向学生信息表（XS）中插入数据，Ssex 列插入“男”和“女”以外的字符，会发生什么情 况？ 2. 若要修改 PRIMARY KEY 、UNIQUE 约束，是否需要先删除现有的 PRIMARY KEY、 UNIQUE 约束后重新创建？ 3. 定义表间参照关系后，当主码与外码发生不一致时，系统可采取哪些策略避免数据不一致？ 七、课外自主实验 （一）创建存储过程 存储过程是一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中，用户可以通过指 定存储过程的名字并给出参数来执行它。 以 STU 数据库为例，按照以下要求，创建相应的存储过程，并执行。 1. 从 Student、course、sc 三个表中查询某学生指定课程的成绩和学分，并执行该存储过程； 2. 查询指定学生的学号、姓名、所选课程名称及该课程的成绩，并执行该存储过程； 3. 修改第 1 题中创建的存储过程，将第一个参数改成学生的学号；

4.删除以上创建的存储过程 (二)创建触发器 触发器是一种特殊类型的存储过程,它在指定的表中的数据发生变化时自动生效 以ST数据库为例,按照以下要求,创建触发器,并测试。 1.创建触发器,当修改 student表中的学号时,同时也要将sc表中的学号修改成相应的学号 (假设 student表和sc表之间没有定义外键约束),并测试 2.在删除 student表中的一条学生记录时将sc表中该学生的相应记录也删除(假设 student 表和sc表之间没有定义外键约束),并测试; 3.删除以上创建的触发器。 八、实验扩展资源 1.图书 (1)郑阿奇 SQL Server实用教程(第3版)( SQL Server2008版)[M]电子工业出版 社,2009 (2)闪四清 SQL Server2008基础教程[M清华大学出版社,2010 2.期刊论文 无 3.在线学习平台 无 4.其他资源 (1)http://msdn.microsoftcom/zh-cn/sqlserver (2)http://bbs.csdn.net/

4. 删除以上创建的存储过程。 （二）创建触发器 触发器是一种特殊类型的存储过程，它在指定的表中的数据发生变化时自动生效。 以 STU 数据库为例，按照以下要求，创建触发器，并测试。 1. 创建触发器，当修改 student 表中的学号时，同时也要将 sc 表中的学号修改成相应的学号 （假设 student 表和 sc 表之间没有定义外键约束），并测试； 2. 在删除 student 表中的一条学生记录时将 sc 表中该学生的相应记录也删除（假设 student 表和 sc 表之间没有定义外键约束），并测试； 3. 删除以上创建的触发器。 八、实验扩展资源 1. 图书 （1） 郑阿奇.SQL Server 实用教程（第 3 版）（SQL Server 2008 版）[M].电子工业出版 社,2009. （2） 闪四清.SQL Server 2008 基础教程 [M]. 清华大学出版社,2010. 2. 期刊论文 无 3. 在线学习平台 无 4. 其他资源 （1）http://msdn.microsoft.com/zh-cn/sqlserver （2）http://bbs.csdn.net/