学生信息管理系统的数据库设计MySQL

学生信息管理系统的数据库设计

1.课程设计目的
学生信息管理系统是一个教育单位不可缺少的部分。一个功能齐全、简单易用的信息管理系统不但能有效地减轻学校相关工作人员的工作负担，它的内容对于学校的决策者和管理者来说都至关重要。所以学生信息管理系统应该能够为用户提供充足的信息和快捷的查询手段。但一直以来人们使用传统人工的方式管理文件档案、统计和查询数据，这种管理方式存在着许多缺点,如:效率低、保密性差,人工的大量浪费;另外时间一长，将产生大量的文件和数据,这对于查找、更新和维护都带来了不少困难。随着科学技术的不断提高,计算机科学日渐成熟,其强大的功能已为人们深刻认识,它已进入人类社会的各个领域并发挥着来越重要的作用。数据库课程设计是为了增强学生对所学课程的理解，学会综合地、灵活地运用所学课程知识的一个重要的实践环节。本课程设计是应用java程序设计语言进行数据库应用系统的开发，用MySQL进行后台数据库的管理，编写出某一个小型的管理信息系统。

2.开发环境
硬件环境：服务器、客户机连成局域网结构；
软件环境：
数据库管理系统：MySQL
辅助设计工具ProcessOn、word画图
集成开发环境IntelliJ IDEA或Eclipse
3.系统任务概述
随着学校的规模不断扩大,学生数量急剧增加，有关学生的各种信息量也成倍增长。面对庞大的信息量需要有学生管理系统来提高学生管理工作的效率。为了能够为高校学生信息管理提供一种更加高效实用的管理手段，为学生信息的存储、计算、统计、分析、交流提供一种更加安全快捷的信息平台，并且能够减少大量的人工操作，以及在人工操作中由于人为因素而引起的数据错误，保证学生信息数据的安全性和完整性，使学生管理人员能够轻松,正确无误地完成各项工作，为学生管理工作服务。通过这样的系统可以做到信息的规范管理、科学统计和快速查询、修改、增加、删除等，从而减少管理方面的工作量。目前社会上信息管理系统发展飞快,各个企业事业单位都引入了信息管理软件来管理自己日益增长的各种信息,学生管理系统也是有了很大的发展,商业化的学生信息管理软件也不少。但本系统完全独立开发,力求使系统功能简洁明了,但功能齐全且易于操作。
4.系统需求分析
目前随着学校的规模不断扩大，学生数量急剧增加，有关学生的各种信息量也成倍增长，需要学校统一管理的数据也越来越多，管理的难度大大增加。面对大量的数据，学校管理者要做的工作实在是太繁杂，因此设计一个学生信息管理系统的必要性显而易见。管理者需要对学生信息进行规范管理、科学统计和快速查询、修改、增加、删除等操作，从而减少管理方面的工作量。本系统可以满足学校管理者的需求，解决大部分学校在学生信息管理上所面临的难题。
4.1分析系统的参与者
学生：可通过该系统查询相关信息，提出相关相关事项的申请如修改密码等。
教师：包括辅导员、院系主任、科任老师等，科任老师可通过该系统录入、修改、核对学生成绩，院系主任主要负责学生学籍信息和课程信息的录入、添加、修改、删除等操作管理，辅导员主要负责学生毕业、奖惩、贫困生等相关信息的管理。
管理员：负责设置各参与用户使用系统的权限和学生学籍信息、学生成绩、辅导员相关信息等的维护，备份系统数据库，恢复系统数据库，系统安全维护。
4.2系统功能需求
学生信息管理系统中，根据管理员、学生和教师的活动步骤，创建活动图如下图4-1所示：

图4-1系统活动图
学生用例图：学生在本系统中可以进行登录、查询成绩、查询学籍、查询档案和查询缴费信息相关操作，通过这些活动创建学生用例图如图4-2所示：

图4-2学生用例图
教师用例图：教师在本系统中可以进行登录、输入学生学号之后可以查询学生成绩、查询学生学籍、查询学生档案和查询学生缴费相关操作，通过这些活动创建教师用例图如图4-3所示：

图4-3教师用例图
管理员用例图：管理员在本系统中可以进行登录、添加用户、修改用户（可以修改用户权限和修改用户信息）、删除用户和查询用户相关操作，通过这些活动创建管理员用例图如图4-4所示：

图4-4管理员用例图
4.3系统数据及数据处理需求
该系统主要包括三个模块，学生管理信息模块、教师管理模块、管理员管理模块。学生管理信息模块主要是入学后对信息的查看，教师管理模块主要是对学生成绩信息的添加、修改、删除和查询。管理员模块能对学生和教师进行添加、修改、删除和查询。根据上述功能，设计出数据流图，如图4-5所示：

图4-5数据流图
数据字典描述
数据存储描述
项目 描述
数据存储编号 D1
数据存储名称 管理员表
简述 添加、修改、查询、删除用户
数据存储组成 姓名+性别+家庭住址+系部
相关联的处理 学籍管理，成绩管理，课程管理

项目 描述
数据存储编号 D2
数据存储名称 学生表
简述 查询学生信息
数据存储组成 学号+姓名+性别+系部班级
相关联的处理 成绩管理，课程管理

项目 描述
数据存储编号 D3
数据存储名称 教师表
简述 教师上课安排
数据存储组成 教师编号+教师姓名+所教课程+上课班级
相关联的处理 课程管理

项目 描述
数据存储编号 D4
数据存储名称 学生成绩表
简述 存放学生各科考试成绩
数据存储组成 学号+姓名+系部班级+课程名称+课程编号
相关联的处理 课程管理，成绩管理

项目 描述
数据存储编号 D5
数据存储名称 学生选课表
简述 学生选课课程信息存储
数据存储组成 姓名+系部班级+课程名称+课程编号+学号
相关联的处理 课程管理，成绩管理

项目 描述
数据存储编号 D6
数据存储名称 课程表
简述 学生课程上课安排
数据存储组成 教师姓名+系部班级+课程名称+课程编号
相关联的处理 课程管理

项目 描述
数据存储编号 D7
数据存储名称 班级表
简述 存储班级信息
数据存储组成 班级编号+班级名称
相关联的处理 课程管理，成绩管理，学籍管理

项目 描述
数据存储编号 D8
数据存储名称 专业表
简述 存储专业信息
数据存储组成 专业编号+专业名称
相关联的处理 课程管理，成绩管理

4.4系统性能需求
本学生信息管理系统要能够抵御用户可能的各种操作，包括修改、更新、删除等操作，以保证软件的可行性，安全性，稳定性，同时要对数据进行检验，保证数据有效性，可操作性，安全性。只有管理员才能修改和删除、更新有关数据，学生只有查看浏览的权限。同时系统严格控制对数据库的修改等操作设置功能，只有高级管理员和系统管理员才能对数据库进行修改、更新等操作。
5.概念结构设计
5.1分析构成系统的实体
分析学生信息管理系统的基本需求，利用概念结构设计的抽象机制，对数据字典中的信息进行分类、组织、得到系统的实体、实体属性、实体的键、实体之间的联系以及联系的类型。
通过分析，学生信息管理系统的主要实体包含学生、教师、管理员、课程、班级和专业。

图5-1学生实体及属性
图5-2教师实体及属性
图5-3管理员实体及属性
图5-4课程实体及属性
图5-5班级实体及属性
图5-6专业实体及属性
5.2系统局部E-R图
从数据流图和数据字典分析得出实体及其属性后，可进一步分析各实体之间的关系。
“学生”实体与“课程”实体存在“选课”的联系，一个学生可以学修多门课程，每门课程可以被多个学生选修，所以他们之间存在多对多联系（m:n），如图5-7所示。

图5-7“学生”与“课程”实体的局部E-R图

“教师”实体与“课程”实体存在“讲授”的联系，一个教师可以教授多门课程，每门课程可以由多个教师讲授，所以它们之间存在多对多联系（m:n），如图5-8所示。

                 图5-8“教师”与“课程”实体的局部E-R图

“学生”实体与“专业”实体存在“学习”的联系，一个学生只可学习一个专业，每个专业有多个学生学习，所以“专业”实体和“学生”实体存在一对多联系（1：n），如图5-9所示。

                     图5-9“学生”与“专业”实体的局部E-R图

“班级”实体与“专业”实体存在“属于”联系，一个班级只可能属于一个专业，每个专业包含多个班级，所以“专业”实体和“班级”实体存在一对多联系（1：n），如图5-10所示。

                   图5-10“专业”和“班级”实体的局部E-R图

“学生”实体和“班级”实体存在“组成”的联系，一个学生只可属于一个班级，每个班级由多个学生组成，所以“班级”实体和“学生”实体存在一对多联系（1：n），如图5-11所示。

                图5-11“班级”和“学生”实体的局部E-R图

“管理员”实体与“学生”和“教师”实体存在“管理”的联系，一个管理员可管理多个教师和学生，教师和学生可被一个管理员管理，所以“管理员”实体和“教师”和“学生”实体存在一对多联系(1:n)，如图5-12所示。

                图5-12“管理员”和“学生”、“教师”实体的局部E-R图

5.3合成全局E-R图
系统的局部E-R图只能反映局部应用实体之间的联系，不能从整体上反映实体之间的相互关系。各局部E-R图之间可能存在一些冲突和数据冗余，为了减少这些问题，必须根据实体联系在实际应用中的语义进行综合和调整，根据对上面局部E-R图的分析，得到系统的全局E-R图。如图5-13所示。

6.逻辑结构设计
概念设计阶段设计的数据模型是独立于任何一种商用化的DBMS的信息结构。逻辑设计阶段的主要任务是把E-R图转化为选用的DBMS产品支持的数据模型。所以应把概念设计的E-R模型转化为关系数据模型。
6.1概念模型转换为关系模型
首先从“教师”实体和“课程”实体以及它们之间的联系来考虑。“教师”与“课程”实体之间存在多对多的关系，所以“教师”和“课程”以及“讲授”之间的关系分别设计如下关系模式。
教师（教师编号，教师姓名，所教课程，上课班级）
课程（课程编号，课程名称，教师，系部班级）
讲授（教师编号，课程编号，）
“专业”实体和“班级”实体之间的联系是一对多的联系（1：n），所以可以用如下两个关系模式来表示，其中联系被移动到“班级”实体中。
班级（班级编号，班级名称，专业编号）
专业（专业编号，专业名称）
“班级”和“学生”实体之间的联系是一对多的联系（1：n），所以可以用两个关系模式来表示。但是“班级”已有关系模式，所以下面只生成一个关系模式，其中联系被移动到“学生”实体中。
学生（学号，姓名，性别，系部班级，班级编号）
“学生”与“课程”实体之间存在多对多的联系）（m:n），所以“学生”和“课程”以及“选课”之间的关系分别设计如下关系模式。
学生（学号，姓名，性别，系部班级）
课程（课程编号，课程名称，教师，系部班级）
选课（学号，课程编号，成绩）
“管理员”实体与“管理”联系的关系是采用聚集来表示的，它们之间的关系是一对多的关系，可以使用以下关系模式来表示。
管理员（管理员编号，姓名，性别，管理系部）
管理（学号，教师编号，课程编号）
6.2关系模式优化
一般情况下，关系模式只需要满足3FN即可。前面设计出的“教师”“课程”“班级”“专业”以及“学生”等关系模式都比较适合实际应用，一般不需要做结构上的优化。
对于“讲授”（教师编号，课程编号）关系模式，既可用作存储教学计划信息，又代表某门课程由某个老师任课。但是，同一门课可能在同一学期由多个老师主讲，教师编号和课程编号对于用户不直观，使用教师姓名和课程名称比较直观，要得到教师姓名和课程名称就必须分别在“教师”以及“课程”关系模式进行连接，因此可将关系模式的名字改为“授课-计划”，因此将关系模式改为“授课-计划”（教师编号，课程编码）。
对于“管理”关系模式，由于管理员要审核学生、教师的信息和考试成绩，因此需要增加审核信息属性。所以，“管理”关系模式调整为管理（学号，教师编号，课程编号，学生姓名，教师姓名，课程名称，成绩，成绩审核人）。
6.3设计用户子模式（视图）
视图是由select子查询语句定义的一个逻辑表，只有定义没有数据，是一个“虚表”。
本系统创建了两个视图，利用SQL语句CREATE VIEW建立一个名为v_stu_c的视图，显示学生的学号、姓名、所学课程的课程编号。SQL语句如下：
CREATE VIEW v_stu_c
AS
SELECT s.scode,sname,课程编号 FROM studinfor s,grade g
WHERE s.scode=g.学号;
运行结果如图6-1所示：

图6-1
在利用SQL语句创建一个名为v_stu_g的视图，基于studinfor表、courseinfor表、grade表，视图能显示学生的学号、姓名、课程名称、成绩。SQL语句如下：
CREATE VIEW v_stu_g
AS
SELECT s.scode,sname,coursename,成绩 FROM studinfor s,grade g,courseinfor c
WHERE s.scode=g.学号 AND g.课程编号=c.Ccode;
运行结果如图6-2所示：

图6-2
7.物理结构设计
物理数据库设计的任务是将逻辑设计映射到存储介质上，利用可用的硬件和软件功能尽可能快的对数据进行物理访问和维护。
7.1表结构设计
在得到数据库的各个关系模式后，需要根据需求分析阶段数据字典的数据项描述给给出各数据库表的结构。考虑到系统的兼容性以及编写程序的方便性，可以将关系模式的属性对应表字段的英文名。同时，考虑到数据依赖关系和数据完整性，需要指出表的主键和外键，以及字段的值域约束和数据类型。
系统各表的结构如表7-1~7-9表所示。

表7-1 数据信息表
数据库表名 对应的关系模式名 中文说明
TeachInfor 教师 教师信息表
SpeInfor 专业 专业信息表
ClassInfor 班级 班级信息表
StudInfor 学生 学生信息表
CourseInfor 课程 课程基本信息表
SchemeInfor 授课-计划 授课计划信息表
StudCourse 选课 学生选课信息表
ManagerInfor 管理员 管理员信息表

表7-2 教师信息表(TeachInfor)
字段名 字段类型 长度 主键或外键 字段值约束 中文属性名
Tcode VARCHAR 10 PRIMARY KEY NOT NULL 教师编号
Tname VARCHAR 10 NOT NULL 教师姓名
Tcourse VARCHAR 12 NOT NULL 所教课程
Tclass VARCHAR 10 NOT NULL 上课班级

表7-3 专业信息表(SpeInfor)
字段名 字段类型 长度 主键或外键 字段值约束 中文属性名
Specode VARCHAR 8 PRIMARY KEY NOT NULL 专业编码
Spename VARCHAR 30 NOT NULL 专业名称

表7-4 班级信息表(ClassInfor)
字段名 字段类型 长度 主键或外键 字段值约束 中文属性名
Classcode VARCHAR 8 PRIMARY KEY NOT NULL 班级编码
Classname VARCHAR 20 NOT NULL 班级名称
Specode VARCHAR 8 FOREIGN KEY SpeInfor,Specode 专业编码

表7-5学生信息表(StudInfor)
字段名 字段类型 长度 主键或外键 字段值约束 中文属性名
Scode VARCHAR 10 PRIMARY KEY NOT NULL 学号
Sname VARCHAR 10 NOT NULL 姓名
Sex VARCHAR 4 (男，女) 性别
Sclass VARCHAR 20 系部班级
Classcode VARCHAR 8 FOREIGN KEY ClassInfor,Classcode 班级编号

表7-6课程基本信息表(CourseInfor)
字段名 字段类型 长度 主键或外键 字段值约束 中文属性名
Ccode VARCHAR 8 PRIMARY KEY NOT NULL 课程编码
Coursename VARCHAR 20 NOT NULL 课程名称
Courseteach VARCHAR 10 NOT NULL 教师
Courseclass VARCHAR 10 NOT NULL 系部班级

表7-7授课计划信息表(SchemeInfor)
字段名 字段类型 长度 主键或外键 字段值约束 中文属性名
Tcode VARCHAR 10 FOREIGN KEY TeachInfor.Tcode 教师编号
Ccode VARCHAR 8 FOREIGN KEY CourseInfor.Ccode 课程编号
Tname VARCHAR 10 教师姓名
Coursename VARCHAR 20 课程名称

表7-8学生选课信息表(StudCourse)
字段名 字段类型 长度 主键或外键 字段值约束 中文属性名
Scode VARCHAR 10 FOREIGN KEY StudeInfor.Scode 学号
Tcode VARCHAR 10 FOREIGN KEY TeachInfor.Tcode 教师编号
Ccode VARCHAR 8 FOREIGN KEY CourseInfor.Ccode 课程编号
Sname VARCHAR 10 学生姓名
Tname VARCHAR 10 教师姓名
Coursename VARCHAR 20 课程名称
ExamGrade DECIMAL 4,1 考试成绩
GradeAudit VARCHAR 10 成绩审核人

表7-9 管理员信息表(ManagerInfor)
字段名 字段类型 长度 主键或外键 字段值约束 中文属性名
Managercode VARCHAR 10 PRIMARY KEY NOT NULL 管理员编号
Managername VARCHAR 10 NOT NULL 姓名
Sex VARCHAR 4 （男，女） 性别
Managerspe VARCHAR 20 NOT NULL 管理系部
7.2存储结构设计
存储过程是一条或多条SQL语句的集合，利用这些SQL语句完成一个或者多个逻辑功能。
创建存储过程stu_grade,执行时通过输入学号可以查询该学生的各科成绩。
DELIMITER @@
CREATE PROCEDURE stu_grade()
BEGIN
SELECT sname,coursename,成绩 FROM studinfor s,courseinfor c,grade g
WHERE s.scode=g.学号 AND g.课程编号=c.ccode AND s.scode=‘202101’;
END @@
调用结果如图7-1所示

图7-1
创建存储过程stu_g_r,当输入一个学生的学号时，通过返回输出参数获取该学生选修课程的门数。
DELIMITER @@
CREATE PROCEDURE stu_g_r(IN scode VARCHAR(8),OUT num INT)
BEGIN
SELECT COUNT(*) INTO num FROM grade WHERE 课程编号=scode;
END @@
调用结果如图7-2所示

图7-2
7.3游标
当通过select语句查询时，返回的结果是一个由多行记录组成的集合，而程序设计语言并不能处理以集合形式返回的数据，为此SQL提供了游标机制。游标充当指针的作用，使应用程序设计语言一次只能处理查询结果中的一行。在学生信息管理系统中，创建存储过程stu_s,用游标提取studinfor表中学号为202101学生的姓名和班级。运行及调用结果如图7-3和7-4所示。

图7-3 图7-4
7.4索引的设计
在数据库中，索引可以帮助用户提高查询数据的效率，类似于书中的目录。当用户在一个表中建立主键（PRIMARY KEY）或唯一（UNIQUE）约束时，系统会自动创建唯一索引（UNIQUE INDEX）。
(1)主键索引
主键索引不能为空，一个表里只能有一个主键，学生信息管理系统的索引（主键）有：Tcode、Specode、Classcode、Scode、Ccode、Managercode.
(2)普通索引
通过建立单个字段的索引，可以通过该字段映射结果集找到该数据，为grade表中的“成绩”字段创建一个普通索引，命名为grade_idx,提高查询速度。
实现结果如图7-5所示

图7-5
(3)唯一索引
唯一索引一般是应用于字段数据不可重复（null值除外），唯一索引可以有多个null值，唯一索引也可以有多个字段。为courseinfor表中的课程编号（ccode）创建唯一索引，命名为cou_idx。
实现结果如图7-6所示：

图7-6
8.数据库实施与维护
8.1创建数据库
CREATE DATABASE xsgl;
USE xsgl;

8.2创建表
(1)表teachinfor

(2)表speinfor

(3)表classinfor

(4)表studinfor

(5)表Courseinfor

(6)表schemeinfor

(7)表studcourse

(8)表managerinfor

8.3数据完整性约束
数据库的完整性是指数据的正确性和相容性。利用完整性约束，DBMS可帮助用户组织非法数据的输入。
在学生信息管理系统中，学生的学号必须是唯一的；学生所选的课程必须是学校开设的课程。
学生信息表中创建主键约束：

课程信息表中课程编号设置主键约束：

在课程表中的课程名称建立唯一约束：

8.4触发器
触发器是一种特殊类型的存储过程，不由用户直接调用。创建触发器时会对其进行定义，以便在对特定表或列作特定类型的数据修改时执行。在学生信息管理系统中，创建触发器test_trig,实现在studinfor表中每插入一条学生记录自动在test中追加一条插入成功时的日期时间。SYSDATE()函数用来获取当前的日期和时间。
首先创建测试表test，它包含一个字段date_time，字段类型为VARCHAR(50)。

创建触发器test_trig。

为studinfor表插入一条记录引发触发器，查看test表中的内容。

在courseinfor表创建触发器del_trig，当courseinfor表中删除一门课程时，级联删除grade表中该课程的记录。

8.5查询过程
(1)单表查询
在studinfor表中查询每个学生的学号、姓名、性别。

查询studinfor表中学号为202109的学生的姓名和班级。

查询studinfor表中班级编号00000001的学生信息。

(2)连接查询
查询成绩在80-90的学生的学号、课程编号和成绩。

使用INNER JOIN连接方式查询学习“数据库”课程的学生的学号、姓名、成绩。

查询每个学生所选课程的最高成绩，列出学号、姓名、最高成绩。

(3)嵌套查询
在studinfor表中查询与“张三”性别相同的所有学生的信息。

使用IN子查询查找所选课程编号为01、03的学生的学号、姓名、性别。

9.总结
在设计一个系统的时候，前期的准备工作是非常重要的。前期的需求分析的好坏，很大程度得决定了整个系统的好坏，所以一定要做好需求分析。本系统是一个简化后的学生信息管理系统。系统总体需求描述了系统的四大功能，提出保密、完整和可靠的安全要求；系统总体设计主要从系统结构、开发平台和总体功能模块上进行考虑。系统需求利用DFD与DD结合的方式描述，主要包括全局DFD。在系统概念模型设计中，在需求分析的基础上，利用E-R模型描述系统的局部E-R图和全局E-R图，并对全局E-R图进行优化。系统逻辑设计将E-R模型转化为关系模型，形成数据库中各表的结构。系统物理设计部分从存储介质、表、视图及索引的创建等方面进行了介绍。
学生信息管理系统作为学校日常管理的基本工具，它不仅能为教学工作中提供便利，还大大地提高了工作效率。本次所设计开发的信息管理系统虽具备基本管理功能，但是仍然存在许多不足，这需要我们进一步完善和探索，进而使其更好地服务于学校日常管理工作。
10.参考文献
[1]王珊,萨师煊.《数据库系统概论》(第5版)[M].高等教育出版社,2014.9
[2]王珊.《数据库系统概论》(第5版)学习指导与习题解析[M].高等教育出版社,2015.7
[3]张红娟,金洁洁,匡芳君.《数据库课程设计》[M]西安电子科技大学出版社,2019
[4]李月军,付良廷《数据库原理及应用》(MySQL版)[M].清华大学出版社,2019
[5]李龙澍,郑诚.《软件工程课程设计》[M].机械工业出版社,2016