信电学院CDIO试点班项目实践报告--煤气管理系统数据库

1、.信息与电气工程学院CDIO项目报告（2016/2017学年第一学期）课程名称 ： 数据库原理及应用 项目名称 ： 煤气公司送气管理系统 专业班级 ： 学生姓名 ： 学 号： 项目成绩 ： 2016年12月14日*;目 录1 项目背景及需求分析21.1项目背景21.2需求分析22 概念结构设计22.1抽象出系统实体22.2局部E-R图22.3全局E-R图43 逻辑结构设计43.1初始关系模式设计43.2关系模式的规范化54 创建数据库及相关操作54.1 创建数据库54.2建立视图64.3建立存储过程84.4建立触发器84.5查询问题及查询结果95备份和恢复的维护、安全的设置126 项目设计心得

2、总结12参考文献13评分表141 项目背景及需求分析1.1项目背景某煤气公司为收集和管理数据简便，将工作数据导入数据库。使其实现工作人员、客户人员的管理；实现煤气类别和供应商的管理；实现煤气入库管理和出库管理；实现费用管理。1.2需求分析创建触发器，实现入库、出库时相应类型煤气的数量的增加或减少。创建存储过程统计每个送气员工指定月份送气的数量。创建存储过程查询指定月份用气量最大的前10个用户，并按用气量递减排列。建立数据库相关表之间的参照完整性约束；建立表间关系。2 概念结构设计2.1抽象出系统实体一个company可以雇佣多名worker，一名worker工作在一个company工作。一名w

3、orker可以服务多名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图之supply2.3全局E-R图图2.3.1 全局E-R图3 逻辑结构设计3.1初始关系模式设计C

4、ompany（公司编号，公司名称）Worker（工号，职工姓名，职工性别）Customer（顾客编号，顾客姓名，顾客性别）Gas（煤气编号，煤气名称，存储量）Supply（供应商编号，供应商名称）Goin（入库序号，入库日期，入库量，煤气编号，供应商编号）Goout（出库序号，出库日期，出库量，煤气编号，客户编号）Servers（服务序号，费用，服务日期，工号，客户编号）其中有下滑直线标记的为主码，下滑波浪线标记的为外码。3.2关系模式的规范化Company、Worker、Customer、Gas、Supply、Goin、Goout、Servers符合第一范式，即这些关系模式的所有属性都是不可

5、再分的基本数据项。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)结

6、果展示：如图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 NUL

7、L ,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

8、.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表的建

9、立：代码：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 ,煤气名

10、称 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 客户费用视图结

11、果入库视图图4.2-3 入库视图代码图4.2.4 入库视图结果出库视图图4.2.5 出库视图代码图4.2.6 出库视图结果4.3建立存储过程查询月用气量最大的10个用户存储过程代码：BEGINselect 客户编号,出库量,出库日期 from gooutwhere 客户编号 in( select 客户编号 from customer where 出库日期>'2016-05-01' and 出库日期<'2016-06-01') order by 出库量 desc limit 10 ;END调用语句：call show_cost;调用结果（由于表内数据数

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

13、4.3-2图4.3.2 统计工人的送气量4.4建立触发器入库触发器作用：当插入一条入库数据时，gas表内对应煤气编号的存储量发生变化代码：Delimiter /Create trigger goin after insertFor each rowbeginupdate 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 t

14、rigger goout after insertFor each rowbeginupdate 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

15、问题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 职工用户顾客用户：如图5.5图5.5 顾客用户6 项目设计心得总结从需求分析开始到彻底完成这个项目，也花费了将近一个星期的时间。由于这门课结课较早，做这个项目的时候也忘得差不多了，造成做项目的过程中有很多不懂的地方，甚至出错造成数据库的重新构建。经过反反复复的出错修正出错修正，才意识到刚开始的需求分析、概念结构设计和逻辑结构设计是多么的重要，很多问题都是在开始分析的不全面造成的。例如在构建入库表时，开始只把供应商编号和煤气编号设为主键，造成同一供货商无法对同一煤气进行第二次供货。解决的方法就是在出货表中设置一个进货单号的属性，把进货