數(shù)據(jù)庫與信息管理技術(shù)課程設(shè)計設(shè)計題目：物業(yè)管理系統(tǒng)學(xué)院：信息科學(xué)與工程學(xué)院專業(yè)：計算機科學(xué)與技術(shù)班級：計信1201學(xué) 號：20121222091學(xué)生姓名：龐穎指導(dǎo)教師：蔡衛(wèi)東2014年7月11日TOC\o"1-5"\h\z\o"CurrentDocument"系統(tǒng)背景 3\o"CurrentDocument"需求分析 4功能簡介 4管理員子系統(tǒng) 4普通業(yè)主子系統(tǒng) 4物業(yè)代表子系統(tǒng) 4數(shù)據(jù)字典 5數(shù)據(jù)流圖 5\o"CurrentDocument"3 概念結(jié)構(gòu)設(shè)計 5管理員子系統(tǒng)E-R圖 6普通業(yè)主子系統(tǒng)E-R圖 7\o"CurrentDocument"物業(yè)代表子系統(tǒng)E-R圖 8\o"CurrentDocument"4 邏輯結(jié)構(gòu)設(shè)計 8關(guān)系模式 9初步關(guān)系模式 9分解合并 9用戶子模式 10外鍵約束 10數(shù)據(jù)庫安全性 10角色 10權(quán)限 11\o"CurrentDocument"物理設(shè)計 11存儲設(shè)計 11表格建立 11索引設(shè)計 13\o"CurrentDocument"數(shù)據(jù)庫的實現(xiàn) 14基本表 14視圖 14角色 15索引 15觸發(fā)器 17條件約束 17\o"CurrentDocument"測試方案 18插入操作 18更新操作 19對人員信息表的更新 19\o"CurrentDocument"用戶對維修業(yè)務(wù)進行更新 20生成水電業(yè)務(wù)賬單 20刪除操作 21查詢操作 21查詢基本人員信息 21查詢賬單 21\o"CurrentDocument"總結(jié) 23\o"CurrentDocument"參考文獻 24系統(tǒng)背景本課程是為《數(shù)據(jù)庫與信息管理技術(shù)》課程所開的實踐環(huán)節(jié)。數(shù)據(jù)庫與信息管理技術(shù)課程是一門實踐性很強的技術(shù)課程，而且是計算機科學(xué)與技術(shù)中發(fā)展最快的領(lǐng)域之一。本課程設(shè)計的目的旨在使學(xué)生能夠掌握數(shù)據(jù)庫的基本原理、數(shù)據(jù)庫設(shè)計的基本方法、SQL語言的應(yīng)用、SQLServer2000/2005數(shù)據(jù)庫環(huán)境的使用，并能根據(jù)所應(yīng)用到的數(shù)據(jù)庫管理系統(tǒng)的相關(guān)技術(shù)，按照規(guī)范化設(shè)計的方法解決現(xiàn)實中數(shù)據(jù)庫設(shè)計的問題。選修本課程前應(yīng)已選修《數(shù)據(jù)庫與信息管理技術(shù)》課程，并熟練掌握SQL語言，以及數(shù)據(jù)庫設(shè)計的規(guī)范化等基本方法。先修課程：數(shù)據(jù)庫與信息管理技術(shù)。需求分析2.1功能簡介2.1.1管理員子系統(tǒng)管理員登錄：管理員通過輸入管理員賬號和密碼后登錄管理員子系統(tǒng)進行系統(tǒng)操作。業(yè)主添加功能：管理員通過該功能添加本小區(qū)入住業(yè)主，填寫業(yè)主真實信息。業(yè)主信息查詢功能：管理員通過該功能查看業(yè)主信息。業(yè)主信息修改功能：管理員在業(yè)主信息發(fā)生變化以后，根據(jù)變化情況修改業(yè)主信息。業(yè)主信息刪除功能：在業(yè)主搬出本小區(qū)以后刪除業(yè)主基本信息。2.1.2普通業(yè)主子系統(tǒng)業(yè)主登錄：業(yè)主通過輸入業(yè)主賬號和密碼后登錄普通業(yè)主子系統(tǒng)進行系統(tǒng)操作。業(yè)主個人信息查看功能：業(yè)主通過該功能查看自己在本小區(qū)的基本信息。業(yè)務(wù)添加功能：業(yè)主通過該功能添加各類維修業(yè)務(wù)。業(yè)務(wù)查看功能：業(yè)主通過此功能查看各項維修業(yè)務(wù)進行狀態(tài)。業(yè)務(wù)狀態(tài)修改功能：修改業(yè)務(wù)進行狀態(tài)，業(yè)務(wù)是否完成，或?qū)S修不滿意，或因各種情況需再次維修，通過此功能通知物業(yè)代表。業(yè)務(wù)刪除功能：業(yè)主自己把維修業(yè)務(wù)做完，不再需要維修人員，或業(yè)務(wù)已確認被維修人員修理完好，則刪除本業(yè)務(wù)。賬單查看功能：業(yè)主通過此功能查看維修、水、電、物業(yè)管理各類賬單，以確認自己所要交納的費用。維修業(yè)務(wù)查看功能：小區(qū)把提供的物業(yè)維修服務(wù)展示給業(yè)主。2.1.3物業(yè)代表子系統(tǒng)物業(yè)代表登錄：物業(yè)各類代表通過輸入其各自獨有的賬號和密碼后登錄物業(yè)代表子系統(tǒng)進行系統(tǒng)操作。。耗水耗電量填寫功能：水費代表通過此功能把業(yè)主的月耗水量錄入，電費代表通過此功能把業(yè)主的月耗電量錄入。耗水耗電量更改功能：水費代表通過此功能修改輸入錯誤或查詢有誤的耗水量，電費代表通過此功能修改輸入錯誤或查詢有誤的耗電量。生成水電費賬單功能：水費代表完成水量錄入以后，通過此功能生成各業(yè)主當(dāng)月水費，電費代表完成水量錄入以后，通過此功能生成各業(yè)主當(dāng)月電費。耗水耗電量查看功能：水費代表通過此功能查看各月各個業(yè)主的耗水量統(tǒng)計，電費代表通過此功能查看各月各個業(yè)主的耗電量統(tǒng)計委派維修人員功能：物業(yè)維修代表通過此功能委派相關(guān)人員去為業(yè)主提供維修服務(wù)。清除已完成業(yè)務(wù)功能：在業(yè)主沒有對維修業(yè)務(wù)提出舉報，并且已付清相關(guān)費用以后，物業(yè)維修代表通過此功能把業(yè)務(wù)從數(shù)據(jù)庫中刪除。物業(yè)費用修改功能：各類物業(yè)代表通過此功能修改其管轄業(yè)務(wù)的物業(yè)費用。物業(yè)維修費用填寫：維修人員通過此功能填寫維修費用，即在維修人員辦理完維修業(yè)務(wù)以后，根據(jù)勞動量和消耗器械的多少而合理收費。2.2數(shù)據(jù)字典具體的數(shù)據(jù)字典會在數(shù)據(jù)庫實現(xiàn)一章中詳細給出，此處不再重復(fù)。2.3數(shù)據(jù)流圖概念結(jié)構(gòu)設(shè)計3?1管理員子系統(tǒng)E-R圖

3.3物業(yè)代表子系統(tǒng)E-R圖注：由于頁面限制，圖中只畫出了各實體的主碼。邏輯結(jié)構(gòu)設(shè)計4.1關(guān)系模式4.1.1初步關(guān)系模式根據(jù)概念結(jié)構(gòu)設(shè)計，可以得到初步的關(guān)系模式如下（其中各關(guān)系模式的主碼用下劃線標(biāo)出）：管理員登錄（管理員賬號,密碼）管理員（管理員賬號,姓名，性別，生日，電話，住址）業(yè)主登錄（業(yè)主賬號,密碼）業(yè)主（業(yè)主賬號,姓名，性別，生日，住址，賬號）維修業(yè)務(wù)（維修編號,業(yè)主賬號，開始時間，結(jié)束時間，維修人員編號,維修人員姓名，滿意情況，維修明細，維修費用）物業(yè)代表登錄（物業(yè)代表賬號,密碼）物業(yè)代表（物業(yè)代表賬號,代表業(yè)務(wù)，姓名，性別，生日，住址，電話）水電業(yè)務(wù)管理（業(yè)務(wù)號,業(yè)務(wù)內(nèi)容，單位價格，業(yè)務(wù)管理費）水業(yè)務(wù)（業(yè)主賬號,當(dāng)月用水量，總用水量，繳納費用，繳費時間）電業(yè)務(wù)（業(yè)主賬號,當(dāng)月用電量，總用電量，繳納費用，繳費時間）4.1.2分解合并對上述關(guān)系模式進一步分析，解決沖突，消除冗余：經(jīng)分析發(fā)現(xiàn)，管理員登錄和管理員兩個實體，實際上是一個實體，只是功能不同而已，所以我們可以將這兩個表合并：管理員（管理員賬號,密碼，姓名，性別，生日，電話，住址）其它兩個登錄表同理可以合并：業(yè)主（業(yè)主賬號,密碼，姓名，性別，生日，住址，電話）物業(yè)代表（物業(yè)代表賬號,密碼，代表業(yè)務(wù)，姓名，性別，生日，住址,電話）除此之外，雖然上述功能沒有用到，但還應(yīng)該增加關(guān)于業(yè)主所有財產(chǎn)的詳細信息，如車位，地下室等。業(yè)主財產(chǎn)（業(yè)主賬號,房產(chǎn)地址，房產(chǎn)面積，地下室地址，地下室面積,車庫地址，車庫面積，車位地址）還用增加維修人員的相關(guān)信息：維修人員（維修人員編號,姓名，性別，生日，電話，住址）分析發(fā)現(xiàn)水電業(yè)務(wù)的列大體相似，因此也可以合并在一起：水電業(yè)務(wù)（業(yè)務(wù)代號，業(yè)主賬號,當(dāng)月用電量，總用電量，繳納費用，繳費時間）

4.2用戶子模式給業(yè)主看的繳費賬單可以用外模式可以用試圖來實現(xiàn)。在'業(yè)主'和'水業(yè)務(wù)'上建立'水賬單'試圖：水賬單（業(yè)主賬號，業(yè)主姓名，月用水量，繳納金額，繳納日期）在'業(yè)主'和'電業(yè)務(wù)'上建立'電賬單'試圖：水賬單（業(yè)主賬號，業(yè)主姓名，月用電量，繳納金額，繳納日期）在維修業(yè)務(wù)上建立維修賬單視圖：維修賬單（維修編號，業(yè)主賬號，開始時間，結(jié)束時間，繳納費用）4?3外鍵約束根據(jù)各表功能和存儲結(jié)構(gòu)的分析，我們可以建立以下外鍵約束:/401_024.ft...?_ 『如1_0￡4■?物...o.活空菅理員tbljcfelk^—疝赭列）業(yè)務(wù)買號總用垂tbljcfelk^—疝赭列）業(yè)務(wù)買號總用垂▲［卿小|_雛舷業(yè)勞:嚴(yán)f所百孑山L7屛底呂::雉修人員煽號:開始時目zl|俚0』主□*府有妣亠——性到二］.*（所向列）卄十案呂二房產(chǎn)地址二岳產(chǎn)面積匚地玉室地址4.4數(shù)據(jù)庫安全性4.4.1角色根據(jù)本系統(tǒng)的功能，可以創(chuàng)建三個角色，來實現(xiàn)數(shù)據(jù)庫權(quán)限的分配,分別是管理員角色，用戶角色和物業(yè)代表角色。4?4?2權(quán)P艮根據(jù)用戶角色不同，為其分配不同的權(quán)限如下：管理員：對表tbl_管理員，tbl_業(yè)主，tbl_業(yè)主財產(chǎn)擁有全部權(quán)限。業(yè)主：對表tbl_業(yè)主，tbl_業(yè)主財產(chǎn)擁有查詢權(quán)限，對表tbl_維修業(yè)擁有查詢，修改和刪除的權(quán)限。物業(yè)代表：對表tbl_水電業(yè)務(wù)擁有查詢，修改和添加的權(quán)限，對表tbl_維修業(yè)務(wù)擁有全部權(quán)限，對表tbl_維修人員有查詢權(quán)限，對表bl_水電業(yè)務(wù)管理有查詢和修改的權(quán)限。5物理設(shè)計5?1存儲設(shè)計5?1?1表格建立根據(jù)關(guān)系模式，確定各屬性的相關(guān)類型，建立各個表格:Tbl_管理員表格：列名數(shù)堀類型允許f^ull值!菅理員賬號nchar(lO)r密碼nvar匚h呂門：2匚i；ir姓営nvarchar(15)r生曰dater性別bitr電話nvarcharflS)p住址nvarchar(SO)prTbl_水電業(yè)務(wù):列名數(shù)據(jù)類型允許fJull值業(yè)務(wù)代昌nchar(LO)廠業(yè)主賬號nchar(LO)廠未數(shù)費用重Host總用重Float廠繳賈時間nchar(LO)廠Tbl_水電業(yè)務(wù)管理:

列名數(shù)據(jù)類型允許如11值nchar(LO)r業(yè)務(wù)內(nèi)容nvarchar(lO)r單位價格Floatr管理費FloatrrTbl_維修人員列名數(shù)據(jù)類型允許如II值維幔人員編號nchdr(lO)r姓容nvarchar(lE)r性別bitr生曰date[7電話nvdri：har(15Jr住址nv3rchar(50)盲rTbl_維修業(yè)務(wù):列名數(shù)據(jù)類型允許恤jII值nchar(LO)r業(yè)主賬號nchar(LO)廠維修人員漏號nchar(LO)廠開始時間dater結(jié)束時間date麗満意情；兄bit維修明細nvarcharf'SCl)維修費用FloatrTbl_物業(yè)代表:列名數(shù)據(jù)類型允許Null值2?物業(yè)代表帳號nchar(LO)r密碼nvarch3r(2O)廠姓呂nvarchar(15)廠性別bit廠生曰dater電話nvarchar(15)代夷業(yè)勢代號nchar(LO)廠住址nvarcharfSO)17rTbl_業(yè)主:

列名數(shù)據(jù)類型允許Null值2?業(yè)主賬號nchar(LO)r密碼nvarchar(2Ll)廠姓呂nvarchar(15)廠性別bit廠生曰dater電話nvarchar(15)住址nvarchar(SO)麗rTbl_業(yè)主財產(chǎn):列容埶據(jù)類型允許恤jII值業(yè)主賬號nchar(LO)廠房產(chǎn)地址n'v-archar(2Li)Float地下室地址nvarchar(EO)地下室面稅Float車庫地址nvarchar(20)車庫面稅float17車位地址nvarch3r(2O)r5?2索引設(shè)計根據(jù)性能的需要決定是否建立索引，為了提高查詢效率，對每個表格按照其主鍵建立相應(yīng)的索引：如對于tbl_業(yè)主表格，可以根據(jù)其業(yè)主賬號建立相應(yīng)的索引“業(yè)主賬號”，這樣可以大大提高日后的查詢效率，類似的，也分別對tbl_管理員，tbl_維修人員，tbl_維修業(yè)務(wù)，tbl_物業(yè)代表建立了相應(yīng)的索引，其中由于tbl_維修業(yè)務(wù)的表由兩個屬性作為主鍵，可以對其組合作為索引條件。具體SQL語句會在數(shù)據(jù)庫實現(xiàn)章節(jié)詳細給出。6數(shù)據(jù)庫的實現(xiàn)6.1基本表以表tbl_管理員為例，SQL實現(xiàn)語句如下:createtabletbl_管理員管理員賬號primarykeynchar(10),密碼nvarchar(20),姓名nvarchar(15),生日date,性別bit,電話nvarchar(15),住址nvarchar(50);本系統(tǒng)至少需要建立以下基本表：-］物業(yè)管理親統(tǒng)；丄數(shù)據(jù)庫關(guān)丟圏-一J志+一I系統(tǒng)克+□dbt>.tbl_管理員+□dbt>. 電業(yè)努+□dbc. 電業(yè)勞管理+□dbc. 人員+□dbc.訃1洗修業(yè)努+□dbc.七訂_韌業(yè)代表+_Jdbo.tbl■］［主+□db?.tblJl主財產(chǎn)+一J視圏6.2視圖以生成水費賬單為例，實現(xiàn)創(chuàng)建視圖的SQL語句如下：createview水賬單視圖(業(yè)主賬號，姓名，用量，繳納時間，繳納費用asselecttbl_業(yè)主.業(yè)主賬號，tbl_業(yè)主.姓名，tbl_水電業(yè)務(wù).未繳費用量，tbl_水電業(yè)務(wù).繳費時間,tbl_水電業(yè)務(wù).繳納費用fromtbl_業(yè)主，tbl_水電業(yè)務(wù)wheretbl_業(yè)主.業(yè)主賬號=tbl_水電業(yè)務(wù).業(yè)主賬號andtbl_水電業(yè)務(wù).業(yè)務(wù)代號='01';本系統(tǒng)至少需要生成的視圖如下：-一J觀圖+1-J系繞視圖十1因並0.電賬單視囹十1因並0.水賬單視囹+1EU血Q.雛檢舐單+_J同義詞+_J可編程性ffi_JSer^iceBroker401_024.ft.. 單視囹401_024.物...Jbc.電賬單視囹業(yè)主賬號姓宅I^S巍納時間繳納費用3000000011020H/6/3L2230000000215MJLL33300000003賈蓉132014/7/1423.6300000004秦可卿56.52014/4/14124.300000000...30000000523磁f50.6/IU￡磁￡/1W6.3角色正文實現(xiàn)角色創(chuàng)建的SQL語句如下:createrole管理員;createrole業(yè)主；createrole物業(yè)代表；創(chuàng)建成功后數(shù)據(jù)庫系統(tǒng)截圖:+」用戶-I角超日口數(shù)擁庫角色離、ilb_access：ii3niin基db_ba>:kug'-'Pei_atcr違ilb_datiii-ead已r卷ilb_datawriter■邑ilti_diiLailriirL乞ilti_derLydat:u-ealerU、'lb_dt!rL7-l：=LtiTritwU、'Ib-OwrL&r爲(wèi)、ilb_Eecur1tya?iri1npmTillc咎管理員曾物業(yè)代表住ik主十一i血丙程序角色6.4索引實現(xiàn)建立索引的SQL語句如下:createuniqueindex管理員賬號

ontbl_管理員（管理員賬號）；createuniqueindex業(yè)主賬號ontbl_業(yè)主（業(yè)主賬號）；createuniqueindex業(yè)主賬號ontbl_業(yè)主財產(chǎn)（業(yè)主賬號）；createuniqueindex維修賬號ontbl_維修業(yè)務(wù)（維修編號，業(yè)主賬號）；createuniqueindex維修人員編號ontbl_維修人員（維修人員編號）；createuniqueindex物業(yè)代表賬號ontbl_物業(yè)代表（物業(yè)代表賬號）；索引建立完成后的部分截圖如下:--J恙十-J親絨表-二dbodbl-管理員a_j列±1_j約束H丄觸發(fā)器-I索引AFK_tbi_^g員嗓集）缶管理員賬號雌一?非案集）H丄縊計信息十Jdbc.tbi_?K電業(yè)勞十二dbc.tbi_?K電業(yè)窯管理-Jdbc. 人員■+I_J列+1」鍵■+I_J約束創(chuàng)丄觸發(fā)器-I」索引AFK.tbl^BA員嗪隼〕1錐幔人員編號 非聚集〕創(chuàng)丄統(tǒng)計信息+二dbo.tbl灘儈業(yè)勞+二dbo.tbl_^lE代表-二dbo.tbl主a_j列創(chuàng)一］健■+I_j約束創(chuàng)_j觸發(fā)盅-I_j索引FK_tt.lJk主傑隼〕±業(yè)主賬號雎一，豐乘隼）41_J純計信息43dbd.tKL―業(yè)主財產(chǎn)+_J視圉I 16?5觸發(fā)器在系統(tǒng)的實際應(yīng)用中，每個表的增刪改查都會受到其他表的限制，比如在本系統(tǒng)中，在對tbl_業(yè)主進行增加操作的同時也要對tbl_業(yè)主操作進行相應(yīng)的增加操作，同理，當(dāng)刪除一個業(yè)主時，相應(yīng)的tbl_業(yè)主財產(chǎn)中也應(yīng)該刪除該業(yè)主的全部信息。實現(xiàn)該功能的觸發(fā)器創(chuàng)建SQL語句如下：插入觸發(fā)器：ALTERtrigger[dbo].[insert_業(yè)主]on[dbo].[tbl_業(yè)主]afterinsertasbegininsertintotbl_業(yè)主財產(chǎn)(業(yè)主賬號)select(業(yè)主賬號)frominserted;end;建成后的數(shù)據(jù)庫截圖：□□dbo.tbijiki日口列y業(yè)主賬號卩反nchartlO^notnuiliU密碼[nvarchar(￡OXnotnull)1*11 (nvarchar(15)1notnull)iU性別[bit,notnull〕回生日(date,!no-tnull)圍電話[nvarch3r(15).muM)茴(nvarcharfSO^null]田口養(yǎng)田uJ為康I7\irsert_lk^田UJ索引6?6條件約束根據(jù)每個屬性的具體內(nèi)容，為其增加必要的條件約束，如給每個存放人員信息的表格，增加生日屬性的條件約束，是它更加符合實際情況。實現(xiàn)條件約束的SQL語句如下：altertabletbl_管理員addconstraintc1check(生日between'1960-01-01'and'1990-01-01');-一J表+1_J素藐夷-］口tbl_B理員+1一J列+1一］鍵-I_J約束3口融岌器3口索引+1丄統(tǒng)計信息+1口亂亠tbZL辦電業(yè)務(wù)+］口血,畑_7水電Ik窯管理-1口dt。,tbijilgA員+iEj列+i」鍵-I一I約束□c維修人員3口釉岌器+1一I索引7測試方案7?1插入操作以插入新業(yè)主為例：insertintotbl_業(yè)主（業(yè)主賬號，密碼，姓名，性別，生日電話，住址）values（'2000000006','123','薛寶釵',1,'1988-12-29',,'鼎舜趙苑小區(qū)號'）；SQLQue^sql- （51}ZX］Flinsertitrtotbl_lk主{業(yè)主賬號，密碼，姓容，性別，生日，電話"住扯） 匡］valuesC2000000006'^123','薛寶釵‘1,'19dt-12-|29'.□陰陽14時2■鼎編世:苑小犀呂，）1OT% --J消息當(dāng)行愛黑幀如上圖的執(zhí)行結(jié)果截圖，由于有觸發(fā)器限制，在向表tbl_業(yè)主添加新記錄的同時，系統(tǒng)會自動的向表tbl_添加相應(yīng)的業(yè)主信息7?2更新操作7.2.1對人員信息表的更新updatetbl_業(yè)主set住址二’鼎舜小區(qū)22號樓6單元401',業(yè)主賬號二'300000006'where姓名」薛寶釵';EQLQueryZsql (51)I"X|Hinsertinto+bl_lk主fit主賬號，密馮，姓務(wù)性別，生曰，電話，住址) -J-valucsF2OOOOOOOO0r.'J23F. 1'1990-12-29F^647914572■和疣鉛死小區(qū)號’，耳updatetbl_lti setl￡dlf-f小區(qū) 摟6早■元401-it主賠號3DOODOOD6?vhere姓名-?'曲寶釵'」□srlrct女Iron 主vhere牲容/番寶鋰'too%■1紙果J:餾業(yè)主聊號 莎姓氧 性￡1主日 電話 哇址r— 17?2?2用戶對維修業(yè)務(wù)進行更新cupdate丈匕丄_議底址獰set-結(jié)束時間='2014-7-18'，滿意'情況='falsewhere維修編號=J0002?;a行受影響）7?2?3生成水電業(yè)務(wù)賬單cupdate訃1■—維修業(yè)奔set-結(jié)束時間='2C14-7-1?'：■霸意情況='false'where維修編逬='口口W;-]select單f立帯格from.水電1L務(wù)寰理where業(yè)務(wù)號='0/:updatetbl_zK電業(yè)勞set應(yīng)繳赍甬=（未繳證用1^2.2）where業(yè)笄代呂三CT（5行更尉響）7?3刪除操作刪除業(yè)主信息：