[计算机软件及应用]数据库设计.doc

宁夏大学数学计算机学院数据库设计报告 指导老师：毕利 专 业：软件工程 学 生：李可新 学 号：12009242539 班 级：09级（7）班 2012年6月22号实验目的熟悉数据库设计的基本方法和步骤，搞清数据库设计各个阶段所要完成的任务。通过该实验更加清楚地了解数据库设计的过程。实验准备1熟悉Power Designer设计环境；2熟悉SQL语言命令；3熟悉E-R图的绘制； 4熟悉数据库设计的方法、步骤实验要求 1在实验之前做好实验准备 2. 完成数据库设计，并验收实验结果，提交实验报告实验内容医疗保险综合管理系统数据库设计。医疗保险综合管理系统具体要求如下：l 医保中心为参保单位人员建档，并确定应缴费金额。l 医保中心为参保人员建立个人帐户并发医保卡。l 参保单位或人员定期（按月）到医保中心缴费，所缴费用按一定比例划入统筹帐户及个人帐户。l 对未缴费或欠缴单位人员，医保中心发出催缴通知。l 参保人员持医保卡到指定医院门诊看病，费用由个人帐户支付，超支自理。l 参保人员到指定医院住院治疗，费用按统筹基金支付，同时个人也要负担一定比例。统筹基金支付实行起付标准、分段计算、累加支付的办法。l 医保中心与定点医院药房结算费用，交换数据（如黑名单等）。l 凡参加基本医疗保险，按时足额缴纳基本医疗保险费的，方可享受医疗保险待遇。征缴业务征缴业务是医保经办机构的核心业务之一，它是依据国家医疗保险征缴政策对参保单位和职工缴纳保费所开展的业务。1.1档案管理完成参保单位档案的新建，变更维护，注销等功能，建立参保单位档案后，根据单位提供的职工列表，完成参保个人基本档案的新建，变更维护，注销等功能。建立个人基本档案后，办理一个医疗证卡用于标识身份，同时为每个参保职工建立一个个人帐户。1.2征缴计划每月生成所有参保单位应缴金额（注意职工的某些异动直接影响征缴计划）。征缴标准如下：u 基础数据月社会平均工资(X)（上级提供）职工月工资(Y)（职工基本信息中含有，参保单位提供）u 缴费基数: 每个参保职工缴费基数(Z)：YX*300%Z= X*300% X*60%Y X*300%Z= Y最后参保单位缴费金额（M）：全体职工Z的总和*9%1.3 收款处理收取现金做交费处理，打印现金收据。检索本月未交费单位，按单位制作催缴单（每个单位一份邮递）和催缴明细表（一份含所有欠费单位，内部电话催缴用）催缴后仍未缴费单位经领导核实后做冻结处理（对统筹基金、个人帐户同时冻结），个人将无法使用基金帐户。1.4 划拨处理单位缴费后，每月定期向个人帐户中的自有资金划拨一定资金，该资金是个人支配，仅用于医疗支出。根据以下标准，系统每月划拨一次。u 个人帐户划拨比例 在职：年令 30 （2+0.7） 3145 （2+1.5） 退休：（符合医保要求年令） 4u 个人帐户月划拨金额=个人月工资*个人帐户划拨比例扣除划拨给个人账户资金之外的收入，就是由医保中心支配的统筹基金。支付业务支付业务是医保经办机构（各医院）的核心业务之一，它是依据医保基金支付政策和经办机构的管理办法对参保职工就诊所发生的费用进行偿付所开展的业务。它由门诊收费、住院收费、特殊报销及审核与结算组成。具体如下：2.1住院收费业务流程病人持身份证、证卡在住院处办理入院登记手续，住院期间证卡停止门诊使用；住院期间发生的费用按日期分类记录，在病人出院时按照医疗保险政策计算各项费用：总额、统筹记帐金额、个人自付金额及对病人转科业务的处理。入院登记登记入院病人基本信息住院费用录入录入病人住院发生的费用出院结算计算病人住院期间发生的费用，办理出院手续转科办理病人转科手续住院病人查询查询住院病人费用情况2.2入院登记每位病人住院都需要登记其基本信息,作为这位病人的基本信息以供使用。2.3 住院费用录入录入分类费用(由费用字典提供)，记录费用发生时所在科室。确认后可以计算个人自付金额，检查预交款。2.4出院结算 根据结算政策计算各项费用，填写出院疗效：痊愈、好转、未愈、死亡，打印住院费用结算单。 结算方法如下：1、 首先按照住院次数，计算本次住院的最低起付金额，只有超出起付金额后才会采用医疗保险政策来计算，不足起付金额时，所有费用都由个人承担；2、 然后按照每个项目的自负比例计算个人应付金额和进入统筹金额；3、 对于计入统筹金额的部分，再根据统筹分段，按照比例计算出个人应付金额和统筹基金支付金额；4、 合计个人应付金额，先从个人账户中扣除，不足时收取现金，统筹基金支付金额也记入个人账户；5、 每人每年累计统筹基金支付金额最高不超过20000，超出后将全部自负。2.5转科实现科室的更改，每次费用总是记录在当前所在科室。实验步骤 根据需求建立ER模型：分析需求，并用图形表示实验要求的ER模型。 ER模型转换为数据库表l 实体转化为表l 实体的属性转化为表中的列l 多对多联系也转化为表，主键确定为相联系表中主键的组合，联系本身的属性转化为表的列l 一对一、一对多联系无需新建表，被决定的一方应含有决定方的主键作为外部键代码：829A5A30-C3E6-4422-90BB-C7F2AFFE7A3C李可新_医疗保险综合信息管理数据库物理模型李可新_医疗保险综合信息管理数据库物理模型1340087152李可新1340087210李可新ORG 29B11162-8CE5-4AEB-A1FA-4C49652BEDE3DAT 1340087170ATT NAMEATT DISPNAMEATT CODEATT FOPTFolderOptionsFolderOptionsPhysical ObjectsGenerationCheckModel=YesGenerationPath=GenerationOptions=GenerationTasks=GenerationTargets=GenerationSelections=RevPkey=YesRevFkey=YesRevAkey=YesRevCheck=YesRevIndx=YesRevOpts=YesRevViewAsTabl=NoRevViewOpts=YesRevSystAsTabl=YesRevTablPerm=NoRevViewPerm=NoRevProcPerm=NoRevDbpkPerm=NoRevSqncPerm=NoRevAdtPerm=NoRevUserPriv=NoRevUserOpts=NoRevGrpePriv=NoRevRolePriv=NoRevDtbsOpts=YesRevDtbsPerm=NoRevViewIndx=YesRevJidxOpts=YesRevStats=NoRevTspcPerm=NoRevCaseSensitive=NoGenTrgrStdMsg=YesGenTrgrMsgTab=GenTrgrMsgNo=GenTrgrMsgTxt=TrgrPreserve=NoTrgrIns=YesTrgrUpd=YesTrgrDel=YesTrgrC2Ins=YesTrgrC2Upd=YesTrgrC3=YesTrgrC4=YesTrgrC5=YesTrgrC6=YesTrgrC7=YesTrgrC8=YesTrgrC9=YesTrgrC10=YesTrgrC11=YesTrgrC1=YesTrgrC12Ins=YesTrgrC12Upd=YesTrgrC13=YesUpdateTableStatistics=YesUpdateColumnStatistics=YesFolderOptionsPhysical ObjectsDatabase GenerationGenScriptName=crebasGenScriptName0=GenScriptName1=GenScriptName2=GenScriptName3=GenScriptName4=GenScriptName5=GenScriptName6=GenScriptName7=GenScriptName8=GenScriptName9=GenPathName=GenSingleFile=YesGenODBC=NoGenCheckModel=YesGenScriptPrev=YesGenArchiveModel=NoGenUseSync=NoGenSyncChoice=0GenSyncArch=GenSyncRmg=0FolderOptionsPhysical ObjectsDatabase GenerationFormatGenScriptTitle=YesGenScriptNamLabl=NoGenScriptQDtbs=YesGenScriptQOwnr=YesGenScriptCase=0GenScriptEncoding=ANSIGenScriptNAcct=NoIdentifierDelimiter="FolderOptionsPhysical ObjectsDatabase GenerationDatabaseCreate=YesOpen=YesClose=YesDrop=YesPermission=NoFolderOptionsPhysical ObjectsDatabase GenerationDatabaseCreatePhysical Options=YesHeader=YesFooter=YesFolderOptionsPhysical ObjectsDatabase GenerationTablespaceCreate=YesDrop=YesComment=YesPermission=NoFolderOptionsPhysical ObjectsDatabase GenerationTablespaceCreateHeader=YesFooter=YesFolderOptionsPhysical ObjectsDatabase GenerationStorageCreate=YesDrop=YesComment=YesFolderOptionsPhysical ObjectsDatabase GenerationUserCreate=YesGrant=YesDrop=YesComment=YesPrivilege=NoFolderOptionsPhysical ObjectsDatabase GenerationUserCreatePhysical Options=NoFolderOptionsPhysical ObjectsDatabase GenerationGroupCreate=YesDrop=YesComment=YesPrivilege=NoFolderOptionsPhysical ObjectsDatabase GenerationRoleCreate=YesDrop=YesPrivilege=NoFolderOptionsPhysical ObjectsDatabase GenerationUserDefinedDataTypeCreate=YesComment=YesDrop=YesFolderOptionsPhysical ObjectsDatabase GenerationUserDefinedDataTypeCreateDefault value=YesCheck=YesFolderOptionsPhysical ObjectsDatabase GenerationAbstractDataTypeCreate=YesHeader=YesFooter=YesDrop=YesComment=YesInstall JAVA class=YesRemove JAVA class=YesPermission=NoFolderOptionsPhysical ObjectsDatabase GenerationRuleCreate=YesDrop=YesComment=YesFolderOptionsPhysical ObjectsDatabase GenerationDefaultCreate=YesComment=YesDrop=YesFolderOptionsPhysical ObjectsDatabase GenerationSequenceCreate=YesDrop=YesComment=YesPermission=NoFolderOptionsPhysical ObjectsDatabase GenerationTable&&ColumnFolderOptionsPhysical ObjectsDatabase GenerationTable&&ColumnTableCreate=YesDrop=YesComment=YesPermission=NoFolderOptionsPhysical ObjectsDatabase GenerationTable&&ColumnTableCreateCheck=YesPhysical Options=YesHeader=YesFooter=YesFolderOptionsPhysical ObjectsDatabase GenerationTable&&ColumnTableCreateCheckConstraint declaration=NoFolderOptionsPhysical ObjectsDatabase GenerationTable&&ColumnColumnUser datatype=YesDefault value=YesCheck=YesPhysical Options=YesComment=YesFolderOptionsPhysical ObjectsDatabase GenerationTable&&ColumnColumnCheckConstraint declaration=NoFolderOptionsPhysical ObjectsDatabase GenerationTable&&ColumnKeyFolderOptionsPhysical ObjectsDatabase GenerationTable&&ColumnKeyPrimary keyCreate=YesDrop=YesComment=YesFolderOptionsPhysical ObjectsDatabase GenerationTable&&ColumnKeyPrimary keyCreateConstraint declaration=NoPhysical Options=YesFolderOptionsPhysical ObjectsDatabase GenerationTable&&ColumnKeyAlternate keyCreate=YesDrop=YesComment=YesFolderOptionsPhysical ObjectsDatabase GenerationTable&&ColumnKeyAlternate keyCreateConstraint declaration=NoPhysical Options=YesFolderOptionsPhysical ObjectsDatabase GenerationTable&&ColumnForeign keyCreate=YesDrop=YesComment=YesFolderOptionsPhysical ObjectsDatabase GenerationTable&&ColumnForeign keyCreateConstraint declaration=YesFolderOptionsPhysical ObjectsDatabase GenerationTable&&ColumnIndexCreate=YesDrop=YesComment=YesFolderOptionsPhysical ObjectsDatabase GenerationTable&&ColumnIndexCreateConstraint declaration=YesPhysical Options=YesFolderOptionsPhysical ObjectsDatabase GenerationTable&&ColumnIndexFilterPrimary key=YesForeign key=YesAlternate key=YesCluster=YesOther=YesFolderOptionsPhysical ObjectsDatabase GenerationTable&&ColumnTriggerCreate=YesDrop=YesComment=YesFolderOptionsPhysical ObjectsDatabase GenerationTable&&ColumnTriggerFilterFor insert=YesFor update=YesFor delete=YesFor other=YesFolderOptionsPhysical ObjectsDatabase GenerationViewCreate=YesDrop=YesComment=YesPermission=NoFolderOptionsPhysical ObjectsDatabase GenerationViewCreateForce Column list=NoPhysical Options=YesHeader=YesFooter=YesFolderOptionsPhysical ObjectsDatabase GenerationViewViewColumnComment=YesFolderOptionsPhysical ObjectsDatabase GenerationViewViewIndexCreate=YesDrop=YesComment=YesFolderOptionsPhysical ObjectsDatabase GenerationViewViewIndexCreatePhysical Options=YesFolderOptionsPhysical ObjectsDatabase GenerationViewViewIndexFilterCluster=YesOther=YesFolderOptionsPhysical ObjectsDatabase GenerationViewTriggerCreate=YesDrop=YesComment=YesFolderOptionsPhysical ObjectsDatabase GenerationViewTriggerFilterFor insert=YesFor update=YesFor delete=YesFor other=YesFolderOptionsPhysical ObjectsDatabase GenerationDBMSTriggerCreate=YesDrop=YesComment=YesFolderOptionsPhysical ObjectsDatabase GenerationSynonymCreate=YesDrop=YesFolderOptionsPhysical ObjectsDatabase GenerationSynonymFilterTable=YesView=YesProc=YesSynonym=YesDatabase Package=YesSequence=YesFolderOptionsPhysical ObjectsDatabase GenerationJoinIndexCreate=YesDrop=YesComment=YesFolderOptionsPhysical ObjectsDatabase GenerationJoinIndexCreatePhysical Options=YesHeader=YesFooter=YesFolderOptionsPhysical ObjectsDatabase GenerationProcedureCreate=YesDrop=YesComment=YesPermission=NoFolderOptionsPhysical ObjectsDatabase GenerationProcedureCreateHeader=YesFooter=YesFolderOptionsPhysical ObjectsDatabase GenerationDatabasePackageCreate=YesDrop=YesPermission=NoFolderOptionsPhysical ObjectsDatabase GenerationWebServiceCreate=YesDrop=YesComment=YesFolderOptionsPhysical ObjectsDatabase GenerationDimensionCreate=YesDrop=YesFolderOptionsPhysical ObjectsDatabase GenerationSynchronizationGenBackupTabl=1GenKeepBackTabl=1GenTmpTablDrop=NoGenKeepTablOpts=NoFolderOptionsPhysical ObjectsDatabase GenerationExtendedObject <<Event>>Create=YesDrop=YesComment=YesFolderOptionsPhysical ObjectsTest DataGenDataPathName=GenDataSinglefile=YesGenDataScriptName=testdataGenDataScriptName0=GenDataScriptName1=GenDataScriptName2=GenDataScriptName3=GenDataScriptName4=GenDataScriptName5=GenDataScriptName6=GenDataScriptName7=GenDataScriptName8=GenDataScriptName9=GenDataOdbc=0GenDataDelOld=NoGenDataTitle=NoGenDataDefNumRows=20GenDataCommit=0GenDataPacket=0GenDataOwner=NoGenDataProfNumb=GenDataProfChar=GenDataProfDate=GenDataCSVSeparator=,GenDataFileFormat=CSVGenDataUseWizard=NoFolderOptionsPdmIndxIQName=%COLUMN%_%INDEXTYPE%IndxPK=YesIndxFK=YesIndxAK=YesIndxPKName=%TABLE%_PKIndxFKName=%REFR%_FKIndxAKName=%AKEY%_AKIndxPreserve=NoIndxThreshold=0IndxStats=NoRefrPreserve=NoJidxPreserve=NoRbldMultiFact=YesRbldMultiDim=YesRbldMultiJidx=YesCubePreserve=NoTablStProcPreserve=NoProcDepPreserve=YesTrgrDepPreserve=YesCubeScriptPath=CubeScriptCase=0CubeScriptEncoding=ANSICubeScriptNacct=NoCubeScriptHeader=NoCubeScriptExt=csvCubeScriptExt0=txtCubeScriptExt1=CubeScriptExt2=CubeScriptSep=,CubeScriptDeli="DfltDomnName=D_%.U:VALUE%DfltColnName=D_%.U:VALUE%DfltReuse=YesDfltDrop=YesModelOptionsModelOptionsPhysical ObjectsCaseSensitive=NoDisplayName=YesEnableTrans=NoEnableRequirements=NoDefaultDttp=IgnoreOwner=NoRebuildTrigger=YesRefrUnique=YesRefrAutoMigrate=YesRefrMigrateReuse=YesRefrMigrateDomain=YesRefrMigrateCheck=YesRefrMigrateRule=YesRefrMigrateExtd=NoRefrMigrDefaultLink=NoRefrDfltImpl=DRefrPrgtColn=NoRefrMigrateToEnd=NoRebuildTriggerDep=NoColnFKName=%.3:PARENT%_%COLUMN%ColnFKNameUse=NoDomnCopyDttp=YesDomnCopyChck=NoDomnCopyRule=NoDomnCopyMand=NoDomnCopyExtd=NoDomnCopyProf=NoNotation=0DomnDefaultMandatory=NoColnDefaultMandatory=NoTablDefaultOwner=ViewDefaultOwner=TrgrDefaultOwnerTabl=TrgrDefaultOwnerView=IdxDefaultOwnerTabl=IdxDefaultOwnerView=JdxDefaultOwner=DBPackDefaultOwner=SeqDefaultOwner=ProcDefaultOwner=DBMSTrgrDefaultOwner=Currency=USDRefrDeleteConstraint=1RefrUpdateConstraint=1RefrParentMandatory=NoRefrParentChangeAllow=YesRefrCheckOnCommit=NoModelOptionsPhysical ObjectsNamingOptionsTemplatesModelOptionsPhysical ObjectsClssNamingOptionsModelOptionsPhysical ObjectsClssNamingOptionsPDMPCKGModelOptionsPhysical ObjectsClssNamingOptionsPDMPCKGNameTemplate=MaxLen=254Case=MValidChar=InvldChar=AllValid=YesNoAccent=NoDefaultChar=Script=ConvTable=ConvTablePath=%_HOME%Resource FilesConversion TablesModelOptionsPhysical ObjectsClssNamingOptionsPDMPCKGCodeTemplate=MaxLen=254Case=MValidChar=InvldChar=AllValid=YesNoAccent=NoDefaultChar=Script=ConvTable=ConvTablePath=%_HOME%Resource FilesConversion TablesModelOptionsPhysical ObjectsClssNamingOptionsPDMDOMNModelOptionsPhysical ObjectsClssNamingOptionsPDMDOMNNameTemplate=MaxLen=254Case=MValidChar=InvldChar=AllValid=YesNoAccent=NoDefaultChar=Script=ConvTable=ConvTablePath=%_HOME%Resource FilesConversion TablesModelOptionsPhysical ObjectsClssNamingOptionsPDMDOMNCodeTemplate=MaxLen=254Case=MValidChar=InvldChar=AllValid=YesNoAccent=NoDefaultChar=Script=ConvTable=ConvTablePath=%_HOME%Resource FilesConversion TablesModelOptionsPhysical ObjectsClssNamingOptionsTABLModelOptionsPhysical ObjectsClssNamingOptionsTABLNameTemplate=MaxLen=254Case=MValidChar=InvldChar=AllValid=YesNoAccent=NoDefaultChar=Script=ConvTable=ConvTablePath=%_HOME%Resource FilesConversion TablesModelOptionsPhysical ObjectsClssNamingOptionsTABLCodeTemplate=MaxLen=254Case=MValidChar=InvldChar=AllValid=YesNoAccent=NoDefaultChar=Script=ConvTable=ConvTablePath=%_HOME%Resource FilesConversion TablesModelOptionsPhysical ObjectsClssNamingOptionsCOLNModelOptionsPhysical ObjectsClssNamingOptionsCOLNNameTemplate=MaxLen=254Case=MValidChar=InvldChar=AllValid=YesNoAccent=NoDefaultChar=Script=ConvTable=ConvTablePath=%_HOME%Resource FilesConversion TablesModelOptionsPhysical ObjectsClssNamingOptionsCOLNCodeTemplate=MaxLen=254Case=MValidChar=InvldChar=AllValid=YesNoAccent=NoDefaultChar=Script=ConvTable=ConvTablePath=%_HOME%Resource FilesConversion TablesModelOptionsPhysical ObjectsClssNamingOptionsINDXModelOptionsPhysical ObjectsClssNamingOptionsINDXNameTemplate=MaxLen=254Case=MValidChar=InvldChar=AllValid=YesNoAccent=NoDefaultChar=Script=ConvTable=ConvTablePath=%_HOME%Resource FilesConversion TablesModelOptionsPhysical ObjectsClssNamingOptionsINDXCodeTemplate=MaxLen=254Case=MValidChar=InvldChar=AllValid=YesNoAccent=NoDefaultChar=Script=ConvTable=ConvTablePath=%_HOME%Resource FilesConversion TablesModelOptionsPhysical ObjectsClssNamingOptionsREFRModelOptionsPhysical ObjectsClssNamingOptionsREFRNameTemplate=MaxLen=254Case=MValidChar=InvldChar=AllValid=YesNoAccent=NoDefaultChar=Script=ConvTable=ConvTablePath=%_HOME%Resource FilesConversion TablesModelOptionsPhysical ObjectsClssNamingOptionsREFRCodeTemplate=MaxLen=254Case=MValidChar=InvldChar=AllValid=YesNoAccent=NoDefaultChar=Script=ConvTable=ConvTablePath=%_HOME%Resource FilesConversion TablesModelOptionsPhysical ObjectsClssNamingOptionsVREFModelOptionsPhysical ObjectsClssNamingOptionsVREFNameTemplate=MaxLen=254Case=MValidChar=InvldChar=AllValid=YesNoAccent=NoDefaultChar=Script=ConvTable=ConvTablePath=%_HOME%Resource FilesConversion TablesModelOptionsPhysical ObjectsClssNamingOptionsVREFCodeTemplate=MaxLen=254Case=MValidChar=InvldChar=AllValid=YesNoAccent=NoDefaultChar=Script=ConvTable=ConvTablePath=%_HOME%Resource FilesConversion TablesModelOptionsPhysical ObjectsClssNamingOptionsVIEWModelOptionsPhysical ObjectsClssNamingOptionsVIEWNameTemplate=MaxLen=254Case=MValidChar=InvldChar=AllValid=YesNoAccent=NoDefaultChar=Script=ConvTable=ConvTablePath=%_HOME%Resource FilesConversion TablesModelOptionsPhysical ObjectsClssNamingOptionsVIEWCodeTemplate=MaxLen=254Case=MValidChar=InvldChar=AllValid=YesNoAccent=NoDefaultChar=Script=ConvTable=ConvTablePath=%_HOME%Resource FilesConversion TablesModelOpti