成绩管理系统数据库应用Word格式.docx
《成绩管理系统数据库应用Word格式.docx》由会员分享，可在线阅读，更多相关《成绩管理系统数据库应用Word格式.docx（23页珍藏版）》请在冰点文库上搜索。
（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一般关系模型的四条原则：
E—R图中的每一个独立实体变换为一个关系，其属性变为关系的属性，气主标识变为关系的主码；
E—R图中的从实体及相应的“的联系变换为一个关系，从实体的属性上加主体的关系的主码构成这个关系的属性。
如果“的”联系时1：
1的，则以主实体关系的主码为这个关系的主码；
M的，则以主实体关系的主码加上同一主实体个体联系的不同从属实个体赖以互相区分的属性组，组成该关系的主码；
原則3:
1：
M联系通过在“多”实体关系中增加相联系的“1”实体关系的主码及联系本身的属性来表达。
其中“1”实体主码为外来码；
原则4：
M：
M联系转换成一个独立的关系，被联系的实体关系的主码和联系本身的属性作为该关系的属性，被联系实体关系的主码组成其复合码。
3.12数据库初步关系框架
学生表（学号、姓名、性别、出生日期、院系名称、入学时间、出生地、政治面貌、备注）
教师表（教师编号、姓名、性别、出生日期、院系名称、学历、职称、备注）
课程表（课程号、课程名、学分、备注）
成绩表（学号、教师编号、课程号、分数）
3.2具体逻辑模型设计：
表3.2.1学生表（student）
字段名
学号
姓名
性别
出生日期
入学时间
院系名称
出生地
政治面貌
备注
类型
int
char
chai
datetime
smalldatetime
nvarchar
text
宽度
8
10
2
6
20
NULL
否
是
表3.2.2教师表（teacher）
教师编号
学历
职称
nchar
4
表3.2.3课程表（course）
课程号
课程
学分
表3.2.4成绩表（grade）
分数
smallint
四、数据库物理结构设计与数据库实施以及维护设计
4.1设计索引
（1）在学生表建立索引，按照学号升序排列。
（2）在教师表建立索引，按教师编号升序排列。
（3）在课程表建立索引，按课程号升序排列。
（4）在成绩表建立索引，按学号升序排列，在同一学号下，按成绩升序排列。
4.2设计表之间的关系：
父表
学生表
成绩表
教师表
课程表
子表
关联索引
表间关系类型
m:
n
4.3建立查询
查询是数据库的核心操作。
在SQL语句中，数据库查询时数据库的核心，包括单表查询、连接查询、嵌套查询以及集合查询。
SQL提供了select语句、orderby语句、groupby语句等进行查询操作。
根据不同用户的查询需要创建下述十一个查询：
（1）查询女教师的教师编号、姓名和出生日期。
select教师编号,姓名,出生日期
from教师表
where性别='
女'
;
（2）根据教师的年龄，将教师分为：
老年（大于等于50岁）、中年（40岁至50岁之间）和青年（小于等于40岁）。
SELECT*,
（CASE
WHEN（age>
=50）THEN'
老年'
=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）查询计算机学院姓张，并且姓名是两个字的学生的信息。
where姓名like'
张_'
and院系名称='
计算机学院'
（6）查询所有学生的分数信息，如果分数大于等于80，则为“优秀”；
大于等于60，则为“及格”；
小于60，则为“不及格”。
使用CASE函数给每个学生的分数设定等级。
select*,
（case
when（分数>
=80）then'
优秀'
=60）then'
及格'
when（分数<
60）then'
不及格'
end）
from成绩表
（7）统计选修了5号课程的学生的总分、平均分、最高分和最低分。
selectsum（分数）,avg（分数）,max（分数）,min（分数）
where课程号='
5'
（8）统计每门课程的总分和平均分，并按平均分从高到低排序输出。
select总分=sum（分数）,平均分=avg（分数）
groupby课程号
orderbyavg（分数）desc
（9）统计每个学院学生的男女生人数。
select院系名称,性别,人数=count（*）
groupby院系名称,性别
（10）查询选修了“数据库应用”课程的学生的学号和姓名。
select学生表.学号,姓名
from学生表,成绩表,课程表
where学生表.学号=成绩表.学号
and课程表.课程号=成绩表.课程号
and课程名='
数据库应用'
（11）查询分数都大于王林分数的学生、课程名和分数。
selectdistinct姓名,成绩表.学号,姓名,成绩表.课程号,分数
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]
@学号varchar（50）,@课程号int,
@分数float
AS
SELECT学号,教师编号,课程号,分数
from成绩表
updategra_table
set分数=@分数
where学号=@学号
（3）对学生表建立存储过程，在表中插入一条记录。
然后执行该存储过程，验证插入一条学生记录的结果。
ALTERPROCEDURE[dbo].[stuinfo]
@学号varchar（50）,@姓名varchar（50）,@性别varchar（50）,
@出生日期varchar（50）,@院系名称varchar（50）,@入学时间varchar（50）,
@出生地varchar（50）,@政治面貌varchar（50）
select*
from学生表
insertintostuinfo
values（'
10024'
'
王一'
1987-7-140:
00:
00'
'
2006-9-100:
上海'
党员'
GO
（4）对课程表建立存储过程，根据课程号在课程表中删除某个课程记录。
（注意，要首先在成绩表中删除相关记录），然后执行存储过程，验证结果。
IFOBJECT_ID（N'
cour_table'
N'
FN'
）ISNOTNULL
DROPPROCEDUREcour_table;
CREATEPROCEDUREcour_table
--Addtheparametersforthestoredprocedurehere
@课程名varchar（50）,@课程号int,@学分int
SELECT课程名,课程号,学分
from课程表
delete
fromcour_table
where课程号=@课程号
（5）对成绩表建立存储过程，根据学号修改某个同学的某门课的成绩，然后执行存储过程。
（6）对教师表创建存储过程，根据年龄划分等级：
teac_table'
）ISNOTNULL
DROPPROCEDUREteac_table;
CREATEPROCEDUREteac_table
@姓名varchar（50）,@出生日期varchar（50）
select姓名,年龄=casewhenyear（getdate（））-year（出生日期）>
=50
then'
whenyear（getdate（））-year（出生日期）>
40and
year（getdate（））-year（出生日期）<
50
then'
whenyear（getdate（））-year（出生日期）<
=40then'
end
（7）创建存储过程，查询所有学生的分数信息，如果分数大于等于80，则为“优秀”；
查询出的结果应尽量清晰。
gra_info'
DROPPROCEDUREgra_info;
CREATEPROCEDUREgra_info
@学号varchar（50）,@分数int
select学号,等级=casewhen分数>
=80then'
when分数>
=60then'
when分数<
60then'
end
from成绩表;
4.6游标的创建与使用
SQL是面向集合的，一条SQL语句可以产生或处理多条记录。
而主语言是面向记录的。
一组主变量一次只能存放一条记录。
所以仅使用主变量并不能完全满足SQl语句向应用程序输出数据的要求，为此嵌入式SQL引入了游标的概念，用游标来协调这两种不同的处理方式。
游标是系统为用户开始的一个数据缓冲区，存放SQL语句的执行结果，每个游标去都一个名字。
用户可以通过游标逐一获取记录，并赋给主变量，交由主语言进一步处理。
根据不同类型用户的查询需要创建下述三个游标：
（1）利用T-SQL扩展方式声明一个游标，查询学生表中的学号、姓名、性别和出生日期信息，并读取数据。
要求：
1）读取最后一条记录。
FETCHLASTFROM学生
2）读取第一条记
升级会员 