实验4

实验四：数据完整性

1、实验目的

（1）了解SQL Serer数据库系统中数据完整性控制的基本方法 （2）熟练掌握常用CREATE 或ALTER 在创建或修改表时设置约束 （3）了解触发器的机制和使用 （4）验证数据库系统数据完整性控制

2、实验平台

使用SQL Server数据库管理系统提供的SSMS和查询编辑器。

3 实验内容及要求

结合ST数据库中的各个表，设置相关的约束，要求包括主键约束、外键约束、唯一约束、检查约束、非空约束等，掌握各约束的定义方法。

设置一个触发器，实现学生选课总学分的完整性控制，了解触发器的工作机制。 设计一些示例数据，验证完整性检查机制。 要求包括如下方面的内容：

3.1 使用SQL语句设置约束

使用CREATE或ALTER语句完成如下的操作，包括： 1． 设置各表的主键约束 2． 设置相关表的外键

3. 设置相关属性的非空约束、默认值约束、唯一约束 4. 设置相关属性的CHECK约束

3.2 使用触发器

创建一个触发器，实现如下的完整性约束：

SC Student

学号 010001 010002 010003 姓名 王林 程明 王玉民 … … 总学分 ７ ３ ４ … .. … 学号 010001 010001 010002 … 课程号 106 206 106 … 成绩 80 90 70 … 学分 3 4 3 … … … … ? 当向SC表中插入一行数据时，自动将学分累加到总学分中。 ? 记录修改学分的操作。

3.4 检查约束和触发器

分别向相关表插入若干条记录，检查你设置的完整性约束是否有效：

1． 插入若干条包含正确数据的记录，检查插入情况

2． 分别针对设置的各个约束，插入违反约束的数据，检查操作能否进行 3． 向SC表插入若干行数据，检查触发器能否实现其数据一致性功能。

４ 实验报告

要求写出实验的基本过程。解释操作过程中出现的现象。

参考示例：

建立一个学生选课数据库，熟悉约束及触发器的使用方法。

一、声明完整性约束

创建学生选课数据库TEST，包括三个基本表，其中Student表保存学生基本信息，Course

表保存课程信息，SC表保存学生选课信息，其结构如下表：

表1. Student表结构

列名称 Sno Sname Ssex Sage Sdept Sclass Stotal 用途 学号 姓名 性别 年龄 所在系 班级 总学分 类型 字符 字符 字符 整型 字符 字符 整型 8 8 2 20 4 长度 约束 主键 非空 {男，女} 非空 默认值0 备注 表2. Course表结构

列名称 Cno Cname Cpno Ccredit 用途 课程号 课程名 先修课程号 学分 类型 字符 字符 字符 整型 长度 4 40 4 约束 主键 备注 非空，唯一 外键 引用本表的Cno 表3. SC表结构

列名称 Sno Cno Grade

用途 学号 课程号 成绩 类型 字符 字符 整型 长度 8 4 约束 备注 主码属性，外键 引用Student的Sno 主码属性，外键 引用Course的Cno 0<=成绩<=100 1．创建基本表及约束 （1）创建Student表

CREATE TABLE Student

( Sno CHAR(8) PRIMARY KEY, Sname CHAR(8) NOT NULL,

Ssex CHAR(2) CHECK( Ssex in ('男','女')), Sage SMALLINT, Sdept CHAR(20),

Sclass CHAR(4) NOT NULL,

Stotal smallint DEFAULT 0 );

? 掌握主键约束、非空约束、CHECK约束、默认值的定义格式。

（2）创建Course表

CREATE TABLE Course

( Cno CHAR(4) CONSTRAINT FK_Course PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4) , Ccredit SMALLINT );

（3）创建SC表

CREATE TABLE SC

( Sno CHAR(8) FOREIGN KEY (Sno) REFERENCES Student(Sno), Cno CHAR(4),

Grade SMALLINT CONSTRAINT SC_CHECK CHECK(Grade >0 AND Grade<100), PRIMARY KEY (Sno,Cno),

CONSTRAINT FK_SC FOREIGN KEY (Cno) REFERENCES Course(Cno) );

? 掌握多个属性的主键约束、外键约束、CHECK约束的定义格式。 ? 掌握约束的命名方式。

（4）检查表是否创建成功。 2． 插入数据

将如下表格中的数据分别插入到数据库相应的表中：

表4.学生基本信息表

学号 20100001 20100002 姓名 李勇 刘晨 性别 男 女 年龄 20 19 所在系 CS CS 班级 1001 1001 总学分 0 0 20100021 20100031 20100003 20100010 20100022 王敏 张立 刘洋 赵斌 张明明 女 男 女 男 男 18 19 19 19 MA IS IS CS 1002 1003 1001 1005 1002 0 0 0 0 0 表5.课程信息表

课程号 1 2 3 4 5 6 7 课程名 数据库系统原理 高等数学 管理信息系统 操作系统原理 数据结构 数据处理 C语言 先修课程号 56 1 6 7 表6.学生选课信息表

学号 20100001 20100001 20100001 20100002 20100002 20100003 200100010 （1）插入学生信息到Student表

INSERT INTO Student VALUES('20100001','李勇','男',20,'CS','1001'，0) INSERT INTO Student VALUES('20100002','刘晨','女',19,'CS','1001'，0) 请写出插入其余行的插入语句，并插入数据。 （2）插入到课程信息到Course表

INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('1','数据库系统原理', '5',4) INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('2','高等数学', null,2) 请写出插入其余行的插入语句，并插入数据。

课程号 1 2 3 1 2 1 3 成绩 92 85 88 90 80 学分 4 2 4 3 4 2 4

（3）插入到SC表

INSERT INTO SC VALUES('20100001','1',92) INSERT INTO SC VALUES('20100002','2',80) 请写出插入其余行的插入语句，并运行。 （4）检查插入到表中的数据 3． 修改约束

对数据库中已经存在的表，可对其增加约束或修改已存在的约束： （1）添加约束

ALTER TABLE Course ADD UNIQUE(Cname)

ALTER TABLE Course ADD FOREIGN KEY (Cpno) REFERENCES Course(Cno)

? 掌握如何添加约束 ? 掌握如何声明唯一约束

（2）修改约束

ALTER TABLE SC DROP CONSTRAINT SC_CHECK

ALTER TABLE SC ADD CONSTRAINT SC_CHECK CHECK(Grade >=0 AND Grade<=100)

? 掌握如何修改约束 ? 理解命名约束的优点

二、检查完整性约束

通过修改数据库中的数据检查完整性约束条件的作用。 1．检查主键约束

（1）执行下面的语句修改Student表，观察语句能否正确运行，解释为什么？ INSERT INTO Student VALUES('20100101','李斌','男',20,'CS','1001',0) INSERT INTO Student VALUES('20100001','李斌','男',20,'CS','1001',0) UPDATE Student SET Sno='20100021' WHERE Sname = '张立'

（2）执行下面的语句修改SC表，观察语句能否正确运行，解释为什么？ INSERT INTO SC VALUES('20100001','1',78) INSERT INTO SC VALUES('20100001',null,78) 2．检查唯一约束

执行下面的语句修改Course表，观察语句能否正确运行，解释为什么？ INSERT INTO Course VALUES('8','JAVA',7, 3) INSERT INTO Course VALUES('9','数据结构',7, 3)

3．检查默认值、允许空值列

运行如下的语句：

INSERT INTO Student(Sno,Sname,Ssex,Sclass) VALUES('20100102','张盛','男','1008') 观察插入数据行的数值

SELECT * FROM Student WHere Sno='20100102' 4．检查非空约束

下面的语句包含空值，检查运行结果，解释为什么？

INSERT INTO Student(Sno,Sname,Ssex,Sclass) VALUES('20100103','张盛','男',NULL) INSERT INTO Student(Sno,Sname,Ssex) VALUES('20100104','张盛','男') 5．检查CHECK约束

执行下面的语句，解释其运行结果。

INSERT INTO SC VALUES('20100001','4',95) INSERT INTO SC VALUES('20100001','4',102)

INSERT INTO Student(Sno,Sname,Ssex,Sclass) VALUES('20100103','张盛','男','1008') INSERT INTO Student(Sno,Sname,Ssex,Sclass) VALUES('20100104','张盛','','1008') 6．检查外键约束

（1）执行下面的语句检查外键约束的作用 INSERT INTO SC VALUES('20100301','1',95) INSERT INTO SC VALUES('20100001','10',95) UPDATE SC SET Cno = '10' Where Cno='1' UPDATE Course SET Cno = '10' Where Cno='3' （2）执行下面的语句检查对被引用表的约束

DELETE Student WHERE Sno='20100021' DELETE Student WHERE Sno='20100001'

UPDATE Course SET Cno = '10' WHERE Cname = '数据库系统原理'

三、触发器的定义及使用

1．定义触发器

（1）定义一个触发器，实现有关学分的完整性约束：当向SC表插入一行选课记录时，自动将该课程的学分累加到该学生的总学分中。 CREATE TRIGGER tr_INSERT ON SC FOR INSERT AS --声明变量 DECLARE @sno char(8)

GO

DECLARE @cridit int DECLARE @cno char(4) --提取插入的数据

SELECT @sno=Sno,@cno=Cno FROM inserted --提取学生的总学分

SELECT @cridit = Ccredit FROM SC join Course ON (SC.Cno = Course.Cno) WHERE SC.Cno = @cno --更新总学分

UPDATE Student SET Stotal = Stotal + @cridit WHERE Sno = @sno

（2）定义一个触发器，实现对SC表的操作登记：当用户向SC表插入或修改时，记录该操作到数据库中。

? 创建日志登记表:

CREATE TABLE LOG_TABLE ( username char(10), --操作人员 date datetime, --修改时间 Sno char(8) , --学生学号 Cno char(4) --课程号 )

? 创建日志登记触发器:

CREATE TRIGGER tr_UPDATE ON SC FOR INSERT,UPDATE AS DECLARE @sno char(8) DECLARE @cno char(4) DECLARE @new smallint SELECT @sno=Sno,@cno=Cno FROM inserted

INSERT INTO LOG_TABLE VALUES(CURRENT_USER,getdate(),@Sno,@Cno); GO

（3）执行插入操作，触发触发器： INSERT INTO SC VALUES('20100001','6',95) （4）验证触发器是否触发 select * from student select * from LOG_TABLE

本文来源：https://www.bwwdw.com/article/h333.html