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

1、/* 完成表中约束的定义*/- -创建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 n

2、umber<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表添加

3、参照完整性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 cas

4、cade, 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的INSERTS Delete权限授予用户张三，并允许他再把此权限授予其 他用户。Grant insert,delete on student to 张三 wit

5、h 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 se

6、lect 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

7、 studentafter insertasupdate class set number=number+1from class,inserted where =- -创建delete 触发器，适用于student 表的单行数据的删除create trigger stu_delete on studentafter deleteasupdate classset number=number-1from class,deletedwhere =- -将insert 和 delete 写入一个触发器内，适用于student 表的单行数据的添加或删除create trigger tri_stuon

8、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')- -验证触发器，删

9、除数据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(*)

10、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-声明游标-打开游标-获取数据/* 0 操作成功，-1 FETCH 语句失-关闭游标-释放游标

11、open mycursorfetch next from mycursor into sno,clno while(fetch_status =0 )败或此行不在结果集中，-2 被提取的行不存在*/beginupdate classset number=number+1 where clno = clnofetch next from mycursor into sno,clnoendclose mycursordeallocate mycursorendelsebegindeclare mycursor cursor for select sno,clno from deletedopen

12、mycursorfetch next from mycursor into sno,clnowhile(fetch_status = 0)beginupdate classset number = number -1 where clno=clnofetch next from mycursor into sno,clnoendclose mycursordeallocate mycursorend end-为class表再建一更新触发器：当更新班长学号时，检查新输入的学号是否为同一班级的学生学号，若不是，给出适当的提示信息。create trigger stu_updateon classa

13、fter update asif 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'

14、;-创建商品表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 )-设计触发器，当新增商品时，自动生成该商品在所有仓库的库存记录，库存数量为0create trigger tri_producton product after insertasbegindeclare pno char(3)select pno=pno from insertedinsert into whproducts