数据库实验5实验报告

淮海工学院计算机工程学院实验报告书
课程名：《数据库原理及应用》
题目：数据库的完整性
班级：软件132
学号：2013122907
姓名：莹莹
一．目的与要求
1.掌握索引创建和删除的方法；
2.掌握创建视图和使用视图的方法；
3.掌握完整性约束的定义方法，包括primary key、foreign key等。

二．实验容
1．基于前面建立的factory数据库，使用T-SQL语句在worker表的“部门号”列上创建一个非聚集索引，若该索引已经存在，则删除后重建。

2．在salary表的“职工号”和“日期”列创建聚集索引，并且强制唯一性。

3．建立视图view1，查询所有职工的职工号、、部门名和2004年2月工资，并按部门名顺序排列。

4．建立视图view2，查询所有职工的职工号、和平均工资；
5．建立视图view3，查询各部门名和该部门的所有职工平均工资；
6．显示视图view3的定义；
7．实施worker表的“性别”列默认值为“男”的约束；
8．实施salary表的“工资”列值限定在0~9999的约束；
9．实施depart表的“部门号”列值唯一的非聚集索引的约束；
10．为worker表建立外键“部门号”，参考表depart的“部门号”列。

11．建立一个规则sex：性别=’男’ OR 性别=’女’，将其绑定到“性别”上；
12．删除上面第7、8、9和10建立的约束；
13．解除第11题所建立的绑定并删除规则sex。

三．实验步骤
1
USE factory
GO
--判断是否存在depno索引；若存在,则删除之
IF EXISTS(SELECT name FROM sysindexes WHERE name='depno')
DROP INDEX worker.depno
GO
--创建depno索引
CREATE INDEX depno ON worker(部门号)
GO
EXEC sp_helpindex worker
GO
2
USE factory
GO
--判断是否存在no_date索引；若存在,则删除之
IF EXISTS(SELECT name FROM sysindexes WHERE name='no_date')
DROP INDEX salary.no_date
GO
--创建no_date索引
CREATE UNIQUE CLUSTERED INDEX no_date ON salary(职工号,日期)
GO
EXEC sp_helpindex salary
GO
3
USE factory
GO
--如果视图viewl存在，则删除
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'view1' AND type='V') DROP VIEW view1
GO
--创建视图viewl
CREATE VIEW view1
AS SELECT TOP 15 worker.职工号,worker.,depart.部门名,
salary.工资AS '2004年月工资'
FROM worker,depart,salary
WHERE worker.部门号=depart.部门号AND worker.职工号=salary.职工号AND YEAR(salary.日期)=2004 AND MONTH(salary.日期)=2
ORDER BY worker.部门号
GO
SELECT * FROM view1
GO
4
USE factory
GO
--如果视图view2存在，则删除
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'view2' AND type='V') DROP VIEW view2
GO
--创建视图view2
CREATE VIEW view2
AS SELECT worker.职工号,worker.,AVG(salary.工资) AS '平均工资'
FROM worker,salary
WHERE worker.职工号=salary.职工号
GROUP BY worker.职工号,worker.
GO
SELECT * FROM view2
GO
5
USE factory
GO
--如果视图view3存在，则删除
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'view3' AND type='V') DROP VIEW view3
GO
--创建视图view3
CREATE VIEW view3
AS SELECT depart.部门名,AVG(salary.工资) AS '平均工资'
FROM worker,depart,salary
WHERE worker.部门号=depart.部门号AND worker.职工号=salary.职工号GROUP BY depart.部门名
GO
SELECT * FROM view3
GO
6
USE factory
GO
EXEC sp_helptext 'view3'
GO
7
USE factory
GO
ALTER TABLE worker
ADD CONSTRAINT default_sex DEFAULT '男' FOR 性别
GO
8
USE factory
GO
ALTER TABLE salary
ADD CONSTRAINT check_salary CHECK(工资>0 AND 工资<9999)
GO
9
USE factory
GO
ALTER TABLE depart
ADD CONSTRAINT unique_depart1 UNIQUE NONCLUSTERED(部门号) GO
EXEC sp_helpindex depart --显示depart表上的索引
GO
10
USE factory
GO
ALTER TABLE worker
ADD CONSTRAINT FK_worker_no
FOREIGN KEY(部门号)
REFERENCES depart(部门号)
GO
11
USE factory
GO
CREATE RULE sex AS 性别='男' OR 性别='女'
GO
EXEC sp_bindrule 'sex','worker.性别'
GO
12
USE factory
GO
ALTER TABLE worker
DROP CONSTRAINT default_sex
GO
13
USE factory
GO
ALTER TABLE salary
DROP CONSTRAINT check_salary
GO
14
USE factory
GO
ALTER TABLE depart
DROP CONSTRAINT unique_depart
GO
15
USE factory
GO
ALTER TABLE worker
DROP CONSTRAINT FK_worker_no
GO
16
USE factory
GO
EXEC sp_unbindrule 'worker.性别'
GO
DROP RULE sex
GO
四．测试数据与实验结果
第1题图
第2题图
第3题图
第4题图
第5题图
第6题图
第9题图
五．结果分析与实验体会
定义聚集索引键时使用的列越少越好，这一点很重要。

如果定义了一个大型的聚集索引键，则同一个表上定义的任何非聚集索引都将增大许多，因为非聚集索引条目包含聚集键。

当前数据库正在备份时不能再其上创建索引。

若要使用T-SQL修改PRIMARY KEY，必须先删除现有的PRIMARY KEY约束，然后再用新定义重新创建。

删除UNIQUE约束，以删除对约束中所包含列或列组合输入值的惟一性要求。

如果相关列是表的全文键，则不能删除UNIQUE约束。

规则是一个向后兼容的功能，用于执行一些与CHECK约束相同的功能。

CHECK约束是用来限制列值的首选标准方法。

CHECK约束比规则更简明，一个列只能应用一个规则，但是却可以应用多个CHECK约束。

CHECK约束作为CREATE TABLE语句的一部分进行指定，而规则以单独的对象创建，然后绑定到列上。