小區(qū)物業(yè)管理數(shù)據(jù)庫設計

數(shù)據(jù)庫應用課程設計報告題目： 小區(qū)物業(yè)管理數(shù)據(jù)庫設計 專業(yè)： 班級： 學號： 姓名： 指導教師： 完成日期： 年 月 日小區(qū)物業(yè)管理數(shù)據(jù)庫設計（國脈信息學院 11 級 1 班）摘要：這個數(shù)據(jù)庫是管理樓盤住戶的信息，便于管理用戶的信息。用數(shù)據(jù)庫管理可以更高效和更清晰的看清住戶的信息還有投訴，保修等信息，有利于小區(qū)的管理，每個小區(qū) 都必須應用一個小區(qū)的數(shù)據(jù)庫管理系統(tǒng)，這個才能更好的管理。如果一個小區(qū)沒有一個數(shù)據(jù)庫管理的系統(tǒng)，小區(qū)的管理就會混亂，住戶的投訴和保修就得不到解決，所以數(shù)據(jù)庫您值得擁有！關鍵詞: 數(shù)據(jù)庫 級聯(lián)更新、刪除 觸發(fā)器 視圖I目 錄1概述 .11.1 設計背景： .11.2 設計目的： .11.3 設計內容： .12. 需求分析 22.1 系統(tǒng)功能分析： 22.2 數(shù)據(jù)字典 22.3 數(shù)據(jù)流程圖： .33數(shù)據(jù)庫結構 .33.1 概念模型設計（E-R 圖） .33.2 邏輯設計 .54 數(shù)據(jù)庫物理設計（主要包括數(shù)據(jù)存儲位置、存儲格式；索引及索引類型。） .65 數(shù)據(jù)庫實施與測試（源代碼及查詢截圖） .86 總結（設計過程中遇到的問題以及解決方法；課程學習及課程設計的體會）。 .287. 參考文獻 2801概述1.1 設計背景：數(shù)據(jù)庫基礎與應用課程設計是計算機專業(yè)集中實踐性環(huán)節(jié)之一，是學習完數(shù)據(jù)庫系統(tǒng)概論課程后進行的一次全面的綜合練習。1.2 設計目的：在于加深對數(shù)據(jù)庫基礎理論和基本知識的理解，掌握進行數(shù)據(jù)庫開發(fā)的全過程，提高運用數(shù)據(jù)庫解決實際問題的能力。1.3 設計內容：進行需求分析，設計數(shù)據(jù)庫的概念模型。系統(tǒng)基本功能：樓盤信息管理：樓盤信息的添加、編輯和查詢；住戶信息管理：住戶信息的添加、編輯和查詢；報修事項管理：報修事項的添加、編輯和查詢；投訴信息管理：投訴信息的添加、編輯和查詢；報表管理：輸出樓盤信息、住戶信息、報修信息和投訴信息。其中：（除了以下信息，也可以按需要增加屬性列）樓盤信息：包括的主要數(shù)據(jù)項有：樓盤編號，樓盤名稱，樓層數(shù)，面積，戶型，地址住戶信息：包括的主要數(shù)據(jù)項有：業(yè)主編號，姓名，性別，電話，門牌號，面積，身份證報修事項：包括的主要數(shù)據(jù)項有：報修事項編號，報修名稱，業(yè)主編號，維修狀態(tài)，日期，受理人投訴信息：包括的主要數(shù)據(jù)項有：投訴事項編號，投訴名稱，業(yè)主編號，解決狀態(tài)，日期，受理人12. 需求分析2.1 系統(tǒng)功能分析： 樓盤信息管理：樓盤信息的添加、編輯和查詢；住戶信息管理：住戶信息的添加、編輯和查詢；報修事項管理：報修事項的添加、編輯和查詢；投訴信息管理：投訴信息的添加、編輯和查詢；報表管理：輸出樓盤信息、住戶信息、報修信息和投訴信息。2.2 數(shù)據(jù)字典數(shù)據(jù)項數(shù)據(jù)項編號 數(shù)據(jù)項名稱 數(shù)據(jù)類型 長度 可否為空 描述1 樓盤編號 int 否 樓盤的編號 2 樓盤名稱 char 20 否 樓盤的名稱3 樓層數(shù) int 否 樓盤的層數(shù)4 面積 int 否 房間的大小5 戶型 char 20 否 屬于什么戶型6 地址 char 20 否 所在地址7 業(yè)主編號 int 否 業(yè)主的編號 8 名字 char 20 否 業(yè)主的名字9 性別 char 20 否 業(yè)主性別10 電話 int 否 業(yè)主電話11 門牌號 int 否 房間門牌號12 身份證 char 20 否 業(yè)主身份證13 報修事項號 int 否 報修事項編號 14 報修名稱 char 20 否 報修的東西15 維修狀態(tài) char 20 否 維修的狀態(tài)16 日期 datetime 20 否 受理時間17 受理人 char 20 否 管理人員18 投訴事項編號 int 否 投訴事項編號 19 投訴名稱 char 20 否 投訴的東西20 解決狀態(tài) char 20 否 解決的狀態(tài)2數(shù)據(jù)結構：數(shù)據(jù)結構編號數(shù)據(jù)結構名數(shù)據(jù)結構定義組成1 樓盤信息表樓盤信息 樓盤編號，樓盤名稱，樓層數(shù)，面積，戶型，地址2 住戶信息表住戶信息 業(yè)主編號，姓名，性別，電話，門牌號，面積，身份證3 報修信息表報修信息 報修事項編號，報修名稱，業(yè)主編號，維修狀態(tài)，日期，受理人4 投訴信息表投訴信息投訴事項編號，投訴名稱，業(yè)主編號，解決狀態(tài)，日期，受理人2.3 數(shù)據(jù)流程圖：樓盤信息管理：住戶看好樓盤 填購買表 付款 管理人員把資料錄入數(shù)據(jù)庫保存帶住戶到房間及交代一些注意事項投訴事項管理：住戶提出投訴 填投訴表 交給管理人員 錄入數(shù)據(jù)庫 工作人員來處理 工作人員上報處理投訴結果 再次錄入數(shù)據(jù)庫保存報修事項管理：住戶申請報修 填申請表 交給管理人員 錄入數(shù)據(jù)庫 專門人員上門修理 工作人員上報修理結果 錄入數(shù)據(jù)庫保存住戶信息管理：住戶申請修改住戶信息 填住戶信息表 管理人員審核審核通過錄入信息到數(shù)據(jù)庫保存3數(shù)據(jù)庫結構3.1 概念模型設計（E-R 圖）實體及其屬性圖：(1).樓盤信息表的 E-R 圖3樓盤編號 樓盤名稱 樓層數(shù) 面積 戶型 地址樓盤信息.住戶信息表的 E-R 圖姓名 性別 電話 門牌號 面積 身份證住戶信息業(yè)主編號.保修信息表的 E-R 圖報修事項編號 報修名稱 業(yè)主編號 維修狀態(tài) 日期 受理人報修信息表.投訴信息表的 E-R 圖投訴事項編號 投訴名稱 業(yè)主編號 解決狀態(tài) 日期 受理人投訴信息表4(2). 實體及其聯(lián)系圖：樓盤住戶投訴報修擁有提出申請1nn nmm3.2 邏輯設計關系模式：樓盤（樓盤編號，樓盤名稱，樓層數(shù)，面積，戶型，地址）住戶（業(yè)主編號，姓名，性別，電話，門牌號，面積，身份證）投訴（投訴事項編號，業(yè)主編號，投訴名稱，解決狀態(tài)，日期，受理人）報修（報修事項編號，業(yè)主編號，報修名稱，維修狀態(tài)，日期，受理人）擁有（業(yè)主編號，樓盤編號）數(shù)據(jù)庫表的設計樓盤信息表字段名稱 數(shù)據(jù)類型 可否為空 說明樓盤編號 int 否 主鍵樓盤名稱 char(20) 否樓層數(shù) int 否面積 int 否戶型 char(20) 否地址 char(20) 否5住戶信息表字段名稱 數(shù)據(jù)類型 可否為空 說明業(yè)主編號 int 否 主鍵姓名 char(20) 否性別 char(20) 可電話 int 否門牌號 int 否面積 int 否身份證 char(20) 否投訴信息表字段名稱 數(shù)據(jù)類型 可否為空 說明投訴事項編號 int 否 主鍵業(yè)主編號 char(20) 否 外鍵投訴名稱 char(20) 否解決狀態(tài) char(20) 否 默認 not日期 datetime 否 默認當前時間受理人 char(20) 否報修信息表字段名稱 數(shù)據(jù)類型 可否為空 說明報修事項編號 int 否 主鍵業(yè)主編號 char(20) 否 外鍵保修名稱 char(20) 否維修狀態(tài) char(20) 否 默認 not日期 datetime 否 默認當前時間受理人 char(20) 否擁有信息表字段名稱 數(shù)據(jù)類型 可否為空 說明業(yè)主編號 int 否 主鍵樓盤編號 int 否 64 數(shù)據(jù)庫物理設計（主要包括數(shù)據(jù)存儲位置、存儲格式；索引及索引類型。）數(shù)據(jù)存放在 E 盤的根目錄下，數(shù)據(jù)庫存儲格式為 *.mdf 日志存儲格式為 *.ldf索引類型有兩個：1、唯一索引72、聚簇索引因為主鍵就可以看做是一個聚簇索引，每個表都有設主鍵，所以不可在建聚簇索引5數(shù)據(jù)庫實施與測試（源代碼及查詢截圖）-創(chuàng)建數(shù)據(jù)庫create database 小區(qū)業(yè)務管理on primary(name = 小區(qū)業(yè)務管理,filename = E:小區(qū)業(yè)務管理.mdf,size = 3mb , maxsize = 100mb,filegrowth = 10%)log on (name = 小區(qū)業(yè)務管理.ldf,filename = E:小區(qū)業(yè)務管理_log.ldf,size = 1mb , maxsize = 2mb,filegrowth = 10%)go 8-創(chuàng)建 樓盤信息表create table 樓盤信息表(樓盤編號 int primary key not null,樓盤名稱 char(20) not null,樓層數(shù) int not null,面積 int not null,戶型 char(20) not null ,地址 char(20) not null)go-創(chuàng)建表 住戶信息表create table 住戶信息表(業(yè)主編號 int primary key not null,姓名 char(20) not null,性別 char(20) check(性別 in(男,女),電話 int not null,門牌號 int unique not null ,面積 int not null,身份證 char(20) unique not null)go -創(chuàng)建投訴信息表create table 投訴信息表(投訴事項編號 int primary key not null,業(yè)主編號 int not null,投訴名稱 char(20) not null,9解決狀態(tài) char(20) default not,日期 datetime default getdate(),受理人 char(20) not null,foreign key (業(yè)主編號) references 住戶信息表(業(yè)主編號)go-創(chuàng)建表 報修信息表create table 報修信息表(報修事項編號 int primary key not null,業(yè)主編號 int not null,報修名稱 char(20) not null,維修狀態(tài) char(20) default not,日期 datetime default getdate() ,受理人 char(20) not null,foreign key (業(yè)主編號) references 住戶信息表(業(yè)主編號) )go-創(chuàng)建擁有信息表create table 擁有信息表(業(yè)主編號 int primary key not null,樓盤編號 int not null)go10-編輯樓盤信息管理的操作-信息的添加-樓盤信息的添加insert into 樓盤信息表values(1,海天 ,5,120,三室一廳,中山路號 )insert into 樓盤信息表values(2,檸檬夏天 ,10,120,四室一廳,中山路號 )insert into 樓盤信息表values(3,指尖剎那 ,9,300,四室一廳,中山路號 )-住戶信息的添加insert into 住戶信息表values(11,林達 ,女,15980123 ,520,120,3505251992)insert into 住戶信息表values(12,謝可 ,女,15980147 ,521,100,3505251993)insert into 住戶信息表values(13,謝華 ,男,15980520 ,522,180,3505251994)insert into 住戶信息表values(14,張琳 ,男,15980369 ,523,250,3505251995)insert into 住戶信息表values(15,仲夏 ,男,15980789 ,524,300,3505251996)-報修信息的添加insert into 報修信息表( 報修事項編號,業(yè)主編號,報修名稱,受理人)values(10,15,水管爆裂 ,王磚家)insert into 報修信息表( 報修事項編號,業(yè)主編號,報修名稱,受理人)values(11,14,電線端口安裝,王磚家)insert into 報修信息表( 報修事項編號,業(yè)主編號,報修名稱,受理人)11values(12,13,大門鎖壞掉,王磚家) insert into 報修信息表( 報修事項編號,業(yè)主編號,報修名稱,受理人)values(13,12,燈泡更換 ,王磚家) -投訴信息的添加insert into 投訴信息表( 投訴事項編號,業(yè)主編號,投訴名稱,受理人)values(100,11,墻壁裂痕 ,胡說)insert into 投訴信息表( 投訴事項編號,業(yè)主編號,投訴名稱,受理人)values(101,12,停水 ,胡說)insert into 投訴信息表( 投訴事項編號,業(yè)主編號,投訴名稱,受理人)values(102,13,周圍太吵 ,胡說)insert into 投訴信息表( 投訴事項編號,業(yè)主編號,投訴名稱,受理人)values(103,14,亂丟垃圾 ,胡說)-擁有信息表的添加insert into 擁有信息表values(11,1)insert into 擁有信息表values(12,2)insert into 擁有信息表values(13,2)insert into 擁有信息表values(14,2)insert into 擁有信息表values(15,3)-信息的查詢select * from 樓盤信息表12select * from 住戶信息表select * from 報修信息表select * from 投訴信息表select * from 擁有信息表13-信息的編輯-樓盤信息編輯 update 樓盤信息表 set 樓盤名稱 = 彩虹之家where 樓盤名稱 = 海天 go-住戶信息的編輯update 住戶信息表 set 電話 = 1314520where 電話 = 15980123 go-5.3創(chuàng)建視圖：為提高數(shù)據(jù)庫使用效率，增強數(shù)據(jù)庫安全性，按如下要求設計視圖：-1：業(yè)主信息視圖：通過該視圖可以得到住戶信息，包含業(yè)主姓名，樓盤名稱，門牌號，性別，電話，面積，身份證create view 業(yè)主信息視圖asselect 姓名 業(yè)主姓名,樓盤名稱 ,門牌號,性別,電話, 住戶信息表.面積,身份證 from 住戶信息表,樓盤信息表,擁有信息表where 樓盤信息表 .樓盤編號 = 擁有信息表.樓盤編號 and 住戶信息表.業(yè)主編號 = 擁有信息表.業(yè)主編號14-2：未維修事項信息視圖：報修名稱，報修人，樓盤名稱，門牌號，電話，保修日期，受理人。create view 未維修事項信息視圖asselect 報修名稱,姓名 報修人 ,樓盤名稱,門牌號,電話 ,日期 保修日期,受理人 from 報修信息表 ,住戶信息表 ,樓盤信息表,擁有信息表where 維修狀態(tài) != ok and 樓盤信息表.樓盤編號 = 擁有信息表.樓盤編號 and 住戶信息表.業(yè)主編號 = 擁有信息表.業(yè)主編號and 報修信息表 .業(yè)主編號 = 住戶信息表.業(yè)主編號-3.未解決投訴信息視圖：create view 未解決投訴信息視圖asselect 投訴名稱,姓名 投訴人 ,樓盤名稱,門牌號,電話 ,日期 保修日期,受理人 from 投訴信息表 ,住戶信息表 ,樓盤信息表,擁有信息表where 解決狀態(tài) != OK and 樓盤信息表.樓盤編號 = 擁有信息表.樓盤編號 and 住戶信息表.業(yè)主編號 = 擁有信息表.業(yè)主編號and 投訴信息表 .業(yè)主編號 = 住戶信息表.業(yè)主編號15-5.5 創(chuàng)建觸發(fā)器： .如利用觸發(fā)器使相關連的表能實現(xiàn)級聯(lián)更新和級聯(lián)刪除。-實現(xiàn)級聯(lián)更新和級聯(lián)刪除-住戶信息表的級聯(lián)更新刪除create trigger tri_Delete_Updateon 住戶信息表 instead of update ,deleteasbegindeclare inset char(6),delet char(6)select inset=業(yè)主編號 from inserted select delet=業(yè)主編號 from deleted if (update(業(yè)主編號)begin EXEC sp_msforeachtable ALTER TABLE ? NOCHECK CONSTRAINT ALL -禁用約束update 報修信息表set 業(yè)主編號=insetwhere 業(yè)主編號 =deletupdate 投訴信息表set 業(yè)主編號=insetwhere 業(yè)主編號 =deletupdate 擁有信息表set 業(yè)主編號=insetwhere 業(yè)主編號 =deletupdate 住戶信息表set 業(yè)主編號= insetwhere 業(yè)主編號 = deletEXEC sp_msforeachtable ALTER TABLE ? CHECK CONSTRAINT ALL -啟用約束 16endelse begindelete from 報修信息表where 業(yè)主編號 =deletdelete from 投訴信息表where 業(yè)主編號 =deletdelete from 擁有信息表where 業(yè)主編號 =deletdelete from 住戶信息表where 業(yè)主編號 =delet endend-查詢結果select * from 住戶信息表select * from 投訴信息表select * from 報修信息表-測試代碼update 住戶信息表 set 業(yè)主編號 = 520where 業(yè)主編號 = 1117因為做實驗是先建好觸發(fā)器，在用數(shù)據(jù)測試的，且住戶信息表有一個刪除觸發(fā)器，所以顯示刪除成功！delete from 住戶信息表where 業(yè)主編號= 520-樓盤信息表的級聯(lián)更新刪除create trigger tri_樓盤on 樓盤信息表 instead of update ,deleteasbegin18declare inset char(6),delet char(6)select inset=樓盤編號 from inserted select delet=樓盤編號 from deleted if (update(樓盤編號)begin EXEC sp_msforeachtable ALTER TABLE ? NOCHECK CONSTRAINT ALL -禁用約束update 擁有信息表set 樓盤編號=insetwhere 樓盤編號 =deletupdate 樓盤信息表set 樓盤編號 = insetwhere 樓盤編號 = deletEXEC sp_msforeachtable ALTER TABLE ? CHECK CONSTRAINT ALL -啟用約束 endelse begindelete from 擁有信息表where 樓盤編號 =deletdelete from 樓盤信息表where 樓盤編號 =delet endEnd19這個觸發(fā)器是后面做完實驗感覺不完整再添加上去的，所以這里的數(shù)據(jù)是做完試驗后的數(shù)據(jù)。 20-2.如當刪除一條住戶信息記錄能夠顯示提示，當添加一條報修信息能夠顯示提示。create trigger 刪除住戶 on 住戶信息表after deleteasprint 刪除成功 !gocreate trigger 添加報修 on 報修信息表after insertasprint 添加成功 !go-5.6創(chuàng)建存儲過程 :-1.創(chuàng)建查詢某一業(yè)主信息的存儲過程-查詢住戶的信息存儲過程create proc 查詢住戶信息asselect * from 住戶信息表exec 查詢住戶信息21-2.創(chuàng)建添加報修信息記錄的存儲過程create proc 添加報修信息報修事項編號 char(20),業(yè)主編號 char(20),報修名稱 char(20),受理人 char(20)asbegininsert into 報修信息表( 報修事項編號,業(yè)主編號,報修名稱,受理人)values(報修事項編號,業(yè)主編號,報修名稱, 受理人 )end-使用方法 exec 添加報修信息 業(yè)主編號, 報修名稱 , 受理人 -因為維修狀態(tài)和日期默認。exec 添加報修信息 100,12,水管不來水,王磚家-3.創(chuàng)建返回某個樓盤的業(yè)主人數(shù)，并設置沒有指定值時，指定一個默認樓盤-默認樓盤編號為222create proc 業(yè)主人數(shù)asselect COUNT(*) 業(yè)主人數(shù) from 擁有信息表where 樓盤編號 = 2 - 4.利用存儲過程和游標設計報表：- a.輸出指定的樓盤名稱如輸入“樓盤名稱，輸出一張報表顯示該樓盤里面的所有住戶信息，如下：/* 樓盤名稱業(yè)主姓名 性別 電話 門牌號 面積 身份證-徐紅 女 12345678 A301 105平方 12345678909 - */create proc 樓盤住戶表樓盤名稱 char(20)asprint 樓盤名稱begindeclare 姓名 char(10)declare 性別 char(10)declare 電話 char(10)declare 門牌號 char(10)declare 面積 char(10)declare 身份證 char(10)declare GR cursorfor select 姓名,性別,電話,門牌號 ,面積,身份證 from 住戶信息表where 業(yè)主編號 in (select 業(yè)主編號 from 擁有信息表where 樓盤編號 in (select 樓盤編號 from 樓盤信息表where 樓盤名稱 = 樓盤名稱)open GRfetch next from GR into 姓名, 性別,電話,門牌號,面積,身份證23print -print 業(yè)主姓名 + + 性別 + + 電話+ + 門牌號 + + 面積+ + 身份證while FETCH_STATUS = 0beginprint 姓名 + 性別 + 電話+ 門牌號 + 面積+ 身份證fetch next from GR into 姓名, 性別,電話,門牌號,面積,身份證endprint -close GRdeallocate GR endgo 24-b.設計一個存儲過程，在該存儲過程中建立生成報表的游標，分別顯示-已解決的報修事項信息和未解決的報修事項信息。-存儲過程. 生成報表create proc 報修情況asbegindeclare 業(yè)主編號 char(10),報修名稱 char(10), 日期 char(22)declare 受理人 char(10)-已解決的報修declare BX_OK cursorforselect 業(yè)主編號,報修名稱 ,日期,受理人 from 報修信息表where 維修狀態(tài) = okope