数据库大作业——警大学生内卷辅助系统
数据库大作业开发文档
项目简介：
??“我们被淹没在网络数据资料的海洋中，却又忍受着知识的饥渴。”曾几何时，你是否也在某些交流群中忍受着泥沙俱下带来的痛苦与迷茫，是否也曾在一些选修课程上焦虑地看着窗外感受着时间的流逝。 这是一个数据爆炸的年代，也是一个人们所谓“内卷”的年代；我们被海量资源包围，却又缺乏着真正有用的资源。
??为了帮助警大学生这一群体更好地学（nei）习（juan），我们准备针对大家迫切需要的”选课指导“、”自习场地选择“以及”比赛组队“等方面设计出一款”警大学生内卷辅助系统”1.0版本的数据库系统，将会包含大家最喜爱的选修课评级、自习场地选择以及比赛组队等模块，针对用户需求，提供各种现成的增删查改方法。由于时间与技术有限，只能搜集部分的数据，且大部分数据需要由DBA来手动更新，交互界面也比较简陋。或许在未来，我们能够挖掘出更多的实时数据信息，做出更好的系统。
参与成员 ：
- 项目管理员：施南汐
- 需求分析：陈俊儒、张万乘
- 可行性分析及数据库设计：施南汐、袁远
- 系统实现及数据录入：邱杨安、穆昭宇
- GUI设计：施南汐
项目概述
目的
为有意内卷的同学提供各种方面的便利，促进其更高效地内卷。
背景
- 如今警大选修课多种多样，缺乏提前研究的同学经常会踩雷，选到不适合自己的选修课，或是遭遇手写论文、答辩结课或1500米体测结课等情况。
- 同学们选择自习场地总会有一些纠结，有时产生分歧，不如直接采用我们推荐的最优地点，节省选择的时间；
- 有些同学希望通过比赛找到自己的方向，抑或是加点综测分，为了能更好的组队，需要一个比赛组队系统。
需求分析
- 选课助手
选修课是大学学习生活中非常重要的一部分，往往能起到能起到开拓眼界、发掘兴趣的作用，但是很多学生对选修课程与自身发展的关系缺乏考虑，选择了与自身发展毫不相关的课程，并疲于点名、演讲、论文答辩、考试，浪费了很多非常宝贵的学习时间，得不偿失。为了让大家更好地意识到自己的发展方向，选择到自己真正需要的选修课，我们推出了选课指导这一模块。
经过需求分析员的调查：同学们对于选课的需求主要如下：
- 不同的研究方向有着不同的学习路径，很多同学因为对自己感兴趣的方向不够了解，从而在选修课的选择上有一些迷茫，希望我们的项目能够根据每个人的专业乃至目前的学习程度推荐适合的选修课。有时一些选修课需要有先修课，也希望我们能给予提示。当然有些同学对学校开设的大部分选修课不感兴趣，希望更快修完学分抽出更多的时间按照自己的规划来学习，因此希望上一些不用经常出勤、要求比较宽松的“水课”，希望我们针对这些同学进行一个“水课”推荐。
- 经过询问，大家希望这个模块的界面里，同学们输入自己的方向及学习程度（选填），填写自己选课的目的，系统能够给出相应的推荐。
- 同时，大家还希望了解一些只有“学长学姐才会告诉”选修课的详情，来作为我们的付费项目。通过一键查询，了解到有无点名、有无结课论文、有无演讲或答辩以及有无考试等等。同时希望我们总结出一些“雷区”。（如：手写论文、答辩结课，跑800m和1500m、体测结课等）
- 自习场地选择助手
即便是内卷，也需要找一个合适的场地“偷偷进行”，休闲热闹的宿舍显然不是一个好的选择，我们往往需要去教室、活动室、图书馆等安静的场所进行自习。然而，可供自习的场地是有限的，环境也各不相同，自习场地选择助手这个模块旨在为同学们们推荐更合适的场所进行自习。
经过调查，有以下需求：
- 希望通过这个模块一键找到最适合自己的自习场地；
- 希望通过这个模块实现对各自习场地的详细情况。如是否常常人满为患、是否有空调、离寝室的距离、是否已被占用等。
- 界面设计上，希望学生仅仅输入自己现在处于的位置。经过简单的一键匹配，找到一些合适的自习场地。
还希望通过输入场地编号，经过简单的一键查询，看到自习场地的详情。
- 比赛组队
近年来公安院校掀起了一阵“比赛热”，为了迅速获得某一方向的成就感，抑或是混些综测分来更好地内卷，我们加入了一个能帮助同学们更好地组建比赛队伍的小模块。
经过分析：我们需要：
- 查询正在组队的比赛队伍，增加新的比赛待组队比赛队伍。（同时显示该队伍所要参与比赛的简短信息）
- 查询某一队伍各成员基本信息及简介（可包括比赛中的主要分工，之前获得过的奖项等）。
- 修改数据库内已经存储的自己的记录。
- 通过登录账号的方式，避免有人对数据库中的数据做胡乱的修改。
可行性分析
主要针对对数据进行查询一般不能完成的情况进行分析。
-
选课助手部分：在水课推荐方面，我们可以给每种课堂活动赋一个权值（作为痛苦程度），在将课程和课堂活动多对多地联系起来，最后使用group by子句分组求每一课程的总痛苦值，位同学推荐较不痛苦的课程。
-
自习场地助手： 可用精度不是很高的坐标来唯一标识一个地点，因为学校很少或不存在死路的情况，可直接通过两点坐标算出距离。其次再根据占用情况进行排除，再通过心愿对优秀程度进行加权，最终实现推荐。
-
比赛组队：目前准备采用“内测、邀请制”，由我们的DBA手动录入账号密码进行注册。通过登录记录下用户名，在创建队伍时直接作为队伍创建人的字段值进行插入。此外不用录入其他数据。只需要DBA偶尔对数据进行检查、清理，将胡乱创建或加入队伍的账号从数据库中删除即可。
系统设计
（一）总体设计
（二）详细设计
第一部分：选课助手模块
首先根据需求设计出如下er图：
- 直接转换为关系模式：
专业方向(方向名)
课程方向(课程号，方向名)
课程(课程号,课程名)
课程事项(课程号，事项名)
事项(事项名，痛苦程度)
先修(课程号，先修课程号)
先修课(先修课程号,先修课程名)
发现专业方向关系中只有一个属性列，且与需求无关，因此将其直接删除；其余关系每一属性不可再分，没有非主属性部分依赖于码，没有非主属性对码的传递依赖且每一决定因素都包含码，因此已经属于BCNF。
最后的关系模式为：
课程方向(课程号，方向名)
课程(课程号,课程名)
课程事项(课程号，事项名)
事项(事项名，痛苦程度)
先修(课程号，先修课程号)
先修课(先修课程号,先修课程名)
第二部分：自习场地选择模块
- 转化为关系模型为：
自习场地(自习场地坐标,热门度,占用状态,有无空调,场地名称)
前往(自习场地坐标，所在位置坐标,距离)
所在位置(所在位置坐标,所在位置名称)
依赖关系简单，已经比较优秀。
第三部分：比赛组队模块
根据需求可设计出如下er图：
- 从而直接转化为关系模式：
用户(用户名,密码)
比赛队伍(队伍编号,队伍名,创建人,队伍简介,比赛名,比赛简要情况)
操作(用户名，队伍编号,操作时间,操作内容)
队员(队员姓名,队员简介)
队员队伍(队伍编号，队员姓名)
显然在上面的关系模式中每一属性不可再分，符合1NF；
又显然用户、队员、队员队伍关系十分简单，已经符合BCNF，因而只对比赛队伍、操作两个关系进行分析：
发现其中只存在如下依赖关系：
队伍编号——>队伍名，创建人，队伍简介，比赛名，比赛简要情况；
比赛名——>比赛简要情况；
（用户名，队伍编号）——>操作时间，操作内容；
经过对属性组作为码的关系进行分析，发现不存在非主属性部分依赖于码，因而符合2NF；
又发现存在比赛队伍关系中存在：队伍编号——>比赛名——>比赛简要情况这一非主属性对主码的传递依赖关系，因而将其分解，得到如下的关系模式：
用户(用户名,密码)
比赛队伍(队伍编号,队伍名,创建人,队伍简介,比赛名)
比赛(比赛名,比赛简要情况)
操作(用户名，队伍编号,操作时间,操作内容)
队员(队员姓名,队员简介)
队员队伍(队伍编号，队员姓名)
此时，已经不存在非主属性对主码的传递依赖关系，关系模式符合3NF，又容易看出每一决定因素确实都包含码，可知关系模式已经符合BCNF。
系统实现
数据库的建立
选课助手模块
create database 选课推荐数据库
on primary
(name=选课推荐,
filename='D:\数据库大作业\选课推荐_Data.mdf',
size=4MB,
maxsize=50MB,
filegrowth=1MB)
log on
(name=选课推荐_log,
filename='D:\数据库大作业\选课推荐_log.ldf',
size=1MB,
maxsize=50MB,
filegrowth=1%)
create table 课程
(
课程号 varchar(20) primary key,
课程名 nvarchar(20)
)
create table 事项
(
事项名 nvarchar(10) primary key,
痛苦程度 smallint
)
create table 先修课
(
先修课程号 varchar(20) primary key,
先修课程名 nvarchar(20)
)
create table 先修表
(
课程号 varchar(20),
先修课程号 varchar(20),
primary key(课程号,先修课程号),
foreign key (课程号) references 课程(课程号),
foreign key(先修课程号) references 先修课(先修课程号)
)
create table 课程事项
(
课程号 varchar(20),
事项名 nvarchar(10),
primary key(课程号,事项名),
foreign key(课程号) references 课程(课程号),
foreign key(事项名) references 事项(事项名)
)
create table 课程方向
(
课程号 varchar(20),
方向名 nvarchar(20),
primary key(课程号,方向名),
foreign key(课程号) references 课程(课程号)
)
自习场地选择模块
create database 自习场地数据库
on primary
(name=自习场地数据库,
filename='D:\数据库大作业\自习场地_Data.mdf',
size=3MB,
maxsize=50MB,
filegrowth=10%)
log on
(name=自习场地数据库_log,
filename='D:\数据库大作业\自习场地_log.ldf',
size=3MB,
filegrowth=1MB)
create table 自习场地
(Lxcoord float,
Lycoord float,
Lname nvarchar(20),
有无空调 nVarChar(5),
占用状态 nVarChar(5),
热门度 smallint,
primary key(Lxcoord,Lycoord)
);
create table 当前位置
(Pxcoord float,
Pycoord float,
Pname nVarChar(20),
primary key(Pxcoord,Pycoord)
);
/*create table LProute
(Lxcoord smallint ,
Lycoord smallint,
Pxcoord smallint,
Pycoord smallint,
distance nVarChar(10),
PRIMARY KEY (Lxcoord,Lycoord,Pxcoord,Pycoord),
foreign key(Lxcoord,Lycoord) references 自习场地(Lxcoord,Lycoord),
foreign key(Pxcoord,Pycoord) references 当前位置(Pxcoord,Pycoord)
);*/ --经过实践，将其淘汰
create view LProute
as
select Lxcoord,Lycoord,Pxcoord,Pycoord,sqrt(power(Lxcoord-Pxcoord,2)+power(Lycoord-Pycoord,2)) distance
from local,ppp
比赛组队模块
create database 比赛组队数据库
on primary
(name=比赛组队_Data,
filename='D:\数据库大作业\比赛组队_Data.mdf'
)
log on
(name=比赛组队_log,
filename='D:\数据库大作业\比赛组队_log.ldf'
)
create table users
(uname nvarchar(20) primary key,
password VarChar(20),
);
create table games
(Gname nvarchar(20) primary key,
Gbrief nvarChar(50),
);
create table teams
(Tno VarChar(20) primary key,
Tname nvarchar(20),
Founder nVarChar(10),
Tbrief nVarChar(50),
Gname nvarchar(20),
foreign key(Gname) references games(Gname)
);
create table players
(Pname nvarchar(10) primary key,
Pprofile nVarChar(50),
);
create table operate
(Username nvarchar(10),
Tno VarChar(20),
Otime Datetime,
Obrief nVarChar(20)
PRIMARY KEY (Username,Tno)
);
create table groups
(
Tno VarChar(20),
Pname nvarchar(10),
PRIMARY KEY (Pname,Tno),
foreign key(Tno) references teams(Tno),
foreign key(Pname) references players(Pname)
);
--用户注册（不开放接口，需联系管理员）
insert into users(uname,password)
values
('施南汐','666666'),
('邱杨安','666666'),
('穆昭宇','666666'),
('张万乘','666666'),
('陈俊儒','666666'),
('袁远','666666')
常用操作的sql实现（嵌入python以后）
比赛组队系统
- 登录按钮所触发的函数：
由于还在测试阶段，未对sql注入加以防范，直接使用select * from users where uname = '' and password = '' 的方式进行账号密码验证。
def tt2tt2(self):
# print('ing')
global_value.un = self.lineEdit.text()
print(global_value.un)
pw = self.lineEdit_2.text()
# 获取cursor
cursor = connect.cursor()
cursor.execute("select * from users where uname = '%s' and password = '%s'" % (global_value.un,pw))
row = cursor.fetchone()
if row:
while row:
print("pname=", row[0])
row = cursor.fetchone()
tt.close()
tt2.show()
cursor.close() # 关闭游标
else:
self.lineEdit.setText("坏蛋！账号密码错误！！")
- 创建战队按钮触发的函数
def setateam(self):
print(1)
texts = self.textEdit.toPlainText()
print(global_value.un)
print(texts.split()[0], texts.split()[1], global_value.un, texts.split()[2], texts.split()[3])
cursor.execute("if '%s' not in(select gname from games) insert into games values('%s','%s')" % (
texts.split()[3], texts.split()[3], texts.split()[4]))
connect.commit() # 提交，修改数据库需要用到
cursor.execute(
"if '%s' not in(select Tno from teams) insert into teams(Tno,Tname,Founder,Tbrief,Gname) values('%s','%s','%s','%s','%s')" % (
texts.split()[0], texts.split()[0], texts.split()[1], global_value.un, texts.split()[2],
texts.split()[3]))
# row = cursor.fetchone()
connect.commit() # 提交，修改数据库需要用到
self.textEdit.setText("创建成功！")
- 查询现有战队函数
def findteam(self):
self.textEdit.append("现有队伍：\n\n")
cursor.execute("select Tno,Tname,Founder,Tbrief,games.Gname,Gbrief from games,teams where games.Gname=teams.Gname")
row = cursor.fetchone()
while row:
self.textEdit.append(row[0]+' '+row[1]+' '+row[2]+' '+row[3]+' '+row[4]+' '+row[5]+'\n')
row = cursor.fetchone()
- 查询战队成员及简介
select groups.Pname,Pprofile from groups,players where groups.Pname=players.Pname and Tno=''
- 结果样例
选课推荐系统
- 一键查课
我们的选课推荐数据库中有六个数据表，若要按要求对这六个数据表进行连接查询需要五个连接条件，同时需要进行外连接，过于复杂，因此建立视图：
create view pain_view
as
select course.cno,cname,sum(pain) as pain
from course,courdow,things
where course.cno=courdow.cno and things.tname=courdow.tname
group by course.cno,cname
此时只要如此查询即可：
select pain_view.cno,cname,pain,rname,preno
from cour2w,pain_view left outer join pretable on(pain_view.cno=pretable.cno)
where pain_view.cno=cour2w.cno and pain_view.cname=''
嵌入槽函数：
def findinf(self):
self.textBrowser.clear()
connect = pymssql.connect(serverName, userName, passWord, 'ccbase')
if connect:
print("数据库已经连接成功！")
cursor = connect.cursor()
line=self.lineEdit.text()
print(line)
cursor.execute("select pain_view.cno,cname,pain,rname,preno from cour2w,pain_view left outer join pretable on(pain_view.cno=pretable.cno) where pain_view.cno=cour2w.cno and pain_view.cname='%s'" % line)
print("有")
row = cursor.fetchone()
if row:
while row:
self.textBrowser.append(row[0] + ' ' + row[1]+' 痛苦程度：'+str(row[2])+ '\n有关方向：' + row[3]+' 先修课：'+str(row[4])+'\n')
row = cursor.fetchone()
else:
self.textBrowser.append('没有这门课哦！')
- 水课推荐部分，在课程目的中选择“玩耍”，匹配时会使用如下语句查询总痛苦值不大于5的课程并按总痛苦值升序排列。
select 课程.课程号,课程名,sum(痛苦程度) 总痛苦值
from 课程,课程事项,事项
where 课程.课程号=课程事项.课程号 and 事项.事项名=课程事项.事项名
group by 课程.课程号,课程名
having sum(痛苦程度)<=5
order by sum(痛苦程度)
利用已经建立的视图可以优化为：
select cno,cname,pain from pain_view where pain<=5 order by pain
- 在课程目的中选择“学习”，并选择想要查询的专业方向，点击“一键推荐”按钮，进行连接查询：
select 课程.课程号,课程名,方向名 from 课程,课程方向 where 课程.课程号=课程方向.课程号 and 方向名 like ''
若没有方向，那么自动推荐属于所有方向的课程：
select cname 课程名,count(*) 所属方向个数
from course,cour2w
where course.cno=cour2w.cno
group by cname
having count(*)=(select count(distinct rname)
from cour2w where rname<>'无')
结果如下：
- 嵌入python中的槽函数：
def findcourse(self):
self.textBrowser.clear()
connect = pymssql.connect(serverName, userName, passWord, 'ccbase')
if connect:
print("数据库已经连接成功！")
cursor = connect.cursor()
box1=self.comboBox.currentText()
box2=self.comboBox_2.currentText()
if box1=="玩耍":
cursor.execute("select cno,cname,pain from pain_view where pain<=5 order by pain")
print(11)
row = cursor.fetchone()
while row:
self.textBrowser.append(row[0] + ' ' + row[1] + ' ' + str(row[2]))
row = cursor.fetchone()
else:
cursor = connect.cursor()
if box2=="<请选择的你的方向>":
print(1)
cursor.execute("select cname,count(*) from course,cour2w where course.cno=cour2w.cno group by cname having count(*)=(select count(distinct rname) from cour2w where rname<>'无')")
row = cursor.fetchone()
while row:
self.textBrowser.append(row[0] + ' ' + str(row[1]))
row = cursor.fetchone()
else:
cursor.execute("select course.cno,cname from cour2w,course where cour2w.cno=course.cno and rname='%s'" % box2)
row = cursor.fetchone()
while row:
self.textBrowser.append(row[0] + ' ' + row[1])
row = cursor.fetchone()
自习场地推荐助手
一键查询槽函数
def selectual(self):
text=self.lineEdit.text()
connect = pymssql.connect(serverName, userName, passWord, "LPbase")
if connect:
print("数据库已经连接成功！")
# print(texts.split()[0],texts.split()[1],texts.split()[2],texts.split()[3],texts.split()[4])
# 获取cursor
cursor = connect.cursor()
print(222)
cursor.execute("select ppp.Lxcoord,ppp.Lycoord,kongtiao,zhanyong,hlevel from ppp,LProute where ppp.Lxcoord=LProute.Pxcoord and ppp.Lycoord=LProute.Pycoord and Lname='%s'" %text)
print(111)
row = cursor.fetchone()
while row:
self.textBrowser.append('坐标:('+str(row[0])+','+str(row[1]) + ');空调:' + str(row[2]) + ';占用:'+str(row[3])+ ';热度:'+str(row[4]))
row = cursor.fetchone()
自动匹配：以9号楼有空调为例
select temp.Lxcoord,temp.Lycoord,temp.Lname,distance
from LProute,(select ppp.Lxcoord,ppp.Lycoord,ppp.Lname from ppp where kongtiao='1' and zhanyong='0') temp
where temp.Lxcoord=LProute.Lxcoord and temp.Lycoord=LProute.Lycoord
and Pxcoord=1 and Pycoord=9
and distance<=all(select distance
from LProute,(select ppp.Lxcoord,ppp.Lycoord,ppp.Lname from ppp where kongtiao='1' and zhanyong='0') temp1
where temp1.Lxcoord=LProute.Lxcoord and temp1.Lycoord=LProute.Lycoord
and Pxcoord=1 and Pycoord=9)
不考虑心愿时的实现函数
def pipei(self):
box1 = self.comboBox.currentText()
connect = pymssql.connect(serverName, userName, passWord, "LPbase")
if connect:
print("数据库已经连接成功！")
cursor = connect.cursor()
# 根据位置名称获取坐标
cursor.execute("select Pxcoord from local where pname like '%s'" % box1)
cur=cursor.fetchone()
xx=cur[0]
cursor.execute("select Pycoord from local where pname like '%s'" % box1)
cur = cursor.fetchone()
yy = cur[0]
print(222)
cursor.execute("select temp.Lxcoord,temp.Lycoord,temp.Lname,distance from LProute,(select ppp.Lxcoord,ppp.Lycoord,ppp.Lname from ppp where zhanyong='0') temp where temp.Lxcoord=LProute.Lxcoord and temp.Lycoord=LProute.Lycoord and Pxcoord=%f and Pycoord=%f and distance<=all(select distance from LProute,(select ppp.Lxcoord,ppp.Lycoord,ppp.Lname from ppp where zhanyong='0') temp1 where temp1.Lxcoord=LProute.Lxcoord and temp1.Lycoord=LProute.Lycoord and Pxcoord=%f and Pycoord=%f)" %(xx,yy,xx,yy))
row = cursor.fetchone()
while row:
self.textBrowser.setText("("+str(row[0])+","+str(row[1])+") "+row[2]+" 距离："+str(row[3]))
row = cursor.fetchone()
接口及GUI设计
为了方便，gui窗口使用python的pyqt5函数库编写。
-
主菜单：
-
自习场地选择：
-
选课助手界面
-
登陆界面设计：
-
比赛组队系统界面
值得改进的地方
- 数据： 目前搜集的真实数据不足，应当搜集更多的数据才能保证数据库的实用性。
- 鲁棒性： 目前设计与数据库连接的python程序鲁棒性较差，用户输入必须严格按照说明的格式，否则将面临程序崩溃与闪退。
- 复杂业务逻辑问题 目前程序不能适应更复杂的业务逻辑，或者说要实现更复杂的业务需要掉更多的头发，而且非常的的难以维护。同时需要通过网络传输更多的数据到用户的计算机中。为此可以定义一些可复用的存储过程来解决这些问题。
4.安全性 目前虽然有通过用户认证、管理员手动管理用户的简单方式来防止数据库中的数据被任意修改，但尚未对sql注入加以防范，可以通过前端屏蔽一些敏感符号，或者将登录认证语句封装进存储过程，将用户名密码参数化来减少sql注入。