數(shù)據(jù)庫設(shè)計(jì)實(shí)例運(yùn)動(dòng)會(huì)管理系統(tǒng).doc

本科學(xué)生設(shè)計(jì)性實(shí)驗(yàn)報(bào)告課程名稱：現(xiàn)代數(shù)據(jù)庫技術(shù)實(shí)驗(yàn)項(xiàng)目名稱：運(yùn)動(dòng)會(huì)管理系統(tǒng)數(shù) 據(jù) 庫 設(shè) 計(jì)姓名：何東靈 學(xué)號(hào)：0041400班級(jí)：C03 班 第小組2006至2007學(xué)年 第2學(xué)期2007年6月15日目 錄目 錄2引言3編寫目的3背景3定義31.數(shù)據(jù)字典3字典1：運(yùn)動(dòng)員數(shù)據(jù)字典3字典2：班級(jí)數(shù)據(jù)字典3字典3：項(xiàng)目數(shù)據(jù)字典4字典4：記錄數(shù)據(jù)字典4字典5：規(guī)則數(shù)據(jù)字典4字典6：工作人員數(shù)據(jù)字典5字典7：場地?cái)?shù)據(jù)字典5字典8：器材數(shù)據(jù)字典5字典9：項(xiàng)目人員數(shù)據(jù)字典5字典10：項(xiàng)目器材數(shù)據(jù)字典5字典11：運(yùn)動(dòng)員成績數(shù)據(jù)字典6字典12：團(tuán)體賽成績數(shù)據(jù)字典6字典13：班級(jí)總分?jǐn)?shù)據(jù)字典62.數(shù)據(jù)庫設(shè)計(jì)63.數(shù)據(jù)庫設(shè)計(jì)中的一個(gè)編號(hào)過程10引言編寫目的本數(shù)據(jù)庫設(shè)計(jì)說明書專門為SGS系統(tǒng)的項(xiàng)目開發(fā)人員編寫。背景項(xiàng)目名稱：學(xué)校運(yùn)動(dòng)會(huì)管理系統(tǒng)（SchoolGymkhanaSystem簡稱SGS）項(xiàng)目提出者：江財(cái)SGS項(xiàng)目組織人員項(xiàng)目開發(fā)者：需求分析階段有SGS項(xiàng)目開發(fā)小組人員共同完成,其它各階段由小組成員各自負(fù)責(zé)完成項(xiàng)目鑒定者：吳京慧教授項(xiàng)目開始時(shí)間：2007-6-15定義列出本文件中用到的專門術(shù)語的定義和縮寫詞的源詞組。1 所有的表名的命名規(guī)則：所有表名都根據(jù)表意用相關(guān)的英文全稱命名。2 所有的主鍵命名規(guī)則：由表名+_PK用編號(hào)區(qū)分3 所有外鍵的命名規(guī)則：由表名+_FK用編號(hào)區(qū)分4 所有規(guī)則的命名規(guī)則：由列名+_CK用編號(hào)區(qū)分5 所有索引的命名規(guī)則：表名+_INDEX+列名6 所有字段的命名規(guī)則：類型+具體含義1. 數(shù)據(jù)字典字典1：運(yùn)動(dòng)員數(shù)據(jù)字典記錄運(yùn)動(dòng)員相關(guān)信息字典結(jié)構(gòu)屬性名稱屬性類型是否允許為空默認(rèn)值約束含義AthleteNoChar(8)NOT NULL主鍵、屆數(shù)流水號(hào)運(yùn)動(dòng)員編號(hào)AthleteNameVarchar2(20)NOT NULL運(yùn)動(dòng)員姓名SexVarchar2(2)NOT NULL“男”只取“男”、“女”性別TelephoneVarchar2(20)NULL聯(lián)系方法ClassNoChar(8)NOT NULL外鍵、屆數(shù)流水號(hào)班級(jí)編號(hào)字典2：班級(jí)數(shù)據(jù)字典記錄班級(jí)相關(guān)信息字典結(jié)構(gòu)屬性名稱屬性類型是否允許為空默認(rèn)值約束含義ClassNoChar(8)NOT NULL主鍵、屆數(shù)流水號(hào)班級(jí)編號(hào)InstituteNameVarchar2(30)NOT NULL學(xué)院名稱ClassNameVarchar2(30)NOT NULL班級(jí)全稱字典3：項(xiàng)目數(shù)據(jù)字典記錄項(xiàng)目相關(guān)信息字典結(jié)構(gòu)屬性名稱屬性類型是否允許為空默認(rèn)值約束含義ItemNoChar(3)NOT NULL主鍵項(xiàng)目編號(hào)ItemNameVarchar2(50)NOT NULL項(xiàng)目名稱SexLimitchar(2)NOT NULL只取“男”“女”性別限制ComityRequireNumber(2)NOT NULL集體項(xiàng)目人數(shù)StartTimeDateNULL開始時(shí)間EndTimeDateNULL結(jié)束時(shí)間TrackFieldJudgeNumber(1)NOT NULL田徑賽區(qū)分ClassNoLimitNumber(2)NOT NULL每班最大人數(shù)TotalAthleteNumber(3)NOT NULL0已選人數(shù)RuleNoChar(3)NOT NULL外鍵規(guī)則編號(hào)FieldNoChar(3)NOT NULL外鍵場地編號(hào)字典4：記錄數(shù)據(jù)字典記錄記錄相關(guān)信息字典結(jié)構(gòu)屬性名稱屬性類型是否允許為空默認(rèn)值約束含義ItemNoChar(3)NOT NULL主鍵、外鍵項(xiàng)目編號(hào)ItemNameVarchar2(50)NOT NULL項(xiàng)目名稱AthleteNoChar(8)NULL外鍵運(yùn)動(dòng)員編號(hào)AthleteNameVarchar2(20)NOT NULL運(yùn)動(dòng)員姓名ClassNoChar(8)NULL外鍵、屆數(shù)流水號(hào)班級(jí)編號(hào)ClassDiscribeVarchar2(50)NULL班級(jí)描述GymkhanaNameVarchar2(50)NULL運(yùn)動(dòng)會(huì)名TrackFieldJudgeNumber(1)NOT NULL田徑賽區(qū)分ScoreNumber(3)NULL成績字典5：規(guī)則數(shù)據(jù)字典記錄規(guī)則相關(guān)信息字典結(jié)構(gòu)屬性名稱屬性類型是否允許為空默認(rèn)值約束含義RuleNoChar(3)NOT NULL主鍵規(guī)則編號(hào)RuleNameVarchar2(30)NULL規(guī)則名稱TextPathVarchar2(200)NULL文本路徑字典6：工作人員數(shù)據(jù)字典記錄工作人員相關(guān)信息字典結(jié)構(gòu)屬性名稱屬性類型是否允許為空默認(rèn)值約束含義OperatorNoChar(3)NOT NULL主鍵、職位游水號(hào)工作人員編號(hào)OperatorNameVarchar2(20)NOT NULL工作人員姓名PostVarchar2(30)NULL單位DutyVarchar2(100)NULL運(yùn)動(dòng)會(huì)職務(wù)字典7：場地?cái)?shù)據(jù)字典記錄場地相關(guān)信息字典結(jié)構(gòu)屬性名稱屬性類型是否允許為空默認(rèn)值約束含義FieldNoChar(3)NOT NULL主鍵場地編號(hào)FieldNameVarchar2(20)NULL場地名稱AddressVarchar2(50)NULL具體地址字典8：器材數(shù)據(jù)字典記錄器材相關(guān)信息字典結(jié)構(gòu)屬性名稱屬性類型是否允許為空默認(rèn)值約束含義EquipmentNoChar(3)NOT NULL主鍵器材編號(hào)EquipmentNameVarchar2(20)NOT NULL器材名稱DepositVarchar2(100)NULL存放簡介字典9：項(xiàng)目人員數(shù)據(jù)字典記錄項(xiàng)目人員相關(guān)信息字典結(jié)構(gòu)屬性名稱屬性類型是否允許為空默認(rèn)值約束含義ItemNoChar(3)NOT NULL主鍵、外鍵項(xiàng)目編號(hào)OperatorNoChar(8)NOT NULL外鍵、外鍵、職位游水號(hào)工作人員編號(hào)字典10：項(xiàng)目器材數(shù)據(jù)字典記錄項(xiàng)目器材相關(guān)信息字典結(jié)構(gòu)屬性名稱屬性類型是否允許為空默認(rèn)值約束含義ItemNoChar(3)NOT NULL主鍵、外鍵項(xiàng)目編號(hào)EquipmentNoChar(3)NOT NULL主鍵、外鍵器材編號(hào)字典11：運(yùn)動(dòng)員成績數(shù)據(jù)字典記錄運(yùn)動(dòng)員成績相關(guān)信息字典結(jié)構(gòu)屬性名稱屬性類型是否允許為空默認(rèn)值約束含義ItemNoChar(3)NOT NULL主鍵、外鍵項(xiàng)目編號(hào)AthleteNoChar(8)NOT NULL0主鍵、外鍵運(yùn)動(dòng)員編號(hào)TrackFieldJudgeNumber(1)NOT NULL田徑賽區(qū)分PropertyNumber(1)NOT NULL0:決賽;1:預(yù)賽比賽性質(zhì)GroupNoNumber(2)NULL小組號(hào)CourseNoNumber(1)NULL1-8跑道號(hào)ScoreVarChar(20)NULL成績PlaceNumber(3)NULL名次字典12：團(tuán)體賽成績數(shù)據(jù)字典記錄團(tuán)體賽成績相關(guān)信息字典結(jié)構(gòu)屬性名稱屬性類型是否允許為空默認(rèn)值約束含義ItemNoChar(3)NOT NULL主鍵、外鍵項(xiàng)目編號(hào)ClassNoChar(8)NOT NULL主鍵、外鍵、屆數(shù)流水號(hào)班級(jí)編號(hào)TrackFieldJudgeNumber(1)NOT NULL田徑賽區(qū)分GroupNoNumber(2)NULL小組號(hào)CourseNoNumber(1)NULL1-8跑道號(hào)PropertyNumber(1)NOT NULL0:決賽;1:預(yù)賽比賽性質(zhì)ScoreNumber(3)NULL成績PlaceNumber(3)NULL名次字典13：班級(jí)總分?jǐn)?shù)據(jù)字典記錄班級(jí)總分相關(guān)信息字典結(jié)構(gòu)屬性名稱屬性類型是否允許為空默認(rèn)值約束含義ClassNoChar(8)NOT NULL主鍵、外鍵、屆數(shù)流水號(hào)班級(jí)編號(hào)TotalScoreNumber(3)NULL總分PlaceNumber(3)NULL總分排名2. 數(shù)據(jù)庫設(shè)計(jì)3.1班級(jí)表設(shè)計(jì)3.1.1結(jié)構(gòu)設(shè)計(jì)/*Class*/CREATE TABLE Class(ClassNoChar(8)NOT NULL,/*班級(jí)編號(hào)*/InstituteNameVarchar2(30)NOT NULL,/*學(xué)院名稱*/FullNameVarchar2(30)NOT NULL,/*班級(jí)全稱*/constraint Class_PK primary key(ClassNo);3.2運(yùn)動(dòng)員表設(shè)計(jì)3.2.1結(jié)構(gòu)設(shè)計(jì)/*Athlete*/CREATE TABLE Athlete(AthleteNoChar(8)NOT NULL,/*運(yùn)動(dòng)員編號(hào)*/AthleteNameVarchar2(20)NOT NULL,/*運(yùn)動(dòng)員姓名*/SexVarchar2(2)NOT NULL,/*性別*/ check(Sex in(男,女) ,TelephoneVarchar2(20)NULL, /*聯(lián)系方法*/ClassNoChar(8)NOT NULL,/*班級(jí)編號(hào)*/constraint Athlete_PK primary key(AthleteNo),constraint Athlete_FK1 foreign key(ClassNo) references Class(ClassNo);3.3規(guī)則表設(shè)計(jì)3.3.1結(jié)構(gòu)設(shè)計(jì)/*RuleList*/CREATE TABLE RuleList(RuleNoChar(3)NOT NULL,/*規(guī)則編號(hào)*/RuleNameVarchar2(30)NULL,/*規(guī)則名稱*/TextPathVarchar2(200)NULL,/*文本路徑*/constraint RuleList_PK primary key(RuleNo);3.4場地表設(shè)計(jì)3.4.1結(jié)構(gòu)設(shè)計(jì)/*FieldList*/CREATE TABLE FieldList(FieldNoChar(3)NOT NULL,/*場地編號(hào)*/FieldNameVarchar2(20)NULL,/*場地名稱*/AddressVarchar2(50)NULL,/*具體地址*/constraint FieldList_PK primary key(FieldNo);3.5項(xiàng)目表設(shè)計(jì)3.5.1結(jié)構(gòu)設(shè)計(jì)/*Item*/CREATE TABLE Item(ItemNoChar(3)NOT NULL,/*項(xiàng)目編號(hào)*/ItemNameVarchar2(50)NOT NULL,/*項(xiàng)目名稱*/SexLimitchar(2)NOT NULL,/*性別限制*/ComityRequireNumber(2)NOT NULL,/*集體項(xiàng)目人數(shù)*/StartTimeDateNULL,/*開始時(shí)間*/EndTimeDateNULL,/*結(jié)束時(shí)間*/TrackFieldJudgeNumber(1)NOT NULL,/*田徑賽區(qū)分*/ClassNoLimitNumber(2)NOT NULL,/*每班最大人數(shù)*/TotalAthleteNumber(3)default 0, /*已選人數(shù)*/RuleNoChar(3)NOT NULL,/*規(guī)則編號(hào)*/FieldNoChar(3)NOT NULL,/*場地編號(hào)*/constraint Item_PK primary key(ItemNo),constraint Item_FK1 foreign key(RuleNo) references RuleList(RuleNo),constraint Item_FK2 foreign key(FieldNo) references FieldList(FieldNo);3.6記錄表設(shè)計(jì)3.6.1結(jié)構(gòu)設(shè)計(jì)/*RecordList*/CREATE TABLE RecordList(ItemNoChar(3)NOT NULL,/*項(xiàng)目編號(hào)*/ItemNameVarchar2(50)NOT NULL,/*項(xiàng)目名稱*/AthleteNoChar(8)NULL,/*運(yùn)動(dòng)員編號(hào)*/AthleteNameVarchar2(20)NOT NULL,/*運(yùn)動(dòng)員姓名*/ClassNoChar(8)NULL,/*班級(jí)編號(hào)*/ClassDiscribeVarchar2(50)NULL,/*班級(jí)描述*/GymkhanaNameVarchar2(50)NULL,/*運(yùn)動(dòng)會(huì)名*/TrackFieldJudgeNumber(1)NOT NULL,/*田徑賽區(qū)分*/ScoreNumber(3)NULL,/*成績*/constraint RecordList_PK primary key(ItemNo),constraint RecordList_FK1 foreign key(ItemNo) references Item(ItemNo),constraint RecordList_FK2 foreign key(AthleteNo) references Athlete(AthleteNo),constraint RecordList_FK3 foreign key(ClassNo) references Class(ClassNo);3.7工作人員表設(shè)計(jì)3.7.1結(jié)構(gòu)設(shè)計(jì)/*OperatorList*/CREATE TABLE OperatorList(OperatorNoChar(3)NOT NULL,/*工作人員編號(hào)*/OperatorNameVarchar2(20)NOT NULL,/*工作人員姓名*/PostVarchar2(30)NULL,/*單位*/DutyVarchar2(100)NULL,/*運(yùn)動(dòng)會(huì)職務(wù)*/constraint OperatorList_PK primary key(OperatorNo);3.8器材表設(shè)計(jì)3.8.1結(jié)構(gòu)設(shè)計(jì)/*EquipmentList*/CREATE TABLE EquipmentList(EquipmentNoChar(3)NOT NULL,/*器材編號(hào)*/EquipmentNameVarchar2(20)NOT NULL,/*器材名稱*/DepositVarchar2(100)NULL,/*存放簡介*/constraint EquipmentList_PK primary key(EquipmentNo);3.9項(xiàng)目人員表設(shè)計(jì)3.9.1結(jié)構(gòu)設(shè)計(jì)/*ItemOperator*/CREATE TABLE ItemOperator(ItemNoChar(3)NOT NULL,/*項(xiàng)目編號(hào)*/OperatorNoChar(3)NOT NULL,/*工作人員編號(hào)*/constraint ItemOperator_PK primary key(ItemNo,OperatorNo),constraint ItemOperator_FK1 foreign key(OperatorNo) references OperatorList(OperatorNo);3.10項(xiàng)目器材表設(shè)計(jì)3.10.1結(jié)構(gòu)設(shè)計(jì)/*ItemEquipment*/CREATE TABLE ItemEquipment(ItemNoChar(3)NOT NULL,/*項(xiàng)目編號(hào)*/EquipmentNoChar(3)NOT NULL,/*器材編號(hào)*/constraint ItemEquipment_PK primary key(ItemNo,EquipmentNo),constraint ItemEquipment_FK1 foreign key(ItemNo) references Item(ItemNo),constraint ItemEquipment_FK2 foreign key(EquipmentNo) references EquipmentList(EquipmentNo);3.11運(yùn)動(dòng)員得分表設(shè)計(jì)3.11.1結(jié)構(gòu)設(shè)計(jì)/*AthleteScore*/CREATE TABLE AthleteScore(ItemNoChar(3)NOT NULL,/*項(xiàng)目編號(hào)*/AthleteNoChar(8)default 0,/*運(yùn)動(dòng)員編號(hào)*/TrackFieldJudgeNumber(1)NOT NULL,/*田徑賽區(qū)分*/PropertyNumber(1)NOT NULL/*比賽性質(zhì)*/ check(Property in(0,1),GroupNoNumber(2)NULL,/*小組號(hào)*/CourseNoNumber(1)NULL/*跑道號(hào)*/check(CourseNo between 1 and 8),ScoreVarChar(20)NULL,/*成績*/PlaceNumber(3)NULL,/*名次*/constraint AthleteScore_PK primary key(ItemNo,AthleteNo),constraint AthleteScore_FK1 foreign key(ItemNo) references Item(ItemNo);3.12集體項(xiàng)目得分表設(shè)計(jì)3.12.1結(jié)構(gòu)設(shè)計(jì)/*ComityScore*/CREATE TABLE ComityScore(ItemNoChar(3)NOT NULL,/*項(xiàng)目編號(hào)*/ClassNoChar(8)default 0,/*班級(jí)編號(hào)*/TrackFieldJudgeNumber(1)NOT NULL,/*田徑賽區(qū)分*/GroupNoNumber(2)NULL,/*小組號(hào)*/CourseNoNumber(1)NULL /*跑道號(hào)*/check(CourseNo between 1 and 8),ScoreVarChar(20)NULL,/*成績*/PropertyNumber(1)NOT NULL/*比賽性質(zhì)*/ check(Property in(0,1),PlaceNumber(3)NULL,/*名次*/constraint ComityScore_PK primary key(ItemNo,ClassNo),constraint ComityScore_FK1 foreign key(ItemNo) references Item(ItemNo),constraint ComityScore_FK2 foreign key(ClassNo) references Class(ClassNo);3.13班級(jí)總得分表設(shè)計(jì)3.13.1結(jié)構(gòu)設(shè)計(jì)/*ClassTotalScore*/CREATE TABLE ClassTotalScore(ClassNoChar(8)default 0,/*班級(jí)編號(hào)*/TotalScoreNumber(3)NULL,/*總分*/PlaceNumber(3)NULL,/*總分排名*/constraint ClassTotalScore_PK primary key(ClassNo),constraint ClassT