杭电数据库张红娟编第4章习题参考答案

/* 完成表中约束的定义 */ --创建course表

create table course (

cno char(1) primary key,

cname varchar(20) not null,

credit smallint check（credit>=1 and credit<=6） )

--创建class表 create table class (

clno char(5) primary key,

speciality varchar(20) not null,

inyear char(4) not null,

number integer check(number>1 and number<100), monitor char(7) )

--创建student表 create table student3 (

sno char(7) primary key, sname varchar(20) not null,

ssex char(2) not null default('男'), sage smallint check(sage>14 and sage<65),

clno char(5) not null references class(clno) on delete cascade on update cascade )

--为class表添加参照完整性 alter table class

add constraint fk_monitor foreign key (monitor) references student(sno) on delete no action

--创建grade表 create table grade (

sno char(7) not null references student(sno) on delete cascade on update cascade, cno char(1) not null references course(cno) on delete cascade on update cascade, gmark decimal(4,1) check(gmark>0 and gmark <100), primary key (sno,cno) )

/* 针对成绩管理数据库中的表，完成以下操作：*/ --（1）用户张勇对Student表和Course表有Select权力。 Grant select on student to 张勇 Grant select on course to 张勇

--（2）把对表Student的INSERT和Delete权限授予用户张三，并允许他再把此权限授予其他用户。

Grant insert,delete on student to 张三 with grant option

--（3）把查询Course表和修改属性Credit的权限授给用户李四。 Grant select,update(credit) on course to 李四

--（4）授予用户李勇敏对Student表的所有权力（读、插、删、改），并具有给其他用户授权的权力。

Grant all privilege on student to 李勇敏 with grant option

--（5）撤销（1）中对张勇所授予的所有权力。 Revoke select on student to 张勇 Revoke select on course to 张勇

或：Revoke select on student from 张勇 Revoke select on course from 张勇

--（6）撤销（2）中对张三所授予的所有权力。 revoke insert,delete on student to 张三cascade 或revoke insert,delete on student from 张三cascade

/* 为成绩管理数据库中的Student表创建一触发器：当向表中插入或删除记录时，修改Class表中相应班级的人数。*/

--创建insert触发器，适用于student表的单行数据的添加 create trigger stu_insert on student after insert as

update class

set number=number+1 from class,inserted

where class.clno = inserted.clno

--创建delete触发器，适用于student表的单行数据的删除 create trigger stu_delete on student after delete

as

update class

set number=number-1 from class,deleted

where class.clno = deleted.clno

--将insert和delete写入一个触发器内，适用于student表的单行数据的添加或删除 create trigger tri_stu on student after insert,delete as

if update(sno)

update class

set number=number+1

where clno = (select clno from inserted) else update class

set number=number-1

where clno = (select clno from deleted)

--验证触发器，添加数据 insert into student

values ('2222','tom','男',20,'00311') --验证触发器，删除数据 delete from student where sno='2222'

--假设向student表添加或删除的多行数据都来自同一个班级 create trigger tri_stu2 on student

after insert,delete as

if update(sno)

update class

set number=number+(select count(*) from inserted) where clno = (select clno from inserted) else update class

set number=number-(select count(*) from inserted) where clno = (select clno from deleted)

----适用于student表的多行数据的添加或删除（最靠谱解决方案） create trigger tri_stu2 on student

after insert,delete as begin

declare @sno char(7),@clno char(5)

if update(sno) begin

declare mycursor cursor for select sno,clno from inserted --声明游标 open mycursor --打开游标 fetch next from mycursor into @sno,@clno --获取数据

while(@@fetch_status =0 ) /* 0操作成功，-1 FETCH 语句失败或此行不在结果集中，-2 被提取的行不存在 */ begin

update class

set number=number+1 where clno = @clno

fetch next from mycursor into @sno,@clno

end

close mycursor --关闭游标 deallocate mycursor --释放游标 end else

begin

declare mycursor cursor for select sno,clno from deleted open mycursor

fetch next from mycursor into @sno,@clno while(@@fetch_status = 0) begin

update class

set number = number -1

where clno=@clno

fetch next from mycursor into @sno,@clno end

close mycursor deallocate mycursor end end

--为class表再建一更新触发器：当更新班长学号时，检查新输入的学号是否为同一班级的学生学号，若不是，给出适当的提示信息。 create trigger stu_update on class after update as

if update(monitor)

if ( select monitor from inserted ) not in ( select sno from student

where clno = (select clno from deleted ) )

begin print 'there is not the new monitor in the class' rollback transaction end

--验证触发器执行 update class

set monitor = '2001104' where clno = '00312'

--创建商品表

create table product

( pno char(6) primary key, pname varchar(20) not null, price decimal(7,2) )

--创建仓库表

create table warehouse

( whno char(3) primary key, whname varchar(20) not null, whaddress varchar(20) )

--创建库存商品表 create table whproduct

( whno char(3) references warehouse(whno) on delete no action on update cascade, pno char(6) references product(pno) on delete cascade on update cascade, number int )

--设计触发器，当新增商品时，自动生成该商品在所有仓库的库存记录，库存数量为0 create trigger tri_product on product after insert as begin

declare @pno char(3) select @pno=pno from inserted insert into whproduct select whno,@pno,0 from warehouse end

----设计触发器，当新增仓库时，自动生成该仓库在所有商品的库存记录，库存数量为0 create trigger tri_warehouse on warehouse after insert as begin

declare @whno char(6)

select @whno=whno from inserted insert into whproduct select @whno,pno,0 from product end

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