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

数据库原理与应用课程设计说明书题目：运动会成绩管理系统学院：班级：完成人：组号：姓名：XXX 学号：XXXX 成绩：姓名：XXX 学号：XXXX 成绩：指导教师：山东科技大学2014年6月27日课程设计任务书一、课程设计题目：运动会成绩管理系统二、课程设计应解决的主要问题：（按实现的系统功能）（1）运动项目、运动员、运动成绩以及班级名次的记录存储（2）添加修改和删除项目表、运动员表和成绩表信息（3）自动生成班级名次表（触发器）（4）运动员表有改动时，自动更新班级名次表（触发器）（5）成绩表有改动时自动对成绩表中记录按项目排名次（触发器）（6）成绩表自动排名自动更新班级名次表中班级总分（存储过程）（7）更新班级总分之后自动对班级排名(学院名次，学校名次)(存储过程) （8）对成绩表进行初次排名功能（9）通过项目号获得相应项目的排名表（表值函数）（10）通过学号或姓名或运动号获得某学生运动成绩表（表值函数）（11）通过班级名称获得某班所有运动员运动成绩表（表值函数）（12）通过学院名称获得学院所有运动员运动成绩表（表值函数）（13）获得全校所有运动员的运动成绩表（表值函数）（14）获得全校所有班级总分数以及在学院排名和早全校排名（表值函数）三、任务发出日期：2014-4-25 课程设计完成日期：2014-6-27小组分工说明小组编号题目：运动会成绩管理系统小组分工情况：XXX：参与数据库的讨论和设计；搜集整理数据、录入数据；设计了具体的逻辑结构，绘制E-R图；创建数据库以及项目表、运动员表、成绩表和班级名次表；实现了表之间的关联，创建必要的默认值、规则；创建了相应的索引；创建了班级名次表的视图。

XXX：参与数据库的讨论和设计；创建某项目名次表值函数；创建触发器实现成绩表的按项目自动排名功能；创建存储过程实现班级名次表的班级总成绩的更新功能；创建存储过程实现班级名次表的排名功能；创建触发器实现运动员表改动时自动更新班级名次表的功能；创建多个表值函数用于实现不同的查询功能：项目查询函数，个人成绩查询函数，班级成绩查询函数，学院成绩查询函数，全校成绩查询函数，全校班级名次查询函数。

整个数据库部分的设计共创建了三个触发器，四个存储过程以及七个表值函数。

另外还设计了本运动会成绩管理系统的界面部分，以及文档编辑工作。

组长签字：年月日指导教师对课程设计的评价成绩：指导教师签字：年月日目录1 需求分析本系统是用于管理运动会成绩的系统，要实现的功能就是运动会成绩的增加，修改和删除，同时还应该具有运动员和运动项目的增加修改和删除功能。

（1）本系统面向用户有两种：成绩管理员、学生运动员。

（2）管理员功能介绍管理员正确登陆后，可以增加、删除、修改、查询运动员信息，比赛项目以及比赛成绩。

（3）学生运动员主要功能介绍学生登录后，可以查询（比赛成绩、运动动员信息、运动项目）。

1．1 数据事实数据实例1．2 主要用户视图用户 需求成绩管理员 登录（正确输入用户名及密码登录）增加、删除、修改、查询运动员信息，比赛项目以及比赛成绩 学生登录（默认用户名）查询（比赛成绩、运动动员信息、运动项目）2 概念结构设计管理员登入成绩查询基本信息查询 信息录入查询与修改查询与修改查询与修改项目表 成绩表运动员表个人成绩查询 项目成绩查询班级成绩查询 全校成绩查询 全校名次查询学院成绩查询 添加学生信息比赛成绩录入添加比赛项目学生登录成绩查询基本信息查询查询查询图系统边界2．1 局部ER图图1 项目表图2 学生表图3 成绩表图4 班级名次表2．2 全局ER图3 逻辑结构设计关系模式如下：班级排名（学院、班级、总成绩、在学院名次、在校名次）4 物理结构设计学生表项目表项目成绩表班级名次表5 主要功能详细设计5．1 创建数据库CREATE DATABASE sportsON (NAME=运动会数据,FILENAME='D:\SQL\',SIZE= 10MB,MAXSIZE= 100MB)LOG ON (NAME=运动会数据日志,FILENAME='D:\SQL\',SIZE= 5MB,MAXSIZE= 25MB)5．2 创建数据表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,sportno1int foreign key references sp(sportno)not null,sportno2int 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 intprimary key (sno,sportno))4、创建名次表-------------记录全校所有班级的得分以及在学院的名次和在全校的名次create table clscore(sdeptname varchar(20),--学院名sclassname varchar(20)primary key,--班级名称sclscore int,--班级成绩sclrankindept int,--班级在学院中的名次sclrankinsch int--班级在学校中的名次)5．3 添加数据（仅以一条为例）1、添加运动项目表记录use sportsinsert 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,'')5．4 数据库完整性1、绑定列上默认值use sportsgocreate default ssex as'男'goexec sp_bindefault'ssex',''go2、绑定规则create rule rule_sportno as@c1between 1 and 12gocreate rule rule_sno as@c1between 10101 and 40340goexec sp_bindrule'rule_sportno',''goexec sp_bindrule'rule_sno',''go5．5 成绩表按成绩自动排名（触发器，函数，存储过程）功能简介：当sc 表有成绩插入删除或者修改时就触发，实现对sc表中单项目名次nrank属性的及时更新，更新完之后，调用存储过程，实现对clscore表中班级成绩和班级排名的更新。

5．5．1 更新项目名次触发器--触发器----及时更新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@sportno0int,@sno0int,@sportno1int,@sportno2intselect@sno0=sno,@sportno0=sportno from insertedselect@sportno1=sportno1,@sportno2=sportno2from stu where sno=@sno0if@sportno0not 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_STA TUS= 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测试代码及截图如下：5．5．2 计算班级总成绩存储过程存储过程：当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_STA TUS= 0beginif@srank< 8set@srankTotal=@srankTotal+(8-@srank)elseset@srankTotal=@srankTotal+0fetch next from cursor_srank into@srankendupdate clscoreset sclscore=@srankTotal where sclassname=@classnameclose cursor_srankdeallocate cursor_srankfetch next from cursor_class into@classnameendclose cursor_classdeallocate cursor_class------------执行存储过程，更新clscore表，进行班级排名---------------exec updateClscore_rankendGo测试代码及截图如下：5．5．3 班级排名存储过程存储过程：更新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 desc open cursor_classfetch next from cursor_class into@classwhile@@FETCH_STA TUS= 0beginupdate clscoreset 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_classendGo5．5．4 项目排名表值函数------------------------------某项目排名函数表值函数--输入：某个项目的编号--输出：此项目的排序后的运动员学号顺序表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 scoreendreturnendGo5．6 更新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,@sclassname endclose stuSclassname_cursordeallocate stuSclassname_cursorendgogo5．7 成绩表按照项目初始名次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+ 1endGo5．8 查询某个项目的全部成绩和排名（表值函数）输入：项目号输出：此项目的所有运动员的学号，姓名，成绩，名次，班级，学院----------------表值函数实现下面的查询功能--------------------查询某个项目的全部成绩和排名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,,,,,from stu right join sc on=where=@sportnoorder by srankreturnendgo---测试程序select*from spFun(1)5．9 查询某个班的运动员成绩（表值函数）输入：班级名输出：此班级的所有运动员的学号，姓名，项目，成绩，名次--查询某个班的运动员成绩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,,,,from stu right join(select sno,sportname,score,srank from sc left join sp on=)aon=where=@sclassnamereturnendgo----测试程序select*from classFun('数学二班')5．10 查询某个学生的所有成绩（表值函数）输入：类型和学号或运动号或姓名输出：此运动员的学号，姓名，项目，成绩，名次--查询某个学生的所有成绩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,,,,from (select sno,score,srank,sportname from sc left join sp on=)aright join stu on=where=@snoreturnendgo-----测试程序select*from stuFun(1,'10103')5．11 查询某个学生的某个项目成绩（表值函数）输入：学号，运动号输出：此运动员的学号，姓名，项目，成绩，名次--查询某个学生的某个项目成绩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,,,,from (select sno,score,srank,sportname,from sc left join sp on=)aright join stu on=where=@sno and=@sportnoreturnendgo-----测试程序select*from stuspFun(10103,1)5．12 查询某个学院的运动员成绩（表值函数）输入：学院名输出：此学院所有运动员的学号，姓名，项目，成绩，名次，班级--查询某个学院的运动员成绩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(20),名次int,班级varchar(20)) asbegininsert into@stselect,,,,,from stu right join(select sno,sportname,score,srank from sc left join sp on=)aon=where=@sdeptnameorder byreturnendgo-----测试程序select*from sdeptFun('计算机')5．13 查询全校运动员成绩（表值函数）输入：无输入输出：学校全体运动员的学号，姓名，项目，成绩，名次，班级，学院--查询全校运动员成绩use sportsif exists(select*from sysobjects where name='schFun'and type='TF')drop function schFungocreate function schFun()returns@st table(学号int,姓名varchar(20),项目varchar(20),成绩varchar(20),名次int,班级varchar(20),学院varchar(20))asbegininsert into@stselect,,,,,,from stu right join(select sno,sportname,score,srank from sc left join sp on=)aon=order byreturnendgo------测试程序select*from schFun()5．14 通过学号和运动号查询成绩和名次（存储过程）输入：学号，运动号输出：姓名，成绩，名次use sportsgoif exists(select*from sysobjects where name='stuScore'and type='P')drop procedure stuScoregocreate procedure stuScore(@sno int,@sportno int,@sname varchar(10)output,@stuScore varchar(10)output,@stuRank int output)asselect@sname=sname from stu where sno=@snoselect@stuScore=score,@stuRank=srank from scwhere sno=@sno and sportno=@sportnogoGo5．15 创建班级成绩和名次视图use sportsif exists(select*from sysobjects where name='view_clscore'and type='v')drop view view_clscoregocreate view view_clscore asselect sdeptname as'学院',sclassname as'班级',sclscore as'总分',sclrankindept as'学院名次',sclrankinsch as'学校名次'from clscoreGo------测试程序select*from view_clscore5．16 创建索引use sportsif exists(select*from sysindexes where name='IDX_sc')drop indexgocreate index IDX_sc on sc(sportno,srank)Go6 界面设计主要过程为了使用户的使用更加方便，同时也为了增加对数据库的保护，尽量减少用户可以直接更改数据库的机会，所以加入了运动会成绩管理系统的界面部分。