小区物业管理数据库设计培训讲学.doc

小区物业管理数据库设计

精品文档

《数据库应用课程设计报告》

题目： 小区物业管理数据库设计

专业：
班级：
学号：
姓名：
指导教师：

完成日期： 年 月 日

收集于网络，如有侵权请联系管理员删除

小区物业管理数据库设计

（国脉信息学院 11级1班）

摘要：
这个数据库是管理楼盘住户的信息，便于管理用户的信息。用数据库管理可以更高效和更清晰的看清住户的信息还有投诉，保修等信息，有利于小区的管理，每个小区 都必须应用一个小区的数据库管理系统，这个才能更好的管理。如果一个小区没有一个数据库管理的系统，小区的管理就会混乱，住户的投诉和保修就得不到解决，所以数据库您值得拥有！

关键词: 数据库 级联更新、删除 触发器 视图

目 录
1．概述 1
1.1设计背景： 1
1.2设计目的： 1
1.3设计内容： 1
2. 需求分析 2
2.1 系统功能分析： 2
2.2 数据字典 2
2.3数据流程图： 3
3．数据库结构 3
3.1概念模型设计（E-R图） 3
3.2逻辑设计 5
4． 数据库物理设计（主要包括数据存储位置、存储格式；索引及索引类型。） 6
5． 数据库实施与测试（源代码及查询截图） 8
6． 总结（设计过程中遇到的问题以及解决方法；课程学习及课程设计的体会）。 28
7. 参考文献 28

1．概述

1.1设计背景：
数据库基础与应用课程设计是计算机专业集中实践性环节之一，是学习完《数据库系统概论》课程后进行的一次全面的综合练习。
1.2设计目的：
在于加深对数据库基础理论和基本知识的理解，掌握进行数据库开发的全过程，提高运用数据库解决实际问题的能力。
1.3设计内容：
进行需求分析，设计数据库的概念模型。系统基本功能：
楼盘信息管理：楼盘信息的添加、编辑和查询；
住户信息管理：住户信息的添加、编辑和查询；
报修事项管理：报修事项的添加、编辑和查询；
投诉信息管理：投诉信息的添加、编辑和查询；
报表管理：输出楼盘信息、住户信息、报修信息和投诉信息。
其中：（除了以下信息，也可以按需要增加属性列）
楼盘信息：包括的主要数据项有：楼盘编号，楼盘名称，楼层数，面积，户型，地址
住户信息：包括的主要数据项有：业主编号，姓名，性别，电话，门牌号，面积，身份证
报修事项：包括的主要数据项有：报修事项编号，报修名称，业主编号，维修状态，日期，受理人
投诉信息：包括的主要数据项有：投诉事项编号，投诉名称，业主编号，解决状态，日期，受理人
2. 需求分析
2.1 系统功能分析：
楼盘信息管理：楼盘信息的添加、编辑和查询；
住户信息管理：住户信息的添加、编辑和查询；
报修事项管理：报修事项的添加、编辑和查询；
投诉信息管理：投诉信息的添加、编辑和查询；
报表管理：输出楼盘信息、住户信息、报修信息和投诉信息。
2.2 数据字典
数据项
数据项编号
数据项名称
数据类型
长度
可否为空
描述
1
楼盘编号
int

否
楼盘的编号
2
楼盘名称
char
20
否
楼盘的名称
3
楼层数
int

否
楼盘的层数
4
面积
int

否
房间的大小
5
户型
char
20
否
属于什么户型
6
地址
char
20
否
所在地址
7
业主编号
int

否
业主的编号
8
名字
char
20
否
业主的名字
9
性别
char
20
否
业主性别
10
电话
int

否
业主电话
11
门牌号
int

否
房间门牌号
12
身份证
char
20
否
业主身份证
13
报修事项号
int

否
报修事项编号
14
报修名称
char
20
否
报修的东西
15
维修状态
char
20
否
维修的状态
16
日期
datetime
20
否
受理时间
17
受理人
char
20
否
管理人员
18
投诉事项编号
int

否
投诉事项编号
19
投诉名称
char
20
否
投诉的东西
20
解决状态
char
20
否
解决的状态

数据结构：
数据结构编号
数据结构名
数据结构定义
组成
1
楼盘信息表
楼盘信息
楼盘编号，楼盘名称，楼层数，面积，户型，地址
2
住户信息表
住户信息
业主编号，姓名，性别，电话，门牌号，面积，身份证
3
报修信息表
报修信息
报修事项编号，报修名称，业主编号，维修状态，日期，受理人
4
投诉信息表
投诉信息
投诉事项编号，投诉名称，业主编号，解决状态，日期，受理人

2.3数据流程图：
楼盘信息管理：
住户看好楼盘 à 填购买表 à 付款 à 管理人员把资料录入数据库保存à带住户到房间及交代一些注意事项

投诉事项管理：
住户提出投诉 à 填投诉表 à 交给管理人员 à 录入数据库 à工作人员来处理 à工作人员上报处理投诉结果 à再次录入数据库保存

报修事项管理：
住户申请报修 à 填申请表 à 交给管理人员 à 录入数据库 à专门人员上门修理 à工作人员上报修理结果 à录入数据库保存

住户信息管理：
住户申请修改住户信息 à 填住户信息表 à 管理人员审核à审核通过录入信息到数据库保存
3．数据库结构
3.1概念模型设计（E-R图）
实体及其属性图：
(1).楼盘信息表的E-R图

.住户信息表的E-R图

.保修信息表的E-R图

.

投诉信息表的E-R图

(2).
实体及其联系图：

3.2逻辑设计

关系模式：
楼盘（楼盘编号，楼盘名称，楼层数，面积，户型，地址）
住户（业主编号，姓名，性别，电话，门牌号，面积，身份证）
投诉（投诉事项编号，业主编号，投诉名称，解决状态，日期，受理人）
报修（报修事项编号，业主编号，报修名称，维修状态，日期，受理人）
拥有（业主编号，楼盘编号）

数据库表的设计

楼盘信息表
字段名称
数据类型
可否为空
说明
楼盘编号
int
否
主键
楼盘名称
char(20)
否

楼层数
int
否

面积
int
否

户型
char(20)
否

地址
char(20)
否

住户信息表
字段名称
数据类型
可否为空
说明
业主编号
int
否
主键
姓名
char(20)
否

性别
char(20)
可

电话
int
否

门牌号
int
否

面积
int
否

身份证
char(20)
否

投诉信息表
字段名称
数据类型
可否为空
说明
投诉事项编号
int
否
主键
业主编号
char(20)
否
外键
投诉名称
char(20)
否

解决状态
char(20)
否
默认not
日期
datetime
否
默认当前时间
受理人
char(20)
否

报修信息表
字段名称
数据类型
可否为空
说明
报修事项编号
int
否
主键
业主编号
char(20)
否
外键
保修名称
char(20)
否

维修状态
char(20)
否
默认not
日期
datetime
否
默认当前时间
受理人
char(20)
否

拥有信息表
字段名称
数据类型
可否为空
说明
业主编号
int
否
主键
楼盘编号
int
否

4． 数据库物理设计（主要包括数据存储位置、存储格式；索引及索引类型。）
数据存放在E盘的根目录下，数据库存储格式为 *****.mdf
日志存储格式为 *****.ldf

索引类型有两个：
一、 唯一索引

二、 聚簇索引
因为主键就可以看做是一个聚簇索引，每个表都有设主键，所以不可在建聚簇索引

5． 数据库实施与测试（源代码及查询截图）
--创建数据库
create database 小区业务管理
on primary
(
name = '小区业务管理',
filename = 'E:\小区业务管理.mdf',
size = 3mb ,
maxsize = 100mb,
filegrowth = 10%
)
log on
(
name = '小区业务管理.ldf',
filename = 'E:\小区业务管理_log.ldf',
size = 1mb ,
maxsize = 2mb,
filegrowth = 10%
)
go

--创建 楼盘信息表
create table 楼盘信息表
(
楼盘编号 int primary key not null,
楼盘名称 char(20) not null,
楼层数 int not null,
面积 int not null,
户型 char(20) not null ,
地址 char(20) not null
)
go

--创建表 住户信息表
create table 住户信息表
(
业主编号 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

--创建投诉信息表
create table 投诉信息表
(
投诉事项编号 int primary key not null,
业主编号 int not null,
投诉名称 char(20) not null,
解决状态 char(20) default 'not',
日期 datetime default getdate(),
受理人 char(20) not null,
foreign key (业主编号) references 住户信息表(业主编号)

)
go

--创建表 报修信息表
create table 报修信息表
(
报修事项编号 int primary key not null,
业主编号 int not null,
报修名称 char(20) not null,
维修状态 char(20) default 'not',
日期 datetime default getdate() ,
受理人 char(20) not null,
foreign key (业主编号) references 住户信息表(业主编号)

)
go

--创建拥有信息表
create table 拥有信息表
(
业主编号 int primary key not null,
楼盘编号 int not null
)
go

--编辑楼盘信息管理的操作

--信息的添加

--楼盘信息的添加
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 报修信息表(报修事项编号,业主编号,报修名称,受理人)
values(10,15,'水管爆裂','王砖家')

insert into 报修信息表(报修事项编号,业主编号,报修名称,受理人)
values(11,14,'电线端口安装','王砖家')

insert into 报修信息表(报修事项编号,业主编号,报修名称,受理人)
values(12,13,'大门锁坏掉','王砖家')

insert into 报修信息表(报修事项编号,业主编号,报修名称,受理人)
values(13,12,'灯泡更换','王砖家')

--投诉信息的添加

insert into 投诉信息表(投诉事项编号,业主编号,投诉名称,受理人)
values(100,11,'墙壁裂痕','胡说')

insert into 投诉信息表(投诉事项编号,业主编号,投诉名称,受理人)
values(101,12,'停水','胡说')

insert into 投诉信息表(投诉事项编号,业主编号,投诉名称,受理人)
values(102,13,'周围太吵','胡说')

insert into 投诉信息表(投诉事项编号,业主编号,投诉名称,受理人)
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 楼盘信息表

select * from 住户信息表

select * from 报修信息表

select * from 投诉信息表

select * from 拥有信息表

--信息的编辑

--楼盘信息编辑
update 楼盘信息表 set 楼盘名称 = '彩虹之家'
where 楼盘名称 = '海天'
go

--住户信息的编辑
update 住户信息表 set 电话 = '1314520'
where 电话 = '15980123'
go

--5.3创建视图：为提高数据库使用效率，增强数据库安全性，按如下要求设计视图：
--1：业主信息视图：通过该视图可以得到住户信息，包含业主姓名，楼盘名称，门牌号，性别，电话，面积，身份证

create view 业主信息视图
as
select 姓名 业主姓名,楼盘名称,门牌号,性别,电话,住户信息表.面积,身份证 from 住户信息表,楼盘信息表,拥有信息表
where 楼盘信息表.楼盘编号 = 拥有信息表.楼盘编号 and 住户信息表.业主编号 = 拥有信息表.业主编号

--2：未维修事项信息视图：报修名称，报修人，楼盘名称，门牌号，电话，保修日期，受理人。

create view 未维修事项信息视图
as
select 报修名称,姓名 报修人,楼盘名称,门牌号,电话,日期 保修日期,受理人 from 报修信息表,住户信息表,楼盘信息表,拥有信息表
where 维修状态 != 'ok' and 楼盘信息表.楼盘编号 = 拥有信息表.楼盘编号 and 住户信息表.业主编号 = 拥有信息表.业主编号
and 报修信息表.业主编号 = 住户信息表.业主编号

--3.未解决投诉信息视图：

create view 未解决投诉信息视图
as
select 投诉名称,姓名 投诉人,楼盘名称,门牌号,电话,日期 保修日期,受理人 from 投诉信息表,住户信息表,楼盘信息表,拥有信息表
where 解决状态 != 'OK' and 楼盘信息表.楼盘编号 = 拥有信息表.楼盘编号 and 住户信息表.业主编号 = 拥有信息表.业主编号
and 投诉信息表.业主编号 = 住户信息表.业主编号

--5.5 创建触发器：.如利用触发器使相关连的表能实现级联更新和级联删除。

--实现级联更新和级联删除
--住户信息表的级联更新删除
create trigger tri_Delete_Update
on 住户信息表 instead of update ,delete
as
begin
declare @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 业主编号=@inset
where 业主编号=@delet

update 投诉信息表
set 业主编号=@inset
where 业主编号=@delet

update 拥有信息表
set 业主编号=@inset
where 业主编号=@delet

update 住户信息表
set 业主编号= @inset
where 业主编号= @delet

EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
--启用约束
end

else
begin
delete from 报修信息表
where 业主编号=@delet

delete from 投诉信息表
where 业主编号=@delet

delete from 拥有信息表
where 业主编号=@delet

delete from 住户信息表
where 业主编号=@delet
end
end

--查询结果

select * from 住户信息表
select * from 投诉信息表
select * from 报修信息表

--测试代码
update 住户信息表 set 业主编号 = 520
where 业主编号 = 11

因为做实验是先建好触发器，在用数据测试的，且住户信息表有一个删除触发器，所以显示删除成功！

delete from 住户信息表
where 业主编号= 520

--楼盘信息表的级联更新删除
create trigger tri_楼盘
on 楼盘信息表 instead of update ,delete
as
begin
declare @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 楼盘编号=@inset
where 楼盘编号=@delet

update 楼盘信息表
set 楼盘编号 = @inset
where 楼盘编号 = @delet

EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL' --启用约束
end

else
begin

delete from 拥有信息表
where 楼盘编号=@delet

delete from 楼盘信息表
where 楼盘编号=@delet
end
End

这个触发器是后面做完实验感觉不完整再添加上去的，所以这里的数据是做完试验后的数据。

--2.如当删除一条住户信息记录能够显示提示，当添加一条报修信息能够显示提示。
create trigger 删除住户 on 住户信息表
after delete
as
print '删除成功!!'
go

create trigger 添加报修 on 报修信息表
after insert
as
print '添加成功!!'
go

--5.6创建存储过程:
--1.创建查询某一业主信息的存储过程

--查询住户的信息存储过程
create proc 查询住户信息
as
select * from 住户信息表

exec 查询住户信息

--2.创建添加报修信息记录的存储过程

create proc 添加报修信息
@报修事项编号 char(20),
@业主编号 char(20),
@报修名称 char(20),
@受理人 char(20)
as
begin
insert into 报修信息表(报修事项编号,业主编号,报修名称,受理人)
values(@报修事项编号,@业主编号,@报修名称,@受理人)
end

--使用方法 exec 添加报修信息'业主编号', '报修名称' , '受理人'
--因为维修状态和日期默认。
exec 添加报修信息 '100','12','水管不来水','王砖家'

--3.创建返回某个楼盘的业主人数，并设置没有指定值时，指定一个默认楼盘
--默认楼盘编号为2
create proc 业主人数
as
select COUNT(*) 业主人数 from 拥有信息表
where 楼盘编号 = 2

-- 4.利用存储过程和游标设计报表：
--　a.输出指定的楼盘名称如输入“楼盘名称，输出一张报表显示该楼盘里面的所有住户信息，如下：
/*　　
楼盘名称
业主姓名 性别 电话 门牌号 面积 身份证
------------------------------------------------------------
徐红 女 12345678 A301 105平方 12345678909
……
------------------------------------------------------------ */

create proc 楼盘住户表
@楼盘名称 char(20)
as
print @楼盘名称
begin
declare @姓名 char(10)
declare @性别 char(10)
declare @电话 char(10)
declare @门牌号 char(10)
declare @面积 char(10)
declare @身份证 char(10)
declare GR cursor
for
select 姓名,性别,电话,门牌号,面积,身份证 from 住户信息表
where 业主编号 in (select 业主编号 from 拥有信息表
where 楼盘编号 in (select 楼盘编号 from 楼盘信息表
where 楼盘名称 = @楼盘名称))
open GR
fetch next from GR into @姓名,@性别,@电话,@门牌号,@面积,@身份证
print '--------------------------------------------------------------------------------'
print '业主姓名' +' '+ '性别' +' '+ '电话'+' '+ '门牌号' +' '+ '面积'+' '+ '身份证'
while @@FETCH_STATUS = 0
begin
print @姓名 + @性别 + @电话+ @门牌号 + @面积+ @身份证
fetch next from GR into @姓名,@性别,@电话,@门牌号,@面积,@身份证
end
print '--------------------------------------------------------------------------------'
close GR
deallocate GR
end
go

--b.设计一个存储过程，在该存储过程中建立生成报表的游标，分别显示
--已解决的报修事项信息和未解决的报修事项信息。

--存储过程...生成报表
create proc 报修情况
as
begin
declare @业主编号 char(10),@报修名称 char(10), @日期 char(22)
declare @受理人 char(10)

--已解决的报修
declare BX_OK cursor
for
select 业主编号,报修名称,日期,受理人 from 报修信息表
where 维修状态 = 'ok'
open BX_OK
fetch next from BX_OK into @业主编号,@报修名称,@日期,@受理人
print '已经解决的报修事项'
print '--------------------------------------------------------------------------------'
print '业主编号 ' + '报修名称 ' + '日期 ' + '受理人 '
while @@FETCH_STATUS = 0
begin
print @业主编号 + @报修名称 + @日期 + @受理人
fetch next from BX_OK into @业主编号,@报修名称,@日期,@受理人
end
print '--------------------------------------------------------------------------------'
close BX_OK
deallocate BX_OK
print ''
print ''

--未完成报修的游标操作
declare BX_NOT cursor
for
select 业主编号,报修名称,日期,受理人 from 报修信息表
where 维修状态 = 'not'
open BX_NOT
fetch next from BX_NOT into @业主编号,@报修名称,@日期,@受理人
print '未解决的报修事项'
print '--------------------------------------------------------------------------------'
print '业主编号 ' + '报修名称 ' + '日期 ' + '受理人 '
while @@FETCH_STATUS = 0
begin
print @业主编号 + @报修名称 + @日期 + @受理人
fetch next from BX_NOT into @业主编号,@报修名称,@日期,@受理人
end
print '--------------------------------------------------------------------------------'
close BX_NOT
deallocate BX_NOT
end

--把维修好的报修写入数据库的存储过程 方便处理维修好的数据
create proc 报修完成
@编号 int
as
update 报修信息表 set 维修状态 = 'ok'
where 报修事项编号 = @编号
go

在运行报修情况存储过程看下结果：

--C．设计一个存储过程，利用游标，输出投诉表中第一、二行和最后一行的信息。

create proc 输出
as
begin
declare @业主编号 char(10),@投诉名称 char(10), @日期 char(22)
declare @受理人 char(10)
--输出第一第二行
declare TS cursor
for
select top 2 业主编号,投诉名称,日期,受理人 from 投诉信息表
open TS
fetch next from TS into @业主编号,@投诉名称,@日期,@受理人
print '投诉事项'
print '--------------------------------------------------------------------------------'
print '业主编号 ' + '投诉名称 ' + '日期 ' + '受理人 '
while @@FETCH_STATUS = 0
begin
print @业主编号 + @投诉名称 + @日期 + @受理人
fetch next from TS into @业主编号,@投诉名称,@日期,@受理人
end

close TS
deallocate TS
--输出最后一项
declare TS_last cursor
for
select top 1 业主编号,投诉名称,日期,受理人 from 投诉信息表
order by 投诉事项编号 desc
open TS_last
fetch next from TS_last into @业主编号,@投诉名称,@日期,@受理人

while @@FETCH_STATUS = 0
begin
print @业主编号 + @投诉名称 + @日期 + @受理人
fetch next