1、課程名稱：數據庫系統原理與應用實驗項目名稱：校運動會數據庫設計數據庫總體設計 目 錄1需求分析31.1 編寫目的31.2 背景31.3 定義31.4 目標31.5 需求分析41.5.1 系統總體需求41.5.2 軟硬件平臺設計52 概念設計52.1 實體學院與運動員之間的聯系52.2 運動員、個人項目和個人項目獎勵之間的聯系62.3 學院、集體項目和集體項目獎勵之間的聯系62.4 賽事類別與項目之間的聯系72.5 裁判、項目、職務之間的聯系：72.6 總E-R圖73 邏輯設計84 物理設計105 觸發器11(1) 建立觸發器T1(保證一名運動員最多只能報兩項個人項目項目)11(2) 建立觸發器

2、T2(保證一名運動員在同一時間內只能參加一項項目):11(3) 建立觸發器T3(保證在同一時間內只能舉行一項集體項目):12(4) 建立觸發器T4(保證更新個人項目里運動員的名次時,更新他所在的學院的總分:12(5) 建立觸發器T5(保證一名裁判在同一時間內只能負責一項項目的比賽):13(6) 建立觸發器T6(保證在同一時間內只能進行一項徑賽項目):146 存儲過程14(1) 建立存儲過程P1, 輸入一個學院名稱,即可查出其總分:14(2) 建立存儲過程P2,利用存儲過程查找姓“韓”裁判的編號，性別，負責賽事的時間，項目號等15(3) 建立存儲過程CountsCollageScore,輸入一個

3、學院名稱， 統計該學院的總分，并自動更新:15(4) 建立存儲過程Sorts，根據每一個學院的得分自動進行排名，并自動更新：167 數據庫腳本171需求分析1.1 編寫目的使用該文檔的使用成員為實現SGS系統的開發人員以及學校的運動會具體組織管理人員。小組各成員所做的工作： * 分析設計，概念設計，邏輯設計* 邏輯設計，物理設計，表項設計* E-R圖設計，邏輯設計，數據處理* E-R圖繪制，邏輯設計，數據處理* 表項分析，數據處理1.2 背景項目名稱：學校運動會管理系統（School Gymkhana System簡稱SGS）。項目提出者：*。項目開發者：SGS項目開發小組。項目鑒定者：暫無。

4、項目開始時間：*-*-*。1.3 定義系統數據流程圖的一些定義1.4 目標當學校運動會管理人員使用了學校運動會管理系統SGS軟件后，可以由計算機完成：運動員報名的自動核對；比賽時各種人員信息的查詢、核對、更新、刪除。學校運動會管理系統SGS用戶分為兩類：普通操作員：只須具有初級的計算機操作能力，一般高中生以上經過稍稍培訓都可勝任，只是負責比賽進行時一些簡單數據的插入、更新、查詢。管理人員：這些人員應具備一定的數據庫知識，不過只要了解就行，這些人員負責對數據庫中重要數據及基本數據的更新，如對靜態表的重新定義，普通用戶的權限分配。1.5 需求分析 系統總體需求(l) 通過對運動會各個步驟、所需的各

5、項信息等的分析，我們規定：項目有個人項目、集體項目之分對于集體項目，我們并不關心具體參加比賽的運動員在每一項集體項目中，每個學院的參賽人數不可超過限制的參賽人數，且必須是該學院的學生一個項目可有多名運動員參加，但一名運動員最多只能參加兩項個人項目（用一個觸發器實現），且只能屬于一個學院一個項目可由多名裁判主持，每一個裁判在此項目中所擔任的職務不同，且有主、副裁判之分在同一時間內可同時舉行多項比賽，但是只能進行一項徑賽比賽（用一個觸發器實現）在同一時間內，一名裁判只能主持一項比賽、擔任一個職務（用一個觸發器實現）但在不同的時間內，一名裁判可主持不同的項目、擔任不同的職務一名運動員在同一時間內只能

6、參加一項比賽（用一個觸發器實現）在同一時間內，一個學院只能參加一項集體項目比賽（通過用一個觸發器限制在同一時間內只能舉行一項集體項目）在某一項目中，只有一個主裁判，其余都為副裁判一項個人項目可有多個同時破記錄者，一名運動員同時破多項記錄一項集體項目可同時有多個破記錄的學院，一個學院可同時破多項記錄。(2) 通過對運動會各方面分析，運動會中包含的實體有學院、運動員（不包含集體項目中的運動員）、項目（集體項目和個人項目）、裁判、職務、獎勵（集體項目獎勵和個人項目獎勵，名次為0表示破記錄）、賽事類別（田賽、徑賽、集體項目等）集體項目獎勵和個人項目獎勵不同。包含的數據項分別如下：學院編號，學院名稱，上

7、屆運動會名次，歷史最高名次，本次運動會總分，本次運動會名次等運動員編號，學號，姓名，性別，所屬學院，班級等項目編號，項目名稱，參賽者性別（男或女或不限）、每個學院限參賽人次，比賽時間，結束時間等裁判編號，姓名，性別等職務編號，職務名稱（如計時員、測量員、記錄員等），職務級別（主裁判和副裁判）等名次，獎勵分數，獎勵物品（如獎狀等），獎勵金額等賽事類別編號，賽事類別名稱等(3) 實體之間的聯系有：學院與運動員之間（1：M）運動員、個人項目與個人項目獎勵之間（1：M，1：1）學院、集體項目與集體項目獎勵之間（1：M，1：1）賽事類別與項目之間（1：M）裁判、項目與職務之間（M：N）(4)通過以上分析

8、，運動會數據庫包含的模塊有：輸入模塊主要是各種信息的輸入，如學院信息、運動員信息、項目信息、比賽信息、獎勵信息等輸出模塊主要是各種查詢結果的輸出等 軟硬件平臺設計(1) 軟件平臺： windowsXP操作系統、Sql-Server2000數據庫(2) 硬件平臺：普通微機2 概念設計2.1 實體學院與運動員之間的聯系相關說明：上述E-R圖表示一個學院有多名運動員參加個人項目，一名運動員只能屬于一個學院。2.2 運動員、個人項目和個人項目獎勵之間的聯系上述E-R圖表示一個項目可有多項不同的獎勵（如第一名、第二名等的獎勵不同）對于每一項獎勵可在不同的項目中出現；一名運動員最多只能有兩項個人項目獎勵（

9、此時p=02）,每一項獎勵可有多名運動員獲得；一名運動員最多能參加兩項個人項目（此時m=12），一項個人項目可有多名運動員參加。2.3 學院、集體項目和集體項目獎勵之間的聯系相關說明：上述E-R圖表示一個學院可獲得多項集體項目獎勵、參加多項集體項目，一項集體項目可有多個學院參賽、有多項不同的獎勵，一項集體項目獎勵可被多個學院獲得、可在多項集體項目中出現2.4 賽事類別與項目之間的聯系2.5 裁判、項目、職務之間的聯系：上述E-R圖表示一名裁判在不同的時間內可主持多項比賽、擔任多項職務，同一職務可被多名裁判擔任、可出現在多項比賽中，一項項目可由多名裁判共同主持、可有多個職務（如計時員、測量員、記

10、錄員等）2.6 總E-R圖將上述分E-R全部合并，并化簡的到如下的總E-R圖（為簡化畫圖工作，上述各實體中的屬性 未畫出，并把集體項目和個人項目合并為項目、集體項目獎勵和個人項目獎勵合并）：上述E-R圖中學院、項目和獎勵之間的聯系如上述中的E-R圖一樣，運動員、項目和獎勵之間表示的聯系如上述2中的E-R圖一樣。3 邏輯設計 本次設計的數據庫在SQL Server 2000上實現，將概念結構設計中的E-R圖轉換成SQL Sever 2000支持的關系數據模型后，結合上述分析得到如下數據表： （1）賽事類別表（match_sort）：主鍵為賽事類別號sort_no sort_nochar(3)no

11、t null primary key賽事類別號sort_namechar(10)not null類別名稱(2) 比賽項目表（match_item）：主鍵為項目編號，其中對每個學院限參賽人數設置了check約束，并且類別編號也作為外鍵約束，受到賽事類別表中賽事類別號的約束item_nochar(4)not nullprimary key項目編號item_namechar(20)not null項目名稱Item_sexchar(2)Not null項目性別sort_nochar(3)not null類別編號qtysmallintnot null每個學院限參賽人數start_timedatetime

12、Not null比賽開始時間finish_timedatetimeNull比賽結束時間(3) 裁判信息表(judge)：主鍵為裁判編號，并在性別屬性列上設置check約束（f表示女性，m代表男性） judge_nochar()Not nullPrimary key裁判編號judge_namechar(10)Not null姓名sexchar(2)Not null性別(4) 職務表(duty)：職務編號是主碼 duty_nochar(4)Not nullPrimary key職務編號duty_namechar(20)Not null職務名稱rankingchar(10)Null職務級別(5) 裁

13、判安排表(judge_scheme)：主碼為（裁判編號，項目編號），設置了兩個外鍵約束，外鍵項目編號item_no受到match_item表中item_no的約束，職務號duty_no受duty表中的duty_no約束judge_nochar()Not nullPrimary key裁判編號item_nochar(4)Not null項目編號duty_nochar(4)Null職務號isattendchar(1)Null是否到場(6) 學院信息表(collage)：主鍵是學院編號，并在其上設置check約束collage_nochar(4)Not nullprimary學院編號collage_

14、namechar(20)Not null學院名稱last_positiontinyintNull上屆運動會名次max_positiontinyintNull歷史最高名次total_scoreintNull本屆運動會總分positiontinyintNull本屆運動會名次(7) 運動員信息表(athlete)：運動員編號athlete_no是主鍵，并在學院編號屬性列上設置外鍵約束athlete_nochar(4)Not nullprimary運動員編號stu_nochar(6)Not null學號athlete_namechar(10)Not null姓名sexchar(2)Not null性別

15、collage_nochar(4)Not null學院編號(8) 個人項目比賽表(sig_match)：運動員編號，項目編號為主碼，外鍵有項目編號，運動員編號，名次分別受到表match_item，athlete及person_prize中相應屬性的約束athlete_nochar(4)Not nullPrimary key運動員編號item_nochar(4)Not null項目編號resultChar(20)null成績positionsmallintnull名次isattendchar(1)null是否參賽(9) 個人項目獎勵表(person_prize)：名次為主碼 positionsm

16、allintNot nullPrimary key名次scoretinyintNot null分數prizechar(40)null獎勵物品moneytinyintnull獎勵金額(10) 個人項目破記錄表(sig_record)：主碼為項目編號+破記錄者姓名 item_nochar(4)Not nullPrimary key項目編號stu_namechar(10)Not null破記錄者姓名collage_nochar(4)Not null學院編號resultChar(20)Not null成績timedatetimeNot null破記錄時間(11) 集體項目比賽表(together_ma

17、tch)：主碼為學院編號+項目編號 collage_nochar(4)Not nullPrimary key學院編號item_nochar(4)Not null項目編號resultnumeric(9,2)null成績positiontinyintnull名次isattendchar(1)null是否參賽(12) 個人項目獎勵表(together_prize)：名次做主鍵positiontinyintNot nullPrimary key名次scoretinyintNot null獎勵分數prizechar(40)null獎勵物品moneytinyintnull獎勵金額(13) 集體項目破記錄表

18、（together_record）：集體項目編號+破記錄學院編號做主鍵item_nochar(4)Not nullPrimary key集體項目編號collage_nochar(4)Not null破記錄學院編號resultChar(20)Not null成績timedatetimeNot null破記錄時間4 物理設計為了加速表的查詢，根據所設計的表的特點，我們決定在對比賽項目表中的項目編號item_no，運動員信息表中的運動員編號athlete_no，裁判信息表中的裁判編號judge_no 分別建立索引. CREATE INDEX IX_match_item ON dbo.match_it

19、em(item_no) GOCREATE INDEX IX_athlete ON dbo.athlete(athlete_no) GOCREATE INDEX IX_judge ON dbo.judge(judge_no) GO5 觸發器(1) 建立觸發器T1(保證一名運動員最多只能報兩項個人項目項目) create trigger T1 on sig_matchfor insert,updateasif rowcount=1begindeclare count tinyintselect count=count(sig_match.item_no)from sig_match,inserte

20、dwhere sig_match.athlete_no=inserted.athlete_noif count>2 begin print '一名運動員最多只能報兩項個人項目' rollback endend(2) 建立觸發器T2(保證一名運動員在同一時間內只能參加一項項目): create trigger T2 on sig_matchfor insert,updateasif rowcount=1begindeclare time1 datetime,time2 datetime,item_no1 char(4),item_no2 char(4)select top

21、1 item_no1=sig_match.item_no /*取所插入或更新的運動員所參加的一項項目*/from sig_match,insertedwhere sig_match.athlete_no=inserted.athlete_noselect top 1 item_no2=sig_match.item_nofrom sig_match,insertedwhere sig_match.athlete_no=inserted.athlete_no and sig_match.item_no!=item_no1 /*取所插入的運動員所參加的另一項項目*/select time1=star

22、t_time /*取第一項項目的開始時間*/from match_itemwhere item_no=item_no1select time2=start_time /*取第二項項目的開始時間*/from match_itemwhere item_no=item_no2if time1=time2 begin print '一名運動員在同一時間內只能參加一項項目' rollback endend(3) 建立觸發器T3(保證在同一時間內只能舉行一項集體項目): create trigger T3 on match_itemfor insert,updateasif rowcoun

23、t=1begin declare start_time datetime,count tinyint /*count記錄在同一時間內進 行比賽的集體項目的個數*/ select start_time=inserted.start_time /*取所插入或更新的集體項目的開始比賽時間*/ from inserted select count=count(match_item.item_no) from match_item,match_sort /*取同一時間內進行比賽的集體項目的個數*/ where match_item.start_time=start_time and match_item

24、.sort_no=match_sort.sort_no and match_sort.sort_name='集體項目' if count>=2 begin print '同一時間內只能舉行一項集體項目' rollback endend(4) 建立觸發器T4(保證更新個人項目里運動員的名次時,更新他所在的學院的總分:if exists (select * from sysobjects where name='T5')drop trigger dbo.T4GOprint 'create trigger T4'gocreate

25、trigger T4 on sig_matchfor updateasif rowcount=1begindeclare oldScore smallint,newScore smallintif update(position) begin select oldScore=person_prize.score /*取更新前的得分*/ from person_prize,deleted where person_prize.position=deleted.position select newScore=person_prize.score /*取更新后的得分*/ from person_p

26、rize,inserted where person_prize.position=inserted.position update collage set /*更新所在學院的得分*/ collage.total_score=collage.total_score-oldScore+newScore from athlete,inserted,deleted,collage where athlete.athlete_no=deleted.athlete_no and athlete.athlete_no=inserted.athlete_no and athlete.collage_no=c

27、ollage.collage_no endenddrop trigger T4(5) 建立觸發器T5(保證一名裁判在同一時間內只能負責一項項目的比賽):create trigger T5 on judge_schemefor insert,updateasif rowcount=1begin declare start_time datetime,count tinyint select start_time=match_item.start_time from inserted,match_item where match_item.item_no=inserted.item_no sele

28、ct count=count(judge_scheme.item_no) from inserted,match_item,judge_schemewhere match_item.item_no=judge_scheme.item_no and judge_scheme.judge_no=inserted.judge_no and match_item.start_time=start_time if count>=2 begin print '一名裁判在同一時間內只能負責一項比賽' rollback endenddrop trigger T5(6) 建立觸發器T6(保

29、證在同一時間內只能進行一項徑賽項目): create trigger T6 on match_itemfor insert,updateasif rowcount=1begin declare count tinyint select count=count(*) from match_item,inserted,match_sortwhere match_item.start_time=inserted.start_time and match_item.sort_no=match_sort.sort_no and sort_name='徑賽' if count>=2

30、begin print '同一時間內只能進行一項徑賽項目' rollback endenddrop trigger T66 存儲過程(1) 建立存儲過程P1, 輸入一個學院名稱,即可查出其總分:if exists (select * from sysobjects where name='P1')drop procedure dbo.P1GOcreate procedure P1 C_name char(20),S_tot int output asselect S_tot=total_scorefrom collagewhere collage_name=C_

31、name godeclare S_tot intexec P1 信息管理學院, s_tot outputselect s_tot(2) 建立存儲過程P2,利用存儲過程查找姓“韓”裁判的編號，性別，負責賽事的時間，項目號等if exists (select * from sysobjects where name='P2')drop procedure dbo.P2GOcreate procedure P2 name varchar(4)as /*定義一個變量接收查找的名字韓%*/select a.judge_no,a.judge_name,sex,c.start_time,b.

32、item_no,c.item_namefrom judge a,judge_scheme b,match_item c /*將judge表,judge_scheme和match_item表做連接*/where a.judge_no=b.judge_no and b.item_no=c.item_no and a.judge_name like name /*模糊查詢*/go exec P2 '韓%'(3) 建立存儲過程CountsCollageScore,輸入一個學院名稱， 統計該學院的總分，并自動更新:create procedure CountsCollageScore(c

33、ollage_name char(20)asbegin declare TotalScore smallint,sigTotalScore smallint,togTotalScore smallint select sigTotalScore=0,togTotalScore=0 declare getSigScore cursor for /*建立一個游標,統計給定的學院的個人項目總分*/ select score from sig_match,person_prize,collage,athlete where sig_match.position=person_prize.positio

34、n and collage.collage_no=athlete.collage_no and athlete.athlete_no=sig_match.athlete_no and collage_name=collage_name declare sig_score tinyint open getSigScore fetch getSigScore into sig_score while(fetch_status=0) begin select sigTotalScore=sigTotalScore+sig_score fetch getSigScore into sig_score

35、end close getSigScore deallocate getSigScore declare getTogScore cursor for /*建立一個游標,統計給定的學院的集體項目總分*/ select score from collage,together_match,together_prize where collage.collage_no=together_match.collage_no and together_match.position=together_prize.position and collage_name=collage_name declare t

36、og_score tinyint open getTogScore fetch getTogScore into tog_score while(fetch_status=0) begin select togTotalScore=togTotalScore+tog_score fetch getTogScore into tog_score end close getTogScore deallocate getTogScore select TotalScore=sigTotalScore+togTotalScore /*個人項目總分加集體項目總分*/ update collage set

37、 total_score=TotalScore /*更新給定學院的總分*/ where collage_name=collage_name endexec CountsCollageScore '會計學院'(4) 建立存儲過程Sorts，根據每一個學院的得分自動進行排名，并自動更新： create procedure Sortsasbegin declare position tinyint,collage_no char(4) select position=1 declare getInfo cursor for /*建立一個按總分降序排列的游標*/ select coll

38、age_no from collage order by total_score desc open getInfo fetch getInfo into collage_no while(fetch_status=0) begin update collage set position=position where collage_no=collage_no fetch getInfo into collage_no select position=position+1 endenddrop procedure Sortsexec Sorts7 數據庫腳本-數據庫初始化工作-set noco

39、unt onset dateformat mdygouse mastergoif exists (select * from sysdatabases where name='sports') drop database sportsgo-數據庫的建立-CREATE database sports ON primary (NAME = 'sports_data', FILENAME = 'd:databasesports_data.MDF' , SIZE = 2, FILEGROWTH =1, MAXSIZE=10) LOG ON (NAME =

40、 'sports_Log', FILENAME = 'd:databasesports_Log.LDF' , SIZE = 1, FILEGROWTH = 1, MAXSIZE=5) GO-表的建立-use sportsif exists (select * from sysobjects where name='match_sort')drop table dbo.match_sortgoprint 'drop table match_sort'print 'create match_sort'GO/*賽事類別表

41、*/create table match_sort( sort_no char(4) not null, /*類別編號*/ sort_name char(10) not null, /*類別名稱*/ primary key(sort_no)go-插入數據-insert into match_sort values('a001','田賽')insert into match_sort values('a002','徑賽')insert into match_sort values('a003','集體項目&#

42、39;)-if exists (select * from sysobjects where name='match_item')drop table dbo.match_itemgoprint 'drop table match_item'print 'create match_item'GO/*比賽項目表*/create table match_item( item_no char(4) not null primary key, /*項目編號*/ check(item_no like 'I0-90-90-9'), item_

43、name char(20) not null, /*項目名稱*/ sort_no char(4) not null, /*類別編號*/ qty smallint not null, /*每個項目參賽人（隊）數*/ start_time datetime not null, /*比賽開始時間*/ finish_time datetime null, /*比賽結束時間*/ foreign key(sort_no) references match_sort(sort_no), constraint ck_qty check (qty>=8 and qty<=50)go-插入數據-ins

44、ert into match_item values('I005','男子跳高','a001',22,'2006-10-25 10:50',null)insert into match_item values('I006','男子鉛球','a001',20,'2006-10-25 09:40',null)insert into match_item values('I007','女子鉛球','a001',18,'

45、2006-10-25 10:40',null)insert into match_item values('I008','男子100m決賽','a002',8,'2006-10-25 14:30',null)insert into match_item values('I034','女子4×400m預決賽','a002',20,'2006-10-27 15:20',null) go-if exists (select * from sysobject

46、s where name='judge')drop table dbo.judgegoprint 'drop table judge'print 'create judge'GO/*裁判信息表*/create table judge( judge_no char(4) not null primary key, /*裁判編號*/ check (judge_no like 'J0-90-90-9'), judge_name char(10) not null, /*姓名*/ sex char(1) not null, /*性別*/

47、check(sex in('f','m')go -插入數據-insert into judge values('J001','陳一帆','m')insert into judge values('J002','黎小垣','m')insert into judge values('J003','宋萬達','m')insert into judge values('J004','李強','m')insert into judge values('J005','黃蕭然','m')-if exists (select * from sysobjects where name='duty')drop table dbo.dutygoprint 'drop table duty'print 'create duty'GO/*職務表*/create table duty( duty_no char(4) not null, /*職務編號*/ duty_na