高校社团管理系统数据库设计(8)

FundsAccount（AccountID，FundsNum，UseReason，AssociationName，SchoolYear）外码：AssociationName，SchoolYear FundsAccountWrong（AccountWrongID，AccountID，WrongReason，IsDeal）外码：AccountID

4.逻辑结构设计

AssociationInfmYearAssociationNameSchoolYearPresidentNamePresidentPhoneMemberNumTotalScoreTotalFundsUseFundsStarGradevarchar(50)numericvarchar(10)char(11)numericnumericmoneymoneyvarchar(4)FK_ASSOCIAT_ASSOCIATI_SCHOOLYESchoolYearSchoolYearnumericFK_FUNDSACC_RELATIONS_SCHOOLYEFundsAccountAccountIDSchoolYearFundsNumUseReasonnumeric(1,1)numericmoneyvarchar(500)FK_FUNDSACC_RELATIONS_FUNDSACCFK_ASSOCIAT_ASSOCIATI_ASSOCIATScoreOperateScoreOperateIDFK_SCOREOPE_RELATIONS_SCHOOLYESchoolYearUserIDAssociationNameScoreScoreOprReasonFK_SCOREOPE_RELATIONS_ASSOCIATIsRightnumeric(1,1)numericchar(256)varchar(50)numericvarchar(500)char(1)FundsAccountWrongAccountWrongIDAccountIDWrongReasonIsDealnumeric(1,1)numeric(1,1)varchar(500)char(1)AssociationInfmAssociationNamevarchar(50)FK_SCOREOPE_RELATIONS_SYSTEMUSFK_ACTIVITY_RELATIONS_ASSOCIATSystemUserUserIDRoleNameUserNamePasswordDepartNameIsCheckchar(256)varchar(50)varchar(10)varchar(20)varchar(50)char(1)ScoreDisOptReasonDisScoreOperateIDScoreOperateIDDisReasonIsDealFK_SCOREDIS_RELATIONS_SCOREOPEnumeric(1,1)numeric(1,1)varchar(500)char(1)ActivityInfmActivityIDAssociationNameActivityNameStartTimeEndTimeSpaceCreditFormNumFundsNumIsProcedureDoneIsProposalAgreeIsLeagueAgreeCheckPursonScorenumeric(1,1)varchar(50)varchar(50)datetimedatetimevarchar(100)numericmoneychar(1)char(1)char(1)varchar(10)numericFK_SYSTEMUS_RELATIONS_USERROLEFK_ACTIVITY_RELATIONS_ACTIVITYActivityDisReasonDisActivityIDActivityIDDisReasonIsRightnumericnumeric(1,1)varchar(500)char(1)UserRoleNameRoleNamevarchar(50) 图4.1 逻辑结构设计PDM图

5.物理结构设计

/*==============================================================*/ /* DBMS name: Microsoft SQL Server 2005 */ /* Created on: 2011/6/29 23:51:54 */ /*==============================================================*/

alter table ActivityDisReason

drop constraint FK_ACTIVITY_RELATIONS_ACTIVITY go

alter table ActivityInfm

drop constraint FK_ACTIVITY_RELATIONS_ASSOCIAT go

36

alter table AssociationInfmYear

drop constraint FK_ASSOCIAT_ASSOCIATI_ASSOCIAT go

alter table AssociationInfmYear

drop constraint FK_ASSOCIAT_ASSOCIATI_SCHOOLYE go

alter table FundsAccount

drop constraint FK_FUNDSACC_RELATIONS_SCHOOLYE go

alter table FundsAccountWrong

drop constraint FK_FUNDSACC_RELATIONS_FUNDSACC go

alter table ScoreDisOptReason

drop constraint FK_SCOREDIS_RELATIONS_SCOREOPE go

alter table ScoreOperate

drop constraint FK_SCOREOPE_RELATIONS_SCHOOLYE go

alter table ScoreOperate

drop constraint FK_SCOREOPE_RELATIONS_SYSTEMUS go

alter table ScoreOperate

drop constraint FK_SCOREOPE_RELATIONS_ASSOCIAT go

alter table SystemUser

drop constraint FK_SYSTEMUS_RELATIONS_USERROLE go

if exists (select 1

from sysindexes

where id = object_id('ActivityDisReason') and name = 'Relationship_5_FK' and indid > 0 and indid < 255)

drop index ActivityDisReason.Relationship_5_FK go

if exists (select 1

37

from sysindexes

where id = object_id('ActivityInfm') and name = 'Relationship_2_FK' and indid > 0 and indid < 255)

drop index ActivityInfm.Relationship_2_FK go

if exists (select 1

from sysindexes

where id = object_id('AssociationInfmYear') and name = 'AssociationInfmYear2_FK' and indid > 0 and indid < 255)

drop index AssociationInfmYear.AssociationInfmYear2_FK go

if exists (select 1

from sysindexes

where id = object_id('AssociationInfmYear') and name = 'AssociationInfmYear_FK' and indid > 0 and indid < 255)

drop index AssociationInfmYear.AssociationInfmYear_FK go

if exists (select 1

from sysindexes

where id = object_id('FundsAccount') and name = 'Relationship_8_FK' and indid > 0 and indid < 255)

drop index FundsAccount.Relationship_8_FK go

if exists (select 1

from sysindexes

where id = object_id('FundsAccountWrong') and name = 'Relationship_9_FK' and indid > 0 and indid < 255)

drop index FundsAccountWrong.Relationship_9_FK go

if exists (select 1

from sysindexes

38

where id = object_id('ScoreDisOptReason') and name = 'Relationship_7_FK' and indid > 0 and indid < 255)

drop index ScoreDisOptReason.Relationship_7_FK go

if exists (select 1

from sysindexes

where id = object_id('ScoreOperate') and name = 'Relationship_10_FK' and indid > 0 and indid < 255)

drop index ScoreOperate.Relationship_10_FK go

if exists (select 1

from sysindexes

where id = object_id('ScoreOperate') and name = 'Relationship_11_FK' and indid > 0 and indid < 255)

drop index ScoreOperate.Relationship_11_FK go

if exists (select 1

from sysindexes

where id = object_id('ScoreOperate') and name = 'Relationship_12_FK' and indid > 0 and indid < 255)

drop index ScoreOperate.Relationship_12_FK go

if exists (select 1

from sysindexes

where id = object_id('SystemUser') and name = 'Relationship_1_FK' and indid > 0 and indid < 255)

drop index SystemUser.Relationship_1_FK go

if exists (select 1

from sysobjects

where id = object_id('ActivityDisReason')

39

and type = 'U') drop table ActivityDisReason go

if exists (select 1

from sysobjects

where id = object_id('ActivityInfm') and type = 'U') drop table ActivityInfm go

if exists (select 1

from sysobjects

where id = object_id('AssociationInfm') and type = 'U') drop table AssociationInfm go

if exists (select 1

from sysobjects

where id = object_id('AssociationInfmYear') and type = 'U') drop table AssociationInfmYear go

if exists (select 1

from sysobjects

where id = object_id('FundsAccount') and type = 'U') drop table FundsAccount go

if exists (select 1

from sysobjects

where id = object_id('FundsAccountWrong') and type = 'U') drop table FundsAccountWrong go

if exists (select 1

from sysobjects

where id = object_id('SchoolYear') and type = 'U') drop table SchoolYear go

40