2014小区物业管理数据库设计.doc

1、数据库设计报告题目： 小区物业管理数据库设计 专业： 班级： 学号： 姓名： 指导教师： 完成日期： 2014 年 05 月 19 日小区物业管理数据库设计*（国脉信息学院 12 级 1 班）摘要关键词: 数据库、增删改查、触发器、视图目 录1概述 .12需求分析 .12.1 任务 .12.2 系统功能模块 .12.3 数据流程图 .32.4 数据字典 53数据库结构设计 .73.1 概念设计 .73.1.1 分 E-R 图建立 .83.1.2 全局/整体 E-R 图 93.2 逻辑设计 .93.2.1 逻辑设计目标93.2.2 建立关系模式102.2.3 关系模式规范化处理102.2.4 关

2、系模式逻辑结构定义114数据库物理设计 .114.1 物理设计阶段的目标和任务 .124.2 数据存储 .125数据库实施与测试 .125.1 数据库实施 .125.1.1 数据库及数据库对象建立125.1.1.1 基本表.155.1.12 索引165.1.1.3 视图.175.1.14 触发器185.1.1.3 存储过程.175.1.2 数据入库125.2 数据库测试 .135.2.1 查询175.2.2 更新186总结 .137参考文献 .131概述（设计背景，设计目的，设计内容）背景：数据库基础与应用课程设计是计算机专业集中实践性环节之一，是学习完数据库系统概论课程后进行的一次全面的综合

3、练习。目的：在于加深对数据库基础理论和基本知识的理解，掌握进行数据库开发的全过程，提高运用数据库解决实际问题的能力。设计内容：进行需求分析，设计数据库的概念模型。系统基本功能：楼盘信息管理：楼盘信息的添加、编辑和查询；住户信息管理：住户信息的添加、编辑和查询；报修事项管理：报修事项的添加、编辑和查询；投诉信息管理：投诉信息的添加、编辑和查询；报表管理：输出楼盘信息、住户信息、报修信息和投诉信息。（视图）其中：（除了以下信息，也可以按需要增加属性列）楼盘信息：包括的主要数据项有：楼盘编号，楼盘名称，楼层数，面积，户型，地址住户信息：包括的主要数据项有：业主编号，姓名，性别，电话，门牌号，面积，身

4、份证报修事项：包括的主要数据项有：报修事项编号，报修名称，业主编号，维修状态，日期，受理人投诉信息：包括的主要数据项有：投诉事项编号，投诉名称，业主编号，解决状态，日期，受理人2. 需求分析（功能分析、工作流图或功能模块、数据流图、数据字典）2.1 功能分析楼盘信息管理：楼盘信息的添加、编辑和查询；住户信息管理：住户信息的添加、编辑和查询；报修事项管理：报修事项的添加、编辑和查询；投诉信息管理：投诉信息的添加、编辑和查询；报表管理：输出楼盘信息、住户信息、报修信息和投诉信息。（视图）2.2 数据流图楼盘信息管理住户购买房子 登记信息 付款 工作人员录入住户信息到数据库 投诉信息管理用户提出投诉

5、 工作人员登记投诉信息 把投诉信息录入到数据库 工作人员处理投诉事宜 提交处理结果 将处理结果保存到数据库保修信息管理用户提交保修请求 工作人员登记保修信息 把保修信息录入到数据库 工作人员处理保修事宜 提交处理结果 将处理结果保存到数据库2.3 数据字典数据项数据项编号 数据项名称 数据类型 长度 可否为空 描述1 楼盘编号 int 否 楼盘的编号2 楼盘名称 vachar 20 否 楼盘的名称3 楼层数 int 楼盘拥有的层数4 面积 real 楼盘所占面积5 户型 vachar 8 所属户型数据结构数据结构编号数据结构名称 数据结构定义 组成1 楼盘信息表 楼盘信息 楼盘编号，楼盘名称，

6、楼层数，面积，户型，地址2 住户信息表 住户信息 业主编号，姓名，性别，门牌号，面积，电话，身份证，楼盘编号，3 报修信息表 报修信息 报修事项编号，报修名称，业主编号，受理人，报修状态，日期4 投诉信息表 投诉信息 投诉事项编号，投诉名称，业主编号，受理人，投诉事项，日期3数据库结构3.1 概念模型设计（E-R 图）6 地址 varchar 20 小区所在地址7 业主编号 int 否 业主的编号8 姓名 varchar 20 否 业主的姓名9 电话 Varchar20 否 业主的联系号码10 门牌号 int 业主所在门牌号11 性别 char 8 业主的性别12 身份证 varchar 20

7、 否 业主的身份证号13 报修编号 int 否 保修的编号14 报修名称 Varchar 20 保修的事项名称15 维修状态 char 8 是否维修16 日期 datetime 受理日期17 受理人 varchar 20 否 受理人姓名18 投诉编号 int 否 投诉的编号19 投诉名称 varchar 20 投诉的事项名称20 解决状态 Char 8 是否处理投诉E-R 图楼盘居住住户提出投诉提出 报修1NN NNN3.2 逻辑设计（E-R 模型转换为关系模式，数据库表的设计）楼盘信息（楼盘编号，楼盘名称，楼层数，面积，户型，地址）住户信息（业主编号，楼盘编号，姓名，性别，电话，门牌号，面积

8、，身份证）报修信息（报修事项编号，报修名称，业主编号，维修状态，日期，受理人）投诉信息（投诉事项编号，投诉名称，业主编号，解决状态，日期，受理人）数据库表的设计楼盘信息表名称 数据类型 可否为空 说明楼盘编号 int 否 主键楼盘名称 Vachar（20 ） 否楼层数 int面积 real户型 Vachar（20 ）地址 Varchar（20）业主信息表名称 数据类型 可否为空 说明业主编号 int 否 主键姓名 Vachar（20 ） 否性别 Char（8）电话 Varchar(20) 否门牌号 int面积 real身份证 Varchar（20） 否楼盘编号 int 否 外键报修信息表名称

9、数据类型 可否为空 说明报修事项编号 int 否 主键报修名称 Varchar（20 ） 否业主编号 int 否 外键维修状态 Char（8）日期 datetime受理人 Varchar（20 ） 否投诉信息表名称 数据类型 可否为空 说明投诉事项编号 int 否 主键投诉名称 Varchar 否业主编号 int 否 外键解决状态 Char（8）日期 datetime受理人 Varchar（20 ） 否4 数据库物理设计（主要包括数据存储位置、存储格式；索引及索引类型。）数据存放在 d 盘的目录下：D:sql数据库设计小区物业管理 .mdfD:sql数据库设计小区物业管理.mdf建立索引：5数

10、据库实施与测试（源代码及查询截图）数据库：CREATE DATABASE 小区物业管理ON PRIMARY(NAME = 小区物业管理,FILENAME = D:sql数据库设计小区物业管理 .mdf,SIZE = 3MB,MAXSIZE = 80MB,FILEGROWTH = 10%)LOG ON(NAME = 小区物业管理.ldf,FILENAME = D:sql数据库设计小区物业管理 _log.ldf,SIZE = 1MB,MAXSIZE = 20MB,FILEGROWTH = 10%)基本表：1.楼盘信息表CREATE TABLE 楼盘信息表(楼盘编号INT PRIMARY KEY N

11、OT NULL,楼盘名称VARCHAR (20) UNIQUE NOT NULL,楼层数INT ,面积REAL,户型VARCHAR (20),地址VARCHAR (20)2.住户信息表CREATE TABLE 住户信息表(业主编号INT PRIMARY KEY NOT NULL,姓名VARCHAR (20) NOT NULL,性别CHAR(8) CHECK(性别= 男 or 性别= 女 ),电话VARCHAR (20) NOT NULL,门牌号INT ,面积REAL,身份证VARCHAR (20) NOT NULL,楼盘编号INT ,FOREIGN KEY (楼盘编号) REFERENCES

12、楼盘信息表( 楼盘编号) )3.CREATE TABLE 报修信息表(报修事项编号INT PRIMARY KEY NOT NULL,报修名称VARCHAR (20) NOT NULL,业主编号INT NOT NULL,维修状态CHAR(8) CHECK(维修状态= 已维修 OR 解决状态= 未维修),日期DATETIME NOT NULL,受理人VARCHAR (20) NOT NULL,FOREIGN KEY (业主编号) REFERENCES 住户信息表( 业主编号)4.CREATE TABLE 投诉信息表(投诉事项编号INT PRIMARY KEY NOT NULL,投诉名称VARCHA

13、R (20) NOT NULL,业主编号INT NOT NULL,解决状态CHAR(8) CHECK(解决状态= 已解决 OR 解决状态= 未解决),日期DATETIME NOT NULL,受理人VARCHAR (20) NOT NULL,FOREIGN KEY (业主编号) REFERENCES 住户信息表( 业主编号)楼盘信息的添加INSERT INTO 楼盘信息表VALUES(1,花园小区号楼 ,7,1800.5,A类,福州晋安区鼓岭 )INSERT INTO 楼盘信息表VALUES(2,花园小区号楼 ,5,2100.5,B类,福州晋安区鼓岭)INSERT INTO 楼盘信息表VALUE

14、S(3,花园小区号楼 ,9,1000.5,A类,福州晋安区鼓岭 )INSERT INTO 楼盘信息表VALUES(4,花园小区号楼 ,5,4000.5,C类,福州晋安区鼓岭)INSERT INTO 楼盘信息表VALUES(5,花园小区号楼 ,6,3000.5,D 类,福州晋安区鼓岭 )住户信息添加：INSERT INTO 住户信息表values(1101,张三 ,男,18759010824,101,80.5,350821193705114448,1)INSERT INTO 住户信息表values(1102,李四 ,女,18759010825,102,180,350821198808154416

15、,1)INSERT INTO 住户信息表values(2307,王五 ,男,18759090824,307,135.5,350821196701011111,2)INSERT INTO 住户信息表values(4401,刘柳 ,女,13799010824,401,80.5,350821199111011148,4)INSERT INTO 住户信息表values(5115,蔡七 ,女,18759000000,115,280.5,350821199013145200,5)投诉信息添加INSERT INTO 投诉信息表 (投诉事项编号,投诉名称,业主编号,受理人)VALUES(0001,卫生间修理迟

16、迟不到位 ,1101,李小姐 )INSERT INTO 投诉信息表 (投诉事项编号,投诉名称,业主编号,受理人)VALUES(0002,维修服务态度差 ,5115,李小姐)报修信息添加INSERT INTO 报修信息表 (报修事项编号,报修名称,业主编号,受理人)values(1100,卫生间漏水 ,1101,王师傅)INSERT INTO 报修信息表 (报修事项编号,报修名称,业主编号,受理人)values(1101,水管破裂 ,4401,李师傅)INSERT INTO 报修信息表 (报修事项编号,报修名称,业主编号,受理人)values(1102,煤气阀门失灵,5115,陈师傅)信息的修改

17、（解决状态和维修状态）UPDATE 投诉信息表SET 解决状态= 未解决UPDATE 报修信息表SET 维修状态= 未维修视图：业主信息视图CREATE VIEW 业主信息ASSELECT 姓名,楼盘名称,门牌号,性别,电话,住户信息表.面积,身份证FROM 住户信息表,楼盘信息表WHERE 住户信息表.楼盘编号= 楼盘信息表.楼盘编号未维修事项信息视图CREATE VIEW 未维修事项信息(报修名称,报修人,楼盘名称,门牌号,电话,报修日期,受理人)ASSELECT 报修名称,姓名,楼盘名称,门牌号,电话,日期,受理人FROM 报修信息表,住户信息表,楼盘信息表WHERE 报修信息表.业主编

18、号= 住户信息表.业主编号 and 住户信息表.楼盘编号=楼盘信息表.楼盘编号 and 维修状态= 未维修未解决投诉信息视图CREATE VIEW 未解决投诉信息(投诉名称,投诉人,楼盘名称,门牌号,电话,投诉日期,受理人)ASSELECT 投诉名称,姓名,楼盘名称,门牌号,电话,日期,受理人FROM 投诉信息表,住户信息表,楼盘信息表WHERE 投诉信息表.业主编号= 住户信息表.业主编号 and 住户信息表.楼盘编号=楼盘信息表.楼盘编号 and 解决状态= 未解决索引：CREATE UNIQUE INDEX 升序楼盘编号ON 楼盘信息表(楼盘编号)CREATE UNIQUE INDEX

19、升序业主编号ON 住户信息表(业主编号)CREATE UNIQUE INDEX 升序报修事项编号ON 报修信息表(报修事项编号)CREATE UNIQUE INDEX 升序投诉事项编号 ON 投诉信息表(投诉事项编号)触发器：1.CREATE TRIGGER 级联_楼盘ON 楼盘信息表INSTEAD OF UPDATE,DELETEAS BEGINDECLARE 楼盘编号INT,楼盘编号INTSELECT 楼盘编号= 楼盘编号FROM INSERTEDSELECT 楼盘编号= 楼盘编号FROM DELETEDIF(UPDATE(楼盘编号)BEGINEXEC sp_msforeachtable

20、ALTER TABLE ? NOCHECK CONSTRAINT ALL UPDATE 楼盘信息表SET 楼盘编号=楼盘编号WHERE 楼盘编号= 楼盘编号UPDATE 住户信息表SET 楼盘编号=楼盘编号WHERE 楼盘编号= 楼盘编号EXEC sp_msforeachtable ALTER TABLE ? CHECK CONSTRAINT ALL ENDELSEBEGINDELETE 楼盘信息表WHERE 楼盘编号= 楼盘编号ENDEND2.CREATE TRIGGER 提示_信息ON 住户信息表AFTER INSERTAS BEGINPRINT添加信息成功ENDCREATE TRIGGE

21、R 提示_信息a ON 住户信息表AFTER DELETEAS BEGINPRINT删除信息成功END存储过程1.CREATE PROCEDURE 查询_存储ASSELECT *FROM 住户信息表EXEC 查询_存储2.CREATE PROCEDURE 报修休息_存储 报修事项编号 INT,报修名称VARCHAR(20),业主编号INT,受理人VARCHAR(20)AS BEGININSERT INTO 报修信息表 (报修事项编号,报修名称,业主编号,受理人)VALUES(报修事项编号,报修名称, 业主编号 ,受理人)ENDEXEC 报修休息_存储1103,煤气阀门失灵,5115, 陈师傅

22、3.CREATE PROCEDURE 总人数_存储AS BEGINDECLARE 总人数INTSELECT 总人数 =COUNT(业主编号)FROM 住户信息表WHERE 楼盘编号= 11RETURN 总人数ENDDECLARE jieshou INTEXEC jieshou=总人数_存储PRINT jieshou4.ACREATE PROC 查询楼盘住户 楼盘名称VARCHAR( 20)ASPRINT 楼盘名称BEGINDECLARE 业主姓名VARCHAR (20),性别VARCHAR(8),电话VARCHAR(20),面积VARCHAR(20), 身份证VARCHAR( 20)DECLA

23、RE CHECK_住户CURSOR FORSELECT 姓名,性别,电话,面积,身份证FROM 住户信息表where 楼盘编号 =(SELECT 楼盘编号FROM 楼盘信息表WHERE 楼盘名称=楼盘名称)OPEN CHECK_住户FETCH FROM CHECK_住户INTO 业主姓名,性别,电话,面积, 身份证PRINT业主姓名+ +性别 + +电话+ +面积+ +身份证PRINT-WHILE FETCH_STATUS = 0BEGINPRINT 业主姓名+ + 性别+ 电话+ + 面积+ + 身份证FETCH FROM CHECK_住户INTO 业主姓名,性别,电话,面积, 身份证END

24、PRINT-ENDCLOSE CHECK_住户DEALLOCATE CHECK_住户EXEC 查询楼盘住户 花园小区号楼B.CREATE PROC 报修情况AS BEGINDECLARE 报修事项编号varchar (20),报修名称 varchar(20),业主编号varchar(20),受理人varchar(20)DECLARE 报修CURSOR FORSELECT 报修事项编号,报修名称,业主编号,受理人FROM 报修信息表WHERE 维修状态= 已维修OPEN 报修FETCH FROM 报修INTO 报修事项编号,报修名称,业主编号,受理人PRINT已维修WHILE FETCH_STA

25、TUS = 0BEGINPRINT 报修事项编号+ + 报修名称+ +业主编号+ +受理人FETCH FROM 报修INTO 报修事项编号,报修名称,业主编号,受理人ENDCLOSE 报修DEALLOCATE 报修PRINTPRINTDECLARE 报修_未修CURSOR FORSELECT 报修事项编号,报修名称,业主编号,受理人FROM 报修信息表WHERE 维修状态= 未维修OPEN 报修_未修FETCH FROM 报修_未修INTO 报修事项编号,报修名称,业主编号,受理人PRINT未维修WHILE FETCH_STATUS = 0BEGINPRINT 报修事项编号+ + 报修名称+

26、+业主编号+ +受理人FETCH FROM 报修_未修INTO 报修事项编号,报修名称,业主编号,受理人ENDENDCLOSE 报修_未修DEALLOCATE 报修_未修exec 报修情况C.CREATE PROC 投诉情况AS BEGIN DECLARE 投诉事项编号VARCHAR (20),投诉名称VARCHAR(20),业主编号VARCHAR (20),受理人VARCHAR (20)DECLARE 投诉CURSOR FORSELECT TOP 2 投诉事项编号,投诉名称,业主编号,受理人FROM 投诉信息表OPEN 投诉FETCH FROM 投诉INTO 投诉事项编号,投诉名称,业主编号

27、,受理人PRINTTOP 1 AND 2WHILE FETCH_STATUS =0BEGINPRINT 投诉事项编号+ + 投诉名称+ +业主编号+ +受理人FETCH FROM 投诉INTO 投诉事项编号,投诉名称,业主编号,受理人ENDCLOSE 投诉DEALLOCATE 投诉DECLARE 投诉CURSOR FORSELECT TOP 1 投诉事项编号,投诉名称,业主编号,受理人FROM 投诉信息表ORDER BY 业主编号DESCOPEN 投诉FETCH FROM 投诉INTO 投诉事项编号,投诉名称,业主编号,受理人PRINTLAST 1WHILE FETCH_STATUS =0BEGINPRINT 投诉事项编号+ + 投诉名称+ +业主编号+ +受理人FETCH FROM 投诉INTO 投诉事项编号,投诉名称,业主编号,受理人ENDENDCLOSE 投诉DEALLOCATE 投诉6总结（设计过程中遇到的问题以及解决方法；课程学习及设计的体会）。