第五次实验内容

实验5 数据完整性实验

【实验目的】

1、掌握SQL Server中的声明完整性定义

2、掌握SQL Server中的过程完整性定义

【实验内容】

1.在企业管理器中还原student数据库，还原数据库所用的文件为studentbak

2.声明完整性实验：

在数据库中创建以下表格，除数据类型和not null外，不指定其他任何类型的约束。

student_new

字段名称类型长度是否

空

sno varchar 10 not

null sname varchar 10

ssex varchar 2

sage tinyint

sclass varchar 10

smajor varchar 20

course_new

字段名称类型长度是否

空

cno varchar 10 not

null cname varchar 20

ccredit decimal

sc_new

字段名称类型长度是否

空

sno varchar 10 not

null cno varchar 10 not

null grade tinyint

create table student_new(

sno varchar (10) not null,

sname varchar(10),

ssex varchar(2),

sage tinyint,

sclass varchar (10),

smajor varchar (20)

)

create table course_new(

cno varchar (10) not null,

cname varchar (20) ,

ccredit decimal

)

create table sc_new(

sno varchar (10)not null,

cno varchar (10)not null,

grade tinyint

)

为表格添加以下约束：

①为三张表添加主键约束

alter table student_new

add constraint pk_student_new

primary key (sno)

alter table course_new

add constraint pk_course_new

primary key (cno)

alter table sc_new

add constraint pk_sc_new

primary key (sno)

②为ssex列添加check约束，规定必须为‘男’或者‘女’alter table student_new

add constraint chk_ssex

check(ssex='男'or ssex='女')

③为sage列添加check约束，规定必须大于0，并且小于100。alter table student_new

add constraint chk_sage

check(sage>0 and sage<100)

④为sc_new表添加外键约束

alter table sc_new

add constraint fk_sc_new

foreign key(sno) references student_new(sno)

⑤为grade添加默认约束，默认值为0

alter table sc_new

add constraint df_grade

default 0 for grade

⑥为sname和cname添加唯一性约束

alter table student_new

add constraint uk_sname

unique (sname)

alter table course_new

add constraint uk_cname

unique (cname)

3、过程完成性实验

①在SC表上添加触发器tr1，限制将不及格学生的成绩改为及格

create trigger tr1

on sc for update

as if update(grade)

if exists(select * from inserted join deleted on

inserted.sno=deleted.sno

where inserted.grade>=60 and deleted.grade<60)

rollback

②在SC表上添加触发器tr2，限制每名同学选课的总学分不能超过36。create trigger tr2

on sc for insert,update

as if exists(select *from course join sc on https://www.360docs.net/doc/cf15089606.html,o=https://www.360docs.net/doc/cf15089606.html,o

where sno in (select sno from inserted) group by sno

having sum (ccredit)>36)

begin

print '学分不超过36'

rollback

end

③在SC表上添加触发器tr3，当每门课程的选课人数不足20人时，不能再继续删课

alter trigger tri3

on sc for delete

as

declare @x int

select @x=count(sno) from sc where cno in (select cno from deleted)

if @x<20

rollback

4、选做实验

在SC上添加以下触发器tr4，限制选课时间冲突的情况，并进行验证。

-------------------------------------------------------------

create view v1

as

select sc.sno,sc.grade,tc.*

from sc join tc on https://www.360docs.net/doc/cf15089606.html,o = https://www.360docs.net/doc/cf15089606.html,o and sc.teacher = tc.teacher

go

-------------------------------------------------------------

create function jiaoji(@s1 int, @w1 int, @s2 int, @w2 int)

returns int

as

--如果区间s1,w1和s2,w2有交集，则返回1，否则返回0

begin

declare @x int

if(@w1<@s2 or @w2<@s1)

set @x = 0

else

set @x = 1

return @x

end

go

-------------------------------------------------------------

create trigger tr4

on sc

for insert

as

begin

declare @classtime varchar(20)

declare @sno varchar(10)

declare tmp cursor for

select sno,classtime

from inserted join tc on https://www.360docs.net/doc/cf15089606.html,o = https://www.360docs.net/doc/cf15089606.html,o and

inserted.teacher = tc.teacher

open tmp

fetch next from tmp into @sno,@classtime

while @@fetch_status=0

begin

declare cur cursor for select cast(startweek as int), cast(endweek as int), odd_even from v1

where sno = @sno and classtime = @classtime

declare @s1 int

declare @w1 int

declare @x1 varchar(10)

declare @x int

open cur

fetch next from cur into @s1,@w1, @x1

while @@fetch_status=0

begin

select @x = count(*) from v1

where sno = @sno and classtime = @classtime and

dbo.jiaoji(@s1,@w1,startweek,endweek) = 1 and

charindex(isnull(@x1,'单双'),isnull(odd_even,'单双')) > 0

print '======================================'

print 'sno='+@sno+' and classtime='+@classtime

print '起始周=' + cast(@s1 as varchar(10))

print '结束周=' + cast(@w1 as varchar(10))

print '此时间的课程数目'+ cast(@x as varchar(10))

if (@x >= 2)

begin

print '选课时间冲突，rollback'

rollback

break

end

fetch next from cur into @s1,@w1, @x1

end

close cur

deallocate cur

fetch next from tmp into @sno,@classtime

end

close tmp

deallocate tmp

end

go