煤气管理系统数据库.doc

煤气管理系统数据库

23
2020年4月19日
文档仅供参考，不当之处，请联系改正。

信息与电气工程学院
CDIO项目报告
（ / 第一学期）

课程名称 ： 数据库原理及应用
项目名称 ： 煤气公司送气管理系统
专业班级 ： 计算机1503
学生姓名 ： 曲秋鹏
学 号：

项目成绩 ：

12月14日

目 录
1 项目背景及需求分析 2
1.1项目背景 2
1.2需求分析 2
2 概念结构设计 2
2.1抽象出系统实体 2
2.2局部E-R图 2
2.3全局E-R图 4
3 逻辑结构设计 4
3.1初始关系模式设计 4
3.2关系模式的规范化 5
4 创立数据库及相关操作 5
4.1 创立数据库 5
4.2建立视图 6
4.3建立存储过程 8
4.4建立触发器 8
4.5查询问题及查询结果 9
5备份和恢复的维护、安全的设置 12
6 项目设计心得总结 12
参考文献 13
评分表 14

1 项目背景及需求分析
1.1项目背景
某煤气公司为收集和管理数据简便，将工作数据导入数据库。使其实现工作人员、客户人员的管理；实现煤气类别和供应商的管理；实现煤气入库管理和出库管理；实现费用管理。
1.2需求分析
创立触发器，实现入库、出库时相应类型煤气的数量的增加或减少。
创立存储过程统计每个送气员工指定月份送气的数量。
创立存储过程查询指定月份用气量最大的前10个用户，并按用气量递减排列。
建立数据库相关表之间的参照完整性约束；建立表间关系。
2 概念结构设计
2.1抽象出系统实体
一个company能够雇佣多名worker，一名worker工作在一个company工作。
一名worker能够服务多名customer，一名customer可有由多名worker服务。
一名customer能够使用多种gas,一种gas能够 供多名customers使用。
一个供应商能够提供多种gas,一种gas能够有多个供应商提供。
一个company能够拥有多种gas，一种gas属于一个公司。
2.2局部E-R图

图2.2.1 局部E-R图之company

图2.2.2 局部E-R图之gas

图2.2.3 局部E-R图之worker

图2.2.4 局部E-R图之customer

图2.2.5 局部E-R图之supply
2.3全局E-R图

图2.3.1 全局E-R图
3 逻辑结构设计
3.1初始关系模式设计
Company（公司编号，公司名称）
Worker（工号，职工姓名，职工性别）
Customer（顾客编号，顾客姓名，顾客性别）
Gas（煤气编号，煤气名称，存储量）
Supply（供应商编号，供应商名称）
Goin（入库序号，入库日期，入库量，煤气编号，供应商编号）
Goout（出库序号，出库日期，出库量，煤气编号，客户编号）
Servers（服务序号，费用，服务日期，工号，客户编号）
其中有下滑直线标记的为主码，下滑波浪线标记的为外码。
3.2关系模式的规范化
Company、Worker、Customer、Gas、Supply、Goin、Goout、Servers符合第一范式，即这些关系模式的所有属性都是不可再分的基本数据项。
Company、Worker、Customer、Gas、Supply、Goin、Goout、Servers符合第二范式，即这些关系模式的每个非主属性完全函数依赖于这些关系模式中的某个候选码。
Company、Worker、Customer、Gas、Supply、Goin、Goout、Servers符合第三范式，即每个非主属性都不传递函数依赖于任何候选码。
4 创立数据库及相关操作
4.1 创立数据库
Company表的建立：
代码：
CREATE TABLE `company` (
`cno` tinyint NULL ,
`cname` tinyint NULL ,
PRIMARY KEY (`cno`)
)
结果展示：如图4.1.1

图4.1.1 company表
customer表的建立：
代码：
CREATE TABLE `customer` (
`客户编号` tinyint NULL ,
`客户姓名` varchar(255) NULL ,
`客户性别` enum('女','男') NULL ,
PRIMARY KEY (`客户编号`)
)
;
结果展示：如图4.1.2

图4.1.2 customer表
gas表的建立
代码：
CREATE TABLE `gas` (
`煤气编号` tinyint NULL ,
`煤气名称` varchar(255) NULL ,
`存储量` float NULL ,
`所属公司` tinyint NULL ,
PRIMARY KEY (`煤气编号`)
)
结果展示：如图4.1.3

图4.1.3 gas表
goin表的建立：
代码：
CREATE TABLE `goin` (
`入库序号` tinyint NULL ,
`入库日期` date NULL ,
`入库量` float NULL ,
`煤气编号` tinyint NULL ,
`供应商编号` tinyint NULL ,
PRIMARY KEY (`煤气编号`, `入库序号`, `供应商编号`),
FOREIGN KEY (`供应商编号`) REFERENCES `supply` (`供应商编号`),
FOREIGN KEY (`煤气编号`) REFERENCES `gas` (`煤气编号`)
)
;
结果展示：如图4.1.4

图4.1.4 goin表
goout表的建立：
代码：
CREATE TABLE `goout` (
`出库序号` tinyint NULL ,
`出库日期` date NULL ,
`出库量` float NULL ,
`煤气编号` tinyint NULL ,
`客户编号` tinyint NULL ,
PRIMARY KEY (`煤气编号`, `出库序号`, `客户编号`),
FOREIGN KEY (`供应商编号`, `客户编号`) REFERENCES `mqgs`.`customer` (`客户编号`),
FOREIGN KEY (`煤气编号`) REFERENCES `mqgs`.`gas` (`煤气编号`)
)
;
结果展示：如图4.1.5

图4.1.5 goout表
servers表的建立：
代码：
CREATE TABLE `servers` (
`服务编号` tinyint NULL DEFAULT '' ,
`服务日期` date NULL ,
`费用` int NULL ,
`工号` tinyint NULL ,
`顾客编号` tinyint NULL ,
FOREIGN KEY (`工号`) REFERENCES `worker` (`工号`),
FOREIGN KEY (`顾客编号`) REFERENCES `customer` (`客户编号`)
)
;
结果展示：如图4.1.6

图4.1.6 servers表
supply表的建立：
代码：
CREATE TABLE `supply` (
`煤气编号` tinyint NULL ,
`煤气名称` varchar(255) NULL ,
`存储量` float NULL ,
`所属公司` tinyint NULL ,
PRIMARY KEY (`煤气编号`)
)
;
结果展示：如图4.1.7

图4.1.7supply表
worker表的建立：
代码：
CREATE TABLE `worker` (
`工号` tinyint NULL ,
`职工姓名` char(255) NULL ,
`职工性别` enum('女','男 ') NULL ,
PRIMARY KEY (`工号`)
)
;
结果展示：如图4.1.8

图4.1.8 worker表
4.2建立视图
⑴客户费用视图

图4.2.1 客户费用视图代码

图4.2.2 客户费用视图结果
⑵入库视图

图4.2-3 入库视图代码

图4.2.4 入库视图结果
⑶出库视图

图4.2.5 出库视图代码

图4.2.6 出库视图结果
4.3建立存储过程
⑴查询月用气量最大的10个用户
存储过程代码：
BEGIN
select 客户编号,出库量,出库日期 from goout
where 客户编号 in( select 客户编号 from customer where 出库日期>' -05-01' and 出库日期<' -06-01')
order by 出库量 desc limit 10 ;
END
调用语句：call show_cost;
调用结果（由于表内数据数量不足，故只显示部分数据）：如图4.3-1

图4.3.1 月用气量最大的用户调用结果
⑵统计工人的送气量
存储过程代码：
BEGIN
select 职工姓名,客户编号,费用,服务日期 from servers join worker on servers.工号=worker.工号
where servers.服务日期 in (select 服务日期 from servers
where 服务日期>' -05-01' and 服务日期<' -06-01');
END
调用语句：call show_work
调用结果（由于表内数据数量不足，故只显示部分数据）:如图4.3-2

图4.3.2 统计工人的送气量
4.4建立触发器
⑴入库触发器
作用：当插入一条入库数据时，gas表内对应煤气编号的存储量发生变化
代码：
Delimiter //
Create trigger goin after insert
For each row
begin

update gas as a inner join goin as b on a.煤气编号=new.煤气编号 set a.存储量=a.存储量+new.入库量;
End //
执行结果：如图4.4.1。

图4.4.1 入库触发器
⑵出库触发器
作用：当插入一条出库数据时，gas表内对应煤气编号的存储量发生变化。
代码：
Delimiter //
Create trigger goout after insert
For each row
begin
update gas as a inner join goin as b on a.煤气编号=new.入库煤气编号 set a.存储量=a.存储量-new.出库量;
End //
执行结果：如图4.4-2

图4.4.2 出库触发器
4.5查询问题及查询结果
⑴查询职工得到的报酬并递减排列，代码及结果如图4.5.1。

图4.5.1 问题1结果
⑵显示煤气名称，入库时间，入库量，按照时间递减排列，代码及结果如图4.5.2.

图4.5.2 问题2结果
⑶显示煤气名称，出库时间，出库量，按照时间递减排列，代码及结果如图4.5-3
.
图4.5.3 问题3结果
⑷查询职工信息，代码及结果如图4.5.4。

图4.5.4 问题4结果
⑸查询顾客信息，代码及结果如图4.5-5。

图4.5.5 问题5结果
5备份和恢复的维护、安全的设置
数据库的备份：命令如图5.1，成功后将在指定目录生成一个sql文件。

图5.1备份命令
数据库的恢复：先创立一个新数据库并use进新的数据库，输入如图5.2所示命令。

图5.2 恢复命令
用户安全的设置：root用户可进行所有操作，工人可进行增加和查询操作，顾客可进行查询操作。
Root用户操作：如图5.3

图 5.3 root用户
工人用户操作：如图5.4。

图5.4 职工用户
顾客用户：如图55

图5.5 顾客用户
6 项目设计心得总结
从需求分析开始到彻底完成这个项目，也花费了将近一个星期的时间。由于这门课结课较早，做这个项目的时候也忘得差不多了，造成做项目的过程中有很多不懂的地方，甚至出错造成数据库的重新构建。
经过反重复复的出错修正出错修正，才意识到刚开始的需求分析、概念结构设计和逻辑结构设计是多么的重要，很多问题都是在开始分析的不全面造成的。例如在构建入库表时，开始只把供应商编号和煤气编号设为主键，造成同一供货商无法对同一煤气进行第二次供货。解决的方法就是在出货表中设置一个进货单号的属性，把进货单号、供应商编号、煤气编号都设置成主键，就不会出现上述的问题了。同理出货单表。
大到一些大企业管理，小到理发店vips刷卡，都用到了数据库，可见数据库的普遍性。当我们编写的程序能够走上台面的时候，连接数据库也是必不可少的一步。数据库作为如此重要的一门课，是必须学好的一门课。

参考文献
[1]CSDN论坛，Mysql之触发器和事务控制.

[2]红黑联盟，sql之浅谈视图的作用

[3]CSDN论坛．Mysql数据库的维护、备份和恢复

评分表
项目设计
评 语

课程设计
成 绩

指导教师
（签字）

年 月 日
注：此表必须在同一页面。