数据库课程设计指导材料new

数据库课程设计指导材料适用专业：信息管理与信息系统二〇一二年九月目录一、 数据库课程设计的目的和意义 1二、 选题的原则 1三、 课程设计的内容与要求 3四、 纪律和考核方式 3附录 5运动会管理系统 51需求分析 52概念设计 83逻辑设计 124物理设计 185触发器 196存储过程 257数据库脚本 29-PAGE56-数据库课程设计的目的和意义数据库课程设计主要是围绕《数据库系统原理》课程而开展的综合训练。通过本课程设计，使学生加强对数据库基本概念、原理和技术的掌握，结合实际的操作和设计，巩固课堂教学内容，将理论与实际相结合，应用现有的数据库建模工具和数据库管理系统软件，规范科学地完成一个小型数据库的设计与实现。在此基础上强化学生的实践意识，从而提高学生的实际动手能力和创新能力。选题的原则课程设计题目以选用学生相对比较熟悉的业务模型为宜，要求通过本实践教学环节，能较好地巩固数据库的基本概念、基本原理、关系数据库的设计理论、设计方法等相关知识点，针对实际问题设计概念模型，并应用现有的工具完成小型数据库的设计与实现。选题要求能覆盖多个知识点，使用现有工具能够解决的问题。难易适中，具有典型意义。选题范围可以体现数据库的设计与实现过程的选题，具体题目可以参考后面的参考选题，也可以自己拟定（学生自己提出，但必须经过教师确认），每人选择一个题目即可，每个题目不要超过两个人选。参考选题人事管理系统网上书店家庭理财系统客房管理系统手机话费查询系统超市管理系统学生信息查询系统课程设计成绩统计系统房屋销售管理系统住院收费管理系统干部档案管理系统订单生成系统商家打折信息管理系统实验室数据上报系统工资管理信息系统考勤管理信息系统员工培训管理信息系统仓库货物管理系统内部行文管理系统进销存管理系统酒店管理系统医院管理系统银行账户管理系统光盘管理系统网吧机房管理系统机动车驾驶员考试系统打字店文档管理系统合同管理系统通讯录管理系统试题库管理系统学生成绩管理系统学生选课信息系统电脑销售系统教材发放管理系统教师管理系统设备管理系统运动会管理系统个人财物管理系统洗衣店管理系统体育彩票分析系统高校团委团员管理系统宾馆住宿管理系统学籍管理系统房屋中介管理系统航班售票管理系统旅游管理系统高校科研管理系统校园自助银行模拟系统火车时刻查询系统高考志愿填报及录取系统（注意，此处是指现行的平行志愿）课程设计的内容与要求明确数据库的设计的每个步骤，以及提交各步骤所需图表和文档。通过使用目前流行的DBMS，建立所设计的数据库，并在此基础上实现数据库连接、查询等操作和触发器、存储器等对象设计。1、需求分析：根据自己的选题，首先收集资料，进行需求描述和系统边界的确定，然后进行需求分析（包括业务需求及处理流程，和清晰的功能需求分析，及业务规则分析）。2、概念模型设计：首先确定实体集及属性，然后确定联系集，绘制所选题目详细的E-R图，并检查是否满足需求。3、逻辑数据库设计：将E-R图转换成等价的关系数据库模式；按需求对关系模式进行规范化；对规范化后的模式进行评价，调整模式，使其满足性能、存储等方面要求；根据局部应用需要设计外模式。4、物理数据库设计：确定数据库存储结构，确定数据存取路径，及确定系统配置等。5、进行完整性、安全性规则的设计：权限分析，角色创建及角色授权等。6、用VC++、.net等平台或工具+SQLServer2005设计系统，此处提交源代码及查询截图。7、设计小结：总结课程设计的过程、体会及建议。8、其他：参考文献、致谢等。纪律和考核方式全体同学在课程设计期间，必须严格按时作息并将进行考勤，课程设计时间为早上8：00到11：30，下午2：30到4：10。考勤总分为10分，缺席一次扣1分，迟到早退一次扣0.5分，请假需经班主任同意或团总支同意（请假不扣分）；考勤分扣满10分为止，不倒扣。学生每日完成训练内容后，晚自习时应总结每日得失。整个课程设计结束时，提交课程设计所要求的文档及相关代码。每位同学应独立完成自己能力范围之内可以完成的任务，如发现抄袭或基本雷同，抄袭方与被抄袭方的成绩都自动记为不及格。整个课程设计以周为单位分为两个阶段，每阶段的阶段成绩由阶段初始时所选分组和本阶段任务完成度所决定，即每阶段成绩=初始所选分组获得的基础分值×阶段任务完成度（其中基础组的基础分值为“及格，65分”，中级组的基础分值为“中，75分”，提高组的基础分值为“良好，85分”；基础组的阶段任务完成度为0～1.25，中级组的阶段任务完成度为0～1.2，提高组的阶段任务完成度为0～1.17。每一阶段结束后，进入下一阶段之前需重新选择分组，基础组、中级组的任务完成度达到1.15以上方可选择高一级分组；中级组、提高组的任务完成度未达到0.9需降一级）。阶段总成绩由两个分阶段的成绩决定，即阶段总成绩=一阶段成绩×0.5+二阶段成绩×0.5。课程设计总成绩由三部分决定，其中阶段总成绩占70%、考勤占10%、课程设计文档写作质量占20%。课程设计结束后，每位同学在指定服务器上建立以自己的学号加姓名为文件夹名的文件夹（如200810530102幸勇军），将自己所完成的任务的源程序代码、设计文档等压缩后上传到该文件夹下。注意：在整个课程设计过程中，学生若有3次考勤为缺席，综合评定成绩不得超过中以上成绩；在整个课程设计过程中，学生若有4次考勤为缺席，综合评定成绩不得超过及格以上成绩；在整个课程设计过程中，学生若有5次考勤为缺席，综合评定成绩为不及格。附录运动会管理系统1需求分析1.1编写目的使用该文档的使用成员为实现SGS系统的开发人员以及学校的运动会具体组织管理人员。1.2背景项目名称：学校运动会管理系统（SchoolGymkhanaSystem简称SGS）。项目提出者：SGS项目组织人员。项目开发者：SGS 项目开发小组。项目鉴定者：暂无。项目开始时间：××××-××-××。1.3定义系统数据流程图的一些定义1.4目标当学校运动会管理人员使用了《学校运动会管理系统SGS》软件后，可以由计算机完成：运动员报名的自动核对；比赛时各种人员信息的查询、核对、更新、删除。学校运动会管理系统SGS用户分为两类：普通操作员：只须具有初级的计算机操作能力，一般高中生以上经过稍稍培训都可胜任，只是负责比赛进行时一些简单数据的插入、更新、查询。管理人员：这些人员应具备一定的数据库知识，不过只要了解就行，这些人员负责对数据库中重要数据及基本数据的更新，如对静态表的重新定义，普通用户的权限分配。1.5需求分析1.5.1系统总体需求(l)通过对运动会各个步骤、所需的各项信息等的分析，我们规定：①项目有个人项目、集体项目之分．②对于集体项目，我们并不关心具体参加比赛的运动员．在每一项集体项目中，每个学院的参赛人数不可超过限制的参赛人数，且必须是该学院的学生．③一个项目可有多名运动员参加，但一名运动员最多只能参加两项个人项目（用一个触发器实现），且只能属于一个学院．④一个项目可由多名裁判主持，每一个裁判在此项目中所担任的职务不同，且有主、副裁判之分．⑤在同一时间内可同时举行多项比赛，但是只能进行一项径赛比赛（用一个触发器实现）．⑥在同一时间内，一名裁判只能主持一项比赛、担任一个职务（用一个触发器实现）．但在不同的时间内，一名裁判可主持不同的项目、担任不同的职务．⑦一名运动员在同一时间内只能参加一项比赛（用一个触发器实现）．⑧在同一时间内，一个学院只能参加一项集体项目比赛（通过用一个触发器限制在同一时间内只能举行一项集体项目）．⑨在某一项目中，只有一个主裁判，其余都为副裁判．⑩一项个人项目可有多个同时破记录者，一名运动员同时破多项记录．一项集体项目可同时有多个破记录的学院，一个学院可同时破多项记录。(2)通过对运动会各方面分析，运动会中包含的实体有学院、运动员（不包含集体项目中的运动员）、项目（集体项目和个人项目）、裁判、职务、奖励（集体项目奖励和个人项目奖励，名次为0表示破记录）、赛事类别（田赛、径赛、集体项目等）．集体项目奖励和个人项目奖励不同。包含的数据项分别如下：①学院编号，学院名称，上届运动会名次，历史最高名次，本次运动会总分，本次运动会名次等．②运动员编号，学号，姓名，性别，所属学院，班级等．③项目编号，项目名称，参赛者性别（男或女或不限）、每个学院限参赛人次，比赛时间，结束时间等．④裁判编号，姓名，性别等．⑤职务编号，职务名称（如计时员、测量员、记录员等），职务级别（主裁判和副裁判）等．⑥名次，奖励分数，奖励物品（如奖状等），奖励金额等．⑦赛事类别编号，赛事类别名称等．(3)实体之间的联系有：①学院与运动员之间（1：M）．②运动员、个人项目与个人项目奖励之间（1：M，1：1）．③学院、集体项目与集体项目奖励之间（1：M，1：1）．④赛事类别与项目之间．（1：M）⑤裁判、项目与职务之间．（M：N）(4)通过以上分析，运动会数据库包含的模块有：①输入模块主要是各种信息的输入，如学院信息、运动员信息、项目信息、比赛信息、奖励信息等．②输出模块主要是各种查询结果的输出等．1.5.2软硬件平台设计(1)软件平台：windowsXP操作系统、Sql-Server2000数据库(2)硬件平台：普通微机2概念设计2.1实体学院与运动员之间的联系相关说明：上述E-R图表示一个学院有多名运动员参加个人项目，一名运动员只能属于一个学院。2.2运动员、个人项目和个人项目奖励之间的联系上述E-R图表示一个项目可有多项不同的奖励（如第一名、第二名等的奖励不同）对于每一项奖励可在不同的项目中出现；一名运动员最多只能有两项个人项目奖励（此时p=0…2）,每一项奖励可有多名运动员获得；一名运动员最多能参加两项个人项目（此时m=1…2），一项个人项目可有多名运动员参加。2.3学院、集体项目和集体项目奖励之间的联系相关说明：上述E-R图表示一个学院可获得多项集体项目奖励、参加多项集体项目，一项集体项目可有多个学院参赛、有多项不同的奖励，一项集体项目奖励可被多个学院获得、可在多项集体项目中出现．2.4赛事类别与项目之间的联系2.5裁判、项目、职务之间的联系：上述E-R图表示一名裁判在不同的时间内可主持多项比赛、担任多项职务，同一职务可被多名裁判担任、可出现在多项比赛中，一项项目可由多名裁判共同主持、可有多个职务（如计时员、测量员、记录员等）．2.6总E-R图将上述分E-R全部合并，并化简的到如下的总E-R图（为简化画图工作，上述各实体中的属性未画出，并把集体项目和个人项目合并为项目、集体项目奖励和个人项目奖励合并）：上述E-R图中学院、项目和奖励之间的联系如上述３中的E-R图一样，运动员、项目和奖励之间表示的联系如上述2中的E-R图一样。3逻辑设计本次设计的数据库在SQLServer2000上实现，将概念结构设计中的E-R图转换成SQLSever2000支持的关系数据模型后，结合上述分析得到如下数据表：（1）赛事类别表（match_sort）：主键为赛事类别号sort_nosort_nochar(3)notnullprimarykey赛事类别号sort_namechar(10)notnull类别名称(2)比赛项目表（match_item）：主键为项目编号，其中对‘每个学院限参赛人数’设置了check约束，并且‘类别编号’也作为外键约束，受到赛事类别表中‘赛事类别号’的约束item_nochar(4)notnullprimarykey项目编号item_namechar(20)notnull项目名称Item_sexchar(2)Notnull项目性别sort_nochar(3)notnull类别编号qtysmallintnotnull每个学院限参赛人数start_timedatetimeNotnull比赛开始时间finish_timedatetimeNull比赛结束时间(3)裁判信息表(judge)：主键为裁判编号，并在‘性别’属性列上设置check约束（‘f’表示女性，‘m’代表男性）judge_nochar(４)NotnullPrimarykey裁判编号judge_namechar(10)Notnull姓名sexchar(2)Notnull性别(4)职务表(duty)：职务编号是主码duty_nochar(4)NotnullPrimarykey职务编号duty_namechar(20)Notnull职务名称rankingchar(10)Null职务级别(5)裁判安排表(judge_scheme)：主码为（裁判编号，项目编号），设置了两个外键约束，外键‘项目编号item_no’受到match_item表中item_no的约束，‘职务号duty_no’受duty表中的duty_no约束judge_nochar(４)NotnullPrimarykey裁判编号item_nochar(4)Notnull项目编号duty_nochar(4)Null职务号isattendchar(1)Null是否到场(6)学院信息表(collage)：主键是‘学院编号’，并在其上设置check约束collage_nochar(4)Notnullprimary学院编号collage_namechar(20)Notnull学院名称last_positiontinyintNull上届运动会名次max_positiontinyintNull历史最高名次total_scoreintNull本届运动会总分positiontinyintNull本届运动会名次(7)运动员信息表(athlete)：‘运动员编号athlete_no’是主键，并在‘学院编号’属性列上设置外键约束athlete_nochar(4)Notnullprimary运动员编号stu_nochar(6)Notnull学号athlete_namechar(10)Notnull姓名sexchar(2)Notnull性别collage_nochar(4)Notnull学院编号(8)个人项目比赛表(sig_match)：‘运动员编号，项目编号’为主码，外键有‘项目编号’，‘运动员编号’，‘名次’分别受到表match_item，athlete及person_prize中相应属性的约束athlete_nochar(4)NotnullPrimarykey运动员编号item_nochar(4)Notnull项目编号resultChar(20)null成绩positionsmallintnull名次isattendchar(1)null是否参赛(9)个人项目奖励表(person_prize)：‘名次’为主码positionsmallintNotnullPrimarykey名次scoretinyintNotnull分数prizechar(40)null奖励物品moneytinyintnull奖励金额(10)个人项目破记录表(sig_record)：主码为‘项目编号+破记录者姓名’item_nochar(4)NotnullPrimarykey项目编号stu_namechar(10)Notnull破记录者姓名collage_nochar(4)Notnull学院编号resultChar(20)Notnull成绩timedatetimeNotnull破记录时间(11)集体项目比赛表(together_match)：主码为‘学院编号+项目编号’collage_nochar(4)NotnullPrimarykey学院编号item_nochar(4)Notnull项目编号resultnumeric(9,2)null成绩positiontinyintnull名次isattendchar(1)null是否参赛(12)个人项目奖励表(together_prize)：‘名次’做主键positiontinyintNotnullPrimarykey名次scoretinyintNotnull奖励分数prizechar(40)null奖励物品moneytinyintnull奖励金额(13)集体项目破记录表（together_record）：‘集体项目编号+破记录学院编号’做主键item_nochar(4)NotnullPrimarykey集体项目编号collage_nochar(4)Notnull破记录学院编号resultChar(20)Notnull成绩timedatetimeNotnull破记录时间4物理设计为了加速表的查询，根据所设计的表的特点，我们决定在对比赛项目表中的‘项目编号item_no’，运动员信息表中的‘运动员编号athlete_no’，裁判信息表中的‘裁判编号judge_no‘分别建立索引.CREATEINDEXIX_match_itemONdbo.match_item(item_no)GOCREATEINDEXIX_athleteONdbo.athlete(athlete_no)GOCREATEINDEXIX_judgeONdbo.judge(judge_no)GO5触发器(1)建立触发器T1(保证一名运动员最多只能报两项个人项目项目)(2)建立触发器T2(保证一名运动员在同一时间内只能参加一项项目):/*取所插入或更新的运动员所参加的一项项目*//*取所插入的运动员所参加的另一项项目*//*取第一项项目的开始时间*//*取第二项项目的开始时间*/一名运动员在同一时间内只能参加一项项目'(3)建立触发器T3(保证在同一时间内只能举行一项集体项目):/*count记录在同一时间内进行比赛的集体项目的个数*/(4)建立触发器T4(保证更新个人项目里运动员的名次时,更新他所在的学院的总分:ifexists(select*fromsysobjectswherename='T5')droptriggerdbo.T4GOprint'createtriggerT4'gocreatetriggerT4onsig_matchforupdateasif@@rowcount=1begindeclare@oldScoresmallint,@newScoresmallintifupdate(position)beginselect@oldScore=person_prize.score/*取更新前的得分*/fromperson_prize,deletedwhereperson_prize.position=deleted.positionselect@newScore=person_prize.score/*取更新后的得分*/fromperson_prize,insertedwhereperson_prize.position=inserted.positionupdatecollageset/*更新所在学院的得分*/collage.total_score=collage.total_score-@oldScore+@newScorefromathlete,inserted,deleted,collagewhereathlete.athlete_no=deleted.athlete_noandathlete.athlete_no=inserted.athlete_noandathlete.collage_no=collage.collage_noendenddroptriggerT4(5)建立触发器T5(保证一名裁判在同一时间内只能负责一项项目的比赛):createtriggerT5onjudge_schemeforinsert,updateasif@@rowcount=1begindeclare@start_timedatetime,@counttinyintselect@start_time=match_item.start_timefrominserted,match_itemwherematch_item.item_no=inserted.item_noselect@count=count(judge_scheme.item_no)frominserted,match_item,judge_schemewherematch_item.item_no=judge_scheme.item_noandjudge_scheme.judge_no=inserted.judge_noandmatch_item.start_time=@start_timeif@count>=2beginprint'一名裁判在同一时间内只能负责一项比赛'rollbackendenddroptriggerT5(6)建立触发器T6(保证在同一时间内只能进行一项径赛项目):createtriggerT6onmatch_itemforinsert,updateasif@@rowcount=1begindeclare@counttinyintselect@count=count(*)frommatch_item,inserted,match_sortwherematch_item.start_time=inserted.start_timeandmatch_item.sort_no=match_sort.sort_noandsort_name='径赛'if@count>=2beginprint'同一时间内只能进行一项径赛项目'rollbackendenddroptriggerT66存储过程(1)建立存储过程P1,输入一个学院名称,即可查出其总分:ifexists(select*fromsysobjectswherename='P1')dropproceduredbo.P1GOcreateprocedureP1@C_namechar(20),@S_totintoutputasselect@S_tot=total_scorefromcollagewherecollage_name=@C_namegodeclare@S_totintexecP1信息管理学院,@s_totoutputselect@s_tot(2)建立存储过程P2,利用存储过程查找姓“韩”裁判的编号，性别，负责赛事的时间，项目号等ifexists(select*fromsysobjectswherename='P2')dropproceduredbo.P2GOcreateprocedureP2@namevarchar(4)as/*定义一个变量接收查找的名字‘韩%’*/selecta.judge_no,a.judge_name,sex,c.start_time,b.item_no,c.item_namefromjudgea,judge_schemeb,match_itemc/*将judge表,judge_scheme和match_item表做连接*/wherea.judge_no=b.judge_noandb.item_no=c.item_noanda.judge_namelike@name/*模糊查询*/goexecP2'韩%'(3)建立存储过程CountsCollageScore,输入一个学院名称，统计该学院的总分，并自动更新:createprocedureCountsCollageScore(@collage_namechar(20))asbegindeclare@TotalScoresmallint,@sigTotalScoresmallint,@togTotalScoresmallintselect@sigTotalScore=0,@togTotalScore=0declaregetSigScorecursorfor/*建立一个游标,统计给定的学院的个人项目总分*/selectscorefromsig_match,person_prize,collage,athletewheresig_match.position=person_prize.positionandcollage.collage_no=athlete.collage_noandathlete.athlete_no=sig_match.athlete_noandcollage_name=@collage_namedeclare@sig_scoretinyintopengetSigScorefetchgetSigScoreinto@sig_scorewhile(@@fetch_status=0)beginselect@sigTotalScore=@sigTotalScore+@sig_scorefetchgetSigScoreinto@sig_scoreendclosegetSigScoredeallocategetSigScoredeclaregetTogScorecursorfor/*建立一个游标,统计给定的学院的集体项目总分*/selectscorefromcollage,together_match,together_prizewherecollage.collage_no=together_match.collage_noandtogether_match.position=together_prize.positionandcollage_name=@collage_namedeclare@tog_scoretinyintopengetTogScorefetchgetTogScoreinto@tog_scorewhile(@@fetch_status=0)beginselect@togTotalScore=@togTotalScore+@tog_scorefetchgetTogScoreinto@tog_scoreendclosegetTogScoredeallocategetTogScoreselect@TotalScore=@sigTotalScore+@togTotalScore/*个人项目总分加集体项目总分*/updatecollagesettotal_score=@TotalScore/*更新给定学院的总分*/wherecollage_name=@collage_nameendexecCountsCollageScore'会计学院'(4)建立存储过程Sorts，根据每一个学院的得分自动进行排名，并自动更新：createprocedureSortsasbegindeclare@positiontinyint,@collage_nochar(4)select@position=1declaregetInfocursorfor/*建立一个按总分降序排列的游标*/selectcollage_nofromcollageorderbytotal_scoredescopengetInfofetchgetInfointo@collage_nowhile(@@fetch_status=0)beginupdatecollagesetposition=@positionwherecollage_no=@collage_nofetchgetInfointo@collage_noselect@position=@position+1endenddropprocedureSortsexecSorts7数据库脚本数据库初始化工作setnocountonsetdateformatmdygousemastergoifexists(select*fromsysdatabaseswherename='sports')dropdatabasesportsgo数据库的建立CREATEdatabasesportsONprimary(NAME='sports_data',FILENAME='d:\database\sports_data.MDF',SIZE=2,FILEGROWTH=1,MAXSIZE=10)LOGON(NAME='sports_Log',FILENAME='d:\database\sports_Log.LDF',SIZE=1,FILEGROWTH=1,MAXSIZE=5)GO表的建立usesportsifexists(select*fromsysobjectswherename='match_sort')droptabledbo.match_sortgoprint'droptablematch_sort'print'creatematch_sort'GO/*赛事类别表*/createtablematch_sort(sort_nochar(4)notnull,/*类别编号*/sort_namechar(10)notnull,/*类别名称*/primarykey(sort_no))go插入数据insertintomatch_sortvalues('a001','田赛')insertintomatch_sortvalues('a002','径赛')insertintomatch_sortvalues('a003','集体项目')ifexists(select*fromsysobjectswherename='match_item')droptabledbo.match_itemgoprint'droptablematch_item'print'creatematch_item'GO/*比赛项目表*/createtablematch_item(item_nochar(4)notnullprimarykey,/*项目编号*/check(item_nolike'I[0-9][0-9][0-9]'),item_namechar(20)notnull,/*项目名称*/sort_nochar(4)notnull,/*类别编号*/qtysmallintnotnull,/*每个项目参赛人（队）数*/start_timedatetimenotnull,/*比赛开始时间*/finish_timedatetimenull,/*比赛结束时间*/foreignkey(sort_no)referencesmatch_sort(sort_no),constraintck_qtycheck(qty>=8andqty<=50))go插入数据insertintomatch_itemvalues('I005','男子跳高','a001',22,'2006-10-2510:50',null)insertintomatch_itemvalues('I006','男子铅球','a001',20,'2006-10-2509:40',null)insertintomatch_itemvalues('I007','女子铅球','a001',18,'2006-10-2510:40',null)insertintomatch_itemvalues('I008','男子100m决赛','a002',8,'2006-10-2514:30',null)insertintomatch_itemvalues('I034','女子4×400m预决赛','a002',20,'2006-10-2715:20',null)goifexists(select*fromsysobjectswherename='judge')droptabledbo.judgegoprint'droptablejudge'print'createjudge'GO/*裁判信息表*/createtablejudge(judge_nochar(4)notnullprimarykey,/*裁判编号*/check(judge_nolike'J[0-9][0-9][0-9]'),judge_namechar(10)notnull,/*姓名*/sexchar(1)notnull,/*性别*/check(sexin('f','m')))go插入数据insertintojudgevalues('J001','陈一帆','m')insertintojudgevalues('J002','黎小垣','m')insertintojudgevalues('J003','宋万达','m')insertintojudgevalues('J004','李强','m')insertintojudgevalues('J005','黄萧然','m')ifexists(select*fromsysobjectswherename='duty')droptabledbo.dutygoprint'droptableduty'print'createduty'GO/*职务表*/createtableduty(duty_nochar(4)notnull,/*职务编号*/duty_namechar(20)notnull,/*职务名称*/rankingchar(10)null,/*职务级别*/primarykey(duty_no))go插入数据insertintodutyvalues('D001','计时员','副裁判')insertintodutyvalues('D002','测量员','副裁判')insertintodutyvalues('D003','记录员','主裁判')insertintodutyvalues('D004','其它','副裁判')ifexists(select*fromsysobjectswherename='judge_scheme')droptabledbo.judge_schemegoprint'droptablejudge_scheme'print'createjudge_scheme'GO/*裁判安排表*/createtablejudge_scheme(judge_nochar(4)notnull,/*裁判编号*/item_nochar(4)notnull,/*项目编号*/duty_nochar(4)null,/*职务编号*/isattendchar(1)null,/*是否到场*/constraintpk_judge_schemeprimarykeyclustered(judge_no,item_no),foreignkey(item_no)referencesmatch_item(item_no),foreignkey(duty_no)referencesduty(duty_no),check(isattendin('y','n')))go插入数据insertintojudge_schemevalues('J001','I001','D003','y')insertintojudge_schemevalues('J002','I001','D001','y')insertintojudge_schemevalues('J003','I001','D001','y')insertintojudge_schemevalues('J004','I001','D001','y')insertintojudge_schemevalues('J005','I001','D001','y')ifexists(select*fromsysobjectswherename='collage')droptabledbo.collagegoprint'droptablecollage'print'createcollage'GO/*学院信息表*/createtablecollage(collage_nochar(4)notnullprimarykey/*学院编号*/check(collage_nolike'C[0-9][0-9][0-9]'),collage_namechar(20)notnull,/*学院名称*/last_positionchar(6)null,/*上届运动会名次*/max_positionchar(6)null,/*历史最高名次*/total_scoreintnull,/*本次运动会总分*/positionchar(6)null,/*本次运动会名次*/)go插入数据insertintocollagevalues('C001','工商管理学院','','第二名',0,null)insertintocollagevalues('C002','会计学院','第二名','第一名',0,null)insertintocollagevalues('C003','金融学院','第四名','第二名',0,null)insertintocollagevalues('C004','公共管理学院','第六名','第三名',0,null)insertintocollagevalues('C005','信息管理学院','第三名','第一名',0,null)ifexists(select*fromsysobjectswherename='athlete')droptabledbo.athletegoprint'droptableathlete'print'createathlete'GO/*运动员信息表(只记录个人项目中的运动员信息)*/createtableathlete(athlete_nochar(4)notnullprimarykey/*运动员编号*/check(athlete_nolike'A[0-9][0-9][0-9]'),stu_nochar(7)notnull,/*学号*/athlete_namechar(10)notnull,/*姓名*/sexchar(1)notnull,/*性别*/collage_nochar(4)notnull,/*学院号*/check(sexin('f','m')),foreignkey(collage_no)referencescollage(collage_no))goinsertintoathletevalues('A001','0050002','胡东平','m','C001')insertintoathletevalues('A002','0050113','郑晓林','f','C001')insertintoathletevalues('A003','0050241','张超俊','m','C001')insertintoathletevalues('A004','0050143','刘波','m','C001')insertintoathletevalues('A005','0050201','刘伟','m','C001')goifexists(select*fromsysobjectswherename='person_prize')droptabledbo.person_prizegoprint'droptableperson_prize'print'createperson_prize'GO/*个人项目奖励表(名次为0表示破记录)*/createtableperson_prize(positionsmallintnotnullprimarykey,/*名次*/scoretinyintnotnull,/*奖励分数*/prizechar(40)null,/*奖励物品*/moneytinyintnull/*奖励金额*/)goinsertintoperson_prizevalues(0,18,'奖状',50)insertintoperson_prizevalues(1,9,'奖状',25)insertintoperson_prizevalues(2,7,'奖状',15)insertintoperson_prizevalues(7,2,NULL,NULL)insertintoperson_prizevalues(8,1,NULL,NULL)ifexists(select*fromsysobjectswherename='sig_match')droptabledbo.sig_matchgoprint'droptablesig_match'print'createsig_match'GO/*个人项目比赛表*/createtablesig_match(athlete_nochar(4)notnull,/*运动员编号*/check(athlete_nolike'A[0-9][0-9][0-9]'),item_nochar(4)notnull,/*项目号*/resultnumeric(9,2)null,/*成绩*/positionsmallintnull,/*名次*/isattendchar(1)null,/*是否参赛*/primarykey(athlete_no,item_no),foreignkey(position)referencesperson_prize(position),foreignkey(athlete_no)referencesathlete(athlete_no),foreignkey(item_no)referencesmatch_item(item_no),check(isattendin('y','n')))goinsertintosig_match(athlete_no,item_no,isattend)values('A001','I001','y')insertintosig_match(athlete_no,item_no,isattend)values('A003','I001','y')insertintosig_match(athlete_no,item_no,isattend)values('A022','I001','y')insertintosig_match(athlete_no,item_no,isattend)values('A026','I001','y')insertintosig_match(athlete_no,item_no,isattend)values('A036','I001','y')goifexists(select*fromsysobjectswherename='sig_record')droptabledbo.sig_recordgoprint'droptablesig_record'print'createsig_record'GO/*个人项目破记录表*/createtablesig_record(item_nochar(4)notnull,/*项目号*/stu_namechar(10)notnull,/*破记录者姓名*/collage_nochar(4)notnull,/*学院编号*/result char(20)notnull,/*成绩*/timedatetimenotnull,/*破记录时间*/primarykey(item_no,stu_name),foreignkey(item_no)referencesmatch_item(item_no),foreignkey(collage_no)referencescollage(collage_no))goifexists(select*fromsysobjectswherename='together_prize')droptabledbo.together_prizegoprint'droptabletogether_prize'print'createtogether_prize'GO/*集体项目奖励表(0表示破记录)*/createtabletogether_prize(positiontinyintnotnullprimarykey,/*名次*/scoretinyintnotnull,/*奖励分数*/prizechar(40)null,/*奖励物品*/moneytinyintnull,/*奖励金额*/)goinsertintotogether_prizevalues(0,36,'奖状',200)insertintotogether_prizevalues(1,18,'奖状',100)insertintotogether_prizevalues(6,8,NULL,NULL)insertintotogether_prizevalues(7,6,NULL,NULL)insertintotogether_prizevalues(8,4,NULL,NULL)ifexists(select*fromsysobjectswherename='together_match')droptabledbo.together_matchgoprint'droptabletogether_match'print'createtogether_match'GO/*集体项目比赛表*/createtabletogether_match(collage_nochar(4)notnull,/*学院号*/item_nochar(4)notnull,/*项目编号*/resultchar(20)null,/*成绩*/positiontinyintnull,/*名次*/isattendchar(1)null,/*是否参赛*/primarykey(collage_no,item_no),foreignkey(collage_no)referencescollage(collage_no),foreignkey(item_no)referencesmatch_item(item_no),foreignkey(position)referencestogether_prize(position),check(isattendin('y','n')))goinsertintotogether_match(collage_no,item_no,isattend)values('C001','I014','y')insertintotogether_match(collage_no,item_no,isattend)values('C004','I028','y')insertintotogether_match(collage_no,item_no,isattend)values('C004','I032','y')insertintotogether_match(collage_no,item_no,isattend)values('C005','I016','y')insertintotogether_match(collage_no,item_no,isattend)values('C005','I034','y')insertintotogether_match(collage_no,item_no,isattend)values('C005','I033','y')insertintotogether_match(collage_no,item_no,isattend)values('C006','I015','y')goifexists(select*fromsysobjectswherename='together_record')droptabledbo.together_recordgoprint'droptabletogether_record'print'createtogether_record'GO/*集体项目破记录表*/createtabletogether_record(item_nochar(4)notnull,/*项目号*/collage_nochar(4)notnull,/*破记录学院号*/resultchar(20)notnull,/*成绩*/timedatetimenotnull,/*破记录时间*/constraintpk_together_recordprimarykeyclustered(item_no,collage_no),foreignkey(item_no)referencesmatch_item(item_no),foreignkey(collage_no)referencescollage(collage_no))go建立索引CREATEuniqueINDEXIX_match_itemONdbo.match_item(item_no)GOCREATEuniqueINDEXIX_athleteONdbo.athlete(athlete_no)GOCREATEuniqueINDEXIX_judgeONdbo.judge(judge_no)GO建立触发器T1(保证一名运动员最多只能报两项个人项目项目)createtriggerT1onsig_matchforinsert,updateasif@@rowcount=1begindeclare@counttinyintselect@count=count(sig_match.item_no)fromsig_match,insertedwheresig_match.athlete_no=inserted.athlete_noif@count>2beginprint'一名运动员最多只能报两项个人项目'rollbackendend建立触发器T2(保证一名运动员在同一时间内只能参加一项项目)forinsert,updateasif@@rowcount=1begindeclare@time1datetime,@time2datetime,@item_no1char(4),@item_no2char(4)selecttop1@item_no1=sig_match.item_no/*取所插入或更新的运动员所参加的一项项目*/fromsig_match,insertedwheresig_match.athlete_no=inserted.athlete_noselecttop1@item_no2=sig_match.item_nofromsig_match,insertedwheresig_match.athlete_no=inserted.athlete_noandsig_match.item_no!=@item_no1/*取所插入的运动员所参加的另一项项目*/select@time1=start_time/*取第一项项目的开始时间*/frommatch_itemwhereitem_no=@item_no1select@time2=start_time/*取第二项项目的开始时间*/frommatch_itemwhereitem_no=@item_no2if@time1=@time2beginprint'一名运动员在同一时间内只能参加一项项目'rollbackendend--建立触发器T3(保证在同一时间内只能举行一项集体项目)createtriggerT3onmatch_itemforinsert,updateasif@@rowcount=1begindeclare@start_timedatetime,@counttinyint/*count记录在同一时间内进行比赛的集体项目的个数*/select@start_time=inserted.start_time/*取所插入或更新的集体项目的开始比赛时间*/frominsertedselect@count=count(match_item.item_no)frommatch_item,match_sort/*取同一时间内进行比赛的集体项目的个数*/wherematch_item.start_time=@start_timeandmatch_item.sort_no=match_sort.sort_noandmatch_sort.sort_name='集体项目'if@count>=2beginprint'同一时间内只能举行一项集体项目'rollbackendend--建立触发器T4(保证插入或更新运动员信息时,其性别为'm'或'f')print'droptriggerT4'goifexists(select*fromsysobjectswherename='T4')droptriggerdbo.T4GOprint'createtriggerT4'goCREATETRIGGERT4ONathleteFORINSERT,UPDATEA