数据库设计：百年党史知识问答竞赛
一、系统开发
1.1背景
2021年是党成立100周年，100年披荆斩棘，100年风雨兼程。这一百载是我们党团结带领全国各族人民，解放思想、实事求是，同心同德、艰苦奋斗，开拓进取的一百年，是各项事业蓬勃发展的一百年，也是党史研究长足进步的一百年。百年间，无数革命先辈和爱国志士，为中国人民的革命和建设事业，前仆后继，英勇献身，形成了伟大建党精神。百年后的今天，党开辟了中国特色社会主义道路，领导国家和人民走上了新的征程。作为新时代的青年，我们终将扛起谱写复兴路的大旗，缅怀先烈，学习革命英雄身上的精神品质，理想之光不灭，信念之光不灭。因此，学校特地组织了“百年大党知识问答活动”，以此来庆祝伟大的党成立一百周年。
1.2功能
本次比赛分为个人赛和以学院为单位的团体赛，个人赛与团体赛又分为两个步骤：预赛及决赛。个人专项竞赛和团队竞赛均采用所有参与者面对同一套题背对背答题。
题目设置：预赛题库和决赛题库两个题库，预赛题库和决赛题库各一套题，个人赛道和团队赛道同用一个题库，题型均为客观题。
预赛题库共50题，30道单项选择题，10道判断对错题，10道填空题，每题分值2分。
决赛题库共35题，15道单项选择题，10道判断对错题，10道填空题，单项选择题分值为4，其余题型分值为2。
对于个人赛道，如果答题分数大于60，则可进入决赛，决赛阶段分数大于90分的获一等奖，80-90分的获二等奖，其余获三等奖。
对于团队赛道，团队三个成员分别答题，取每支队伍的成绩最高的成员进入决赛答题，决赛阶段，该成员代表其团队，其成绩就是团队在决赛阶段的成绩 ，决赛阶段的评奖标准与个人赛道的决赛评奖标准相同。
注册功能：
游客可选择注册类型（个人、团队），填入相关信息进行注册。
答题功能：
参赛者经过身份验证后，可以开始预赛答题。预赛答完题后当场评出分数，并根据预赛分数判断是否进入决赛，若进入决赛，则将其添入个人赛道的决赛参赛表中；
决赛答题开始前，验证参赛者是否具有决赛资格，对于有决赛资格的参赛者开放决赛答题。
信息查询功能：
参赛者通过身份验证后，可以查询自己的参赛信息，如ID、姓名、预赛成绩、决赛成绩等。
二、项目设计阶段
2.1 E/R模型
个人赛
团体赛
2.2关系模型
2.2.1模式
题库信息
题型表（题型号，题型）
db_question_type(typenum,type)
预赛题目表（题号，题目，答案，分值，题型号）
db_yu_question(qnum,question,answer,score,typenum)
决赛题目表（题号，题目，答案，分值，题型号）
db_jue_question(qnum,question,answer,score,typenum)
个人赛道信息
个人信息表（ID，姓名，专业，学历，密码）
db_individual_message(id,name,major,education,pwd)
预赛成绩表（ID，分数，是否进入决赛，学历）
db_in_yu_grade(id,grade,enter,education)
决赛成员表（ID，姓名）
db_in_jue_member(id,name)
决赛成绩表（ID，分数，奖项，学历）
db_in_jue_grade(id,grade,prize,education)
团队赛道信息
团队信息表（团队编号，团支部名，成员ID，学历，密码）
db_team_message(teamnum,teamname,member,education,pwd)
团队成员表（ID，成员名，团队编号）
db_team_member(id,membername,teamnum)
预赛成绩表（ID，团队编号，分数，是否进入决赛，学历）
db_team_yu_grade(id,teamnum,grade,enter,education)
决赛成员表（团队编号，ID，分数）
Db_team_jue_member(teamnum,id,grade)
决赛成绩表（团队编号，分数，奖项）
db_team_jue_grade(teamnum,grade,prize)
2.2.2模型优化
数据库逻辑设计的结果不是唯一的。得到初步数据模型后，还应该适当地修改、调整数据模型的结构，以进一步提高数据库应用系统的性能，这就是数据模型的优化。
关系数据模型的优化通常以规范化理论为指导。
确定数据依赖
对于各个关系模式之间的数据依赖进行极小化处理，消除冗余的联系。
如个人的预赛成绩关系模式（ID，分数，是否进入决赛，学历）存在下列数据依赖：
ID->分数
ID->是否进入决赛
ID->学历
个人信息关系模式（ID，姓名，专业，学历，密码）存在下列数据依赖：
ID->姓名
ID->专业
ID->学历
ID->密码
从上面两个关系模式的数据依赖分析，根据ID可以在个人信息关系模式联系地查询到学历的信息，故个人的预赛成绩关系模式中的“学历”属性是冗余的，可以删去。
同理，对个人决赛成员表（ID，姓名）的“姓名”属性、个人决赛成绩表决赛成绩表（ID，分数，奖项，学历）的“学历”属性、团队预赛成绩表（ID，团队编号，分数，是否进入决赛，学历）的“学历”属性都可删去。
对于个人的预赛成绩关系模式（ID，分数，是否进入决赛，学历），根据“分数”可以推理“是否进入决赛”，此关系模型似乎还可以进行优化（对于决赛成绩关系模式中的“分数”和“奖项”也类似），但小组对“是否进入决赛”这一属性进行了保留，因为该属性直观地提供了相关的信息，为提高效率，我们仍然可保留该冗余数据，对关系模式不再做进一步分解。这样使得数据库的信息存储和结构更加清晰，便于管理。
按照数据依赖的理论对关系模式逐一进行分析，考查是否存在部分函数依赖、传递函数依赖、多值依赖等，确定各关系模式分别属于第几范式。
对以上各关系模式分析，均消除了部分函数依赖、传递函数依赖、多值依赖的问题，最低达到了3NF范式。
按照需求分析阶段得到的各种应用对数 据处理的要求，分析对于这样的应用环境 这些模式是否合适，确定是否要对它们进行合并或分解。常用的分解方法有水平分解和垂直分解。
经过以上步骤的优化，关系模式已经可以基本满足需求分析阶段得到的各种应用对数据处理的要求
最终确定的关系模式如下：
题库信息
题型表（题型号，题型）
db_question_type(typenum,type)
预赛题目表（题号，题目，答案，分值，题型号）
db_yu_question(qnum,question,answer,score,typenum)
决赛题目表（题号，题目，答案，分值，题型号）
db_jue_question(qnum,question,answer,score,typenum)
个人赛道信息
个人信息表（ID，姓名，专业，学历，密码）
db_individual_message(id,name,major,education,pwd)
预赛成绩表（ID，分数，是否进入决赛）
db_in_yu_grade(id,grade,enter)
决赛成员表（ID）
db_in_jue_member(id)
决赛成绩表（ID，分数，奖项）
db_in_jue_grade(id,grade,prize)
团队赛道信息
团队信息表（团队编号，团支部名，成员ID，学历，密码）
db_team_message(teamnum,teamname,member,education,pwd)
团队成员表（ID，成员名，团队编号）
db_team_member(id,membername,teamnum)
预赛成绩表（ID，团队编号，分数，是否进入决赛）
db_team_yu_grade(id,teamnum,grade,enter)
决赛成员表（团队编号，ID，分数）
Db_team_jue_member(teamnum,id,grade)
决赛成绩表（团队编号，分数，奖项）
db_team_jue_grade(teamnum,grade,prize)
2.2.3数据库完整性约束
数据完整性：存储在数据库中的所有数据值均正确的状态。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。
数据完整性分为四类：
实体完整性
实体完整性是对关系中的记录唯一性，也就是主键的约束。准确地说，实体完整性是指关系中的主属性值不能为Null且不能有相同值。定义表中的所有行能唯一的标识,一般用主键,唯一索引 unique关键字,及identity属性比如说我们的身份证号码,可以唯一标识一个人.。
以个人信息表db_individual_message(id,name,major,education,pwd)为例，其余表操作类似。
在图形化工具Oracle SQL Developer可以便捷地定义：
或者使用SQL命令在创建表时实现：
Create table db_individual_messsage（
……
Constraint INDIVIDUAL_MESSAGE_PK Primary key(id)
）；
1.域完整性
域完整性是对数据表中字段属性的约束，通常指数据的有效性,它包括字段的值域、字段的类型及字段的有效规则等约束，它是由确定关系结构时所定义的字段的属性决定的。限制数据类型,缺省值,规则,约束,是否可以为空,域完整性可以确保不会输入无效的值.。
下图是个人信息表db_individual_message(id,name,major,education,pwd)的域完整性定义：
2.参照完整性
参照完整性是对关系数据库中建立关联关系的数据表间数据参照引用的约束，也就是对外键的约束。准确地说，参照完整性是指关系中的外键必须是另一个关系的主键有效值，或者是NULL。参考完整性维护表间数据的有效性,完整性,通常通过建立外部键联系另一表的主键实现,还可以用触发器来维护参考完整性。
以个人预赛成绩表db_in_yu_grade(id,grade,enter)为例，其id参照个人信息表db_individual_message(id,name,major,education,pwd)的id，当被参照表删除元组可能破坏参照完整性时，执行级联删除的处理：
或者使用SQL命令在创建表时实现：
Create table db_in_yu_grade（
……
Constraint FK_ID foreign key(id) references db_individual_message(id) on delete cascade
）；
用户定义的完整性。
用户定义的完整性就是针对某一具体应用的数据必须满足的语义要求。
以个人信息表db_individual_message(id,name,major,education,pwd)为例，要求education只能为“本科”或“研究生”：
或者使用SQL命令在创建表时实现：
Create table db_in_yu_grade（
……
Constraint c1 check ( education in ('本科','研究生'))
）；
本项目所有完整性约束定义如下：
db_question_type(typenum,type)
db_yu_question(qnum,question,answer,score,typenum)
db_jue_question(qnum,question,answer,score,typenum)
db_individual_message(id,name,major,education,pwd)
db_in_yu_grade(id,grade,enter)
db_in_jue_member(id)
db_in_jue_grade(id,grade,prize)
db_team_message(teamnum,teamname,member,education,pwd)
db_team_member(id,membername,teamnum)
db_team_yu_grade(id,teamnum,grade,enter)
db_team_jue_member(teamnum,id,grade)
db_team_jue_grade(teamnum,grade,prize)
三、数据库实施
3.1项目实现的功能
1.导入数据
2.代码结构
In_grade.java:负责验证身份登录并显示个人的参赛信息（姓名、初赛成绩等）
In_jue_answer:负责验证身份登录并个人赛决赛的答题实现
In_yu_answer:负责验证身份登录并个人赛预赛的答题实现
Login.java:实现了用户登录的功能
Register.java:实现了个人与团体的报名注册
Team_grade.java:负责验证身份登录并显示团队的参赛信息（姓名、初赛成绩等）Team_yu_answer.java:负责验证身份登录并团体赛预赛的答题实现
Team_jue_answer.java:负责验证身份登录并团体赛决赛的答题实现
Team_jue_member.java:负责记录代表团队进入团体决赛答题的成员
Competiton.java:答题系统的实现
3.2 SQL代码
In_grade.java:
（1）验证身份登录：
select* from db_individual_message where id='id'and pwd='pwd'；
（2）提取该参赛者的预赛成绩信息
select * from db_in_yu_grade where id='"+id+"'
（3）提取该参赛者的决赛成绩信息
select * from db_in_jue_grade where id='id'
In_jue_answer:
（1）验证身份登录：
"select* from db_individual_message where id='"+id+"'and pwd='"+pwd+"'"
（2）查询参赛者是否在决赛名单中，只有名单中的参赛者可进行决赛答题
select * from db_in_jue_member where id='"+id+"'"
（3）提取初赛题库
"select qnum,question,answer from db_jue_question where qnum='"+m+"'";
（4）更新决赛分数
"update db_in_jue_grade set grade ='"+score+"' where id='"+id+"'"
（5）若参赛者分数大于90，更新决赛成绩表中的奖项为“1”，即一等奖
"update db_in_jue_grade set prize ='1' where id='"+id+"'"
（6）若参赛者分数80-90分，更新决赛成绩表中的奖项为“2”，即二等奖
"update db_in_jue_grade set prize ='2' where id='"+id+"'"
（7）其余决赛参赛者获三等奖，更新决赛成绩表中的奖项为“3”
"update db_in_jue_grade set prize ='3' where id='"+id+"'"
In_yu_answer:
（1）验证身份登录：
"select * from db_individual_message where id='"+id+"'and pwd='"+pwd+"'"
（2）提取初赛题库
"select qnum,question,answer from db_yu_question where qnum='"+m+"'"
（3）若参赛者进入决赛，更新其初赛成绩表的“enter”属性为“yes”，表示进入决赛
"update db_in_yu_grade set enter='yes' where id='"+id+"'"
（4）若参赛者进入决赛，将其加入决赛参赛成员表中
"insert into db_in_jue_member(id,name) values('"+id+"','"+name+"')";
"insert into db_in_jue_grade(id,grade,prize) values('"+id+"',null,null)"
（5）更新预赛分数
"update db_in_yu_grade set grade ='"+score+"' where id='"+id+"'"
Register.java:
（1）将注册的团队信息录入db_team_message表
"insert into db_team_message(teamnum,teamname,member,education,pwd) VALUES('"+teamnum+"','"+teamname+"','"+member+"','"+education+"','"+pwd+"')"
（2）将团队成员1的信息录入db_team_member表
"insert into db_team_member(id,membername,teamnum) VALUES('"+id1+"','"+name1+"','"+teamnum+"')"
（3）初始化团队成员1预赛成绩表
"insert into db_team_yu_grade(id,teamnum,grade,enter,education) VALUES('"+id1+"','"+teamnum+"',NULL,NULL,'"+education+"')"
（4）将团队成员2的信息录入db_team_member表
"insert into db_team_member(id,membername,teamnum) VALUES('"+id2+"','"+name2+"','"+teamnum+"')"
（5）初始化团队成员2预赛成绩表
"insert into db_team_yu_grade(id,teamnum,grade,enter,education) VALUES('"+id2+"','"+teamnum+"',NULL,NULL,'"+education+"')"
（6）将团队成员3的信息录入db_team_member表
"insert into db_team_member(id,membername,teamnum) VALUES('"+id3+"','"+name3+"','"+teamnum+"')"
（7）初始化团队成员3预赛成绩表
"insert into db_team_yu_grade(id,teamnum,grade,enter,education) VALUES('"+id3+"','"+teamnum+"',NULL,NULL,'"+education+"')"
（8）将注册的个人信息录入db_individual_message表
"insert into db_individual_message(id,name,major,education,pwd) VALUES('"+id+"','"+name+"','"+major+"','"+education+"','"+pwd+"')"
（9）初始化预赛成绩表
"insert into db_in_yu_grade(id,grade,enter,education) VALUES('"+id+"',NULL,NULL,'"+education+"')";
Team_grade.java:
（1）验证身份登录：
"select* from db_team_message where teamnum='"+teamnum+"'and pwd='"+pwd+"'"
（2）提取团队的预赛成绩信息
"select * from db_team_yu_grade where teamnum"+"='"+teamnum+"'"
（3）提取团队的决赛成绩信息
"select * from db_team_jue_grade where teamnum='"+teamnum+"'"
Team_jue_answer.java:
（1）团队决赛参赛队员验证
"select * from db_team_jue_member where teamnum='"+mteamnum+"' and id='"+mid+"' "
（2）提取决赛题库
"select qnum,question,answer from db_jue_question where qnum='"+m+"'"
（3）更新该参赛团队的决赛分数和奖项
"update db_team_jue_grade set grade ='"+score+"' where teamnum='"+mteamnum+"'"
"update db_team_jue_grade set prize ='1' where teamnum='"+mteamnum+"'"
"update db_in_jue_grade set prize ='2' where teamnum='"+mteamnum+"'"
"update db_in_jue_grade set prize ='3' where teamnum='"+mteamnum+"'";
Team_jue_member.java:
（1）查询团队三个成员中分数最高者
"select distinct a.teamnum,a.id,a.grade from db_team_yu_grade a,db_team_yu_grade b where a.grade=( select max(grade) from db_team_yu_grade b where b.teamnum=a.teamnum )"
（2）将团队三个成员中分数最高者作为代表团队参加决赛的参赛者
"insert into db_team_jue_member(teamnum,id,grade) values('"+teamnum+"','"+id+"','"+grade+"')"
"insert into db_team_jue_grade(teamnum,grade,prize) values('"+teamnum+"',null,null)"
update db_team_yu_grade set enter='yes' where teamnum='"+teamnum+"'and id='"+id+"'"
Team_yu_answer.java:
（1）团队预赛参赛身份验证
"select * from db_team_message where teamnum='"+teamnum+"'and pwd='"+pwd+"'";
"select * from db_team_member where teamnum='"+teamnum+"'and id='"+id+"'";
（2）提取预赛题库
"select qnum,question,answer from db_yu_question where qnum='"+m+"'";
（4）更新团队预赛成绩
"update db_team_yu_grade set grade ='"+score+"' where teamnum='"+teamnum+"'and id='"+id+"'";
四、维护
数据库维护指的是在数据库创建完成之后，对数据库进行的后续的工作，也就是监控和维护数据库的正常运行。
定期备份。大家在做数据库维护的时候，需要定期对数据库进行备份操作，不论是进行人工的备份还是自动的备份，在备份的时候，不能影响数据库的正常工作，同时还需要妥善的保管好备份的文件。
备份数据：以db_individual_message表为例
expdp system/123456@localhost/db dumpfile=db_individual_message_cp.dmp tables=system.db_individual_message
2、对错误进行修复。如果发现数据库出现了错误之后，需要对数据库进行修复。
3、对数据库进行优化。这个主要是按照日常监控的结果，对数据库的运行情况进行优化，以提高数据库的工作效率，比如索引和存储部分。
4、保证数据库的安全性。这个是做数据库维护的一个重要的工作内容，为了保证数据库的安全，防止非法访问或者是病毒侵袭，就需要定期的修改数据库的密码。
本项目在安全性方面定义了存取控制，规定合法用户以制定方法访问数据库的指定部分：
创建u1账户为系统普通管理员，他对所有表仅有查询权限，如果要对数据进行修改更新，必须要向系统高级管理员system报告，由system在合法的情景下操作数据。
创建账户：
create user u1 identified by u1 default tablespace users quota 10M on users;
授予会话权限；
grant create session to u1;
授予查询表格权限：以db_individual_message表为例
grant select on system.db_individual_message to u1;
5、进行数据的重组和迁移。大家在做数据库维护的时候，还需要定期的把一些历史数据进行迁移，以降低数据库的运行压力，同时还需要根据监控的结果，对数据库的空间进行重组，以释放更多的数据库空间。
五、运行测试
以个人注册，参加个人赛道比赛为例：
…(中间预赛答题过程省略)
…(中间决赛答题过程省略)
六、总结
数据库的题目来源于青年大学习的题库，我们小组使用python对题库进行爬虫，非常方便地获取了100道党史知识的题目。后续实现数据库增删改查、进行知识问答竞赛的代码也完全是小组成员一个一个进行实现，不存在任何抄袭或者借鉴。但由于我们能力的不足，以及期末时间太过紧张，我们小组只在控制台界面实现了对数据库的基本操作以及知识竞赛相关工作的实现。如果时间充裕，我们小组也完全能够将本次设计制作成应用软件。
虽然我们对自己的设计还算比较满意，但是我们仍有一些不足的地方：比如题目全是以文字呈现，未有音频或者视频呈现；比如在竞赛过程中，参赛者只能按照1-50的顺序对题目进行作答，而无法随机作答。这是我们小组的不足之处。通过此次数据库的项目设计，我们小组学到了非常多知识，也对本学期数据库课程的知识进行了巩固与加强，小组配合也十分有默契，分工十分明确，这是非常值得纪念的一次课程设计！对于每一个项目，数据库的设计都是至关重要的，它关系到开发时实现的难度，数据库中数据的可维护性，一致性，以及是否会因为数据冗余太多导致系统性能较低等等。
在数据库的设计过程中，我们需要考虑各方面的因素。刚开始设计时，我们小组先把自己能想到的需要用到的数据表画出来，并根据需求暂时给出数据表的几个字段，并在之后的进一步分析中再将其一一分解。照这样的方法依次创建了项目中会用到的一些主要的数据表，包括题型表，预赛题目表，决赛题目表，个人信息表，预赛成绩表等，然后再给每个表添加一些属性，并设置其数据类型、长度，最重要的是选一个属性作为主键，最后再参照第一范式，第二范式和第三范式对已经创建的表进行拆分。现在可以通过控制台来实现参赛者注册，答题，查看是否进入决赛，查看成绩等功能，但没有做出用户应用程序，没有图形交互界面，还有一些如抢答题、必答题、主观题的设置没有实现。所以这个项目可以完善的功能还有很多，之后有时间我们会加以实现。
总的来说，通过这次的数据库设计，从开始设计，到完善设计，到绘制ER图，编写程序等，巩固了我们对数据库基本原理和基础理论的理解，掌握数据库应用系统设计开发的基本方法，进一步提高了我们综合运用所学知识的能力
