杭电数据库张红娟编第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)