《物业管理系统数据库设计》

1、 数据库原理与技术 三级项目报告 姓 名： 学 号：项目名称指导 教师同组成员无实施过程1. 需求描述小区物业管理系统的系统结构分为个部分，系统功能：用户注册、用户管理、修改密码、退出系统。 楼盘管理功能：楼盘管理、添加修改楼盘信息。 住户管理功能：住户管理、添加修改住户信息。 报修管理功能：报修管理、添加删除报修信息。有4个实体类型，其属性如下：小区住户基本信息实体：业主编号 姓名 住户信息 性别 电话 名牌号 面积 身份证报修信息实体：报修信息 业主编号 报修事项编号 报修名称 维修状态 日期 受理人投诉信息实体：投诉事项编号 投诉信息 投诉名称 业主编号 解决状态 日期 受理人楼盘信息实

2、体：楼盘编号 楼盘信息 楼盘名称 楼层数 户型 面积 地址2. 概念结构设计3. 逻辑结构设计小区物业管理系统数据库中各个表格的设计结果如下面的几个表所示。每个表表示在数据库中的一个数据表。这个ER图有4个实体类型，其属性如下：表1-客户表表1-2 住户基本信息表表1-3 楼盘基本信息表表1-4 报修处理表表1-5 投诉管理表4. 数据库实施-建立数据库create database 物业管理系统-建立数据表-用户表Create table 用户表( 业主编号 int primary key not nul, 楼盘编号 int not nul, );-住户信息表Create table 住户信

3、息表(业主编号 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 ); -楼盘信息表Create table 楼盘信息表 (楼盘编号 int primary key not null, 楼盘名称 char(20) not null, 楼层数 int not null, 面积 int not null, 户型 char(2

4、0) not null , 地址 char(20) not null );-报修信息表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 住户信息表(业主编号) ); -投诉信息表Create table 投诉信息表 (投诉事项编号 int

5、 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 住户信息表(业主编号) );-创建业主信息视图 create view 业主信息视图 asselect 姓名 业主姓名,楼盘名称,门牌号,性别,电话,住户信息表.面积,身份证 from 住户信息表,楼盘信息表,拥有信息表where 楼盘信息表.楼盘

6、编号 = 拥有信息表.楼盘编号 and 住户信息表.业主编号 = 拥有信息表.业主编号-创建未维修事项信息视图 create view 未维修事项信息视图 asselect 报修名称,姓名 报修人,楼盘名称,门牌号,电话,日期 保修日期,受理人 from 报修信息表,住户信息表,楼盘信息表,拥有信息表where 维修状态 != ok and 楼盘信息表.楼盘编号 = 拥有信息表.楼盘编号 and 住户信息表.业主编号 = 拥有信息表.业主编号and 报修信息表.业主编号 = 住户信息表.业主编号-创建报修视图，只显示ID，Name，日期和维修否信息 create view 未解决投诉信息视图

7、asselect 投诉名称,姓名 投诉人,楼盘名称,门牌号,电话,日期 保修日期,受理人 from 投诉信息表,住户信息表,楼盘信息表,拥有信息表where 解决状态 != OK and 楼盘信息表.楼盘编号 = 拥有信息表.楼盘编号 and 住户信息表.业主编号 = 拥有信息表.业主编号and 投诉信息表.业主编号 = 住户信息表.业主编号6. 数据库运行-楼盘信息的添加 insert into 楼盘信息表values(1,海天,5,120,三室一厅,中山路号)insert into 楼盘信息表values(2,柠檬夏天,10,120,四室一厅,中山路号)insert into 楼盘信息表v

8、alues(3,指尖刹那,9,300,四室一厅,中山路号)-住户信息的添加 insert into 住户信息表values(11,林达,女,520,120,)insert into 住户信息表values(12,谢可,女,521,100,)insert into 住户信息表values(13,谢华,男,522,180,)insert into 住户信息表values(14,张琳,男,523,250,)insert into 住户信息表values(15,仲夏,男,524,300,)-报修信息的添加insert into 报修信息表(报修事项编号,业主编号,报修名称,受理人) values(10

9、,15,水管爆裂,王砖家)insert into 报修信息表(报修事项编号,业主编号,报修名称,受理人) values(11,14,电线端口安装,王砖家)insert into 报修信息表(报修事项编号,业主编号,报修名称,受理人) values(12,13,大门锁坏掉,王砖家)insert into 报修信息表(报修事项编号,业主编号,报修名称,受理人) values(13,12,灯泡更换,王砖家)-投诉信息的添加insert into 投诉信息表(投诉事项编号,业主编号,投诉名称,受理人) values(100,11,墙壁裂痕,胡说)insert into 投诉信息表(投诉事项编号,业主编

10、号,投诉名称,受理人) 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 拥有

11、信息表 values(15,3)-查询结果select * from 楼盘信息表 select * from 住户信息表 select * from 报修信息表 select * from 投诉信息表 select * from 拥有信息表-楼盘信息编辑 update 楼盘信息表 set 楼盘名称 = 彩虹之家 where 楼盘名称 = 海天 -住户信息的编辑update 住户信息表 set 电话 = where 电话 = -业主信息视图通过该视图可以得到住户信息，包含业主姓名，楼盘名称，门牌号，性别，电话，面积，身份证create view 业主信息视图 asselect 姓名 业主姓名,楼

12、盘名称,门牌号,性别,电话,住户信息表.面积,身份证 from 住户信息表,楼盘信息表,拥有信息表where 楼盘信息表.楼盘编号 = 拥有信息表.楼盘编号 and 住户信息表.业主编号 = 拥有信息表.业主编号-未维修事项信息视图报修名称，报修人，楼盘名称，门牌号，电话，保修日期，受理人create view 未维修事项信息视图 asselect 报修名称,姓名 报修人,楼盘名称,门牌号,电话,日期 保修日期,受理人 from 报修信息表,住户信息表,楼盘信息表,拥有信息表where 维修状态 != ok and 楼盘信息表.楼盘编号 = 拥有信息表.楼盘编号 and 住户信息表.业主编号

13、= 拥有信息表.业主编号and 报修信息表.业主编号 = 住户信息表.业主编号-未解决投诉信息视图create view 未解决投诉信息视图 asselect 投诉名称,姓名 投诉人,楼盘名称,门牌号,电话,日期 保修日期,受理人 from 投诉信息表,住户信息表,楼盘信息表,拥有信息表where 解决状态 != OK and 楼盘信息表.楼盘编号 = 拥有信息表.楼盘编号 and 住户信息表.业主编号 = 拥有信息表.业主编号 and 投诉信息表.业主编号 = 住户信息表.业主编号-将业主编号为11的业主业主编号改为520update 住户信息表 set 业主编号 = 520 where 业

14、主编号 = 117. 数据库维护备份数据库每一个数据库都应在创建之后卸出，从而提供一个装入基点。在此之后按排定的时间周期表卸出。比如每周五卸出数据库。对一般数据库系统卸出数据库周期建议为每周一次。除了按计划周期卸出数据库之外，还需在每次运行没有日志的操作后卸出数据库。例如：每次强制地运行了 DUMP TRAN WITH NO_LOG （因为数据库的磁盘空溢出）；每次用 sp_dboption 允许 select into/bulkcopy 做快速拷贝，或用 SELECT INTO 命令创建一个永久性的表，或使用了 WRITETEXT 命令。卸出数据库的命令为：DUMP DATABASE 物业管

15、理系统TOAdmin下面一条命令用来卸出数据库 ：DUMP DATABASE 物业管理系统TO Admin备份事务日志如果事务日志与数据库放在同一个设备上，则事务日志不应与数据库分开备份。master 数据库和小于 4M 的用户数据库就是这种情况。一般数据库系统的数据库和日志分别放在不同的设备上，因此，可以用 DUMP TRAN 命令单独备份日志。备份事务日志的周期直接影响数据的恢复程度，因此建议每天备份。备份事务日志的命令格式为：DUMP TRANsaction 物业管理系统TO AdminWITH TRUNCATE_ONLY|WITH NO_LOG|WITH NO_TRUNCATE对于 m

16、aster 数据库和小型数据库每次运行 DUMP DATEBASE 之后应当运行 DUMP TRANsaction 命令刷新日志 。下面一条命令备份数据库xb1 的事务日志到备份设备上：DUMP TRANsaction xb1TO db_log_bk_devWITH TRUNCATE_ONLY8. 数据库开发创建触发器-实现级联更新和级联删除住户信息表的级联更新删除 create trigger tri_Delete_Update on 住户信息表 instead of update ,deleteas begindeclare inset char(6),delet char(6) sele

17、ct inset=业主编号 from inserted select delet=业主编号 from deleted if (update(业主编号) beginEXEC sp_msforeachtable ALTER TABLE ? NOCHECK CONSTRAINT ALL -禁用约束update 报修信息表 set 业主编号=inset where 业主编号=deletupdate 投诉信息表 set 业主编号=inset where 业主编号=deletupdate 拥有信息表 set 业主编号=inset where 业主编号=deletupdate 住户信息表 set 业主编号=

18、inset where 业主编号= deletEXEC sp_msforeachtable ALTER TABLE ? CHECK CONSTRAINT ALL-启用约束 endelse begindelete from 报修信息表 where 业主编号=deletdelete from 投诉信息表 where 业主编号=deletdelete from 拥有信息表 where 业主编号=deletdelete from 住户信息表 where 业主编号=delet end end-楼盘信息表的级联更新删除 create trigger tri_楼盘on 楼盘信息表 instead of up

19、date ,deletebegindeclare inset char(6),delet char(6) select inset=楼盘编号 from inserted select delet=楼盘编号 from deleted if (update(楼盘编号) beginEXEC sp_msforeachtable ALTER TABLE ? NOCHECK CONSTRAINT ALL -禁用约束update 拥有信息表 set 楼盘编号=inset where 楼盘编号=deletupdate 楼盘信息表 set 楼盘编号 = inset where 楼盘编号 = deletEXEC

20、sp_msforeachtable ALTER TABLE ? CHECK CONSTRAINT ALL -启用约束end else begindelete from 拥有信息表 where 楼盘编号=deletdelete from 楼盘信息表 where 楼盘编号=delet end End-如当删除一条住户信息记录能够显示提示，当添加一条报修信息能够显示提示。create trigger 删除住户 on 住户信息表 after delete asprint 删除成功! gocreate trigger 添加报修 on 报修信息表 after insert asprint 添加成功! go

21、-创建存储过程:-创建查询某一业主信息的存储过程-查询住户的信息存储过程 create proc 查询住户信息 asselect * from 住户信息表exec 查询住户信息-创建添加报修信息记录的存储过程create proc 添加报修信息 报修事项编号 char(20), 业主编号 char(20), 报修名称 char(20),受理人 char(20) as begininsert into 报修信息表(报修事项编号,业主编号,报修名称,受理人) values(报修事项编号,业主编号,报修名称,受理人) end-创建返回某个楼盘的业主人数，并设置没有指定值时，指定一个默认楼盘-默认楼盘

22、编号为2 create proc 业主人数 asselect COUNT(*) 业主人数 from 拥有信息表 where 楼盘编号 = 2create proc 楼盘住户表 楼盘名称 char(20) asprint 楼盘名称 begin declare 姓名 char(10) declare 性别 char(10) declare 电话 char(10) declare 门牌号 char(10) declare 面积 char(10) declare 身份证 char(10) declare GR cursor forselect 姓名,性别,电话,门牌号,面积,身份证 from 住户信息

23、表 where 业主编号 in (select 业主编号 from 拥有信息表where 楼盘编号 in (select 楼盘编号 from 楼盘信息表where 楼盘名称 = 楼盘名称)open GRfetch next from GR into 姓名,性别,电话,门牌号,面积,身份证 print -print 业主姓名 + + 性别 + + 电话+ + 门牌号 + + 面积+ + 身份证while FETCH_STATUS = 0 beginprint 姓名 + 性别 + 电话+ 门牌号 + 面积+ 身份证 fetch next from GR into 姓名,性别,电话,门牌号,面积,身

24、份证endprint -close GRdeallocate GR end go-b.设计一个存储过程，在该存储过程中建立生成报表的游标，分别显示 -已解决的报修事项信息和未解决的报修事项信息。-存储过程生成报表 create proc 报修情况 as begindeclare 业主编号 char(10),报修名称 char(10), 日期 char(22) declare 受理人 char(10)-已解决的报修 declare BX_OK cursor forselect 业主编号,报修名称,日期,受理人 from 报修信息表 where 维修状态 = ok open BX_OKfetch

25、next from BX_OK into 业主编号,报修名称,日期,受理人 print 已经解决的报修事项print -print 业主编号 + 报修名称 + 日期 + 受理人 while FETCH_STATUS = 0 beginprint 业主编号 + 报修名称 + 日期 + 受理人fetch next from BX_OK into 业主编号,报修名称,日期,受理人endprint -close BX_OK deallocate BX_OK print print -未完成报修的游标操作 declare BX_NOT cursor forselect 业主编号,报修名称,日期,受理人

26、from 报修信息表 where 维修状态 = not open BX_NOTfetch next from BX_NOT into 业主编号,报修名称,日期,受理人print 未解决的报修事项print -print 业主编号 + 报修名称 + 日期 + 受理人 while FETCH_STATUS = 0 beginprint 业主编号 + 报修名称 + 日期 + 受理人fetch next from BX_NOT into 业主编号,报修名称,日期,受理人endprint -close BX_NOT deallocate BX_NOT end-把维修好的报修写入数据库的存储过程 方便处理

27、维修好的数据 create proc 报修完成 编号 int asupdate 报修信息表 set 维修状态 = ok where 报修事项编号 = 编号 go-C设计一个存储过程，利用游标，输出投诉表中第一、二行和最后一行的信息。create proc 输出 as begindeclare 业主编号 char(10),投诉名称 char(10), 日期 char(22) declare 受理人 char(10) -输出第一第二行 declare TS cursor forselect top 2 业主编号,投诉名称,日期,受理人 from 投诉信息表 open TSfetch next from TS into 业主编号,投诉名称,日期,受理人 print 投诉事项print -print 业主编号 + 投诉名称 + 日期 + 受理