杭电数据库张红娟编第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 classadd 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_inserton studentafter insertasupdate classset number=number+1from class,insertedwhere class.clno = inserted.clno--创建delete触发器，适用于student表的单行数据的删除create trigger stu_deleteon studentafter deleteasupdate classset number=number-1from class,deletedwhere class.clno = deleted.clno--将insert和delete写入一个触发器内，适用于student表的单行数据的添加或删除create trigger tri_stuon studentafter insert,deleteasif update(sno)update classset number=number+1where clno = (select clno from inserted)else update classset number=number-1where clno = (select clno from deleted)--验证触发器，添加数据insert into studentvalues ('2222','tom','男',20,'00311')--验证触发器，删除数据delete from studentwhere sno='2222'--假设向student表添加或删除的多行数据都来自同一个班级create trigger tri_stu2on studentafter insert,deleteasif update(sno)update classset number=number+(select count(*) from inserted)where clno = (select clno from inserted)else update classset number=number-(select count(*) from inserted)where clno = (select clno from deleted)----适用于student表的多行数据的添加或删除（最靠谱解决方案）create trigger tri_stu2on studentafter insert,deleteasbegindeclare @sno char(7),@clno char(5)if update(sno)begindeclare 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 被提取的行不存在*/beginupdate classset number=number+1where clno = @clnofetch next from mycursor into @sno,@clnoendclose mycursor --关闭游标deallocate mycursor --释放游标endelsebegindeclare mycursor cursor for select sno,clno from deletedopen mycursorfetch next from mycursor into @sno,@clnowhile(@@fetch_status = 0)beginupdate classset number = number -1where clno=@clnofetch next from mycursor into @sno,@clnoendclose mycursordeallocate mycursorendend--为class表再建一更新触发器：当更新班长学号时，检查新输入的学号是否为同一班级的学生学号，若不是，给出适当的提示信息。

create trigger stu_updateon classafter updateasif update(monitor)if ( select monitor from inserted ) not in( select sno from studentwhere clno = (select clno from deleted ) ) beginprint 'there is not the new monitor in the class'rollback transactionend--验证触发器执行update classset 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_producton productafter insertasbegindeclare @pno char(3)select @pno=pno from insertedinsert into whproductselect whno,@pno,0from warehouseend----设计触发器，当新增仓库时，自动生成该仓库在所有商品的库存记录，库存数量为0 create trigger tri_warehouseon warehouseafter insertasbegindeclare @whno char(6)select @whno=whno from insertedinsert into whproductselect @whno,pno,0from productend。