数据库综合实践
目录
一、实验目的
l 了解环境监测系统的总体设计。
l 掌握系统数据库设计。
l 熟悉数据库连接和访问。
l 熟悉系统功能模块设计。
l 熟悉数据库的存储与读取。
二、实验示例
1.系统设计
对用户的实时定位、环境检测与预警显示主要实现功能地图显示用户的位置信息,同时地图上显示手机端的位置信息;周围的温度、湿度、烟雾浓度和天气情况;关于温度、湿度和烟雾浓度报警和对天气、PM2.5等等的预警。
(1)系统功能需求
环境监测系统信息展示总表 |
|||||
当前时间 |
2018.06.17 |
00:00:00 |
|||
位置 信息 |
用户位置1 |
XXX |
|||
用户位置2 |
XXX |
||||
环 境 显 示 |
位置1 |
温度1 |
|
||
湿度1 |
|
||||
烟雾浓度 |
|
||||
位置2 |
天气类型 |
|
|||
温度2 |
|
||||
湿度2 |
|
||||
PM2.5 |
|
||||
预警 |
类型 |
当前值 |
阈值 |
备注 |
|
烟雾浓度 |
0 |
|
|
||
湿度 |
42 |
30 |
80 |
|
|
温度 |
23 |
40 |
较舒适 |
||
PM2.5 |
103 |
100 |
轻度污染 |
||
管理 权限 |
账号 |
||||
密码 |
①用户模块。、
用户模块有登陆,查询数据和添加、删除预警信息等功能。
②管理员模块。
管理员模块有增删查改。
本案例针对用户模块进行重点分析。
(2)数据库需求分析
①用户登录数据流图
用户注册主要是对注册表的操作,用户要完成注册,需要将自己的信息插入到注册表中,同时也提供用户对自己的注册栏进行查询、修改和删除功能。
图1 用户登录数据流图
②用户查询数据流图。
用户查询数据主要分为两种类型,分别访问到两个数据表,可以对大范围的环境情况进行查询的外网数据表,也可以对具体到室内的环境数据进行查询的传感器数据表。
图2 用户查询数据流图
③用户设置数据流图
用户对数据库的数据没有增加、删除和修改的权限,只能够对数据进行查询和做出反馈。
图3 用户设置数据流图
2、系统总体设计
(1)监测系统模块设计
图4 系统模块设计
(2)系统数据库设计
根据系统的功能需求,要对数据库进行合理设计。在本系统设计过程中,首先要对数据库进行需求分析,然后进行概念结构设计、逻辑结构设计、物理结构设计和数据库实施等操作。
概念结构设计中,经分析得到以下各个局部E-R图。
①用户与账户E-R图如图所示。同一个用户可以申请多个账户,但是同一个账户只能有一个用户,所以用户申请账户时,生成新的注册表。
图5 用户与账户E-R图
②用户与环境E-R图如图所示。同一个账户可以有多个外网的数据,也可以有多个传感器采集数据,同时同一个数据表亦可以为多个用户提供数据来源,所以在用户查询环境时,生成新的外网数据表和传感器采集数据表。
图6 用户与环境E-R图
③传感器与热点E-R图如图所示,一个传感器可以搜索获取多个热点的信息,同一个热点信号也可以被多个传感器接收到,生成新的热点数据表。
图7 传感器与热点E-R图
④合成用户模块整体E-R图
图8 合成用户模块整体E-R图
在次只介绍用户模块中的几个数据表。其整体描述如表1所示。
表1 各数据表描述
数据表 |
描述 |
log_info |
登录表 |
Sensor_data |
传感器采集数据表 |
Net_data |
外网数据表 |
AP |
室内定位热点表 |
接下来,分别对各个数据表进行描述。
①log_info表
该表描述登录的身份基本信息,包括用户ID、用户名、用户密码和系统。Log_info表的逻辑结构如表2所示。
表2 登录表log_info
序号 |
字段名 |
标识 |
类型 |
长度/Byte |
精度 |
允许空 |
主键 |
1 |
user_id |
用户ID |
int |
|
|
|
√ |
2 |
use_name |
用户名 |
varchar |
10 |
|
|
|
3 |
use_pw |
用户密码 |
varchar |
10 |
|
|
|
4 |
system |
系统 |
varchar |
10 |
|
|
|
创建表2的语句如下。
drop table if exists "log_info";
/*==============================================================*/
/* Table: "log_info" */
/*==============================================================*/
create table "log_info"
(
"user id" int not null,
"user name" varchar(10),
"user pw" varchar(10),
system varchar(10),
primary key ("user id")
);
alter table "log_info" add constraint FK_Reference_1 foreign key ("user id")
②sensor_data表
该表描述传感器采集数据信息,包括传感器id、温度、湿度和烟雾浓度的逻辑结构如表3所示。
表3 传感器采集数据信息表sensor_data
序号 |
字段名 |
标识 |
类型 |
长度/Byte |
精度 |
允许空 |
主键 |
1 |
sensor_DT |
采集数据 |
Datatime |
|
|
|
√ |
2 |
temperature |
温度 |
double |
10 |
2 |
|
|
3 |
humidity |
湿度 |
double |
10 |
2 |
|
|
4 |
ingredient |
烟雾浓度 |
double |
10 |
2 |
|
|
5 |
longitude |
经度 |
double |
10 |
6 |
|
|
6 |
latitude |
维度 |
double |
10 |
6 |
|
|
创建表3的语句如下。
drop table if exists "sensor_data";
/*==============================================================*/
/* Table: "sensor_data" */
/*==============================================================*/
create table "sensor_data"
(
Sensor_DT datetime not null,
temperature double(10,2),
humidity double(10,2),
ingredient double(10,2),
longitude double(10,6),
latitude double(10,6),
primary key (PI_DT)
);
ALTER TABLE `sensor_data` ADD CONSTRAINT `longitude` FOREIGN KEY (`longitude`) REFERENCES `ap` (`longitude1`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `sensor_data` ADD CONSTRAINT `lagitude` FOREIGN KEY (`latitude1`) REFERENCES `ap` (`latitude1`) ON DELETE RESTRICT ON UPDATE RESTRICT;
③net_data表
该表描述了我们从互联网上找到的数据信息表,包括id、温度、湿度、PM2.5浓度、天气类型、经度和纬度的逻辑结构如表4所示。
表4 外网数据信息表net_data
序号 |
字段名 |
标识 |
类型 |
长度/Byte |
精度 |
允许空 |
主键 |
1 |
net_DT |
外网数据 |
Datetime |
|
|
|
√ |
2 |
temperature |
温度 |
double |
10 |
2 |
|
|
3 |
humidity |
湿度 |
double |
10 |
2 |
|
|
4 |
Pm2.5 |
Pm2.5 |
double |
10 |
2 |
|
|
5 |
longitude |
经度 |
double |
10 |
6 |
|
|
6 |
latitude |
维度 |
double |
10 |
6 |
|
|
7 |
Weather_type |
天气类型 |
varchar |
10 |
|
|
|
创建表4的语句如下。
drop table if exists "net_data";
/*==============================================================*/
/* Table: "net_data" */
/*==============================================================*/
create table "net_data"
(
net_DT datetime not null,
pm2.5 double(10,2)
temperature double(10,2),
humidity double(10,2),
longitude2 double(10,6),
latitude2 double(10,6),
weather_type varchar(10),
primary key (net_DT)
);
④AP表
该表描述了我们的Access Point信息,用以室内的定位功能实现,包括热点名、MAC地址、信号强度、精度和维度。AP表的逻辑结构如表5所示。
表5 热点表AP
序号 |
字段名 |
标识 |
类型 |
长度/Byte |
精度 |
允许空 |
主键 |
1 |
APname |
热点名 |
varchar |
|
|
|
√ |
2 |
macaddress |
MAC地址 |
varchar |
50 |
|
|
|
3 |
longitude |
经度 |
double |
10 |
6 |
|
|
4 |
latitude |
维度 |
double |
10 |
6 |
|
|
创建表5的语句如下。
drop table if exists AP;
/*==============================================================*/
/* Table: AP */
/*==============================================================*/
create table AP
(
APname varchar(10) not null,
macaddress varchar(50),
longitude double(10,6),
latitude double(10,6),
primary key (APname)
);
三、系统数据库实现
(1)打开MySQL数据库。
在DOS命令窗口输入 mysql -hlocalhost -uroot -p回车 进入mysql数据库,其中-h表示服务器名,localhost表示本地;-u为数据库用户名,root是mysql默认用户名;-p为密码,如果设置了密码,可直接在-p后链接输入,如:-p123456,用户没有设置密码,显示Enter password时,直接回车即可。注意,如果你的mysql没有安装在C盘下,你需要先使用DOS命令进入mysql的安装目录下的bin目录中。以我的电脑为例,方法如下:输入D:进入D盘,在输入cd D:\Works\MySQL5.5.25\bin进入到mysql的bin目录下才可以输入 mysql -hlocalhost -uroot -p
图9 打开MySQL数据库
(2)MySQL 管理界面连接
Navicat for MySQL是一款强大的 MySQL 数据库管理和开发工具,它为专业开发者提供了一套强大的足够尖端的工具,但对于新用户仍然易于学习。Navicat for MySQL 基于Windows平台,为 MySQL 量身订作,提供类似于 MySQL 的用管理界面工具。此解决方案的出现,将解放 PHP、J2EE 等程序员以及数据库设计者、管理者的大脑,降低开发成本,为用户带来更高的开发效率。
图10 选择“连接”对话框
(3)新建数据库
①在Navicat导航视图的左边连接导航栏中的空白区域点击右键就可以看到【新建数据库】的命令,我们来建一个监测环境信息的数据库:environ_inspector
图11 新建数据库
②【新建表】建完数据库后接下来就是建用户注册表:log_info;
在建表过程中有一个地方要特别的注意,就是“栏位”,对于初次使用Navicat的新手来说,这个比较陌生,它的意思就是我们通常所说的“字段”,工具栏中的“添加栏位”即添加字段的意思,不要弄混了就成,添加完所有的字段以后要根据需求设置相应的“主键”。
图12 新建用户注册表
图13 新建net_data的SQL预览
图14 创建表的SQL语句方法
③建立数据库完成。
图15 各数据表
④查看图表
图17 模型图表
四、将数据导入数据库
(1)传感器数据
①传感器数据采集
图18 传感器数据采集
②数据导入数据库
MySQLdb提供了connect方法用来和数据库建立连接,接收数个参数,返回连接对象:
代码如下:
conn=MySQLdb.connect(host="localhost",user="root",passwd="123456",db="environ_inspector",charset="utf8")
(2)外网数据
①数据爬取
爬虫是一种按照一定的规则,自动地抓取万维网信息的程序或者脚本,使用java Jsoup框架写一个获取天气数据的爬虫。
爬取网址:http://www.tianqi.com/ 天气网
Jsoup框架优势:
1.实现了CSS选择器语法,页面内容提取非常方便。
2.解析算法不使用递归,而是enum(枚举)配合状态模式遍历数据(先预设所有语法组合),减少性能瓶颈。另外,不需要任何第三方依赖。
Jsoup获取文档分三个步骤。
1.根据输入构建DOM树
2.解析CSS选择字符串到过滤表中
3.用深度优先算法将树状节点逐一过滤
②数据解析
通过网页源码获取到网页的dom结构,可以知道,我们需要的数据,主要都存在于 weather_info 这个类选择器所在的节点当中,Jsoup 的document类提供了非常方便的解析方式,即类似于jqury 的复合选择器。
图19 获取到的文档
Selector选择器概述
tagname: 通过标签查找元素,比如:a
ns|tag: 通过标签在命名空间查找元素,比如:可以用 fb|name 语法来查找 <fb:name> 元素
#id: 通过ID查找元素,比如:#logo
.class: 通过class名称查找元素,比如:.masthead
[attribute]: 利用属性查找元素,比如:[href]
[^attr]: 利用属性名前缀来查找元素,比如:可以用[^data-] 来查找带有HTML5 Dataset属性的元素
[attr=value]: 利用属性值来查找元素,比如:[width=500]
[attr^=value], [attr$=value], [attr*=value]: 利用匹配属性值开头、结尾或包含属性值来查找元素,比如:[href*=/path/]
[attr~=regex]: 利用属性值匹配正则表达式来查找元素,比如: img[src~=(?i)\.(png|jpe?g)]
*: 这个符号将匹配所有元素
图20 解析获取的数据
③数据存储
调用上述conn数据接口将数据存入数据库:
图21 将外网数据导入数据库
五、实验心得体会
在当今信息化的社会里,数据库可以说已经融入到我们生活的方方面面中,如交通运输、银行金融、工商企业等等。只要有大量的数据要管理或者需要有大量数据支持的工作,都要使用到数据库,它为我们的生活带来了便捷。
结合我所学习的内容,谈谈关于数据库课程的总结。
我们首先从绪论了解了数据库的概念,介绍了数据模型和三层模式数据库。理解实体-联系方法,并学会绘制e-r图。此外还应掌握概念数据模型的意义和传统的三大数据模型,以及数据独立性和数据库三层模式结构。接着开始着重讲述现在普遍使用的关系数据库。包括关系数据模型的数据结构和基本术语,关系模型的完整性约束和关系代数运算。重点是关系模式完整性的分类和功能,以及关系代数中集合运算和关系运算。最后介绍了关系数据系统的三层模式结构。之后讲述关系数据库的标准语言sql的定义功能、查询功能、操作和控制功能。重点在于数据查询功能。另外还介绍了视图的用法和动态sql中定义、操作和查询功能。最后一章,介绍安全性,包括安全性措施的层次、数据库管理系统的安全功能等,用户管理和角色管理,权限管理。其他的安全问题包括:数据加密、审计、统计数据库和用户定义的安全性措施。介绍了事务的概念、性质以及sql对事务的支持。并发控制——干扰问题、可串行性、*、死锁、隔离级别、*与隔离级别;恢复——故障类型、备份类型、日志的概念、恢复模型、备份转储、还原。实验课上,实际完成数据库设计的过程,完善e-r模型中的概念,依赖关系,强制联系;主要掌握数据库建立的步骤。
数据库这一门课就要结束了,在老师的耐心教学和答疑中,这一学期的学习让我收获了很多,最后感谢老师的教导!