数据库课程大作业——数据分析与数据管理系统实践

数据分析与数据管理系统实践

一、数据介绍

公司员工信息 ，Excel文件

提供 44 名员工 姓名、所在部门工作职务车的编号

公司员工的信用卡刷记录（两周） ，Excel 文件

约 1400 行信用卡刷记录，内容包括：员工姓名、地点称金额时间

二、大作业要求

1、根据自己所学数据库操作和管理知识，创建一个数据库，把两个Excel表导入这个数据库，并对应建立两个表；对两个表创建主键、外键、索引，还可以自己增加一些列，或者做一些视图；

2、综合运用自己所学的查询语句和SQL开发技巧对数据进行统计分析，并要求回答如下问题；

问题一：员工的消费有一些什么有趣的模式和规律，请写出你分析的SQL语句，并截取分析结果，说明你是如何找到的这些模式和规律的；（不少于5个模式）

问题二：员工的消费有什么异常现象，你是如何找到这个异常的，请写出你得到分析的SQL语句，并截取分析结果。（不少于3个异常）

3、学习一门开发工具，开发一个小型数据库管理系统

系统基本功能1：增加、删除和修改这两个表的记录

系统基本功能2：提供输入框接受用户手动输入SQL查询语句，能输出用户任意输入的SQL查询语句的查询结果。

系统基本功能3：能够按人、按消费地点、按天这三种组合，设计并输出几个统计报表。

 

三、实验过程及结果

1、创建“大数据作业”数据库后，右键点击该数据库，选择【任务】—>【导入数据】，如右图所示：

2、选择导入的数据源，下一步，选择导入的数据库目标，如下图所示：

3、在指定表复制或查询中应该可以选择【编写查询已指定要传输的数据】对数据进行清洗，尝试着写SQL语句过滤到脏数据，但没有成功，所以就选择直接先全将数据导入到数据库，再对数据进行过滤。

4、通过对数据的查询，我们可以发现creditcard表中存在一些类似“Katerina抯 Caf?28”的奇字符，下面在数据库里对表中数据进行清洗。

5、新建查询对数据清洗，并将新的数据建立一张新表。

将employee表中FirstName和LastName合并，创建newemployee表

select FirstName+' '+LastName as name,CurrentEmploymentType,CurrentEmploymentTitle

into newemployee

from employee

将creditcard表中FirstName和LastName合并，创建newcreditcard表

select FirstName+' '+LastName as name,timestamp,location,price

into newcreditcard

from creditcard

通过对两张表的name查询，可以发现newemployee表共44名员工，newcreditcard表去掉为NULL共55人

select distinct name

from newemployee



select distinct name

from newcreditcard

在这里我们对存在newemployee表中的44名员工进行对他们的消费记录的查询，可以发现creditcard表中错误不会影响，当然也可以尝试清洗一下数据

select *

into newcreditcard1

from newcreditcard

where name in (

select name

from newemployee

)

清洗数据：

select *

into newcreditcard

from credtcard

where location not like '%[0-9]%'and FirstName is not NULL and LastName is not NULL

6、设置表中数据的数据类型、主键，newemployee表中FirstName、LastName没有重复项，可以设置为主键，而newcreditcard1表中有重复项，如果要设置主键，就必须FirstName、LastName、timestamp、location都设置为主键。

7、分析数据：

（1）模式和规律

① 查询每个人两周内的消费总金额和职业，可以发现卡车司机职业的消费金额比其他人都高，Valeria Morlun消费最多。

② 查询所有消费地点的消费人次，可以发现Hippokampos、Guy's Gyros、Brew've Been Served普遍比其他地方消费次数高，比较受欢迎。

③ 查询每个人到某个地点的消费次数，可以发现大部分人两周内基本每天都去某个地方消费一次。

④ 查询每天所有人的消费次数总和，可以发现6号至10号五天和13号至17号五天，消费次数比11、12号和18、19号要多，可以猜想工作日出门次数比周末次数多，消费次数多。

⑤ 查询每天某一时间段内的消费次数，将一天分24小时为【00:00:00-03:00:00】、【03:00:00-06:00:00】、【06:00:00-09:00:00】、【09:00:00-12:00:00】、【12:00:00-15:00:00】、【15:00:00-18:00:00】、【18:00:00-21:00:00】、【21:00:00-24:00:00】八个时间段，发现【00:00:00-03:00:00】、【03:00:00-06:00:00】、【15:00:00-18:00:00】这几个时间段消费次数较少，大多数消费集中在其他时间段：早餐、午餐、下午茶、晚餐。

【06:00:00-09:00:00】

【09:00:00-12:00:00】

【12:00:00-15:00:00】

【15:00:00-18:00:00】

【18:00:00-21:00:00】

【21:00:00-24:00:00】

【00:00:00-03:00:00】

【03:00:00-06:00:00】

（2）异常现象

① Adan Morlun和Claudio Nant两人消费次数都比较少且平均消费金额比较大，最大单笔消费也比较多。

select FirstName,LastName,COUNT(*)as '消费次数',SUM(price)as '总金额',AVG(price)as '平均消费金额',MAX(price)as '单笔最大消费'

from newcredtcard

group by FirstName,LastName 

order by AVG(price)desc

② 13号的凌晨【00:00:00-03:00:00】和12、13号【03:00:00-06:00:00】有消费记录

③ Maximum Iron and Steel、Abila Scrapyard这几个地点，消费次数少，且金额大，平均金额也很大。

select location,COUNT(*)as '消费次数',SUM(price)as '总金额',AVG(price)as '平均消费金额',MAX(price)as '单笔最大消费'

from newcreditcard

group by location

order by AVG(price)desc

8、数据库开发

开发工具：Delphi 2010

开发环境：Windows XP（在虚拟机上完成的，SQL Server Management Studio也在XP里，因为我的主机Win10安装不成功）

开始选择Delphi 7 ，但感觉界面不太友好，就又重新选了Delphi 2010安装，安装网上有教程，**也有，但没有**出来，且只有14天的试用期。DevExpress VCL 13.1.4是后面网上找教程安装的插件，主要是一些控件的使用会用到，根据网上教程以及《Delphi程序开发范例宝典》进行数据库开发，具体实验截图见下：

主界面：

主体框架结构：

Ucreadtcard.pas：对creditcard表（这里命名错了）的增删改操作的主程序；

UDMmain.pas：数据库连接模块，后面会看到；

Uemployee.pas：对employee表的增删改操作的主程序；

Ugroup.pas：对creditcard表分组查询操作（按人、地点、天）的主程序；

Umain.pas：主窗口程序；

USQL.pas：SQL查询的主程序

主窗口：设置了SQL查询、employee表、creditcard表、统计，四个按钮，通过dxBarManager进行管理，点击触发通过ActionList管理，dxSkinController界面皮肤管理控件。

按钮触发事件代码如下：

Employee表的窗口界面，一张表控件和数据库导航控件，导航控件可以对数据库进行增删改操作，creditcard表一样。

ADOConnection连接数据库，ADOQuery查询数据库，DataSource查询后的数据源

SQL查询界面，Memo接收SQL语句，查询按钮触发事件进行查询，表输出结果。

设计代码如下：

分组查询，设置查询条件，按人、地点、时间进行查询。

给ComboBox设置选项，即查询列下的所有数据对象，代码如下图：

按钮触发，进行查询，代码如下：

实验测试结果如下：

启动主界面；

点击SQL查询按钮，输入SQL语句，点击查询。

点击employee表，进行对该表的增删改，增是“+”，删是“-”，改直接在表中改，然后点击“√”

增加数据的效果，SQL Sever数据库中增加了这么一行，如下图：

删除数据的效果，选中某行数据，点击“-”按钮，删除数据。

改，直接在表中改就行，然后点击“√”按钮。

点击统计按钮，进行分组查询，从下拉框中选出查询的对象，点击查询就可以进行条件查询。

 

四、总结

通过本次数据库大作业实验，进一步巩固了我对数据库的基本操作，同时锻炼了对数据的分析能力，以及初步认识和学习数据库开发，掌握了一些数据库开发的基本操作。对于语言，无论是SQL语言、数据库开发语言，还是C、Java，都有其固定的结构，和类似的语法，函数调用、参数传递，可能一看就知道它在做什么，还有网上****资源很多，对于语言、开发的学习有很大帮助，希望在今后学习中能提升更多。