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

数据库原理与应用课程设计说明书题目：运动会成绩管理系统学院：班级：完成人：组号：姓名：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图运动号性别项目二姓名学院学生表学号项目一班级项目表项目号项目名称成绩表学号项目号成绩名次班级名次表班级名班级得分在学院排名在校排名nm学生表项目表成绩表班级名次表选择排名3逻辑结构设计关系模式如下：运动员（学号、姓名、性别、学院、班级、运动号、项目一、项目二）运动项目（项目号、项目名称）成绩（学号、项目号、成绩、名次）班级排名（学院、班级、总成绩、在学院名次、在校名次）4物理结构设计学生表列名数据类型允许空说明SNOInt否学生编号SNameVarchar(50)否姓名SDeptVarchar(50)是系别SClassNOVarchar(50)是班级SportsNOInt否运动编号项目表列名数据类型允许空说明SportsNOInt否项目编号SportsNameVarchar(50)否项目名称项目成绩表列名数据类型允许空说明SNOInt否学生编号SportsNOInt否项目编号ScoreVarchar（50）否成绩Srankint否名次班级名次表列名数据类型允许空说明sdeptnameVarchar（20）是学院sclassnameVarchar（20）否班级sclscoreint是总分sclrankindeptint是学院名次sclrankinschint是学校名次5主要功能详细设计5．1创建数据库CREATEDATABASEsportsON(NAME=运动会数据,FILENAME='D:\SQL\',SIZE=10MB,MAXSIZE=100MB)LOGON(NAME=运动会数据日志,FILENAME='D:\SQL\',SIZE=5MB,MAXSIZE=25MB)5．2创建数据表1、创建项目表usesportscreatetablesp(sportnointNOTNULLPRIMARYKEY,sportnamevarchar(30)notnull)2、创建学生表createtablestu(snointNOTNULLPRIMARYKEY,snamevarchar(10)notnull,sdeptvarchar(20),sclassnamevarchar(20),ssexvarchar(2),spnointnotnullunique,sportno1intforeignkeyreferencessp(sportno)notnull,sportno2intforeignkeyreferencessp(sportno))3、创建成绩表------------全体运动员的各个项目的成绩表createtablesc(snointFOREIGNKEYreferencesstu(sno),sportnointFOREIGNKEYreferencessp(sportno),scorevarchar(10),srankintprimarykey(sno,sportno))4、创建名次表-------------记录全校所有班级的得分以及在学院的名次和在全校的名次createtableclscore(sdeptnamevarchar(20),--学院名sclassnamevarchar(20)primarykey,--班级名称sclscoreint,--班级成绩sclrankindeptint,--班级在学院中的名次sclrankinschint--班级在学校中的名次)5．3添加数据（仅以一条为例）1、添加运动项目表记录usesportsinsertintospvalues(1,'男子一百'),(2,'男子二百')2、添加学生表记录usesportsinsertintostuvalues(10103,'赵浩','数学','数学一班','男',10001,1,3),(10105,'钱浩','数学','数学一班','男',10002,2,4)3、添加比赛成绩表记录usesportsinsertintosc(sno,sportno,score)values(10103,1,'')5．4数据库完整性1、绑定列上默认值usesportsgocreatedefaultssexas'男'goexecsp_bindefault'ssex',''go2、绑定规则createrulerule_sportnoas@c1between1and12gocreaterulerule_snoas@c1between10101and40340goexecsp_bindrule'rule_sportno',''goexecsp_bindrule'rule_sno',''go5．5成绩表按成绩自动排名（触发器，函数，存储过程）功能简介：当sc表有成绩插入删除或者修改时就触发，实现对sc表中单项目名次nrank属性的及时更新，更新完之后，调用存储过程，实现对clscore表中班级成绩和班级排名的更新。5．5．1更新项目名次触发器--触发器----及时更新sc表--当有成绩插入删除或者修改sc表时就触发--实现对sc表中单项目名次nrank属性的及时更新usesportsgo----------------------------插入更新与删除操作sc表ifexists(select*fromsysobjectswherename='trSC'andtype='TR')droptriggertrSCgocreatetriggertrSConscafterinsert,update,deleteasdeclare@sportno0int,@sno0int,@sportno1int,@sportno2intselect@sno0=sno,@sportno0=sportnofrominsertedselect@sportno1=sportno1,@sportno2=sportno2fromstuwheresno=@sno0if@sportno0notin(@sportno1,@sportno2)--if@sportno0!=@sportno1and@sportno0!=@sportno2beginprint'操作有误，此记录与运动员表不一致，请先修改运动员表！'rollbackendelsebegin--更新名次--声明变量declare@snoint,@sportnoint,@rankintdeclareiu_scSportno_cursorCURSORforselectsportnofromspopeniu_scSportno_cursorfetchnextfromiu_scSportno_cursorinto@sportnowhile@@fetch_status=0beginset@rank=1--声明游标declareiu_sc_cursorCURSORforselectsnofromscRankFun(@sportno)--打开游标openiu_sc_cursor--提取第一行fetchnextfromiu_sc_cursorinto@snowhile@@FETCH_STATUS=0beginupdatescsetsrank=@rankwheresno=@snoandsportno=@sportnoset@rank=@rank+1fetchnextfromiu_sc_cursorinto@snoendcloseiu_sc_cursordeallocateiu_sc_cursorfetchnextfromiu_scSportno_cursorinto@sportnoendcloseiu_scSportno_cursordeallocateiu_scSportno_cursorend-----------执行存储过程updateClscore，更新clscore表-----------execupdateClscoregoGo测试代码及截图如下：5．5．2计算班级总成绩存储过程存储过程：当sc表名次有变动时，及时更新clscore表中的班级总成绩---------------当sc表有更新时，及时更新clscore表中每个班的总分数----------usesportsgoifexists(select*fromsysobjectswherename='updateClscore'andtype='P')dropprocedureupdateClscoregocreateprocedureupdateClscoreasbegindeclare@classnamevarchar(