數(shù)據(jù)庫設(shè)計與開發(fā)

數(shù)據(jù)庫設(shè)計與開發(fā)分析與設(shè)計。在與相關(guān)人員進行了充分的交流，庫的基本信息之后，進行基本的需求分析，通1緒論......................................錯誤!未定義書簽。系統(tǒng)簡介...................................錯誤!未定義書簽。2需求分析..................................錯誤!未定義書簽。.需求分析的任務(wù)...........................錯誤!未定義書簽。3概念模型設(shè)計..............................錯誤!未定義書簽。概念模型設(shè)計的方法和步驟..................錯誤!未定義書簽。5物理數(shù)據(jù)庫模型設(shè)計與實現(xiàn).............總結(jié)........................................錯誤!未定義書簽。參考文獻....................................錯誤!未定義書簽。光影傳媒公司是成立于19XX年的一家傳媒公司，公家分公司。該公司囊括電影、電視、音樂、動漫等行業(yè)，公司內(nèi)的部門有董事會、營銷部、策劃部、后勤部等。公司現(xiàn)有職工一千多人，其學(xué)歷、收入、所在部門各不相同，同時，有時由于業(yè)務(wù)需求，各部門之間會有一定的人事調(diào)動。對于如此龐大且繁雜的數(shù)據(jù)系統(tǒng)，如果采用傳統(tǒng)的數(shù)據(jù)管理顯然不能滿足管理的需求。于是，要求設(shè)計一個適合于光影傳媒公司人事信息管理數(shù)據(jù)庫，用以實現(xiàn)統(tǒng)計、管理公司內(nèi)各職員的基本信息、各個部門的情況、員工的轉(zhuǎn)入或轉(zhuǎn)出、人員的調(diào)動，以及公司的各項事務(wù)、各項事務(wù)的負責(zé)情況、進展情況、實施情況和一些相關(guān)的后續(xù)情況等。要求對于建成的數(shù)號、項目名稱、項目進展情況等；記錄各個員工的轉(zhuǎn)入、轉(zhuǎn)出、調(diào)動情況；各個部門的具體情況、負責(zé)人、各項目的負責(zé)人等。用以記錄公司正常運營分析光影傳媒公司人事信息管理數(shù)據(jù)庫所要求要求，做出具體的需求分析，在需求分析之后，需要進行設(shè)計本業(yè)務(wù)之后，設(shè)計觀影傳媒公司人事信息管理的業(yè)務(wù)、數(shù)據(jù)流程圖；及關(guān)系，設(shè)計出業(yè)務(wù)基本的概念模型，并對其進行適當(dāng)?shù)膬?yōu)化；進行整個數(shù)據(jù)庫設(shè)計必須準(zhǔn)確了解與分析用戶需求（包括數(shù)據(jù)和處理）。需求分析是整個設(shè)計過程的基礎(chǔ)，是最困難、最耗費時間的一步。作戶對數(shù)據(jù)庫的如下需求：①公司員工信息：員工編號、員工姓名、性別、學(xué)歷、所屬②公司部門信息：部門編號、部門名稱、部門負責(zé)人、部③項目信息：項目編號、項目名稱、項目描述、項目負責(zé)參與部門、項目開始時間、項目結(jié)束時間；④員工出勤信息：員工編號、員工姓名、日期、出勤狀況⑤員工參與項目信息：員工編號、員工姓名、項目編號①數(shù)據(jù)的錄入：對公司的員工的基本信息、部門的基本信息、本信息等進行信息的錄入，并對數(shù)據(jù)進行存儲；項目信息等進行增加、修改、刪除、查詢等功能；及公司的信息進行查詢，僅有數(shù)據(jù)庫管理人程如圖所示：楚，因此在數(shù)據(jù)字典中主要對數(shù)據(jù)流程圖中量間23期4注5678編號錄錄錄錄息息息素息息資度天天天天式錄錄錄天稱錄況錄概念模型設(shè)計的方法和步驟打開軟件PowerDesigner，使用其輔助設(shè)計概念模型：1）在PowerD2）在設(shè)計頁面下點擊實體圖標(biāo)，按照需求分析的結(jié)果建立實體，并一一對實體的屬性進行設(shè)置；3）在實體和屬性建立完成之后，建立各個實體之間的聯(lián)系。在所有實體、屬性與實體之間的聯(lián)系建立好后，基本的概念模型便設(shè)計好了。設(shè)計的結(jié)果如圖所示：根據(jù)上面的關(guān)系模式，數(shù)據(jù)庫的完整性約束如下所示：3）用戶自定義完整性約束員工表中，員工編號必須以Y開頭，后面根7位數(shù)字；部門表中，部門編號必須以D開頭，后面跟5位數(shù)字；項目表中，項目編號必須以P開頭，后面根5位數(shù)字；項目表中，起始日期必須早于截止日期物理數(shù)據(jù)庫模型的圖示對之前已經(jīng)生成的CDM模型進行檢查，主頁面選擇Tool中的GeneratePhsicalDataModel命令進行設(shè)置，待設(shè)置完成后，點擊確定按鈕，生成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