数据完整性实验

实验五：数据完整性实验

一、实验目的：

掌握使用SQL中的PRIMARY KEY、CHECK、FOREIGN KEY……REFERENCES、NOT NULL、UNIQUE等关键字是现实体完整性、参照完整性及用户定义完整性约束定义。二、实验步骤：

（一）验证分析部分

1、利用SQL查询分析器用PRIMARY KEY子句保证实体完整性。

在查询分析器窗体下键入如下命令：

CREATE TABLE Student(

sno CHAR(5)NOT NULL UNIQUE,

sname CHAR(8),

ssex CHAR(1),

sage INT,

sdept CHAR(20)，

CONSTRAINT PK_Student PRIMARY KEY(sno));

UPDATE Student SET sno=’‘WHERE sdept=’CS’;

UPDATE Student SET sno=’200215121‘WHERE sname=’王敏’;

运行并观察结果。

2、利用SQL查询分析器用FOREIGN KEY……REFERENCES子句保证实体完整性。

在查询分析器窗体下键入如下命令：

(1)先删除原来SC表中关于Sno的外键，再将SC的Sno外键修改为：

alter TABLE SC add CONSTRAINT FK_Sno FOREIGN KEY(sno)

REFERENCES Student(sno)

ON update CASCADE;

然后执行以下语句

Update Student SET sno=’200215128‘WHERE sno=’200215121’;

(2)先删除原来SC表中关于Sno的外键，再将SC的Sno外键修改为：

alter TABLE SC add CONSTRAINT FK_Sno FOREIGN KEY(sno)

REFERENCES Student(sno)

ON delete CASCADE;

然后执行以下语句

delete from student WHERE sno=’200215122’;

运行并观察结果。

3、利用SQL查询分析器用短语NOT NULL、UNIQUE、CHECK保证用户定义完整性。

CREATE TABLE Student(

sno CHAR(5),

sname CHAR(8)CONSTRAINT U1UNIQUE,

ssex CHAR(1),

sage INT CONSTRAINT U2CHECK FOR sage<=28,

sdept CHAR(20)，

CONSTRAINT PK_Student PRIMARY KEY(sno))

执行以下SQL语句

Insert into values(‘2002101’,’li’,’女’,89,’DS’)

运行并观察结果。思考：如果要求sdept输入内容只能为字母该如何约束？

（二）设计分析部分

在Student数据库中，完成以下完整性约束设置。

1、设置Student表中Ssex的取值只能是‘男’或‘女’；

2、设置Student表中Sdept的取值默认为‘CS’；

3、删除操作测试

（1）设置Course表和SC表之间的关系，实现cascade delete related records。

（2）删除Course表中记录，查看SC表中数据的变化（SC表中Cno有与Course表中Cno对应的记录删除和无对应记录的删除都要测试），有什么结果，为什么？

4.修改操作测试

（1）在SC表中，将课程号为3的改为33，都有什么结果？为什么？

（2）将Course表和sc表的课程号为3的都改为33，请用完整性约束实现？

5.插入数据测试

（1）在STUDENT表中插入一条新纪录：

95006李三男21IS

（2）在SC表中插入一条新纪录：

95008380

插入执行后都有什么结果？为什么？如何完成上述操作？

拓展练习

（1）创建表worker（number,name,sex,sage,department），并自定义2个约束U1和U2，其中U1规定name字段唯一，U2规定sage字段的上限是28即年龄最大28岁。

（2）在worker表中插入一条合法记录。

（3）演示插入违反U2约束的例子，U2规定元组的sage属性的值必须<=28。

（4）去除U2约束。

（5）重新插入（3）中想要插入的数据，验证是否插入成功。

三、实验学时

2学时

四、实验设备与环境

Windows2003平台+SQL Server2005系统

--建立test数据库

create database test

on

(name=student,

filename='d:\db\student.MDF',

size=10MB,

maxsize=50MB,

filegrowth=1MB

);

use test;

--建立student表

create table student

(Sno char(10)primary key,

Sname char(10)unique,

Ssex char(2),

Sage int,

Sdept varchar(10)

);

--建立course表

create table course

(Cno char(4)primary key,

Cname char(20),

Cpno char(4),

Ccredit int,

foreign key(Cpno)references course(Cno)

);

--建立SC表

create table SC

(Sno char(10),