校运动会数据库设计

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