数据库课程设计-运动会成绩管理系统.doc

数据库原理与应用课程设计说 明 书题 目： 运动会成绩管理系统 学 院： 班 级： 完成人：组号： 姓名：XXX 学号：XXXX 成绩： 姓名：XXX 学号：XXXX 成绩： 指导教师： 山 东 科 技 大 学2014年6月27日课 程 设 计 任 务 书一、课程设计题目： 运动会成绩管理系统 二、课程设计应解决的主要问题：（按实现的系统功能）（1）运动项目、运动员、运动成绩以及班级名次的记录存储（2）添加修改和删除项目表、运动员表和成绩表信息 （3）自动生成班级名次表（触发器）（4）运动员表有改动时，自动更新班级名次表（触发器）（5）成绩表有改动时自动对成绩表中记录按项目排名次（触发器） （6）成绩表自动排名自动更新班级名次表中班级总分（存储过程）（7）更新班级总分之后自动对班级排名(学院名次，学校名次)(存储过程)（8）对成绩表进行初次排名功能（9）通过项目号获得相应项目的排名表（表值函数）（10）通过学号或姓名或运动号获得某学生运动成绩表（表值函数） （11）通过班级名称获得某班所有运动员运动成绩表（表值函数） （12）通过学院名称获得学院所有运动员运动成绩表（表值函数） （13）获得全校所有运动员的运动成绩表（表值函数） （14）获得全校所有班级总分数以及在学院排名和早全校排名（表值函数） 3、 任务发出日期： 2014-4-25 课程设计完成日期： 2014-6-27 小组分工说明小组编号 题 目： 运动会成绩管理系统 小组分工情况：XXX：参与数据库的讨论和设计；搜集整理数据、录入数据；设计了具体的逻辑结构，绘制E-R图；创建数据库以及项目表、运动员表、成绩表和班级名次表；实现了表之间的关联，创建必要的默认值、规则；创建了相应的索引；创建了班级名次表的视图。XXX：参与数据库的讨论和设计；创建某项目名次表值函数；创建触发器实现成绩表的按项目自动排名功能；创建存储过程实现班级名次表的班级总成绩的更新功能；创建存储过程实现班级名次表的排名功能；创建触发器实现运动员表改动时自动更新班级名次表的功能；创建多个表值函数用于实现不同的查询功能：项目查询函数，个人成绩查询函数，班级成绩查询函数，学院成绩查询函数，全校成绩查询函数，全校班级名次查询函数。整个数据库部分的设计共创建了三个触发器，四个存储过程以及七个表值函数。另外还设计了本运动会成绩管理系统的界面部分，以及文档编辑工作。组长签字： 年 月 日指导教师对课程设计的评价成绩： 指导教师签字： 年 月 日 目 录1 需求分析111 数据事实112 主要用户视图22 概念结构设计221 局部ER图322 全局ER图33 逻辑结构设计44 物理结构设计45 主要功能详细设计551 创建数据库552 创建数据表553 添加数据（仅以一条为例）654 数据库完整性755 成绩表按成绩自动排名（触发器，函数，存储过程）756 更新 clscore 表（触发器）1257 成绩表按照项目初始名次1358 查询某个项目的全部成绩和排名（表值函数）1459 查询某个班的运动员成绩（表值函数）14510 查询某个学生的所有成绩（表值函数）15511 查询某个学生的某个项目成绩（表值函数）16512 查询某个学院的运动员成绩（表值函数）16513 查询全校运动员成绩（表值函数）17514 通过学号和运动号查询成绩和名次（存储过程）17515 创建班级成绩和名次视图18516 创建索引186 界面设计主要过程186.1 界面总体设计196.2 登录界面设计196.3 成绩管理员界面设计206.4 运动员查询界面设计261 需求分析本系统是用于管理运动会成绩的系统，要实现的功能就是运动会成绩的增加，修改和删除，同时还应该具有运动员和运动项目的增加修改和删除功能。（1） 本系统面向用户有两种：成绩管理员、学生运动员。（2） 管理员功能介绍管理员正确登陆后，可以增加、删除、修改、查询运动员信息，比赛项目以及比赛成绩。（3） 学生运动员主要功能介绍学生登录后，可以查询（比赛成绩、运动动员信息、运动项目）。11 数据事实数据实例 12 主要用户视图用户需求成绩管理员登录（正确输入用户名及密码登录）增加、删除、修改、查询运动员信息，比赛项目以及比赛成绩学生登录（默认用户名）查询（比赛成绩、运动动员信息、运动项目）2 概念结构设计学生登录管理员登入学生登录查询查询查询查询查询与修改查询与修改查询与修改基本信息查询成绩查询基本信息查询成绩查询基本信息查询成绩查询信息录入比赛成绩录入添加比赛项目添加学生信息成绩表项目表运动员表全校名次查询全校成绩查询学院成绩查询班级成绩查询项目成绩查询个人成绩查询图2.1系统边界21 局部ER图运动号性别项目二姓名学院学生表学号项目一班级项目表项目号项目名称图1 项目表图2 学生表成绩表学号项目号成绩名次班级名次表班级名班级得分在学院排名在校排名图3 成绩表图4 班级名次表mn22 全局ER图学生表项目表成绩表班级名次表选择排名3 逻辑结构设计关系模式如下：运动员（学号、姓名、性别、学院、班级、运动号、项目一、项目二）运动项目（项目号、项目名称）成绩（学号、项目号、成绩、名次）班级排名（学院、班级、总成绩、在学院名次、在校名次）4 物理结构设计 学生表列名数据类型允许空说明SNO Int否学生编号SNameVarchar(50)否姓名SDeptVarchar(50)是系别SClassNOVarchar(50)是班级SportsNO Int否运动编号项目表列名数据类型允许空说明SportsNOInt否项目编号SportsNameVarchar(50)否项目名称项目成绩表列名数据类型允许空说明SNOInt否学生编号SportsNOInt否项目编号ScoreVarchar（50）否成绩Srankint否名次班级名次表列名数据类型允许空说明sdeptnameVarchar（20）是学院sclassnameVarchar（20）否班级sclscoreint是总分sclrankindeptint是 学院名次sclrankinschint是 学校名次5 主要功能详细设计51 创建数据库CREATE DATABASE sportsON ( NAME = 运动会数据, FILENAME = 'D:SQLsports.MDF', SIZE = 10MB, MAXSIZE = 100MB )LOG ON ( NAME = 运动会数据日志, FILENAME = 'D:SQLsports_Log.LDF', SIZE = 5MB, MAXSIZE = 25MB)52 创建数据表1、 创建项目表use sportscreate table sp(sportno int NOT NULL PRIMARY KEY, sportname varchar(30) not null)2、 创建学生表create table stu(sno int NOT NULL PRIMARY KEY, sname varchar(10)not null, sdept varchar(20), sclassname varchar(20), ssex varchar(2), spno int not null unique, sportno1 int foreign key references sp(sportno) not null, sportno2 int foreign key references sp(sportno) ) 3、 创建成绩表 -全体运动员的各个项目的成绩表create table sc(sno int FOREIGN KEY references stu(sno), sportno int FOREIGN KEY references sp(sportno), score varchar(10), srank int primary key (sno,sportno)4、 创建名次表-记录全校所有班级的得分以及在学院的名次和在全校的名次create table clscore(sdeptname varchar(20),-学院名 sclassname varchar(20) primary key,-班级名称 sclscore int,-班级成绩 sclrankindept int,-班级在学院中的名次 sclrankinsch int-班级在学校中的名次)53 添加数据（仅以一条为例）1、 添加运动项目表记录use sports insert into sp values (1,'男子一百'),(2,'男子二百')2、 添加学生表记录use sportsinsert into stu values(10103,'赵浩','数学','数学一班','男',10001,1,3),(10105,'钱浩','数学','数学一班','男',10002,2,4)3、 添加比赛成绩表记录use sportsinsert into sc (sno,sportno,score) values(10103,1,'12.2')54 数据库完整性1、 绑定列上默认值use sportsgocreate default ssex as '男'goexec sp_bindefault 'ssex','stu.ssex'go2、 绑定规则create rule rule_sportno as c1 between 1 and 12gocreate rule rule_sno as c1 between 10101 and 40340goexec sp_bindrule 'rule_sportno','sp.sportno' goexec sp_bindrule 'rule_sno','stu.sno' go55 成绩表按成绩自动排名（触发器，函数，存储过程）功能简介：当sc 表有成绩插入删除或者修改时就触发，实现对sc表中单项目名次nrank属性的及时更新，更新完之后，调用存储过程，实现对clscore表中班级成绩和班级排名的更新。551 更新项目名次触发器-触发器-及时更新 sc 表-当有成绩插入删除或者修改sc 表时就触发-实现对sc表中单项目名次nrank属性的及时更新use sportsgo-插入更新与删除操作sc 表if exists(select * from sysobjects where name = 'trSC' and type = 'TR')drop trigger trSCgocreate trigger trSCon sc after insert,update,deleteasdeclare sportno0 int,sno0 int,sportno1 int,sportno2 intselect sno0=sno,sportno0=sportno from insertedselect sportno1=sportno1,sportno2=sportno2 from stu where sno = sno0if sportno0 not in(sportno1,sportno2)-if sportno0!=sportno1 and sportno0!=sportno2beginprint '操作有误，此记录与运动员表不一致，请先修改运动员表！'rollbackendelsebegin-更新名次-声明变量declare sno int,sportno int,rank intdeclare iu_scSportno_cursor CURSOR forselect sportno from spopen iu_scSportno_cursorfetch next from iu_scSportno_cursor into sportnowhile fetch_status = 0beginset rank = 1-声明游标declare iu_sc_cursor CURSOR forselect sno from scRankFun(sportno)-打开游标open iu_sc_cursor-提取第一行fetch next from iu_sc_cursor into snowhile FETCH_STATUS = 0beginupdate scset srank = rankwhere sno = sno and sportno = sportnoset rank = rank+ 1fetch next from iu_sc_cursor into snoendclose iu_sc_cursordeallocate iu_sc_cursorfetch next from iu_scSportno_cursor into sportnoendclose iu_scSportno_cursordeallocate iu_scSportno_cursorend-执行存储过程updateClscore，更新clscore 表-exec updateClscoregoGo测试代码及截图如下：552 计算班级总成绩存储过程存储过程：当sc 表名次有变动时，及时更新clscore表中的班级总成绩-当sc 表有更新时，及时更新clscore表中每个班的总分数-use sportsgoif exists(select * from sysobjects where name = 'updateClscore' and type = 'P')drop procedure updateClscoregocreate procedure updateClscore asbegindeclare classname varchar(20),srank int,srankTotal intdeclare cursor_class cursor forselect sclassname from stu group by sclassnameopen cursor_classfetch next from cursor_class into classnamewhile FETCH_STATUS = 0beginset srankTotal = 0declare cursor_srank cursor forselect srank from sc where sno in(select sno from stu where sclassname = classname)open cursor_srankfetch next from cursor_srank into srankwhile FETCH_STATUS = 0beginif srank < 8set srankTotal = srankTotal+(8-srank)else set srankTotal = srankTotal+0fetch next from cursor_srank into srankendupdate clscore set sclscore = srankTotal where sclassname = classnameclose cursor_srankdeallocate cursor_srankfetch next from cursor_class into classnameendclose cursor_classdeallocate cursor_class-执行存储过程，更新clscore表，进行班级排名-exec updateClscore_rankendGo测试代码及截图如下：553 班级排名存储过程存储过程：更新clscore表，对clscore表中的班级总成绩分别按学院和学校排名次-存储过程，更新clscore表，进行班级排名-use sportsif exists(select * from sysobjects where name='updateClscore_rank' and type = 'p')drop proc updateClscore_rankgocreate proc updateClscore_rankasbegindeclare dept varchar(20),class varchar(20),rank int-在学院排名次-declare cursor_dept cursor forselect sdeptname from clscore group by sdeptnameopen cursor_deptfetch next from cursor_dept into deptwhile FETCH_STATUS = 0beginset rank = 1declare cursor_class cursor forselect sclassname from clscore where sdeptname = dept order by sclscore descopen cursor_classfetch next from cursor_class into classwhile FETCH_STATUS = 0beginupdate clscore set sclrankindept = rank where sclassname = classset rank = rank + 1fetch next from cursor_class into classendclose cursor_classdeallocate cursor_classfetch next from cursor_dept into deptendclose cursor_deptdeallocate cursor_dept-在学校排名次-declare cursor_class cursor forselect sclassname from clscore order by sclscoreopen cursor_classfetch next from cursor_class into classset rank = 1while FETCH_STATUS = 0beginupdate clscoreset sclrankinsch = rank where sclassname = classset rank = rank + 1fetch next from cursor_class into classendclose cursor_classdeallocate cursor_classendGo554 项目排名表值函数-某项目排名函数 表值函数-输入：某个项目的编号-输出：此项目的排序后的运动员学号顺序表use sportsgoif exists(select * from sysobjects where name = 'scRankFun' and type = 'TF')drop function scRankFungocreate function scRankFun(sportno int)returns st table(sno int,sportno int)asbeginif sportno in (3,4,8,10)begininsert stselect sno,sportnofrom scwhere sportno = sportnoorder by score descendelsebegininsert stselect sno,sportnofrom scwhere sportno = sportnoorder by scoreendreturnendGo56 更新 clscore 表（触发器）触发条件：当运动员表 stu 有变动时，触发功能：根据运动员表 stu 中的信息，更新 clscore 表中的学院名和班级名use sportsgoif exists(select * from sysobjects where name = 'trClscoreSTU' and type = 'TR')drop trigger trClscoreSTUgo-当stu 表有变动时，及时更新create trigger trClscoreSTUon stu after insert,update,deleteasbegindeclare sdeptname varchar(20),sclassname varchar(20)declare stuSclassname_cursor cursor forselect sdept,sclassname from stuopen stuSclassname_cursorfetch next from stuSclassname_cursor into sdeptname,sclassnamewhile fetch_status = 0beginif not exists(select * from clscore where sclassname = sclassname)insert into clscore(sdeptname,sclassname) values(sdeptname,sclassname)fetch next from stuSclassname_cursor into sdeptname,sclassnameendclose stuSclassname_cursordeallocate stuSclassname_cursorendgogo57 成绩表按照项目初始名次use sports-声明变量godeclare sno int,sportno int,countsp intset sportno = 1select countsp=COUNT(*)from spwhile sportno <= countsp -所有项目均要排名begindeclare rank intset rank = 1-声明游标declare sc_cursor CURSOR forselect sno from scRankFun(sportno)-打开游标open sc_cursor-提取第一行fetch next from sc_cursor into snowhile FETCH_STATUS = 0beginuse sportsupdate scset srank = rankwhere sno = snoset rank = rank+ 1fetch next from sc_cursor into snoendclose sc_cursordeallocate sc_cursorset sportno = sportno+ 1endGo58 查询某个项目的全部成绩和排名（表值函数）输入：项目号输出：此项目的所有运动员的学号，姓名，成绩，名次，班级，学院 -表值函数实现下面的查询功能-查询某个项目的全部成绩和排名use sportsif exists(select * from sysobjects where name = 'spFun' and type = 'TF')drop function spFungocreate function spFun(sportno int)returns st table(学号 int,姓名 varchar(20),成绩 varchar(20),名次 int,班级 varchar(20),学院 varchar(20)asbegininsert into stselect stu.sno,stu.sname,sc.score,sc.srank,stu.sclassname,stu.sdeptfrom stu right join sc on(stu.sno = sc.sno)where sc.sportno = sportnoorder by srankreturnendgo-测试程序select * from spFun(1)59 查询某个班的运动员成绩（表值函数）输入：班级名输出：此班级的所有运动员的学号，姓名，项目，成绩，名次 -查询某个班的运动员成绩use sportsif exists(select * from sysobjects where name = 'classFun' and type = 'TF')drop function classFungocreate function classFun(sclassname varchar(20)returns st table(学号 int,姓名 varchar(20),项目 varchar(20),成绩 varchar(20),名次 int)asbegininsert into stselect stu.sno,stu.sname,a.sportname,a.score,a.srankfrom stu right join(select sno,sportname,score,srank from sc left join sp on(sc.sportno=sp.sportno)aon(stu.sno=a.sno) where stu.sclassname = sclassnamereturnendgo-测试程序select * from classFun('数学二班')510 查询某个学生的所有成绩（表值函数）输入：类型和学号或运动号或姓名输出：此运动员的学号，姓名，项目，成绩，名次 -查询某个学生的所有成绩use sportsif exists(select * from sysobjects where name = 'stuFun' and type = 'TF')drop function stuFungocreate function stuFun(kind int,name varchar(20)returns st table(学号 int,姓名 varchar(20),项目 varchar(20),成绩 varchar(20),名次 int)asbegindeclare sno intif kind=1 set sno = cast(name as int)else if kind=2 select sno = sno from stu where sname = nameelse select sno = sno from stu where spno = nameinsert into stselect a.sno,stu.sname,a.sportname,a.score,a.srankfrom (select sno,score,srank,sportname from sc left join sp on(sc.sportno=sp.sportno) aright join stu on(a.sno=stu.sno)where stu.sno=snoreturnendgo-测试程序select * from stuFun(1,'10103')511 查询某个学生的某个项目成绩（表值函数）输入：学号，运动号输出：此运动员的学号，姓名，项目，成绩，名次 -查询某个学生的某个项目成绩use sportsif exists(select * from sysobjects where name = 'stuspFun' and type = 'TF')drop function stuspFungocreate function stuspFun(sno int,sportno int)returns st table(学号 int,姓名 varchar(20),项目 varchar(20),成绩 varchar(20),名次 int)asbegininsert into stselect a.sno,stu.sname,a.sportname,a.score,a.srankfrom (select sno,score,srank,sportname,sc.sportno from sc left join sp on(sc.sportno=sp.sportno) aright join stu on(a.sno=stu.sno)where stu.sno=sno and a.sportno=sportnoreturnendgo-测试程序select * from stuspFun(10103,1)512 查询某个学院的运动员成绩（表值函数）输入：学院名输出：此学院所有运动员的学号，姓名，项目，成绩，名次，班级-查询某个学院的运动员成绩use sportsif exists(select * from sysobjects where name = 'sdeptFun' and type = 'TF')drop function sdeptFungocreate function sdeptFun(sdeptname varchar(20)returns st table(学号 int,姓名 varchar(20),项目 varchar(20),成绩 varchar