數(shù)據(jù)庫課程設計-運動會成績管理系統(tǒng)

1、數(shù)據(jù)庫原理與應用課程設計說 明 書題 目： 運動會成績管理系統(tǒng) 學 院： 班 級： 完成人：組號： 姓名：XXX 學號：XXXX 成績： 姓名：XXX 學號：XXXX 成績： 指導教師： 山 東 科 技 大 學2014年6月27日課 程 設 計 任 務 書一、課程設計題目： 運動會成績管理系統(tǒng) 二、課程設計應解決的主要問題：（按實現(xiàn)的系統(tǒng)功能）（1）運動項目、運動員、運動成績以及班級名次的記錄存儲（2）添加修改和刪除項目表、運動員表和成績表信息 （3）自動生成班級名次表（觸發(fā)器）（4）運動員表有改動時，自動更新班級名次表（觸發(fā)器）（5）成績表有改動時自動對成績表中記錄按項目排名次（觸發(fā)器） （

2、6）成績表自動排名自動更新班級名次表中班級總分（存儲過程）（7）更新班級總分之后自動對班級排名(學院名次，學校名次)(存儲過程)（8）對成績表進行初次排名功能（9）通過項目號獲得相應項目的排名表（表值函數(shù)）（10）通過學號或姓名或運動號獲得某學生運動成績表（表值函數(shù)） （11）通過班級名稱獲得某班所有運動員運動成績表（表值函數(shù)） （12）通過學院名稱獲得學院所有運動員運動成績表（表值函數(shù)） （13）獲得全校所有運動員的運動成績表（表值函數(shù)） （14）獲得全校所有班級總分數(shù)以及在學院排名和早全校排名（表值函數(shù)） 3、 任務發(fā)出日期： 2014-4-25 課程設計完成日期： 2014-6-27 小

3、組分工說明小組編號 題 目： 運動會成績管理系統(tǒng) 小組分工情況：XXX：參與數(shù)據(jù)庫的討論和設計；搜集整理數(shù)據(jù)、錄入數(shù)據(jù)；設計了具體的邏輯結構，繪制E-R圖；創(chuàng)建數(shù)據(jù)庫以及項目表、運動員表、成績表和班級名次表；實現(xiàn)了表之間的關聯(lián)，創(chuàng)建必要的默認值、規(guī)則；創(chuàng)建了相應的索引；創(chuàng)建了班級名次表的視圖。XXX：參與數(shù)據(jù)庫的討論和設計；創(chuàng)建某項目名次表值函數(shù)；創(chuàng)建觸發(fā)器實現(xiàn)成績表的按項目自動排名功能；創(chuàng)建存儲過程實現(xiàn)班級名次表的班級總成績的更新功能；創(chuàng)建存儲過程實現(xiàn)班級名次表的排名功能；創(chuàng)建觸發(fā)器實現(xiàn)運動員表改動時自動更新班級名次表的功能；創(chuàng)建多個表值函數(shù)用于實現(xiàn)不同的查詢功能：項目查詢函數(shù)，個人成績查詢

4、函數(shù)，班級成績查詢函數(shù)，學院成績查詢函數(shù)，全校成績查詢函數(shù)，全校班級名次查詢函數(shù)。整個數(shù)據(jù)庫部分的設計共創(chuàng)建了三個觸發(fā)器，四個存儲過程以及七個表值函數(shù)。另外還設計了本運動會成績管理系統(tǒng)的界面部分，以及文檔編輯工作。組長簽字： 年 月 日指導教師對課程設計的評價成績： 指導教師簽字： 年 月 日 目 錄1 需求分析111 數(shù)據(jù)事實112 主要用戶視圖22 概念結構設計221 局部ER圖322 全局ER圖33 邏輯結構設計44 物理結構設計45 主要功能詳細設計551 創(chuàng)建數(shù)據(jù)庫552 創(chuàng)建數(shù)據(jù)表553 添加數(shù)據(jù)（僅以一條為例）654 數(shù)據(jù)庫完整性755 成績表按成績自動排名（觸發(fā)器，函數(shù)，存儲過

5、程）756 更新 clscore 表（觸發(fā)器）1257 成績表按照項目初始名次1358 查詢某個項目的全部成績和排名（表值函數(shù)）1459 查詢某個班的運動員成績（表值函數(shù)）14510 查詢某個學生的所有成績（表值函數(shù)）15511 查詢某個學生的某個項目成績（表值函數(shù)）16512 查詢某個學院的運動員成績（表值函數(shù)）16513 查詢全校運動員成績（表值函數(shù)）17514 通過學號和運動號查詢成績和名次（存儲過程）17515 創(chuàng)建班級成績和名次視圖18516 創(chuàng)建索引186 界面設計主要過程186.1 界面總體設計196.2 登錄界面設計196.3 成績管理員界面設計206.4 運動員查詢界面設計2

6、61 需求分析本系統(tǒng)是用于管理運動會成績的系統(tǒng)，要實現(xiàn)的功能就是運動會成績的增加，修改和刪除，同時還應該具有運動員和運動項目的增加修改和刪除功能。（1） 本系統(tǒng)面向用戶有兩種：成績管理員、學生運動員。（2） 管理員功能介紹管理員正確登陸后，可以增加、刪除、修改、查詢運動員信息，比賽項目以及比賽成績。（3） 學生運動員主要功能介紹學生登錄后，可以查詢（比賽成績、運動動員信息、運動項目）。11 數(shù)據(jù)事實數(shù)據(jù)實例 12 主要用戶視圖用戶需求成績管理員登錄（正確輸入用戶名及密碼登錄）增加、刪除、修改、查詢運動員信息，比賽項目以及比賽成績學生登錄（默認用戶名）查詢（比賽成績、運動動員信息、運動項目）2

7、概念結構設計學生登錄管理員登入學生登錄查詢查詢查詢查詢查詢與修改查詢與修改查詢與修改基本信息查詢成績查詢基本信息查詢成績查詢基本信息查詢成績查詢信息錄入比賽成績錄入添加比賽項目添加學生信息成績表項目表運動員表全校名次查詢全校成績查詢學院成績查詢班級成績查詢項目成績查詢個人成績查詢圖2.1系統(tǒng)邊界21 局部ER圖運動號性別項目二姓名學院學生表學號項目一班級項目表項目號項目名稱圖1 項目表圖2 學生表成績表學號項目號成績名次班級名次表班級名班級得分在學院排名在校排名圖3 成績表圖4 班級名次表mn22 全局ER圖學生表項目表成績表班級名次表選擇排名3 邏輯結構設計關系模式如下：運動員（學號、姓名、

8、性別、學院、班級、運動號、項目一、項目二）運動項目（項目號、項目名稱）成績（學號、項目號、成績、名次）班級排名（學院、班級、總成績、在學院名次、在校名次）4 物理結構設計 學生表列名數(shù)據(jù)類型允許空說明SNO Int否學生編號SNameVarchar(50)否姓名SDeptVarchar(50)是系別SClassNOVarchar(50)是班級SportsNO Int否運動編號項目表列名數(shù)據(jù)類型允許空說明SportsNOInt否項目編號SportsNameVarchar(50)否項目名稱項目成績表列名數(shù)據(jù)類型允許空說明SNOInt否學生編號SportsNOInt否項目編號ScoreVarchar

9、（50）否成績Srankint否名次班級名次表列名數(shù)據(jù)類型允許空說明sdeptnameVarchar（20）是學院sclassnameVarchar（20）否班級sclscoreint是總分sclrankindeptint是 學院名次sclrankinschint是 學校名次5 主要功能詳細設計51 創(chuàng)建數(shù)據(jù)庫CREATE DATABASE sportsON ( NAME = 運動會數(shù)據(jù), FILENAME = 'D:SQLsports.MDF', SIZE = 10MB, MAXSIZE = 100MB )LOG ON ( NAME = 運動會數(shù)據(jù)日志, FILENAME =

10、 'D:SQLsports_Log.LDF', SIZE = 5MB, MAXSIZE = 25MB)52 創(chuàng)建數(shù)據(jù)表1、 創(chuàng)建項目表use sportscreate table sp(sportno int NOT NULL PRIMARY KEY, sportname varchar(30) not null)2、 創(chuàng)建學生表create table stu(sno int NOT NULL PRIMARY KEY, sname varchar(10)not null, sdept varchar(20), sclassname varchar(20), ssex varc

11、har(2), spno int not null unique, sportno1 int foreign key references sp(sportno) not null, sportno2 int foreign key references sp(sportno) ) 3、 創(chuàng)建成績表 -全體運動員的各個項目的成績表create table sc(sno int FOREIGN KEY references stu(sno), sportno int FOREIGN KEY references sp(sportno), score varchar(10), srank int

12、primary key (sno,sportno)4、 創(chuàng)建名次表-記錄全校所有班級的得分以及在學院的名次和在全校的名次create table clscore(sdeptname varchar(20),-學院名 sclassname varchar(20) primary key,-班級名稱 sclscore int,-班級成績 sclrankindept int,-班級在學院中的名次 sclrankinsch int-班級在學校中的名次)53 添加數(shù)據(jù)（僅以一條為例）1、 添加運動項目表記錄use sports insert into sp values (1,'男子一百'

13、;),(2,'男子二百')2、 添加學生表記錄use sportsinsert into stu values(10103,'趙浩','數(shù)學','數(shù)學一班','男',10001,1,3),(10105,'錢浩','數(shù)學','數(shù)學一班','男',10002,2,4)3、 添加比賽成績表記錄use sportsinsert into sc (sno,sportno,score) values(10103,1,'12.2')54 數(shù)據(jù)庫完整性

14、1、 綁定列上默認值use sportsgocreate default ssex as '男'goexec sp_bindefault 'ssex','stu.ssex'go2、 綁定規(guī)則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

15、'rule_sno','stu.sno' go55 成績表按成績自動排名（觸發(fā)器，函數(shù)，存儲過程）功能簡介：當sc 表有成績插入刪除或者修改時就觸發(fā)，實現(xiàn)對sc表中單項目名次nrank屬性的及時更新，更新完之后，調用存儲過程，實現(xiàn)對clscore表中班級成績和班級排名的更新。551 更新項目名次觸發(fā)器-觸發(fā)器-及時更新 sc 表-當有成績插入刪除或者修改sc 表時就觸發(fā)-實現(xiàn)對sc表中單項目名次nrank屬性的及時更新use sportsgo-插入更新與刪除操作sc 表if exists(select * from sysobjects where name =

16、 '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(

17、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

18、 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

19、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-執(zhí)行存儲過程updateClscore，更新clscore 表-exec updateClscoregoGo測試代碼及截圖如下：552 計算班級總成績存儲過程存儲過程：當sc 表名次有變動時，及時更新clscore表中的班級總成績-當sc

20、表有更新時，及時更新clscore表中每個班的總分數(shù)-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 sclass

21、name 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 in

22、to 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_clas

23、s into classnameendclose cursor_classdeallocate cursor_class-執(zhí)行存儲過程，更新clscore表，進行班級排名-exec updateClscore_rankendGo測試代碼及截圖如下：553 班級排名存儲過程存儲過程：更新clscore表，對clscore表中的班級總成績分別按學院和學校排名次-存儲過程，更新clscore表，進行班級排名-use sportsif exists(select * from sysobjects where name='updateClscore_rank' and type = &

24、#39;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 r

25、ank = 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 curs

26、or_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 = 1whi

27、le FETCH_STATUS = 0beginupdate clscoreset sclrankinsch = rank where sclassname = classset rank = rank + 1fetch next from cursor_class into classendclose cursor_classdeallocate cursor_classendGo554 項目排名表值函數(shù)-某項目排名函數(shù) 表值函數(shù)-輸入：某個項目的編號-輸出：此項目的排序后的運動員學號順序表use sportsgoif exists(select * from sysobjects wher

28、e 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,spo

29、rtnofrom scwhere sportno = sportnoorder by scoreendreturnendGo56 更新 clscore 表（觸發(fā)器）觸發(fā)條件：當運動員表 stu 有變動時，觸發(fā)功能：根據(jù)運動員表 stu 中的信息，更新 clscore 表中的學院名和班級名use sportsgoif exists(select * from sysobjects where name = 'trClscoreSTU' and type = 'TR')drop trigger trClscoreSTUgo-當stu 表有變動時，及時更新create

30、 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

31、 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-聲明變量godeclar

32、e 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 = 0begi

33、nuse sportsupdate scset srank = rankwhere sno = snoset rank = rank+ 1fetch next from sc_cursor into snoendclose sc_cursordeallocate sc_cursorset sportno = sportno+ 1endGo58 查詢某個項目的全部成績和排名（表值函數(shù)）輸入：項目號輸出：此項目的所有運動員的學號，姓名，成績，名次，班級，學院 -表值函數(shù)實現(xiàn)下面的查詢功能-查詢某個項目的全部成績和排名use sportsif exists(select * from sysobje

34、cts 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 r

35、ight join sc on(stu.sno = sc.sno)where sc.sportno = sportnoorder by srankreturnendgo-測試程序select * from spFun(1)59 查詢某個班的運動員成績（表值函數(shù)）輸入：班級名輸出：此班級的所有運動員的學號，姓名，項目，成績，名次 -查詢某個班的運動員成績use sportsif exists(select * from sysobjects where name = 'classFun' and type = 'TF')drop function classFun

36、gocreate 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

37、(stu.sno=a.sno) where stu.sclassname = sclassnamereturnendgo-測試程序select * from classFun('數(shù)學二班')510 查詢某個學生的所有成績（表值函數(shù)）輸入：類型和學號或運動號或姓名輸出：此運動員的學號，姓名，項目，成績，名次 -查詢某個學生的所有成績use sportsif exists(select * from sysobjects where name = 'stuFun' and type = 'TF')drop function stuFungocreat

38、e 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 sts

39、elect 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 查詢某個學生的某個項目成績（表值函數(shù)）輸入：學號，運動號輸出：此運動員的學號，姓名，項目，成績，名次 -查詢某個

40、學生的某個項目成績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

41、.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 查詢某個學院的運動員成績（表值函數(shù)）輸入：學院名輸出：此學院所有運動員的學號，姓名，項目，成績，名次，班級-

42、查詢某個學院的運動員成績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

43、 a.sno,stu.sname,a.sportname,a.score,a.srank,stu.sclassnamefrom stu right join (select sno,sportname,score,srank from sc left join sp on(sc.sportno=sp.sportno)a on(stu.sno = a.sno)where stu.sdept = sdeptnameorder by a.snoreturnendgo-測試程序select * from sdeptFun('計算機')513 查詢全校運動員成績（表值函數(shù)）輸入：無輸入輸

44、出：學校全體運動員的學號，姓名，項目，成績，名次，班級，學院 -查詢全校運動員成績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)asbeginin

45、sert into stselect a.sno,stu.sname,a.sportname,a.score,a.srank,stu.sclassname,stu.sdeptfrom stu right join (select sno,sportname,score,srank from sc left join sp on(sc.sportno=sp.sportno)a on(stu.sno = a.sno)order by a.snoreturnendgo-測試程序select * from schFun()514 通過學號和運動號查詢成績和名次（存儲過程）輸入：學號，運動號輸出：姓名，

46、成績，名次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 )as select sname = sname from stu where sno = sno

47、 select stuScore = score,stuRank = srank from scwhere sno = sno and sportno = sportnogoGo515 創(chuàng)建班級成績和名次視圖use sportsif exists(select*from sysobjects where name='view_clscore' and type='v')drop view view_clscoregocreate view view_clscore as select sdeptname as '學院',sclassname as

48、 '班級',sclscore as '總分',sclrankindept as '學院名次',sclrankinsch as '學校名次'from clscoreGo-測試程序select * from view_clscore516 創(chuàng)建索引use sportsif exists(select*from sysindexes where name='IDX_sc')drop index sc.IDX_scgocreate index IDX_sc on sc(sportno,srank)Go6 界面設計主要過程為

49、了使用戶的使用更加方便，同時也為了增加對數(shù)據(jù)庫的保護，盡量減少用戶可以直接更改數(shù)據(jù)庫的機會，所以加入了運動會成績管理系統(tǒng)的界面部分。6.1 界面總體設計界面所面向用戶：由于本系統(tǒng)所面對的不同用戶有成績管理員和學生，所以應該開發(fā)出面向著兩種用戶的使用界面。針對不同的用戶，他們所需要的操作也是不同的，所以下面需要根據(jù)不同的用戶身份登錄，設計不同的界面，實現(xiàn)不同的功能。成績管理員具有最高的管理權限，是成績信息的管理者。管理員具有所有運動項目的管理，所有學生運動員的管理，同時也應該具有對所有運動成績記錄的管理。學生運動員的主要功能就是查詢功能，可以查詢某個運動員，班，項目，學院或者整個學校的比賽成績信息和名次情況。6.2 登錄界面設計登錄界面應該是面向上述兩種不同用戶開發(fā)的，所以應該有區(qū)分他們的身份的按鈕，以及用戶的登錄信息輸入框，依此來核對用戶的登錄信息是否正確，判斷是否讓用戶進入系統(tǒng)。核對登錄信息的主要代碼如下： private void button1_Click(object sender, EventArg