杭電數(shù)據(jù)庫張紅娟編第4章習(xí)題參考答案

1、/* 完成表中約束的定義*/- -創(chuàng)建 course 表create table course(cno char(1) primary key,cname varchar(20) not null,credit smallint check ( credit>=1 and credit<=6 )一創(chuàng)建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

2、number<100),monitor char(7)- -創(chuàng)建 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)- -為 clas

3、s 表添加參照完整性alter table classadd constraint fk_monitor foreign key (monitor) references student(sno) on delete no action- -創(chuàng)建 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 upda

4、te cascade, gmark decimal(4,1) check(gmark>0 and gmark <100), primary key (sno,cno)/* 針對成績管理數(shù)據(jù)庫中的表，完成以下操作： */- -（ 1 ）用戶張勇對Student 表和 Course 表有 Select 權(quán)力。Grant select on student to 張勇Grant select on course to 張勇- -（2）把對表Student的INSERTS Delete權(quán)限授予用戶張三，并允許他再把此權(quán)限授予其 他用戶。Grant insert,delete on stud

5、ent to 張三 with grant option- -（3）把查詢Course表和修改屬性 Credit的權(quán)限授給用戶李四。Grant select,update（credit） on course to 李四- -（ 4 ）授予用戶李勇敏對Student 表的所有權(quán)力（讀、插、刪、改） ，并具有給其他用戶授權(quán)的權(quán)力。Grant all privilege on student to 李勇敏 with grant option- -（ 5 ）撤銷（1 ）中對張勇所授予的所有權(quán)力。Revoke select on student to 張勇Revoke select on course t

6、o 張勇或： Revoke select on student from 張勇Revoke select on course from 張勇- -（ 6 ）撤銷（2 ）中對張三所授予的所有權(quán)力。revoke insert,delete on student to 張三 cascade 或 revoke insert,delete on student from 張三 cascade/* 為成績管理數(shù)據(jù)庫中的 Student 表創(chuàng)建一觸發(fā)器： 當向表中插入或刪除記錄時， 修改 Class 表中相應(yīng)班級的人數(shù)。 */- -創(chuàng)建insert 觸發(fā)器，適用于student 表的單行數(shù)據(jù)的添加creat

7、e trigger stu_inserton student after insertasupdate classset number=number+1from class,inserted where =- -創(chuàng)建delete 觸發(fā)器，適用于student 表的單行數(shù)據(jù)的刪除create trigger stu_delete on studentafter deleteasupdate classset number=number-1from class,deletedwhere =- -將 insert 和 delete 寫入一個觸發(fā)器內(nèi)，適用于 student 表的單行數(shù)據(jù)的添加或刪除c

8、reate 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)- -驗證觸發(fā)器，添加數(shù)據(jù)insert into studentvalues ('2222','tom',' 男',20,

9、9;00311')- -驗證觸發(fā)器，刪除數(shù)據(jù)delete from studentwhere sno='2222'- -假設(shè)向student 表添加或刪除的多行數(shù)據(jù)都來自同一個班級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=n

10、umber-(select count(*) from inserted)where clno = (select clno from deleted)適用于 student 表的多行數(shù)據(jù)的添加或刪除(最靠譜解決方案)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-聲明游標-打開游標-獲取數(shù)據(jù)/* 0 操作成功

11、， -1 FETCH 語句失-關(guān)閉游標-釋放游標open mycursorfetch next from mycursor into sno,clno while(fetch_status =0 )敗或此行不在結(jié)果集中， -2 被提取的行不存在 */beginupdate classset number=number+1 where clno = clnofetch next from mycursor into sno,clnoendclose mycursordeallocate mycursorendelsebegindeclare mycursor cursor for select

12、sno,clno from deletedopen 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 表再建一更新觸發(fā)器： 當更新班長學(xué)號時， 檢查新輸入的學(xué)號是否為同一班級的學(xué) 生學(xué)號，若不是，給出適當?shù)奶崾拘畔?。c

13、reate trigger stu_update on classafter 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- -驗證觸發(fā)器執(zhí)行update classset monitor = '200

14、1104'where clno = '00312'- -創(chuàng)建商品表create table product( pno char(6) primary key, pname varchar(20) not null, price decimal(7,2) )-創(chuàng)建倉庫表 create table warehouse( whno char(3) primary key,whname varchar(20) not null, whaddress varchar(20) )-創(chuàng)建庫存商品表create table whproduct( whno char(3) referen

15、ces warehouse(whno) on delete no action on update cascade, pno char(6) references product(pno) on delete cascade on update cascade, number int )-設(shè)計觸發(fā)器，當新增商品時，自動生成該商品在所有倉庫的庫存記錄，庫存數(shù)量為 0 create trigger tri_producton product after insertasbegindeclare pno char(3)select pno=pno from insertedinsert into whproduct