数据库设计—

GitHub: https://github.com/wsxq2/BusSecurityManagement
Web前端: https://gj.wsxq2.xyz/ (用户名为：admin，密码为：658231)
理论部分: 数据库设计——理论

实践使用的是《数据库系统》一课的上机题目——设计一个简单的公交安全管理系统。应注意到 MariaDB 和 SQL 非常相似（MariaDB fork from MySQL），所以它们的好多命令是一样的，本文没有严格区分。下面是本文的目录：

1 题目要求
2 需求分析
3 概念模型设计
4 逻辑结构设计
5 物理结构设计
6 数据库实施
7 前台程序设计
8 遇到的问题
9 链接

1 题目要求

1.1 后台数据库要求

1.2 前台程序要求

1.3 注意事项

1.4 上机报告要求

2 需求分析

2.1 信息要求

根据公交安全管理系统题目要求1.PNG（即上一节的第一张图片）分析如下：

一个车队对应多条线路
一条线路对应多辆车
一个车队对应一个队长
一条线路对应多个司机，一条线路对应一个路队长，一个路队长是一个司机（即一个司机对应一个或零个(1:1联系)路队长）
一个司机对应零个或多个(1:n联系)违章，违章有四个类型（闯红灯、未礼让斑马线、压线、违章停车）
前台程序应赋予队长和路队长录入司机违章的权限。违章包括司机、车辆、车队、线路、站点、时间、违章类型

其实更严格来说，对于每个联系，还应该考虑是否包含 0 的情况。如一个线路对应多辆车，是否允许某一个线路对应 0 辆车（比如说该线路是新建线路，尚未分配车辆，其实这个情况不需要考虑，因为既然是要新建线路了，那么必然是打算分配车辆以投入使用的），以及某一辆车对应 0 个线路（比如它是辆新买的车，目的是备用，暂未分配线路）

（另外我突然发现自己的设计没有满足查询违章时显示车队和线路列，不过通过简单的修改应该就能实现）

根据公交安全管理系统题目要求2.PNG（即上一节的第二张图片）分析如下：

司机信息包括工号、姓名、性别
车辆信息包括车牌号、座数

2.2 处理要求

根据公交安全管理系统题目要求2.PNG（即上一节的第二张图片）分析如下：

录入司机基本信息
录入汽车基本信息
录入司机的违章信息
查询某个车队下的司机基本信息
查询某名司机在某个时间段的违章详细信息

查询某个车队中的每名司机在某个时间段的违章统计信息，如下所示：

张三，2次闯红灯，4次未礼让斑马线
李四，1次压线，6次违章停车

注：此要求和上一节题目要求不符，但这才是老师想表达的要求

2.3 安全性与完整性要求

根据公交安全管理系统题目要求3.PNG（即上一节的第三张图片）分析如下：

在数据库的设计过程中需要运用规范化理论，避免出现插入异常、删除异常、数据冗余等问题
必须设定关系的完整性规则，如实体完整性（例如设置主码），参照完整性（例如设置外码和对应的主码），用户自定义完整性（例如性别只能为“男”或“女”）

2.4 数据字典

由于 ERWin 主文件中已经包含了数据字典中应有的所有信息，且限于篇幅，故在此不再赘述

3 概念模型设计

使用 ERDPlus 这个网页工具进行 E-R 图的绘制，根据分析需求一步中的信息要求可得到如下 E-R 图：

公交安全管理系统E-R图

4 逻辑结构设计

使用百度网盘——ERWin7.3破解版.zip （提取码: g5v4）进行逻辑结构和物理结构设计。将上一步中的 E-R 图转换为关系模型即可得到如下逻辑模型：

公交安全管理系统逻辑模型

注意：ERWin 正版相当贵，破解版又比较老，所以一个可能的替代方案是 MySQL Workbench或者 SQLDBM 。其中 SQLDBM 是一个网页工具，界面非常友好，操作简单方便。我只是浅度使用，感觉最大的问题是功能还是有些不够，如和数据库连接从而直接导入设计

5 物理结构设计

在 ERWin 中细化上一步中的逻辑模型即可得到如下物理模型：

公交安全管理系统物理模型

6 数据库实施

6.1 环境搭建

6.1.1 CentOS 搭建 LAMP 服务

这一部分可以参考我的另一篇博客： CentOS7搭建LAMP服务

6.1.2 Vim 插件安装及使用

因为我使用 Vim 编写 SQL 语句，故在网上找到了如下两个和数据库相关的有用的插件（dbext和vim-autoformat）：

dbext: 该插件包含函数/映射/命令，以使 Vim 能够访问多种数据库。使用 vim-plug安装，整个过程灰常简单：
1. 先在~/.vimrc中的相应位置添加如下内容:
```
1
Plug 'vim-scripts/dbext.vim'
```
2. 然后在Vim中输入以下命令即可安装：
```
1
:PlugInstall
```
至于如何使用，我也不知道。只知道这个插件有点复杂但是异常强大。欢迎大家补充。
vim-autoformat: 这一部分的内容可参考我的另一篇博客——Vim代码格式化插件vim-autoformat

6.2 实施前的准备工作

创建数据库和该项目的管理员用户。

CREATE DATABASE IF NOT EXISTS bus CHARACTER
SET = 'utf8';
DROP USER 'wsxq'@'%';
CREATE USER 'wsxq'@'%' IDENTIFIED BY '658231';
GRANT ALL PRIVILEGES ON bus.* TO 'wsxq'@'%';
FLUSH PRIVILEGES;

将上述 SQL 语句保存为文件create_database_and_user.sql，然后在 bash 中执行如下语句：

mysql -uroot -p'your mysql root password' < create_database_and_user.sql

6.3 使用前向工程实施

即在 ERWin 中点击：Tools > Forward Engineer > Schema Generation，进行相应的配置后点击Preview然后复制 SQL 语句并保存到create-table.sql中，然后在 bash 中执行如下语句：

mysql -uwsxq -p'658231' bus < create-table.sql

6.4 生成并导入测试数据

使用 Data Generator for MySQL 生成测试数据，该软件是付费软件，只能试用 30 天。但是可以在虚拟机中安装 Windows 系统然后使用虚拟机的快照功能“无限使用”。配置好后导出为 SQL 语句，并保存到insert-data.sql文件中。然后在 bash 中执行如下语句：

mysql -uwsxq -p'658231' bus < insert-data.sql

其它不错的测试数据生成工具有SQL Data Generator和https://www.generatedata.com/。二者都是网页工具，都是配合数据库使用的。此外还有单独生成人名的网页工具姓名生成器，以及用于获得公交站点的网页西安公交站点

6.5 实现处理要求

我为了学到更多的东西以及为了后面更好地维护，选择了将所有的处理要求封装为过程。一个处理要求对应一个过程。根据分析需求一步中的处理要求可得到如下四个过程（因为第三个处理要求有歧义，题目上写的和老师要求的略有不同，故第三个处理要求对应了两个过程）：

DELIMITER //
--用户需求一：查询某个车队下的司机基本信息
DROP PROCEDURE IF EXISTS get_siji_by_chedui //
CREATE PROCEDURE
get_siji_by_chedui( cd INT)
BEGIN
        SELECT gonghao,xingming,xingbie FROM RenYuan WHERE gonghao IN (SELECT gonghao FROM SiJi WHERE suoshuxianlu IN (SELECT xianlubianhao FROM XianLu WHERE suoshuchedui=cd));
END
//

--用户需求二：查询某名司机在某个时间段的违章详细信息
DROP PROCEDURE IF EXISTS get_weizhang_by_siji_and_datetime //
CREATE PROCEDURE
get_weizhang_by_siji_and_datetime( sj TEXT, dt1 DATETIME, dt2 DATETIME)
BEGIN
        SELECT ID,siji,xingming,cheliang,weizhangleixin,shijian,zhandian FROM WeiZhang,RenYuan WHERE shijian BETWEEN dt1 AND dt2 AND WeiZhang.siji=RenYuan.gonghao AND xingming=sj;
END
//

--用户需求三：查询某个车队在某个时间段的违章统计信息（直接根据题目要求写的）
DROP PROCEDURE IF EXISTS get_weizhangtongji_by_chedui_and_datetime2 //
CREATE PROCEDURE
get_weizhangtongji_by_chedui_and_datetime2( cd INT, dt1 DATETIME, dt2 DATETIME)
BEGIN
        SELECT weizhangleixin, COUNT(id) FROM WeiZhang WHERE shijian BETWEEN dt1 AND dt2 AND siji IN (SELECT gonghao FROM SiJi WHERE suoshuxianlu IN (SELECT xianlubianhao FROM XianLu WHERE suoshuchedui=cd)) GROUP BY weizhangleixin;
END
//

--用户需求三升级版：查询某个车队的所有司机在某个时间段的违章统计信息（老师的本意）
DROP PROCEDURE IF EXISTS get_weizhangtongji_by_chedui_and_datetime //
CREATE PROCEDURE
get_weizhangtongji_by_chedui_and_datetime( cd INT, dt1 DATETIME, dt2 DATETIME)
BEGIN
        SELECT gonghao,xingming,weizhangleixin,COUNT(*) FROM RenYuan,WeiZhang WHERE WeiZhang.siji=RenYuan.gonghao AND siji IN (SELECT gonghao FROM SiJi WHERE suoshuxianlu IN (SELECT xianlubianhao FROM XianLu WHERE suoshuchedui=cd)) AND shijian BETWEEN dt1 AND dt2 GROUP BY gonghao,xingming,weizhangleixin ORDER BY gonghao;
END
//

DELIMITER ;

/* for test
CALL get_siji_by_chedui(1);
CALL get_weizhang_by_siji_and_datetime('钱智美', '2018-01-01 00:00:00', '2018-10-01 00:00:00');
CALL get_weizhangtongji_by_chedui_and_datetime(1, '2018-01-01 00:00:00', '2018-10-01 00:00:00');
 */

6.6 触发器

我的这个设计中，多弄了个人员表，司机和队长都参照人员表，并根据人员中的列职位来确定。所以需要设定两个触发器，在往司机或队长表中插入数据（人员）时检查该人员在人员表中的职位是否对应。相应的 SQL 语句如下：

DELIMITER //

CREATE TRIGGER zhiwei_siji
BEFORE INSERT ON SiJi FOR EACH ROW
BEGIN
        DECLARE zw enum('司机','队长');
        SELECT zhiwei FROM RenYuan WHERE RenYuan.gonghao=NEW.gonghao into zw;
        if zw!='司机' then
           SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '插入的人员不是司机!';
        end if;

END;//

CREATE TRIGGER zhiwei_duizhang
BEFORE INSERT ON DuiZhang FOR EACH ROW
BEGIN
        DECLARE zw enum('司机','队长');
        SELECT zhiwei FROM RenYuan WHERE RenYuan.gonghao=NEW.gonghao into zw;
        if zw!='队长' then
           SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '插入的人员不是队长!';
        end if;

END;//

DELIMITER ;

将其保存到triggers.sql文件中，然后在 bash 中执行如下命令将以上触发器导入数据库bus：

mysql -uwsxq -p'658231' bus < triggers.sql

6.7 其它 SQL 语句

6.7.1 从数据库导出 csv 文件

由于我的 C 语言前端导入数据的方式是通过 csv 文件导入（因为这样比较方便，csv 文件可以通过 Execl 表导出），为了获取 csv 文件（我不可能自己用 Excel 一个一个地录数据），使用了 MariaDB 的导出为csv 文件的功能，但是由于调用次数很多（有多少个表就得调用多少次），所以我把它封装为一个过程，参数为要导出的表，如下所示：

DELIMITER //

DROP PROCEDURE IF EXISTS `export_csv` //
CREATE  PROCEDURE `export_csv`(IN tab_name TEXT )
BEGIN
        SET @t1 =concat("SELECT * FROM ",tab_name," INTO OUTFILE 'csv/",tab_name,".csv' FIELDS ENCLOSED BY '\"' TERMINATED BY ',' ESCAPED BY '\"' LINES TERMINATED BY '\r\n';");
        PREPARE `stmt` FROM @`t1`;
        execute `stmt`;
        deallocate prepare `stmt`;
END
//

DELIMITER ;

--CALL export_csv('RenYuan');

注意：这个 SQL 语句应使用 MariaDB 的 root 用户执行，否则后面为测试 C 语言前端的导入数据功能时执行export_csv.sh时会出现权限不够的错误（因为该 SQL 语句将 csv 文件导入到了/var/lib/mysql/csv/中，该目录的写入要求root权限，而我也不知道怎么修改以让其导入到指定目录）。即在将其保存到procedure_root.sql文件中后应在 bash 中执行如下命令：

mysql -uroot -p'your mysql root password' bus < procedure_root.sql

6.7.2 创建一个用于 Web 前端身份验证的表

为了赋予不同身份的人（如队长，司机，路队长，最高权限的管理员）以不同的操作权限（即在登陆之后能看到的东西不一样），需要创建一个用于存储用户信息的表user_info（但实际上我只记录了管理员的信息，在登陆之后也只有一个界面，因为精力和时间有限）。相关的 SQL 语句如下：

DROP TABLE IF EXISTS user_info;
CREATE TABLE `user_info` ( `username` char(30) NOT NULL,
        `password` char(30) DEFAULT NULL,
        PRIMARY KEY (`username`));

INSERT INTO user_info
VALUES('admin',
        '658231');

将其保存为create_user_info.sql文件然后执行如下语句：

mysql -uwsxq -p'658231' bus < create_user_info.sql

7 前台程序设计

关于前台程序设计由于感觉 C语言比较简单，于是就先做了 C 语言的前端，而后又感觉 C 语言的界面对于大多数人来说不友好（其实我感觉还行）且程序本身有点问题无法解决，于是又做了 Web 网页设计的前端。下面将分别进行讲述。

7.1 C 语言

7.1.1 开发环境搭建

我在 CentOS 7（虚拟机，并非实际的服务器）上进行整个开发工作，涉及到的开发环境搭建主要包括安装和MariaDB数据库连接需要使用的库（即头文件）和 VIM 配置

主要是安装和MariaDB数据库连接需要使用的库（即头文件）。使用如下命令即可：

yum install mariadb-devel.x86_64

Vim 配置：目的在于让 Vim 编辑 C 源文件变得非常方便。涉及到的插件如下：
- YouCompleteMe：超级强大的代码补全引擎。安装过程可参考我的另一篇博客：Vim代码补全插件YouCompleteMe
- vim-autoformat：参考另一篇博客Vim代码格式化插件vim-autoformat

7.1.2 学习 MariaDB C API

由于 MariaDB 来自于 MySQL，所以 MariaDB 的 C API 和 MySQL 的差不多，又因为 MySQL 的历史悠久，知名度大，社区文档丰富，所以学习时使用的是 MySQL C API 的教程——MySQL C API programming tutorial。在我的 GitHub: 公交安全管理系统中也保留了学习时使用的源码，和该教程有一定的对应关系。

7.1.3 程序源代码

在我的 GitHub: 公交安全管理系统中：https://github.com/wsxq2/BusSecurityManagement/blob/master/front-end/c/main.c

7.2 Web 网页设计

7.2.1 开发环境搭建

主要是对 Vim 进行配置，使用了插件 Emmet-vim 用于编写 html 和 css 文件，超级强大，大大提高了效率。

其配置和使用可以参考我的另一篇博客：Vim-HTML和CSS插件emmet-vim

7.2.2 学习网站相关的知识

Web 前端：
- HTML: https://www.w3schools.com/html/default.asp
- CSS: https://www.w3schools.com/css/default.asp
- 网页框架 W3.CSS: https://www.w3schools.com/w3css/default.asp
- JavaScript: https://www.w3schools.com/js/default.asp
- JavaScript 库 JQuery ：https://www.w3schools.com/jquery/default.asp
Web 后端：
- PHP: https://www.w3schools.com/php/default.asp
- SQL: https://www.w3schools.com/sql/default.asp
XML: https://www.w3schools.com/xml/default.asp

事实上，本项目真正用到的只有 HTML CSS JavaScript W3.CSS PHP SQL 中非常基础的部分

另外对于基本的网站登陆，我参考了用PHP和HTML写一个简单的网站登录注册项目

7.2.3 程序源代码

在我的 GitHub: 公交安全管理系统中：https://github.com/wsxq2/BusSecurityManagement/tree/master/front-end/web

主要文件如下:

├── css/
│   └── w3.css* # W3.CSS 网页框架
├── js/
│   └── my.js* # 自写的用于 AJAX 请求的 JavaScript
├── gettable.php* # 根据 SQL 语句获取表
├── index.php* # 默认页面，登陆页面
├── insertdata.php* # 给定参数表名，往相应的表插入数据
├── login.php* # 用于登陆的PHP
└── main.php* # 登陆后显示的主页面

2 directories, 7 files

8 遇到的问题

MySQL Trigger to prevent INSERT under certain conditions?

DELIMITER //
   
CREATE TRIGGER zhiwei_siji
BEFORE INSERT ON SiJi FOR EACH ROW
BEGIN
	DECLARE zw enum('司机','队长');
	SELECT zhiwei FROM RenYuan WHERE RenYuan.gonghao=NEW.gonghao into zw;
	if zw!='司机' then
	   SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '插入的人员不是司机!';
	end if;
END;//
   
DELIMITER ;

详情参考： MySQL Trigger to prevent INSERT under certain conditions

Prepare Load Data Infile?

Prepare does not support Load Data Infile: The list of statements that you can run with PREPARE are documented in this page: https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html under the subheading “SQL Syntax Allowed in Prepared Statements”. Note this list may be different in earlier versions of MySQL.

即如下语句是错误的：

DELIMITER //
DROP PROCEDURE IF EXISTS import_csv //
CREATE PROCEDURE import_csv(tab_name TEXT)
BEGIN
    SET @t1=CONCAT("LOAD DATA LOCAL INFILE 'csv/", tab_name, ".csv' INTO TABLE ", tab_name, " FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n'");
    SELECT @t1;
    PREPARE stmt FROM @t1;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END
//
DELIMITER ;
DROP TABLE IF EXISTS RenYuan;
CALL import_csv('RenYuan');

错误如下：

MariaDB [bus]>CALL import_csv('RenYuan');
+-------------------------------------------------------------------------------------------------------------------------------+
| @t1                                                                                                                           |
+-------------------------------------------------------------------------------------------------------------------------------+
| LOAD DATA LOCAL INFILE 'csv/RenYuan.csv' INTO TABLE RenYuan FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '
' |
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
   
ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

直接在 ERWin 中将前向工程的代码导入数据库？经测试，如果前向工程中的代码有中文，ERWin7.4 会导入失败

9 链接

下面总结了本文中使用的所有链接：

9.1 项目相关

GitHub: https://github.com/wsxq2/BusSecurityManagement
Web前端: https://gj.wsxq2.xyz/ (用户名为：admin，密码为：658231)
理论部分: 数据库设计——理论