数据库设计与开发

数据库设计与开发分析与设计。在与相关人员进行了充分的交流，库的基本信息之后，进行基本的需求分析，通1绪论......................................错误!未定义书签。系统简介...................................错误!未定义书签。2需求分析..................................错误!未定义书签。.需求分析的任务...........................错误!未定义书签。3概念模型设计..............................错误!未定义书签。概念模型设计的方法和步骤..................错误!未定义书签。5物理数据库模型设计与实现.............总结........................................错误!未定义书签。参考文献....................................错误!未定义书签。光影传媒公司是成立于19XX年的一家传媒公司，公家分公司。该公司囊括电影、电视、音乐、动漫等行业，公司内的部门有董事会、营销部、策划部、后勤部等。公司现有职工一千多人，其学历、收入、所在部门各不相同，同时，有时由于业务需求，各部门之间会有一定的人事调动。对于如此庞大且繁杂的数据系统，如果采用传统的数据管理显然不能满足管理的需求。于是，要求设计一个适合于光影传媒公司人事信息管理数据库，用以实现统计、管理公司内各职员的基本信息、各个部门的情况、员工的转入或转出、人员的调动，以及公司的各项事务、各项事务的负责情况、进展情况、实施情况和一些相关的后续情况等。要求对于建成的数号、项目名称、项目进展情况等；记录各个员工的转入、转出、调动情况；各个部门的具体情况、负责人、各项目的负责人等。用以记录公司正常运营分析光影传媒公司人事信息管理数据库所要求要求，做出具体的需求分析，在需求分析之后，需要进行设计本业务之后，设计观影传媒公司人事信息管理的业务、数据流程图；及关系，设计出业务基本的概念模型，并对其进行适当的优化；进行整个数据库设计必须准确了解与分析用户需求（包括数据和处理）。需求分析是整个设计过程的基础，是最困难、最耗费时间的一步。作户对数据库的如下需求：①公司员工信息：员工编号、员工姓名、性别、学历、所属②公司部门信息：部门编号、部门名称、部门负责人、部③项目信息：项目编号、项目名称、项目描述、项目负责参与部门、项目开始时间、项目结束时间；④员工出勤信息：员工编号、员工姓名、日期、出勤状况⑤员工参与项目信息：员工编号、员工姓名、项目编号①数据的录入：对公司的员工的基本信息、部门的基本信息、本信息等进行信息的录入，并对数据进行存储；项目信息等进行增加、修改、删除、查询等功能；及公司的信息进行查询，仅有数据库管理人程如图所示：楚，因此在数据字典中主要对数据流程图中量间23期4注5678编号录录录录息息息素息息资度天天天天式录录录天称录况录概念模型设计的方法和步骤打开软件PowerDesigner，使用其辅助设计概念模型：1）在PowerD2）在设计页面下点击实体图标，按照需求分析的结果建立实体，并一一对实体的属性进行设置；3）在实体和属性建立完成之后，建立各个实体之间的联系。在所有实体、属性与实体之间的联系建立好后，基本的概念模型便设计好了。设计的结果如图所示：根据上面的关系模式，数据库的完整性约束如下所示：3）用户自定义完整性约束员工表中，员工编号必须以Y开头，后面根7位数字；部门表中，部门编号必须以D开头，后面跟5位数字；项目表中，项目编号必须以P开头，后面根5位数字；项目表中，起始日期必须早于截止日期物理数据库模型的图示对之前已经生成的CDM模型进行检查，主页面选择Tool中的GeneratePhsicalDataModel命令进行设置，待设置完成后，点击确定按钮，生成PDM。如图所示：表格形式表示：ifexists(select1fromrjoinoon=and='F')where=object__id('Relationship__4')and='FK__RELATION__RELATIONS__JIANGCHE')altertableRelationship__4dropconstraintFK__RELATION__RELATIONS__JIANGCHEgoifexists(select1fromrjoinoon=and='F')where=object__id('Relationship__4')and='FK__RELATION__RELATIONS__YUANGONG')altertableRelationship__4dropconstraintFK__RELATION__RELATIONS__YUANGONGgoifexists(select1fromrjoinoon=and='F')where=object__id('Relationship__6')and='FK__RELATION__RELATIONS__BUMENXIN')altertableRelationship__6dropconstraintFK__RELATION__RELATIONS__BUMENXINgoifexists(select1fromrjoinoon=and='F')where=object__id('Relationship__6')and='FK__RELATION__RELATIONS__XIANGMUX')altertableRelationship__6dropconstraintFK__RELATION__RELATIONS__XIANGMUXgoifexists(select1fromrjoinoon=and='F')where=object__id('chuqinxinxi')and='FK__CHUQINXI__RELATIONS__YUANGONG')altertablechuqinxinxidropconstraintFK__CHUQINXI__RELATIONS__YUANGONGgoifexists(select1fromrjoinoon=and='F')where=object__id('gongzixinxi')and='FK__GONGZIXI__RELATIONS__YUANGONG')altertablegongzixinxidropconstraintFK__GONGZIXI__RELATIONS__YUANGONGgoifexists(select1fromrjoinoon=and='F')where=object__id('yuangongxixi')and='FK__YUANGONG__RELATIONS__BUMENXIN')altertableyuangongxixidropconstraintFK__YUANGONG__RELATIONS__BUMENXINgoifexists(select1fromrjoinoon=and='F')where=object__id('zhuanruzhuanchu')and='FK__ZHUANRUZ__RELATIONS__YUANGONG')altertablezhuanruzhuanchudropconstraintFK__ZHUANRUZ__RELATIONS__YUANGONGgoifexists(selectfromwhereandandanddropindexgo1sysindexesid=object__id('Relationship__4')name='Relationship5FK'indid>0indid<255)ifexists(selectfromwhereandand1sysindexesid=object__id('Relationship__4')name='Relationship4FK'indid>0andindid<255)dropindexgoifexists(select1fromsysobjectswhereid=object__id('Relationship__4')andtype='U')droptableRelationship__4goifexists(select1fromsysindexeswhereid=object__id('Relationship__6')andname='Relationship8FK'andindid>0andindid<255)dropindexgoifexists(selectfromwhereandandanddropindexgo1sysindexesid=object__id('Relationship__6')name='Relationship7FK'indid>0indid<255)ifexists(select1fromsysobjectswhereid=object__id('Relationship__6')andtype='U')droptableRelationship__6goifexists(select1fromsysobjectswhereid=object__id('bumenxinxi')andtype='U')droptablebumenxinxigoifexists(selectfromwhereandandanddropindexgo1sysindexesid=object__id('chuqinxinxi')name='Relationship2FK'indid>0indid<255)ifexists(select1fromsysobjectswhereid=object__id('chuqinxinxi')andtype='U')droptablechuqinxinxigoifexists(selectfromwhereandandanddropindexgo1sysindexesid=object__id('gongzixinxi')name='Relationship1FK'indid>0indid<255)ifexists(select1fromsysobjectswhereid=object__id('gongzixinxi')andtype='U')droptablegongzixinxigoifexists(select1fromsysobjectswhereid=object__id('jiangchengxinxi')andtype='U')droptablejiangchengxinxigoifexists(select1fromsysobjectswhereid=object__id('xiangmucanyu')andtype='U')droptablexiangmucanyugoifexists(select1fromsysobjectswhereid=object__id('xiangmuxinxi')andtype='U')droptablexiangmuxinxigoifexists(selectfromwhereandandanddropindexgo1sysindexesid=object__id('yuangongxixi')name='Relationship6FK'indid>0indid<255)ifexists(select1fromsysobjectswhereid=object__id('yuangongxixi')andtype='U')droptableyuangongxixigoifexists(select1fromsysindexeswhereid=object__id('zhuanruzhuanchu')andname='Relationship3FK'andindid>0andindid<255)dropindexgoifexists(select1fromsysobjectswhereid=object__id('zhuanruzhuanchu')andtype='U')droptablezhuanruzhuanchugo/*Table:Relationship__4*/createtableRelationship__4(jiangchengmingchengchar(40)notnull,yuanNumberchar(8)notnull,constraintPK__RELATIONSHIP__4primarykey(jiangchengmingcheng,yuanNumber))go/*Index:Relationship4FK*/createindexRelationship4FKonRelationship__4(jiangchengmingchengASC)go/*Index:Relationship5FK*/createindexRelationship5FKonRelationship__4(yuanNumberASC)go/*Table:Relationship__6*/createtableRelationship__6(bumenNumberchar(6)notnull,xiangmuNumberchar(6)notnull,constraintPK__RELATIONSHIP__6primarykey(bumenNumber,xiangmuNumber))go/*Index:Relationship7FK*/createindexRelationship7FKonRelationship__6(bumenNumberASC)go/*Index:Relationship8FK*/createindexRelationship8FKonRelationship__6(xiangmuNumberASC)go/*Table:bumenxinxi*/createtablebumenxinxi(bumenNumberchar(6)notnull,bumenNamechar(10)notnull,bumenPhonechar(9)null,bumenAddresschar(16)null,bumenzhuguanchar(8)null,constraintPK__BUMENXINXIprimarykeynonclustered(bumenNumber))go/*Table:chuqinxinxi*/createtablechuqinxinxi(chuyuanNumberchar(8)notnull,riqidatetimenotnull,yuanNumberchar(8)notnull,chuqinzhuangkuangchar(4)null,queqinyuanyinchar(4)null,beizhuchar(20)null,constraintPK__CHUQINXINXIprimarykeynonclustered(chuyuanNumber,riqi))go/*Index:Relationship2FK*/createindexRelationship2FKonchuqinxinxi(yuanNumberASC)go/*Table:gongzixinxi*/createtablegongzixinxi(yuangongbianhao4char(8)notnull,yuanNumberchar(8)notnull,yuangongleixingchar(4)null,zhicheng4char(10)null,canyuxiangmu4char(8)null,chuqinchar(2)null,jiangcehngchar(8)null,jiesuanshiujiandatetimenull,constraintPK__GONGZIXINXIprimarykeynonclustered(yuangongbianhao4))go/*Index:Relationship1FK*/createindexRelationship1FKongongzixinxi(yuanNumberASC)go/*Table:jiangchengxinxi*/createtablejiangchengxinxi(jiangchengleibiechar(4)null,jiangchengmingchengchar(40)notnull,jiangfaqingkuangchar(20)null,constraintPK__JIANGCHENGXINXIprimarykeynonclustered(jiangchengmingcheng))go/*Table:xiangmucanyu*/createtablexiangmucanyu(xiangmubianhao3xiangmuzhuguan3xiangmucanyubumenchar(6)char(8)char(6)notnull,null,null,constraintPK__XIANGMUCANYUprimarykeynonclustered(xiangmubianhao3))go/*Table:xiangmuxinxi*/createtablexiangmuxinxi(xiangmuNumberchar(6)notnull,xiangmuNamechar(20)notnull,xiangmufuzerenchar(8)null,qishiriqidatetimenull,jiezhiriqidatetimenull,constraintPK__XIANGMUXINXIprimarykeynonclustered(xiangmuNumber))go/*Table:yuangongxixi*/createtableyuangongxixi(yuanNumberchar(8)notnull,bumenNumberchar(6)notnull,yuanNamechar(12)notnull,sexchar(2)notnull,heathychar(2)null,zhichengchar(10)notnull,photobinary(1)null,lianxichar(11)null,xuelichar(6)null,schoolchar(20)null,bumenchar(6)null,shijiandatetimenull,canyuxiangmubianhaochar(6)null,constraintPK__YUANGONGXIXIprimarykeynonclustered(yuanNumber))go/*Index:Relationship6FK*/createindexRelationship6FKonyuangongxixi(bumenNumberASC)go/*Table:zhuanruzhuanchu*/createtablezhuanruzhuanchu(yuangongbianhao5char(8)notnull,yuanNumberchar(8)null,zhuandongleixingchar(8)null,zhuanchubumenchar(6)null,zhuanrubumenchar(6)null,zhuandongshijiandatetimenull,c