成绩管理系统数据库应用.docx
《成绩管理系统数据库应用.docx》由会员分享，可在线阅读，更多相关《成绩管理系统数据库应用.docx（23页珍藏版）》请在冰豆网上搜索。
成绩管理系统数据库应用
序言
在当今这个社会，迅速发展的经济，日新月异的科技水平，无一不彰显着信息时代的到来，也提醒并要求着我们要熟练掌握信息技术，要学会并擅于运用相应的技术。
而数据库是数据管理的最新技术，是计算机科学的重要分支。
今天，信息资源已成为各个部门的重要财富和资源。
建立一个满足各级部门信息处理要求的行之有效的信息系统也成为一个企业或坐直生存和发展的重要条件。
因此，作为信息系统核心和基础的数据库技术得到越来越光弹的应用，越来越多新的应用领域采用数据库技术来存储和处理信息资源。
随着高端人才需求的日益增加，学校的数目正在逐年增长，如何更加妥善安排师生的日常生活成为了一个备受关注的话题。
传统的手工操作方式不仅易发生数据丢失，而却劳动强度高、时间慢，但如果使用计算机则可以告诉、有效、快捷的完成相应工作。
在这个互联网的时代，可以便捷的传送数据，实现数据共享，避免了冗余无用的劳动，提高了教育教学的管理质量，也对其在管理行为方面进行了规范，可谓一举多得。
学生成绩管理系统以计算机为主要工具，以数据库技术为依托，通过现代化科学技术，提高广大师生工作人员的办公效率，实现管理工作的自动化、高效化。
本系统主要面对的对象是全校师生，针对学生成绩、课程等信息开发的管理系统，主要包含学生的基本信息，学生课程的设置，成绩信息的统计等。
学生成绩管理系统包括学生信息管理、学生成绩管理、学生课程管理以及教师信息管理几部分：
1.学生信息管理包括添加录入学生籍贯、性别、出生日期、院系名称以及政治状态；
2.学生成绩管理包括添加录入教师编号、课程号以及分数；
3.学生课程管理包括添加录入课程名、课程号以及学分；
4.教师信息管理包括添加录入教师籍贯、性别、出生日期、职称以及政学历。
一、系统分析
1.1用户需求分析
随着计算机的日渐普遍，以及网络的深入人心，各大高校也逐渐开始使用数据库来记录或统计分析数据。
“学生成绩管理系统”可以更好的帮助教师便捷、迅速的提高学生成绩管理效率，具有便于查找，安全性好，存储量大，不易损害坏，寿命长，低成本等有点，方便教师对学生课程、成绩等相关内容进行查询、统计等操作。
系统总体构图如下：
图1.1系统总体构图
1.2业务流程分析：
（如图1.2所示）
图1.2业务流程分析图
1.3信息需求分析
从原始资料中抽出各栏目名称等系统要保存的相关事项，去掉组合项、导出项、泛指项、得到基本项，就是所要组织数据库表中的信息。
列举如下：
学号、教师编号、课程号、分数、姓名、性别、出生年月、院系名称、学历、职称、课程名、学分、学生学号、学生姓名、出生地、政治面貌。
1.4功能需求分析
（1）管理员可以根据要求添加、修改或删除学生信息；
（2）管理员可以根据要求添加、修改或删除班级信息；
（3）教师可以查询教师信息
（4）教师可以查询学生信息，录入学生成绩
（5）学生可以用学号登录，对课程、成绩进行查询。
根据以上对系统功能的分析，建立了系统功能分析图，系统功能分析（如下图所示）反映了系统功能模块之间的层次关系：
图1.3系统功能分析图
二、数据库概念模型设计
2.1构思E—R图的原则：
原则1：
能独立存在的事物，例如人、物、事、地、团体、机构、活动、事项等等，在其有多个由基本项描述的特征需要关注时，就应把它作为实体。
原则2：
两个或多个实体间的关系与联合，如主管、从属、组成、占有、作用、配合、协同等等，当需要予以关注时，应作为联系。
联系通常是某类行为动作，ERD中关注的是其状态与结果而非过程。
原则3：
实体的属性是实体的本质特征。
实体应有标识属性（能把不同的个体区分的开来的属性组），并指定其中一个作为主标识。
联系的属性是联系的结果或状态。
属性具有以下几个特点：
a：
非多值性；b：
非符合性；c：
非导出性。
而实体的属性还有非关联性。
原则4（一事一地原则）：
所有基本项在同一E-R图中作为属性要仅在一个地方出现。
2.2数据库需求分析：
针对“学生成绩管理系统”数据库信息的查询、学生成绩信息系统查询流程，以及对学生课程、成绩和教师相关信息的需求分析，设计如下数据项与数据结构：
学生：
学号、姓名、性别、出生日期、院系名称、入学时间、出生地、政治面貌、备注
教师：
教师编号、姓名、性别、出生日期、院系名称、学历、职称、备注
课程：
课程号、课程名、学分、备注
成绩表：
学号、教师编号、课程号、分数
2.3设计E—R图：
图2.1为系统实体联系E—R图
三、数据库逻辑模型设计
3.1一般逻辑模型设计：
3.11一般关系模型的四条原则：
原则1：
E—R图中的每一个独立实体变换为一个关系，其属性变为关系的属性，气主标识变为关系的主码；
原则2：
E—R图中的从实体及相应的“的联系变换为一个关系，从实体的属性上加主体的关系的主码构成这个关系的属性。
如果“的”联系时1：
1的，则以主实体关系的主码为这个关系的主码；如果“的”联系时1：
M的，则以主实体关系的主码加上同一主实体个体联系的不同从属实个体赖以互相区分的属性组，组成该关系的主码；
原則3:
1：
M联系通过在“多”实体关系中增加相联系的“1”实体关系的主码及联系本身的属性来表达。
其中“1”实体主码为外来码；
原则4：
M：
M联系转换成一个独立的关系，被联系的实体关系的主码和联系本身的属性作为该关系的属性，被联系实体关系的主码组成其复合码。
3.12数据库初步关系框架
学生表（学号、姓名、性别、出生日期、院系名称、入学时间、出生地、政治面貌、备注）
教师表（教师编号、姓名、性别、出生日期、院系名称、学历、职称、备注）
课程表（课程号、课程名、学分、备注）
成绩表（学号、教师编号、课程号、分数）
3.2具体逻辑模型设计：
表3.2.1学生表（student）
字段名
学号
姓名
性别
出生日期
入学时间
院系名称
出生地
政治面貌
备注
类型
int
char
chai
datetime
smalldatetime
char
nvarchar
char
text
宽度
8
10
2
6
6
20
10
10
NULL
否
否
是
是
是
是
是
是
是
表3.2.2教师表（teacher）
字段名
教师编号
姓名
性别
出生日期
院系名称
学历
职称
备注
类型
char
nchar
chai
datetime
char
char
char
text
宽度
4
6
2
6
20
10
10
NULL
否
否
是
是
是
是
是
是
表3.2.3课程表（course）
字段名
课程号
课程
学分
备注
类型
char
char
int
text
宽度
2
20
2
NULL
否
否
否
是
表3.2.4成绩表（grade）
字段名
学号
课程号
教师编号
分数
类型
char
char
char
smallint
宽度
8
6
4
6
NULL
四、数据库物理结构设计与数据库实施以及维护设计
4.1设计索引
（1）在学生表建立索引，按照学号升序排列。
（2）在教师表建立索引，按教师编号升序排列。
（3）在课程表建立索引，按课程号升序排列。
（4）在成绩表建立索引，按学号升序排列，在同一学号下，按成绩升序排列。
4.2设计表之间的关系：
父表
学生表
成绩表
教师表
课程表
子表
成绩表
课程表
课程表
成绩表
关联索引
学号
课程号
教师编号
课程号
表间关系类型
m:
n
m:
n
m:
n
m:
n
4.3建立查询
查询是数据库的核心操作。
在SQL语句中，数据库查询时数据库的核心，包括单表查询、连接查询、嵌套查询以及集合查询。
SQL提供了select语句、orderby语句、groupby语句等进行查询操作。
根据不同用户的查询需要创建下述十一个查询：
（1）查询女教师的教师编号、姓名和出生日期。
select教师编号,姓名,出生日期
from教师表
where性别='女';
（2）根据教师的年龄，将教师分为：
老年（大于等于50岁）、中年（40岁至50岁之间）和青年（小于等于40岁）。
SELECT*,
（CASE
WHEN（age>=50）THEN'老年'
WHEN（age>=40）THEN'中年'
ELSE'青年'
END）年纪
FROM（
SELECT*,DATEDIFF（YEAR,出生日期,GETDATE（））ASageFROM教师表
）t
（3）查询选修“大学英语”的学生的姓名，并按照分数从高到低输出前3名。
selecttop3姓名
from学生表,课程表,成绩表
orderby分数DESC;
（4）查询体育学院和外语学院，而且在1986年出生的学生信息。
select*
from学生表
where院系名称='体育学院'or院系名称='外语学院'and出生日期='1986'
（5）查询计算机学院姓张，并且姓名是两个字的学生的信息。
select*
from学生表
where姓名like'张_'and院系名称='计算机学院'
（6）查询所有学生的分数信息，如果分数大于等于80，则为“优秀”；大于等于60，则为“及格”；小于60，则为“不及格”。
使用CASE函数给每个学生的分数设定等级。
select*,
（case
when（分数>=80）then'优秀'
when（分数>=60）then'及格'
when（分数<60）then'不及格'
end）
from成绩表
（7）统计选修了5号课程的学生的总分、平均分、最高分和最低分。
selectsum（分数）,avg（分数）,max（分数）,min（分数）
from成绩表
where课程号='5'
（8）统计每门课程的总分和平均分，并按平均分从高到低排序输出。
select总分=sum（分数）,平均分=avg（分数）
from成绩表
groupby课程号
orderbyavg（分数）desc
（9）统计每个学院学生的男女生人数。
select院系名称,性别,人数=count（*）
from学生表
groupby院系名称,性别
（10）查询选修了“数据库应用”课程的学生的学号和姓名。
select学生表.学号,姓名
from学生表,成绩表,课程表
where学生表.学号=成绩表.学号
and课程表.课程号=成绩表.课程号
and课程名='数据库应用'
（11）查询分数都大于王林分数的学生、课程名和分数。
selectdistinct姓名,成绩表.学号,姓名,成绩表.课程号,分数
from学生表,成绩表,课程表
where成绩表.学号=学生表.学号and成绩表.课程号=课程表.课程号and分数>（select分数
from学生表,成绩表,课程表
where成绩表.学号=学生表.学号and成绩表.课程号=课程表.课程号and姓名='王莹莹'）
4.4建立视图
视图是数据库的一个对象，并且是动态表的静态定义。
视图时一个虚表，通过视图可以对表中的记录进行查看、修改、添加、删除等操作。
根据不同类型用户的查询需要创建下述四个视图：
（1）创建“学生_课程_分数”视图，包括计算机学院的学生的学号、姓名，和他们选修的课程号、课程名、分数。
SELECTdbo.学生表.姓名,dbo.成绩表.课程号,dbo.课程表.课程名,dbo.成绩表.分数,dbo.成绩表.教师编号FROMdbo.成绩表INNERJOINdbo.课程表ONdbo.成绩表.课程号=dbo.课程表.课程号INNERJOINdbo.学生表ONdbo.成绩表.学号=dbo.学生表.学号WHERE（dbo.学生表.院系名称='计算机学院'）
（2）创建“不及格学生信息”视图，包括全校学生中有不及格成绩的姓名、课程名、分数。
SELECTdbo.学生表.姓名,dbo.课程表.课程名,dbo.成绩表.分数FROMdbo.成绩表INNERJOINdbo.课程表ONdbo.成绩表.课程号=dbo.课程表.课程号INNERJOINdbo.学生表ONdbo.成绩表.学号=dbo.学生表.学号WHERE（dbo.成绩表.分数<60）
（3）创建“教师信息”视图，查看教师的所有信息资料。
SELECT教师编号,姓名,性别,出生日期,院系名称,学历,职称,备注
FROMdbo.教师表
（4）创建“课程信息”视图，包括课程号、课程名、学分、任课教师等信息。
SELECTdbo.课程表.课程号,dbo.成绩表.课程号ASExpr1,dbo.教师表.教师编号,dbo.课程表.课程名,dbo.课程表.学分
FROMdbo.成绩表INNERJOIN
dbo.教师表ONdbo.成绩表.教师编号=dbo.教师表.教师编号INNERJOIN
dbo.课程表ONdbo.成绩表.课程号=dbo.课程表.课程号
4.5建立存储过程
存储过程是一组能够完成特定功能的SQL语句集，经编译后存储在数据库中。
用户通过制定存储过程的名字并给出参数来执行它。
存储过程的创建有三种方法：
即使用Transact-SQL命令CREATEPROCEDURE、使用企业管理创建和使用创建存储的向导过程。
主要运用企业管理器创建以下七个存储过程：
（1）用T-SQL语句创建一个存储过程StuScoreInfo，完成的功能是在学生表、课程表和成绩表中查询以下字段：
院系、学号、姓名、性别、课程名称、考试分数。
ALTERPROCEDURE[dbo].[StuScoreInfo]
--Addtheparametersforthestoredprocedurehere
AS
BEGIN
--SETNOCOUNTONaddedtopreventextraresultsetsfrom
--interferingwithSELECTstatements.
SETNOCOUNTON;
--Insertstatementsforprocedurehere
select院系名称,学生表.学号,学生表.姓名,性别,课程名,分数
from学生表innerjoin成绩表on成绩表.学号=学生表.学号
innerjoin课程表on成绩表.课程号=课程表.课程号
END
（2）创建一个带有参数的存储过程Stu_Info，该存储过程根据输入的学号，在学生表中查询此学生的信息。
ALTERPROCEDURE[dbo].[gra_table]
--Addtheparametersforthestoredprocedurehere
@学号varchar（50）,@课程号int,
@分数float
AS
BEGIN
--SETNOCOUNTONaddedtopreventextraresultsetsfrom
--interferingwithSELECTstatements.
SETNOCOUNTON;
--Insertstatementsforprocedurehere
SELECT学号,教师编号,课程号,分数
from成绩表
updategra_table
set分数=@分数
where学号=@学号
END
（3）对学生表建立存储过程，在表中插入一条记录。
然后执行该存储过程，验证插入一条学生记录的结果。
ALTERPROCEDURE[dbo].[stuinfo]
--Addtheparametersforthestoredprocedurehere
@学号varchar（50）,@姓名varchar（50）,@性别varchar（50）,
@出生日期varchar（50）,@院系名称varchar（50）,@入学时间varchar（50）,
@出生地varchar（50）,@政治面貌varchar（50）
AS
BEGIN
--SETNOCOUNTONaddedtopreventextraresultsetsfrom
--interferingwithSELECTstatements.
SETNOCOUNTON;
--Insertstatementsforprocedurehere
select*
from学生表
insertintostuinfo
values（'10024','王一','女','1987-7-140:
00:
00',
'体育学院','2006-9-100:
00:
00','上海','党员'）
END
GO
（4）对课程表建立存储过程，根据课程号在课程表中删除某个课程记录。
（注意，要首先在成绩表中删除相关记录），然后执行存储过程，验证结果。
IFOBJECT_ID（N'cour_table',N'FN'）ISNOTNULL
DROPPROCEDUREcour_table;
GO
CREATEPROCEDUREcour_table
--Addtheparametersforthestoredprocedurehere
@课程名varchar（50）,@课程号int,@学分int
AS
BEGIN
--SETNOCOUNTONaddedtopreventextraresultsetsfrom
--interferingwithSELECTstatements.
SETNOCOUNTON;
--Insertstatementsforprocedurehere
SELECT课程名,课程号,学分
from课程表
delete
fromcour_table
where课程号=@课程号
END
GO
（5）对成绩表建立存储过程，根据学号修改某个同学的某门课的成绩，然后执行存储过程。
ALTERPROCEDURE[dbo].[gra_table]
--Addtheparametersforthestoredprocedurehere
@学号varchar（50）,@课程号int,
@分数float
AS
BEGIN
--SETNOCOUNTONaddedtopreventextraresultsetsfrom
--interferingwithSELECTstatements.
SETNOCOUNTON;
--Insertstatementsforprocedurehere
SELECT学号,教师编号,课程号,分数
from成绩表
updategra_table
set分数=@分数
where学号=@学号
END
（6）对教师表创建存储过程，根据年龄划分等级：
老年（大于等于50岁）、中年（40岁至50岁之间）和青年（小于等于40岁）。
IFOBJECT_ID（N'teac_table',N'FN'）ISNOTNULL
DROPPROCEDUREteac_table;
GO
CREATEPROCEDUREteac_table
--Addtheparametersforthestoredprocedurehere
@姓名varchar（50）,@出生日期varchar（50）
AS
BEGIN
--SETNOCOUNTONaddedtopreventextraresultsetsfrom
--interferingwithSELECTstatements.
SETNOCOUNTON;
--Insertstatementsforprocedurehere
select姓名,年龄=casewhenyear（getdate（））-year（出生日期）>=50
then'老年'
whenyear（getdate（））-year（出生日期）>40and
year（getdate（））-year（出生日期）<50
then'中年'
whenyear（getdate（））-year（出生日期）<=40then'青年'
end
from教师表
END
GO
（7）创建存储过程，查询所有学生的分数信息，如果分数大于等于80，则为“优秀”；大于等于60，则为“及格”；小于60，则为“不及格”。
使用CASE函数给每个学生的分数设定等级。
查询出的结果应尽量清晰。
IFOBJECT_ID（N'gra_info',N'FN'）ISNOTNULL
DROPPROCEDUREgra_info;
GO
CREATEPROCEDUREgra_info
--Addtheparametersforthestoredprocedurehere
@学号varchar（50）,@分数int
AS
BEGIN
--SETNOCOUNTONaddedtopreventextraresultsetsfrom
--interferingwithSELECTstatements.
SETNOCOUNTON;
--Insertstatementsforprocedurehere
select学号,等级=casewhen分数>=80then'优秀'
when分数>=60then'及格'
when分数<60then'不及格'
end
from成绩表;
END
GO
4.6游标的创建与使用
SQL是面向集合的，一条SQL语句可以产生或处理多条记录。
而主语言是面向记录的。
一组主变量一次只能存放一条记录。
所以仅使用主变量并不能完全满足SQl语句向应用程序输出数据的要求，为此嵌入式SQL引入了游标的概念，用游标来协调这两种不同的处理方式。
游标是系统为用户开始的一个数据缓冲区，存放SQL语句的执行结果，每个游标去都一个名字。
用户可以通过游标逐一获取记录，并赋给主变量，交由主语言进一步处理。
根据不同类型用户的查询需要创建下述三个游标：
（1）利用T-SQL扩展方式声明一个游标，查询学生表中的学号、姓名、性别和出生日期信息，并读取数据。
要求：
1）读取最后一条记录。
FETCHLASTFROM学生
2）读取第一条记
升级会员 