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

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

一、数据介绍

公司员工信息 ,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,都有其固定的结构,和类似的语法,函数调用、参数传递,可能一看就知道它在做什么,还有网上视频教程资源很多,对于语言、开发的学习有很大帮助,希望在今后学习中能提升更多。

没有更多推荐了,返回首页