数据库系统概论习题3

题目

假设有如下三个图书数据管理关系模式：

图书表book（bno, bname, author, publisher, price）其属性依次表示图书编号、书名、作者、出版社、单价；

读者表reader（cardno, rname, sex, address）其属性依次表示借书证号、姓名、性别、地址；

借阅表rb（cardno, bno, date）其属性依次表示借书证号、图书编号、借阅日期

用SQL 操作命令完成下列操作：

1. 向rb关系表中插入数据(20220006, stu00001, 2022-3-23)

2. 将所有图书的单价增加5元

3. 删除“高等教育”出版社所有图书的借阅记录

4. 在图书表上，以出版社升序建立索引，如出版社相同则按单价降序建立索引

5. 建立一个“清华大学”出版社图书的视图，视图名称为qhpublisher_view，包括图书编号、书名、作者、单价

6. 将图书表book的查询和插入权限授予用户“李明”，并允许李明继续将权限授予他人

7. 创建角色R1，将图书表book的查询和更新的权限授予该角色

8. 收回用户zhao对读者表reader中姓名（rname）的修改权限

9. 用户zhang具有查询每个出版社图书的最高单价、最低单价、平均单价的权限，但他不能查看每本书的单价。（提示：可借助视图机制）

代码：

--1. 创建“图书管理”数据库。
create database 图书管理;
 

--2. 创建图书表book，price属性的数据类型为Numeric(4,1)，其余属性的数据类型均为char(10)。
create table book(
bno char(10) primary key,
bname char(10),
author char(10),
publisher char(10),
price Numeric(4,1)
);
 
--3. 创建借阅基本表rb，其中cardno属性的数据类型为char(5)，date属性的数据类型为datetime。

--创建读者表
create table reader(
cardno char(5) primary key,
rname varchar(20),
sex char(2),
address varchar(50)
);
--创建借阅表
create table rb(
cardno char(5),
bno char(10),
date datetime,
primary key(cardno,bno),
foreign key(cardno) references reader(cardno),
foreign key(bno) references book(bno)
);

--1.  向rb关系表中插入数据(20220006, stu00001, 2022-3-23)（√）
insert 
into rb(cardno,bno,date)
values('20220006','stu00001','2022-3-23');
 

--2. 将所有图书的单价增加5元（√）
update book
set price=price+5;
 

--3. 删除“高等教育”出版社所有图书的借阅记录（√）
delete from rb
where bno =
(
select bno 
from book
where book.bno=rb.bno and publisher='高等教育'  
);

--4. 在图书表上，以出版社升序建立索引，如出版社相同则按单价降序建立索引(√)
create index bookUnique on book(publisher asc,price desc);
 

--5. 建立一个“清华大学”出版社图书的视图，视图名称为qhpublisher_view，包括图书编号、书名、作者、单价（√）
create view qhpublisher_view
as
select bno,bname,author,price
from book
where publisher='清华大学';
 

--6. 将图书表book的查询和插入权限授予用户“李明”，并允许李明继续将权限授予他人(√)

create user 李明 for login p5;

grant select,insert
on book
to 李明
with grant option;
 

--7. 创建角色R1，将图书表book的查询和更新的权限授予该角色（√）
create role R1;

grant select,update
on book
to R1;
 

--8. 收回用户zhao对读者表reader中姓名（rname）的修改权限
create user zhao for login p2;

revoke update (rname)
on reader
from zhao;

 

--9. 用户zhang具有查询每个出版社图书的最高单价、最低单价、平均单价的权限，但他不能查看每本书的单价。（提示：可借助视图机制）(√)
create user zhang for login p1;

create view bookView(publisher,maxPrice,minPrice,avgPrice)
as 
select publisher,max(price),min(price),avg(price)
from book
group by publisher;

grant select
on bookView
to zhang;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103