杭電數據庫張紅娟編第4章習題參考答案

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 n

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

3、參照完整性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 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 表創(chuàng)建一觸發(fā)器：當向表中插入或刪除記錄時，修改 Class表中相應班級的人數。*/- -創(chuàng)建insert 觸發(fā)器，適用于student 表的單行數據的添加create trigger stu_inserton

7、 studentafter insertasupdate class set number=number+1from class,inserted where =- -創(chuàng)建delete 觸發(fā)器，適用于student 表的單行數據的刪除create trigger stu_delete on studentafter deleteasupdate classset number=number-1from class,deletedwhere =- -將insert 和 delete 寫入一個觸發(fā)器內，適用于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)- -驗證觸發(fā)器，添加數據insert into studentvalues ('2222','tom',' 男 ',20,'00311')- -驗證觸發(fā)器，刪

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表再建一更新觸發(fā)器：當更新班長學號時，檢查新輸入的學號是否為同一班級的學生學號，若不是，給出適當的提示信息。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-驗證觸發(fā)器執(zhí)行update classset monitor = '2001104'where clno = '00312'

14、;-創(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) references warehouse(whno) on delete no action on update cascade, pno char(6) references product(pno) on delete cascade on update cascade, number int )-設計觸發(fā)器，當新增商品時，自動生成該商品在所有倉庫的庫存記錄，庫存數量為0create trigger tri_producton product after insertasbegindeclare pno char(3)select pno=pno from insertedinsert into whproducts