实验五 实体参照完整性以及索引的建立和使用

5.1 实体完整性

1）在数据库School中建立表Stu_Union，进行主键约束，在没有违反实体完整性的前提下插入并更新一条记录

use School

create table Stu_Union(

Sno char(8)not null unique,

Sname char(8),

Ssex char(1),

Sage int,

constraint PK_Stu_Union primary key(Sno)

)

insert Stu_Union values('456','小二','M','22')

update Stu_Union set Sno=''WHERE Sage='22'

update Stu_Union set Sno='7'where Sname='小二'

select * from Stu_Union

2）演示违反实体完整性的插入操作

use School

insert Stu_Union values('7','小三','M','23')

3）演示违反实体完整性的更新操作

use School

update Stu_Union set Sno=NULL where Sno='7'

4）演示事务的处理，包括事务的建立，处理以及出错时的事务回滚,演示事务处理和批处理的区别。

提示：SQL2005相关语句为

BEGIN TRAN

ROLLBACK TRAN

COMMIT TRAN

可以这样演示：新建一个包含两条语句的事务，使第一条成功而第二条失败，然后查看整个事务是否回滚。

重要提示：SQL默认只回滚出错的语句，要回滚整个事务，需要预先执行以下语句：

SET XACT_ABORT ON

事务的建立

use School

set xact_abort on

begin transaction t1

insert into Stu_Union values('001','张三','M','22')

insert into Stu_Union values('002','李四','F','24')

insert into Stu_Union values('003','李四','F','24')

select * from Stu_Union

commit transaction t1

处理以及出错时的回滚

use School

set xact_abort on

begin transaction t2

insert into Stu_Union values('004','王五','M','23')

select * from Stu_Union

insert into Stu_Union values('7','钱六','F','21')

commit transaction t2

use School

select * from Stu_Union

5）通过建立Scholarship表，插入一些数据。演示当与现有的数据环境不等时，无法建立实体完整性以及参照完整性。

提示：即演示不能为表Scholarship生成主键和外键的情况。

1)建立并插入数据

use School

create table Scholarship(

M_id varchar(10),

Stu_id char(8),

R_Money int,

)

insert into Scholarship values ('M001','004','2000')

insert into Scholarship values ('M002','005','3000')

select * from Scholarship

2）相关演示

use School

alter table Scholarship add

consraint PK_Scholarship primary key(M_id)

3）无法建立

use School

alter table Scholarship add

constraint FK_Scholarship foreign key(Stu_id) references

Students(Sno)

5.2参照完整性

1）为演示参照完整性，建立表Course，令cno为其主键，并在Stu_Union中插入数据。为下面的实验步骤做预先准备。

create table Course(

    Cno CHAR(4) NOT NULL UNIQUE,

    Cname VARCHAR(50) NOT NULL,

    Cpoints INT,

    CONSTRAINT PK PRIMARY KEY(Cno));

insert Course values('C001','C++',2);

insert Course values('C002','Java',3);

2）建立表sc，另sno和cno分别为参照Stu_Union表以及Course表的外键，设定为级连删除，并令(sno, cno)为其主键。在不违反参照完整性的前提下，插入数据。

use School

create table SC(

Sno CHAR(8),

Cno CHAR(4),

Scredit INT,

constraint PK_SC primary key(Sno,Cno),

constraint FK_SC_Sno foreign key(Sno) references Stu_Union (Sno) on delete cascade,

constraint FK_SC_Cno foreign key(Cno) references Course (Cno) on delete cascade

);

INSERT INTO SC VALUES('002','C001',2);

INSERT INTO SC VALUES ('002','C002',2);

INSERT INTO SC VALUES ('001','C001',2);

INSERT INTO SC VALUES ('001','C002',2);

SELECT * FROM SC;

3）演示违反参照完整性的插入数据

use School

insert into SC values('099','C099',2)

4）在Stu_Union中删除数据，演示级连删除。

use School

delete from Stu_Union where Sno='001'

select * from SC;

5）Course中删除数据，演示级连删除 。

use School

delete from Course where Cno='C002'

select * from SC;

6）为了演示多重级连删除，建立Stu_Card表，令stu_id为参照Stu_Union表的外键，令card_id为其主键，并插入数据。

use School

create table Stu_Card(

Card_id CHAR(14),

Sno CHAR(3),

Constraint PK_Stu_Card primary key(Card_id),

Constraint FK_Stu_Card_Sno foreign key(Sno) references Students(Sno) on delete cascade

)

insert into Stu_Card values('05212567','003')

insert into Stu_Card values('05212222','7')

select * from Stu_card;

7）为了演示多重级连删除，建立ICBC_Card表，令stu_card_id为参照Stu_Card表的外键，令bank_id为其主键，并插入数据。

use School

create table ICBC_Card(

Bank_id CHAR(20),

Stu_card_id CHAR(14),

Restored_money DECIMAL(10,2),

constraint PK_ICBC_Card PRIMARY KEY(Bank_id),

constraint FK_ICBC_Card_Stu_id FOREIGN KEY(Stu_card_id) references Stu_card(card_id) on delete cascade

)

insert into ICBC_Card values('9558844022312','05212567',15000)

insert into ICBC_Card values('95588440235','05212222',50000)

select * from ICBC_Card;

8）通过删除stu_union表中的一条记录，演示三个表的多重级连删除。

delete Stu_Union where Sno='003'

select * from Stu_card;

select * from ICBC_Card;

9）演示事务中进行多重级连删除失败的处理。修改ICBC_Card表的外键属性，使其变为On delete No action, 演示事务中通过删除stu_union表中的一条记录，多重级连删除失败，整个事务回滚到事务的初始状态。

1）修改

alter table ICBC_Card

drop constraint FK_ICBC_Card_Stu_id;

alter table ICBC_Card

ADD constraint FK_ICBC_Card_Stu_id FOREIGN KEY (Stu_card_id)

references Stu_card(Card_id) on delete no action;

2）删除

delete Stu_Union where Sno='002'

select * from Stu_card;

select * from ICBC_Card

delete Stu_Union where Sno='7'

select * from Stu_card;

select * from ICBC_Card

5.3索引的建立和作用

1.实验目的

学会在SQL SERVER中建立索引

通过本实验体会覆盖索引的作用，在以后的实践中，能适时地使用覆盖索引来提高数据库的性能。

通过实验体会聚簇索引的优缺点，学会根据具体情况创建聚簇索引

2.实验内容

1）STUDENTS(sid,sname,email,grade)在sname上建立聚簇索引，grade上建立非聚簇索引，并分析所遇到的问题

建立非聚簇索引

create index stusname on students(sname)

建立聚簇索引

create clustered index PK_STUDENTS on students(sid)

   所遇到的问题：

   建立聚簇索引出错，出错信息为“不能在表‘students’上创建多个聚簇索引，请在创建新聚簇索引前去掉现有的聚簇索引‘PK_students_2e1bdc42’”

   这是因为在sql建表表示默认为主键建立聚簇索引。所以我们常常在建表时显示取消主键的聚簇索引。所以我们应该先删除先前的索引再建立新的索引。

2）数据库SCHOOL的选课表CHOICES有如下结构：

CHOICES(no，sid，tid，cid，score)

假设选课表集中用于查询分析，经常执行统计某课程修读的学生人数查询访问

要求：

A．首先执行没有索引的实验（设数据库CHOICES表在cid列上没有索引）

select sid from CHOICES

where sid in (

select sid from COURSES 

where cname='java'

)

B．然后做有索引的实验

首先创建索引

create index choices_stu on choices(cid)

select sid from CHOICES

where sid in (

select sid from COURSES 

where cname='java'

)

C．对比试验结果，并进行分析

有索引的，一般是索引扫描或聚集索引扫描 （seek）

没有索引的，是表扫描。

3）以数据库SCHOOL中CHOICES表为例，设建表时考虑到以后经常有一个用sid查询此学生所有选课信息的查询，考虑到一般学生不止选一门课，且要询问这些记录的所有信息，故在sid上建立索引，使相同sid的记录存在一起，取数据页面时能一起取出来，减少数据页面的存取次数

要求：

A．首先执行没有任何索引的情况

use School

set statistics io on

go 

set statistics time on

go 

select * from CHOICES where sid like '800554358'

go

B．在sid上建有非聚簇索引的情况

use School

if not exists(select name from sysindexes

where name='IX_sid')

create index IX_sid

on choices(sid)

go

set statistics io on

go 

set statistics time on

go 

select * from CHOICES where sid like '800554358'

go

C．在sid上建有聚簇索引的情况

use School

if exists(select name from sysindexes

where name='IX_sid')

drop index choices.IX_sid

go

create clustered index IX_sid

on choices(sid)

go

set statistics io on

go 

set statistics time on

go 

select * from CHOICES where sid like '800554358'

go

D．对比实验结果，并进行分析

5.4 触发器的建立和使用

实验目的：通过实验使学生加深对数据完整性的理解，学会创建和使用触发器

实验内容：

1）创建Teacher（Tno，Tname，Tsex，Tage，Tdept）表，并自定义2个约束U1和U2，其中U1规定Tname字段唯一，U2规定Tage上限是28.为Teacher表建立触发器T1，当插入或更新表中的数据时，保证所操作的记录的Tage值大于0，并插入几条数据.

1)创建Teacher表

create table Teacher(

Tno char(5),

Tname char(8) constraint U1 unique,

Tsex char(2),

Tage int constraint U2 check(Tage<=28),

Tdept char(20),

constraint PK_Teacher primary key(Tno)

);

2）为Teacher表建立触发器T1

create trigger T1 on Teacher for insert,update

as

if(select Tage from inserted)<1

begin

print'年龄Tage必须大于，插入或更新失败'

rollback transaction

end

3）插入几条数据并验证

insert into Teacher values('0001','张三','男','-1','财务部')

4）禁用触发器后验证

alter table Teacher disable trigger T1

insert into Teacher values('0001','张三','男','-1','财务部')

select * from Teacher

2）为Teacher表建立触发器T2，禁止删除编号s01的校长。

create trigger T2 on Teacher for delete

as

declare @number_id char(5)

if(select Tno from deleted)='0001'

begin

print'不能删除编号的记录'

rollback transaction

end

验证

delete from Teacher where Tno='0001'

禁用后验证

alter table Teacher disable trigger T2

delete from Teacher where Tno='0001'

select * from Teacher

3）Teacher表中的人员的编号是唯一且不可变更的，创建触发器实现更新中编号的不可改变性。

create trigger T3 on Teacher for update

as

if update(Tno)

begin

print'不能更改编号'

rollback transaction

end

验证

insert into Teacher values('0001','张三','男','22','财务部')

update Teacher

set Tno='0002'where Tno='0001'

禁用触发器后验证

alter table Teacher disable trigger T3

update Teacher

set Tno='0002'where Tno='0001'

select * from Teacher

4）设置一个触发器，该触发器仅允许“dbo”用户可以删除Teacher表内的数据，否则出错。

create trigger T4 on Teacher for delete 

as

begin

if user='dbo'

   commit

else

   begin

      print'不是dbo用户，不能删除！'

      rollback

   end

end

5）设置一个针对Teacher表的触发器，当有人更新Teachder表时，触发器将自动将该操作者的名称和操作时间记录在一张表内，以便追查。

create table mylog(

id int not null primary key,

uid char (10) not null,

operatedata datetime not null,

type varchar(20) not null

)

select * from mylog、

create trigger T5 on Teacher for delete

as

begin

declare @id int,@maxID int

select @maxID=isnull (max(id),0) from mylog

select @maxID=@maxID+1

insert into mylog

values(@maxID,current_user,getdate(),'delete')

end