数据库课程设计——书店管理系统

书店会员管理系统
具体的效果图看博客—–书店管理系统2（https://blog.csdn.net/qq_45981397/article/details/124062654?spm=1001.2014.3001.5502）

1. 需求分析
   (1).信息需求
   书店的管理人员和员工可以为管理系统添加图书的购买记录和退书记录.
   会员可以购买图书以及退书，工作人员对会员的姓名，性别，身份证号码，电话号码和会员编号进行核证，以确定在购书时享受的折扣.
   管理人员可以通过姓名，购买日期，身份证号，书本编号等信息来查询会员的购书和退书记录.
   (补)
   (2) .功能需求
   书店会员管理系统，大致要满足的功能如下：
   ①. 图书购买. 会员凭借会员卡进行购买，记录会员的性别，姓名，电话号码，会员编号等……，同时工作人员协助完成购买操作，记录下购买日期。
   ②. 图书退款.当会员进行退款操作时，工作人员先记录下会员的性别，姓名，电话号码，会员编号等，同时检查是否符合退书规则，并根据退书规则收取相应的罚金。
   ③. 购买信息查询.工作人员及管理人员可以对购买信息进行查询。应支持精准查询：如通过，会员编号，会员电话号码，图书编号进行查询，也可以进行模糊查询：如通过购买日期，会员姓氏，图书类型进行查询。
   ④. 退款信息查询.工作人员及管理人员可以对退款信息进行查询。应支持精准查询：如通过会员编号，会员电话号码，图书编号进行查询，也可以进行模糊查询：如通过退书日期，会员姓氏，图书类型进行查询。
   ⑤. 员工查询.管理人员可以对员工信息进行查询，可查询员工的姓名，性别，联系方式，工资等。
   ⑥. 更改图书信息.管理人员可以对图书信息进行更改，如图书类型，图书价格,上架图书，下架图书等.
   ⑦. 更改员工信息.管理人员可以对员工信息进行更改，如姓名，职位，工资，增加员工，删除员工等.
   ⑧. 会员信息查询.工作人员及管理人员可以对会员的信息进行查询，但所显示的数据并不完全相同.
   ⑨. 更改会员信息.工作人员可以对会员的信息进行添加，更改信息操作，但无法进行删除操作.
   ⑩. 图书信息查询.工作人员可以对图书的信息进行查询.若某书已经售出，则无法根据编号查到，若图书已被退回，则可查到.
   (3) .安全性与完整性要求
   ①．完整性的要求见下文的关系模式约束处。
   ②．为实现数据库的安全性保证，不同的用户拥有不同的权限。暂定如下：
   1.管理人员：具有查看，修改工作人员信息的权限，具有查看，修改购书信息，退书信息的权限，且具有工作人员所具有的所有权限。
   2.工作人员：具有查看，插入会员信息的权限，具有查看，修改图书信息的权限。但工作人员只能查看会员的姓名，性别和会员类别，无法查看到会员的联系方式（而管理人员具有此权限）。工作人员具有查看，插入，购书信息，查看，插入，修改退书信息的权限，工作人员仅可以修改退书信息中的罚款属性，(原因见下文的退书信息表的补充说明)同理，无法查看表中会员的联系方式。
   3.会员：具有查看图书信息的权限。
   (注：此处的修改指的是对数据库中某个关系的一系列操作，包括但不仅限于：INSERT,DELETE,MODIFY等等。
   另：视图权限在下文补充。)

(4) 数据字典
①．数据流图

②．数据结构
数据结构：会员
含义说明：定义了一个会员的相关信息
组成结构：会员编号，会员姓名，会员性别，会员类别，会员联系方式（）

数据结构：工作人员
含义说明：定义了一个工作人员的相关信息
组成结构：员工编号，员工姓名，员工性别，员工身份证号，员工职位，员工工资

数据结构：管理人员
含义说明：定义了一个管理人员的相关信息
组成结构：管理员编号，管理员姓名，（管理员密码）

数据结构：图书
含义说明：定义了图书的相关信息
组成结构：图书编号，图书名称，图书出版社，图书价格，图书作者，图书类别， 图书库存数量

数据结构：会员类别
含义说明：定义了一个会员类别分类的相关信息
组成结构：类别编号，类别名称，折扣

数据结构：购买记录
含义说明：定义了一个会员购买记录的相关信息
组成结构：购买记录编号，购买时间，会员编号，会员联系方式，会员姓名，会员类别，图书编号，图书名称，办理人

数据结构：退书记录
含义说明：定义了一个会员退书记录的相关信息
组成结构：退款记录编号，退款时间，会员编号，会员联系方式，会员姓名，图书编号，图书名称，罚金，办理人

③．数据项

数据项名 数据项含义 别名 类型 长度
会员编号 唯一标识会员 编号 数值型 6
会员姓名 会员的姓名 姓名 字符型 1-3
会员性别 会员的性别 性别 字符型 1
会员类别 会员的类别（等级） 类别 字符型 4
会员联系方式 会员的联系方式（如电话号码） 联系方式 数值型 11
员工编号 唯一标识员工 编号 数值型 6
员工姓名 员工的姓名 姓名 字符型 1-3
员工性别 员工的性别 性别 字符型 1
员工身份证号 员工的身份证号码 身份证号 数值型 18
员工职位 员工所担任的职务 职位 字符型 5
员工工资 员工的每个月工资（死工资（待）） 工资 数值型 4-5
管理员编号 唯一标识管理员 编号 数值型 6
管理员姓名 管理员的姓名 姓名 字符型 1-3
图书编号 唯一标识图书（同一本书具有不同的编号） 编号 数值型 6
图书名称 图书的名称 名称 字符型 1-8
图书出版社 图书的出版社 出版社 字符型 5-12
图书价格 图书的价格 价格 数值型 2-3
图书作者 图书的作者 作者 字符型 3-10
图书类别 图书的类别（科幻，小说，散文等） 类别 字符型 2-5
图书数量 该书店中此图书的存量 数量 数值型 5
类别编号 会员的类别编号 编号 数值型 1-2
类别名称 会员的类别名称 名称 字符型 4
折扣 会员所享有的折扣（依据不同类别而不同） 折扣 数值型 0-3
购买记录编号 唯一标识一条购买记录 编号 数值型 6
购买时间 购买图书的时间 购买时间 字符型 9-11
退款记录编号 唯一标识一条退款记录 编号 数值型 6
退款时间 退掉图书的时间 退款时间 字符型 9-11
罚金 某一次退款中所需缴纳的罚金 罚金 数值型 2-3

2. 概念设计
   ①．局部E-R图
   

②．整体E-R图

各实体的属性如下：
会员(members)：会员编号，会员姓名，会员性别，会员联系方式
图书(book)：图书编号，图书类别，图书作者，图书价格，图书出版社，图书名称，库存数量
会员类别(member_category)：类别编号，类别名称，折扣
工作人员(staff_member)：员工编号，员工姓名，员工性别，员工身份证号，员工职位，员工工资，管理员
管理人员(management)：管理员编号，管理员姓名

各联系集的属性如下：
购书记录(purchase_information)：购买记录编号，会员编号，购书时间，会员姓名，会员联系方式，会员类别编号，图书编号，图书名称，办理人
退书记录(refund_information)：退书记录编号，会员编号，退书时间，会员姓名，会员联系方式，图书编号，图书名称，罚金，办理人

3. 逻辑结构设计
   ①．E-R图转化为关系模式

由E-R图转化而来的关系模式如下：
[会员表] 会员信息表
列名 数据类型 大小 是否为空 其他约束
member_number(会员编号) unsigned mediumint — NOT NULL PRIMARY KEY（主键约束）
member_name(会员姓名) varchar 12 NOT NULL —
member_gender(会员性别) char 3 NOT NULL CHECK(male or female)
member_phone_number(会员联系方式) numeric (12,0) NOT NULL UNIQUE(唯一约束)
member_category(会员类别) numeric (2,0) NOT NULL FOREIGN KEY(外键约束）
补充说明
1.会员编号的值限制在100000 – 999999之间。

[图书表] 图书信息表
列名 数据类型 大小 是否为空 其他约束
book_number(图书编号) unsigned mediumint — NOT NULL PRIMARY KEY(主键约束)
book_category(图书类别) varchar 15 NOT NULL —–
book_author(图书作者) varchar 20 NOT NULL —–
book_price(图书价格) numeric (5,2) NOT NULL —–
book_press(图书出版社) varchar 20 NOT NULL —–
book_name(图书名称) varchar 15 NOT NULL —–
book_quantity(库存数量) numeric (7,0) NOT NULL —–
补充说明
1.同一本书的图书编号相同（此处的同一本书指的是作者出版社及图书名称相同的书，而不是一个个体）
2.图书编号的值限定在100000 – 999999之间。
3.库存数量限定其数值大于等于0.

[会员类别表] 会员类别信息表
列名 数据类型 大小 是否为空 其他约束
category_number(类别编号) numeric (2,0) NOT NULL PRIMARY KEY(主键约束)
category_name(类别名称) varchar 15 NOT NULL —–
discount(折扣) numeric (1,1) NOT NULL CHECK( BETWEEN 0 AND 1)
补充说明
1.折扣应限制在0-1之间（符合实际情况）.

[工作人员表] 工作人员信息表
列名 数据类型 大小 是否为空 其他约束
staff_number(员工编号) unsigned mediumint — NOT NULL PRIMARY KEY(主键约束)
staff_name(员工姓名) varchar 12 NOT NULL —–
staff_gender(员工性别) char 3 NOT NULL CHECK(male or female)
staff_id_number(员工身份证号) char 17 NOT NULL UNIQUE(唯一约束)
staff_post(员工职位) varchar 15 NOT NULL CHECK()
staff_wage(员工工资) numeric (5,2) NOT NULL —–
administractor(管理员) Unsigned mediumint — NOT NULL FOREIGN KEY(外键约束)
补充说明
1.此处的员工工资为每月工资（且随职位而固定）
2.administractor和管理人员表相连，参照其属性management_number.
3.员工编号和管理员号的值限制在100000 – 9999999之间
4.性别仅有两个取值：男或女.
5.对员工的职位也应有限制取值（已通过触发器实现）

[管理人员表] 管理人员信息表
列名 数据类型 大小 是否为空 其他约束
management_number(管理员编号) unsigned mediumint — NOT NULL PRIMARY KEY(主键约束)
management_name(管理员姓名) varchar 15 NOT NULL —–
补充说明
1.管理人员的值应限制在100000 – 999999之间。

[购买记录表] 购买记录信息表
列名 数据类型 大小 是否为空 其他约束
purchase_number(购买记录编号) Unsigned
mediumint — NOT NULL PRIMARY KEY(主键约束)
member_number(会员编号) unsigned mediumint — NOTNULL FOREIGN KEY(外键约束)
purchase_time(购买时间) TIMESTAMP — NOT NULL CHECK()
member_name(会员姓名) varchar 12 NOT NULL —–
member_phone_number(会员联系方式) numeric (12,0) NOT NULL —–
category_number(会员类别编号) numeric (2,0) NOT NULL FOREIGN KEY(外键约束)
book_number(图书编号) unsigned mediumint — NOT NULL FOREIGN KEY(外键约束)
book_name(图书名称) varchar 15 NOT NULL —–
handlers(办理人) unsigned mediumint — NOT NULL FOREIGN KEY(外键约束)
补充说明
1.添加购买记录编号作为此表的主码，应限制购买时间 2.member_number作为外码与会员表相连，参照其主码member_number.
3.category_number作为外码与会员类别表相连，参照其主码category_number(便于购书时的折扣查询).
4.book_number作为外码与图书表相连，参照其主码book_number.
5.handler作为外码与工作人员表相连，参照其主码staff_number.
6.购买记录编号的值应限制在100000 – 999999之间.

[退书记录表] 退书记录信息表

列名 数据类型 大小 是否为空 其他约束
refund_number(退书记录编号) unsigned int — NOT NULL PRIMARY KEY(主键约束)
member_number(会员编号) unsigned int — NOT NULL FOREIGN KEY(外键约束)
refund_time(退书时间) TIMESTAMP — NOT NULL CHECK()
member_name(会员姓名) var char 12 NOT NULL —–
member_phone_number(会员联系方式) numeric (12,0) NOT NULL —–
penalty(罚金) numeric (3,2) NOT NULL —–
book_number(图书编号) unsigned int — NOT NULL FOREIGN KEY(外键约束)
book_name(图书名称) varchar 15 NOT NULL —–
handlers(办理人) unsigned int — NOT NULL FOREIGN KEY(外键约束)
补充说明
1.添加退书记录编号作为此表的主码，应限制退书时间 2.member_number作为外码与会员表相连，参照其主码member_number.
3.book_number作为外码与图书表相连，参照其主码book_number.
4.handler作为外码与工作人员表相连，参照其主码staff_number.
5.会员缴纳了罚金后，工作人员可将该属性修改为0(略不合理，待修改。)
6.handlers限制在100000至999999之间
7.退书记录编号的值应限制在100000 – 999999 之间.

②．关系模式的优化
(1) 会员表
在会员表中，会员编号—–>会员姓名，会员编号—–>会员性别，会员编号—–>会员联系方式，其中会员编号为主码，已满足BCNF范式。

(2) 图书
在图书表中，图书编号—–>图书类别， 图书编号—–>图书作者，图书编号—–>图书价格，图书编号—–>图书出版社，图书编号—–>图书名称，图书编号—–>库存数量，图书作者、图书名称、图书出版社—–>图书价格，但其中存在不包含码的依赖，故满足3NF范式。
分解为BCNF范式如下：
表1(图书编号，图书类别，库存数量，图书作者，图书名称，图书出版社)
表2(图书作者，图书名称，图书出版社，图书价格)

(3) 会员类别
在会员类别表中，类别编号—–>类别名称，类别编号—–>折扣，类别名称—–>折扣，存在传递依赖，满足2NF范式。
分解为BCNF范式如下：
表1(类别编号，类别名称)
表2(类别名称，折扣)

(4) 工作人员
在工作人员表中，员工编号—–>员工姓名，员工编号—–>员工性别，员工编号—–>员工身份证号，员工编号—–>员工职位，员工编号—–>员工工资，员工身份证号—–>员工姓名，员工身份证号—–>员工性别，员工职位—–>员工工资，存在传递依赖，满足2NF范式。
分解为BCNF范式如下：
表1(员工编号，员工身份证号，员工职位)
表2(员工身份证号，员工性别，员工姓名)
表3(员工职位，员工工资)

(5) 管理人员
在管理人员表中，管理员编号—–>管理员姓名，满足BCNF范式

(6) 购买记录
在购买记录表中，购买记录编号—–>会员编号，购买记录编号—–>购买时间，购买记录编号—–>会员姓名，购买记录编号—–>会员联系方式，购买记录编号—–>会员类别编号，购买记录编号—–>图书编号，购买记录编号—–>图书名称，购买记录编号—–>办理人，会员编号—–>会员姓名，会员联系方式，会员类别编号，图书编号—–>图书名称，存在传递依赖，满足2NF范式。
分解为BCNF范式如下：
表1(购买记录编号，购买时间，办理人，会员编号，图书编号)
表2(会员编号，会员姓名，会员联系方式，会员类别编号)
表3(图书编号，图书名称)

(7) 退书记录
在退书记录表中，退书记录编号—–>会员编号，退书时间，会员姓名，会员联系方式，罚金，图书编号，图书名称，办理人，会员编号—–>会员姓名，图书编号—–>图书名称，存在传递依赖，满足2NF范式
分解为BCNF范式如下：
表1(退书记录编号，退书时间，办理人，罚金，会员编号，图书编号)
表2(会员编号，会员姓名，会员联系方式)
表3(图书编号，图书名称)

(注：若单纯的追求更高级别的范式可能会对数据库的管理造成极大的不便。）
③．关系模式的约束
(1).域约束
域约束是保证数据库属性取值的合理性。
属性值应该是域中的值，具体的定义可查看数据项，除此之外，一个属性能否为NULL，也是域完整性约束的主要内容。
包括但不仅限于检查(CHECK)，默认值(DEFAULT)，是否为空(NULL OR NOT NULL),外键(FOREIGN KEY)，主键(PRIMARY KEY)等等。
(2).唯一性约束
在本系统中，编号等，包括会员编号，图书编号，工作人员编号，管理人员编号，购书记录编号，退书记录编号等必须不重复，应创建唯一性索引，但由于均存在主码约束，故可省略。
(3).联系约束(参照约束)
购买记录表参照会员表，以会员编号作为两个关系进行关联的属性。
购买记录表参照会员类别表，以会员类别编号作为两个关系进行关联的属性。
购买记录表参照图书表，以图书编号作为两个关系进行关联的属性。
购买记录表参照工作人员表，以handler/stuff_number作为两个关系进行关联的属性。

退书记录表参照会员表，以会员编号作为两个关系进行关联的属性。
退书记录表参照图书表，以图书编号作为两个关系进行关联的属性。
退书记录表参照工作人员表，以handler/stuff_number作为两个关系进行关联的属性。

工作人员表参照管理人员表，以administractor/management_number作为两个关系进行关联的属性。

(4) .业务约束(用户定义完整性约束)
业务规则约束是针对指定应用的逻辑和要求的，它们来源于使用数据库组织中的应用程序和策略。

在书店会员管理系统中，业务规则大致如下：
·编号均为6位(方便进行数据库的管理)
·购买时间/退书时间均不可大于今日
·性别(管理人员性别，工作人员性别，会员性别)的取值是’男’或‘女’
·会员享受的折扣的取值应在(0,1)之间 (左开右开)
·图书的库存数量始终 >= 0,且当库存数量为0时，无法对该书(通过编号进行区别)进行购书操作。(通过约束完成.)

4.物理结构设计（索引，视图，触发器，存储过程，数据存储和存取方法等）

(1).索引的创建
表名 列名 索引
会员表 会员编号（主键） 唯一索引
会员类别表 会员类别编号（主键） 唯一索引
图书表 图书编号（主键） 唯一索引
管理人员表 管理人员编号（主键） 唯一索引
工作人员表 工作人员编号（主键） 唯一索引
购买记录表 购买记录编号（主键） 唯一索引
退书记录表 退书记录编号（主键） 唯一索引
购买记录表 购买时间 可重复索引
退书记录表 退书时间 可重复索引
图书表 图书名称 可重复索引
补充说明
1.由于可能存在多个客服同时买书/退书，因此在购买时间和退书时间上创建的是可重复索引.
2.根据图书名称的搜索量很大，且图书会存在同名的情况，因此创建可重复索引.

(2) .视图的创建
创建了4个视图，便于工作人员和会员进行信息的查询。
– 视图创建

– 会员基本信息视图
CREATE VIEW members_view
(
会员编号,
会员姓名,
会员性别,
会员联系方式,
会员类别
)
AS
SELECT member_number,
member_name,
member_gender,
member_phone_number,
member_category
FROM
members;

– 图书基本信息视图
CREATE VIEW book_view
(
图书编号,
图书名称,
图书作者,
图书存量
)
AS
SELECT book_number,
book_name,
book_author,
book_quantity
FROM
book;

– 购买记录基本信息视图
CREATE VIEW purchase_view
(
购买编号,
购买时间,
会员编号,
图书编号,
图书名称
)
AS
SELECT purchase_number,
purchase_time,
member_number,
book_number,
book_name
FROM
purchase_information;

– 退书记录基本信息视图
CREATE VIEW refund_view
(
退书编号,
退书时间,
会员编号,
图书编号,
图书名称
)
AS
SELECT refund_number,
refund_time,
member_number,
book_number,
book_name
FROM
refund_information;

(3) .触发器的创建
在图书表上创建触发器，其中一个在购买表进行插入操作时触发，使图书表中该图书（根据图书编号确定）的数量-1（若成立）。另一个在退书表进行插入操作时触发，使图书表中该图书（根据图书编号确定）的数量+1；

– 触发器的创建

– 在购书信息表上上创建触发器 买书时对库存数量进行修改
DELIMITER $$
CREATE TRIGGER purchase_trigger
AFTER INSERT
ON purchase_information
for each row
BEGIN
UPDATE book
SET book.book_quantity = book_quantity – 1
WHERE book.book_number = NEW.book_number;

END $$
DELIMITER ;
DROP TRIGGER purchase_trigger;

– 在退书信息表上创建触发器，退书时对库存数量进行修改
DELIMITER $$
CREATE TRIGGER refund_trigger
AFTER INSERT
ON refund_information
for each row
BEGIN
UPDATE book
SET book.book_quantity = book_quantity + 1
WHERE book.book_number = new.book_number;

END $$
DELIMITER ;
DROP TRIGGER refund_trigger;

– 在购书信息表上创建 ， 需满足职务为柜台
– DELIMITER $$
– CREATE TRIGGER purchase_information_trigger
– AFTER INSERT
– ON purchase_information
– FOR EACH ROW
– BEGIN
– if
– (
– (SELECT staff_member.staff_post
– FROM staff_member
– WHERE staff_member.staff_number = new.handlers
– ) ‘柜台’ #为不等于
– ) then
– delete from purchase_information where purchase_information.purchase_number = new.purchase_number;
– end if;

– END $$
– DELIMITER ;
– DROP TRIGGER purchase_information_trigger;

– 在购书信息表上创建触发器， 约束办理人的职务为：柜台
DELIMITER $$
CREATE TRIGGER purchase_information_trigger
BEFORE INSERT
ON purchase_information
FOR EACH ROW
BEGIN

if
 (
	(SELECT staff_member.staff_post
     FROM staff_member
     WHERE staff_member.staff_number = new.handlers
     )  '柜台'				#为不等于
) then
signal sqlstate '45000' set message_text ='错误！违背职务的规定' ;
end if; 

END $$
DELIMITER ;
DROP TRIGGER purchase_information_trigger;

– 在退书信息表上创建触发器， 约束办理人的职务为：柜台
DELIMITER $$
CREATE TRIGGER refund_information_trigger
BEFORE INSERT
ON refund_information
FOR EACH ROW
BEGIN

if
 (
	(SELECT staff_member.staff_post
     FROM staff_member
     WHERE staff_member.staff_number = new.handlers
     )  '柜台'				#为不等于
) then
signal sqlstate '45000' set message_text ='错误！违背职务的规定' ;
end if; 

END $$
DELIMITER ;
DROP TRIGGER refund_information_trigger;

– 在购书信息表上创建触发器， 约束购书的时间 DELIMITER $$
CREATE TRIGGER purchase_time_trigger
BEFORE INSERT
ON purchase_information
FOR EACH ROW
BEGIN

if
(	
	to_days(new.purchase_time) - to_days(DATE(now())) > 0
)
THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "Insert time cannot be later than now's time";
END IF;

END $$
DELIMITER ;
DROP TRIGGER purchase_time_trigger;

– 在退书信息表上创建触发器， 约束退书的时间 DELIMITER $$
CREATE TRIGGER refund_time_trigger
BEFORE INSERT
ON refund_information
FOR EACH ROW
BEGIN

if
(	
	to_days(new.refund_time) - to_days(DATE(now())) > 0
)
THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "Insert time cannot be later than now's time";
END IF;

END $$
DELIMITER ;
DROP TRIGGER refund_time_trigger；
(4) .存储过程的创建
创建统计某个会员买的所有书籍的过程，以及统计某个会员退的所有书籍的过程（暂时）。
– 存储过程的创建

– 统计某会员买的所有书籍
DELIMITER $$
CREATE PROCEDURE member_total_purchase(temp_number MEDIUMINT)
BEGIN
SELECT book_name,
member_number
FROM purchase_information
WHERE purchase_information.member_number = temp_number;

END $$
DELIMITER ;
DROP PROCEDURE member_total_purchase;

– 统计某会员退过的所有书籍
DELIMITER $$
CREATE PROCEDURE member_total_refund(temp_number MEDIUMINT)
BEGIN
SELECT book_name,
member_number
FROM refund_information
WHERE refund_information.member_number = temp_number;

END $$
DELIMITER ;

(5).数据存储
①．买书信息表和退书信息表的存储引擎均使用MYISAM,因为在买书信息表和退书信息表中，会存在大量的选择操作(查询信息)和大量的插入操作。因此使用MYISAM引擎以便加快筛选数据的速度并且能允许同时选择和插入数据。
②．其余表可均使用INNODB(且默认为INNODB)。
具体实现见5.数据库的创建。

(6).存取方法的创建
①．在各个表的主键上可建立b-tree索引。
②．在会员信息表中，会员类别的属性值重复性可能较高，因此可在该属性上建立聚簇存取方法。
具体实现见5.数据库的创建。

(7).角色的创建
在数据库中创建，方便于对各个用户的权限管理。各个角色（用户）的权限参照数据库的安全性要求。
①．创建角色’administractor_role’，具有的权限见安全性要求，代码如下：

– 角色：管理人员
CREATE ROLE ‘administractor_role’;
GRANT ALTER, DELETE, INSERT, SELECT, UPDATE
ON bookStore.staff_member
TO ‘administractor_role’;

GRANT ALTER, DELETE, INSERT, SELECT, UPDATE
ON bookStore.purchase_information
TO ‘administractor_role’;

GRANT ALTER, DELETE, INSERT, SELECT, UPDATE
ON bookStore.refund_information
TO ‘administractor_role’;

GRANT ALTER, DELETE, INSERT, SELECT, UPDATE
ON bookStore.book
TO ‘administractor_role’;

GRANT ALTER, DELETE, INSERT, SELECT, UPDATE
ON bookStore.member_category
TO ‘administractor_role’;

GRANT ALTER, DELETE, INSERT, SELECT, UPDATE
ON bookStore.members
TO ‘administractor_role’;

GRANT SHOW VIEW
ON bookStore.members_view
TO ‘administractor_role’;

GRANT SHOW VIEW
ON bookStore.book_view
TO ‘administractor_role’;

GRANT SHOW VIEW
ON bookStore.purchase_view
TO ‘administractor_role’;

GRANT SHOW VIEW
ON bookStore.refund_view
TO ‘administractor_role’;

– GRANT ALL PRIVILEGES
– ON .
– TO ‘administractor_role’;

②．创建角色’stuff_role’，具有的权限见安全性要求，代码如下:

– 角色：工作人员
CREATE ROLE ‘stuff_role’;

– GRANT SHOW VIEW
– ON bookStore.members_view
– TO ‘stuff_role’; 可查询联系方式，不可取！

GRANT SHOW VIEW
ON bookStore.book_view
TO ‘stuff_role’;

GRANT SHOW VIEW
ON bookStore.purchase_view
TO ‘stuff_role’;

GRANT SHOW VIEW
ON bookStore.refund_view
TO ‘stuff_role’;

GRANT SELECT, UPDATE
ON bookStore.book
TO ‘stuff_role’;

GRANT SELECT, INSERT
ON bookStore.purchase_information
TO ‘stuff_role’;

GRANT SELECT, INSERT
ON bookStore.refund_information
TO ‘stuff_role’;

GRANT UPDATE(penalty)
ON bookStore.refund_information
TO ‘stuff_role’;

GRANT SELECT(member_numeber,member_name,member_gender,member_category)
on bookStore.members
TO ‘stuff_role’;

③．创建角色’member_role’，具有的权限见安全性要求，代码如下：

– 角色：会员
CREATE ROLE ‘member_role’;
GRANT SHOW VIEW
ON bookStore.book_view
TO ‘member_role’;

GRANT SELECT
ON bookStore.book
TO ‘member_role’;

4. 数据库的创建(代码)
   (ps:database需自己创建！)
   – 创建会员表
   CREATE TABLE members
   (
   member_number MEDIUMINT UNSIGNED PRIMARY KEY CHECK(member_number BETWEEN 100000 AND 999999),
   member_name VARCHAR(12) NOT NULL,
   member_gender CHAR(3) CHECK( member_gender = ‘男’ OR member_gender = ‘女’),
   member_phone_number INT UNIQUE CHECK(member_phone_number BETWEEN 10000000000 AND 19999999999),
   member_category NUMERIC(2,0) NOT NULL,
   FOREIGN KEY(member_category) references member_category(category_number),
   UNIQUE INDEX index_member_number(member_number) using btree
   )ENGINE = INNODB;
   DROP TABLE members;
   – 创建图书表
   CREATE TABLE book
   (
   book_number mediumint UNSIGNED PRIMARY KEY CHECK(book_number BETWEEN 100000 AND 999999),
   book_category VARCHAR(15) NOT NULL,
   book_author VARCHAR(20) NOT NULL,
   book_price NUMERIC(5,2) NOT NULL,
   book_press VARCHAR(20) NOT NULL,
   book_name VARCHAR(15) NOT NULL,
   book_quantity NUMERIC(7,0) NOT NULL,
   UNIQUE INDEX index_book_number(book_number) using btree
   )ENGINE = INNODB;

– 创建会员类别表
CREATE TABLE member_category
(
category_number NUMERIC(2,0) PRIMARY KEY,
category_name VARCHAR(15) NOT NULL,
discount NUMERIC(1,1) CHECK(discount between 0 and 1),
UNIQUE INDEX index_category_number(category_number) using btree
)ENGINE = INNODB;

– 创建工作人员表
CREATE TABLE staff_member
(
staff_number MEDIUMINT UNSIGNED PRIMARY KEY CHECK(staff_number BETWEEN 100000 AND 999999),
staff_name VARCHAR(12) NOT NULL,
staff_gender CHAR(3) CHECK(staff_gender = ‘男’ or staff_gender = ‘女’),
staff_id_number CHAR(18) NOT NULL unique,
staff_post VARCHAR(15) NOT NULL,
staff_wage NUMERIC(5,2) NOT NULL,
administractor MEDIUMINT UNSIGNED NOT NULL CHECK (administractor BETWEEN 100000 AND 999999),
FOREIGN KEY(administractor) REFERENCES management(management_number),
UNIQUE INDEX index_staff_number(staff_number) using btree

)ENGINE = InnoDB;

– 创建管理人员表
CREATE TABLE management
(
management_number MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY CHECK(management_number BETWEEN 100000 AND 999999), #char(6) like’[0-9][0-9][0-9][0-9][0-9][0-9]’
management_name VARCHAR(15) NOT NULL
)ENGINE = INNODB;
DROP TABLE management;

– 创建购书记录表
CREATE TABLE purchase_information
(
purchase_number MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY CHECK(purchase_number BETWEEN 100000 AND 999999),
member_number MEDIUMINT UNSIGNED NOT NULL CHECK(member_number BETWEEN 100000 AND 999999),
purchase_time TIMESTAMP,
member_name VARCHAR(12) NOT NULL,
member_phone_number INT NOT NULL CHECK(member_phone_number BETWEEN 10000000000 AND 19999999999),
category_number NUMERIC(2,0) NOT NULL,
book_number MEDIUMINT UNSIGNED NOT NULL CHECK(book_number BETWEEN 100000 AND 999999),
book_name VARCHAR(15) NOT NULL,
handlers MEDIUMINT UNSIGNED NOT NULL CHECK(handlerS BETWEEN 100000 AND 999999),
FOREIGN KEY(member_number) REFERENCES members(member_number),
FOREIGN KEY(category_number) REFERENCES member_category(category_number),
FOREIGN KEY(book_number) REFERENCES book(book_number),
FOREIGN KEY(handlers) REFERENCES management(management_number),
UNIQUE INDEX index_purchase_number(purchase_number) using btree

)ENGINE = MYISAM;
DROP TABLE purchase_information;

– 创建退书记录表
CREATE TABLE refund_information
(
refund_number MEDIUMINT UNSIGNED PRIMARY KEY CHECK(refund_number BETWEEN 100000 AND 999999),
member_number MEDIUMINT UNSIGNED NOT NULL CHECK(member_number BETWEEN 100000 AND 999999),
refund_time TIMESTAMP NOT NULL,
member_name VARCHAR(12) NOT NULL,
member_phone_number INT NOT NULL CHECK(member_phone_number BETWEEN 10000000000 AND 19999999999),
penalty NUMERIC(3,2) NOT NULL,
book_number MEDIUMINT UNSIGNED NOT NULL CHECK(book_number BETWEEN 100000 AND 999999),
book_name VARCHAR(15) NOT NULL,
handlers MEDIUMINT UNSIGNED NOT NULL CHECK(handlers BETWEEN 100000 AND 999999),
FOREIGN KEY(member_number) REFERENCES members(member_number),
FOREIGN KEY(book_number) REFERENCES book(book_number),
FOREIGN KEY(handlers) REFERENCES management(management_number),
UNIQUE INDEX index_refund_number(refund_number) using btree

)ENGINE = MYISAM;

#数据的植入：

INSERT INTO book VALUES (200001, ‘科幻小说’, ‘马尔克斯’, 53.00, ‘出版社A’, ‘百年孤独’, 100);
INSERT INTO book VALUES (200002, ‘爱情小说’, ‘川端康成’, 58.00, ‘出版社A’, ‘雪国’, 200);
INSERT INTO book VALUES (200003, ‘长篇小说’, ‘三岛由纪夫’, 73.00, ‘出版社B’, ‘金阁寺’, 201);
INSERT INTO book VALUES (200005, ‘长篇小说’, ‘帕穆克’, 59.00, ‘出版社C’, ‘我的名字叫红’, 300);
INSERT INTO book VALUES (200006, ‘爱情小说’, ‘沃勒’, 50.00, ‘出版社A’, ‘廊桥遗梦’, 350);

INSERT INTO member_category VALUES (1, ‘初级会员’, 0.9);
INSERT INTO member_category VALUES (2, ‘中级会员’, 0.7);
INSERT INTO member_category VALUES (3, ‘高级会员’, 0.5);

INSERT INTO management VALUES (400001, ‘管理人员A’);
INSERT INTO management VALUES (400002, ‘管理人员B’);
INSERT INTO management VALUES (400003, ‘管理人员C’);

INSERT INTO staff_member VALUES (300001, ‘工作人员A’, ‘男’, ‘12345678901234567’, ‘柜台’, 5000.00, 400001);
INSERT INTO staff_member VALUES (300002, ‘工作人员B’, ‘女’, ‘12345678901234566’, ‘清洁’, 7000.00, 400001);
INSERT INTO staff_member VALUES (300003, ‘工作人员C’, ‘女’, ‘12345678901234565’, ‘杂务’, 6000.00, 400002);

INSERT INTO members VALUES (100001, ‘会员A’, ‘男’, 11111111111, 1);
INSERT INTO members VALUES (100002, ‘会员B’, ‘女’, 11111111112, 2);
INSERT INTO members VALUES (100003, ‘会员C’, ‘女’, 11111111113, 3);

INSERT INTO purchase_information VALUES (500001, 100001, ‘2021-6-1 00:00:00’, ‘会员A’, 11111111111, 1, 200001, ‘百年孤独’, 300001);
INSERT INTO purchase_information VALUES (500002, 100002, ‘2021-6-2 00:00:00’, ‘会员B’, 11111111112, 2, 200006, ‘廊桥遗梦’, 300002);
INSERT INTO purchase_information VALUES (500003, 100003, ‘2021-6-3 00:00:00’, ‘会员C’, 11111111113, 3, 200005, ‘我的名字叫红’, 300001);

INSERT INTO refund_information VALUES (600002, 100002, ‘2021-6-12 00:00:00’, ‘会员B’, 11111111112, 50.00, 200003, ‘金阁寺’, 300002);
INSERT INTO refund_information VALUES (600003, 100002, ‘2021-6-15 00:00:00’, ‘会员B’, 11111111112, 70.00, 200001, ‘百年孤独’, 300001);
INSERT INTO refund_information VALUES (600001, 100001, ‘2021-6-11 00:00:00’, ‘会员A’, 11111111111, 30.00, 200002, ‘雪国’, 300001);