水费管理物理数据库设计文档模版
第 1 页 共 26页
水费管理系统
物理数据库设计文档
100090786 文档编号:
V0.2 当前版本号:
2011-1-3 最初发布日期:
2010-1-3 最新修订日期:
第 2 页 共 26 页 , 文档位置
, 文档修改历史
版本号 版本日期 修改
总结
初级经济法重点总结下载党员个人总结TXt高中句型全总结.doc高中句型全总结.doc理论力学知识点总结pdf
修订人 V0.0 2010-12-10 利用外键实现数据库的规范化和完整性。 陈晴 V0.1 2010-12-11 可以利用视图进行查询和修改 陈晴 V0.2 2010-12-12 可以利用触发器、存储过程来实现数据库的自动化、系统陈晴
化。
, 参考文档
序号 参考文档 版本 日期 1. V1.0 2008-04-14 管理数据字段
2. V1.0 2008-04-14 商务数据字段
3. V1.0 2008-04-14 销售数据字段
4. V1.0 2008-04-14 业务数据字段
2
第 3 页 共 26 页
目录
1. 物理数据库设计 ..................................................................................... 4 1.2 数据库总体设计 ................................................................................................. 4 1.2.1 表空间规划 ....................................................................................................................... 4 1.2.2 数据库规划 ....................................................................................................................... 4 2. 数据库表设计 ......................................................................................... 5 2.2 实体列表( Entity List) ......................................................................................... 5 2.2.1 <水费管理E-R图> ............................................................................................................ 5 2.2.2 <收费员基本信息E-R图> ................................................................................................. 6 2.2.3 <用户信息E-R图> ............................................................................................................ 7 2.2.4 <缴费信息E-R图> ............................................................................................................ 7 2.2.5 <抄表E-R图>.................................................................................................................... 9 2.2.6 <抄表员信息ER图> ........................................................................................................10 2.3 实体定义 .......................................................................................................... 11 2.3.1
.....................................................................................................................11
2.3.2 < consumer > .................................................................................................................12
2.3.3 < fee> .............................................................................................................................12
2.3.4 < meter_work> ...............................................................................................................13
2.3.5 < worker> .......................................................................................................................14
2.4 数据库其他对象设计 ........................................................................................ 15 2.4.1 存储过程设计 .................................................................................................................16 2.4.2 触发器设计 .....................................................................................................................16 3. 数据库容量及安全性设计 .................................................................... 18 3.2 数据量估计....................................................................................................... 18 3.3 数据库安全性设计 ............................................................................................ 18 3.3.1 用户设置 ........................................................................................................................18 3.3.2 用户组设置 .....................................................................................................................18 3.3.3 数据库权限设计..............................................................................................................19 3.4 附录1 ............................................................................................................... 19
3
第 4 页 共 26 页 1. 物理数据库设计
1.2 数据库总体设计
1.2.1 表空间规划
1.2.2 数据库规划
数据库名 自来水公司水费管理系统数据库
描述 自来水公司水费管理系统数据库设计与开发
Consumer,charger,worker,fee,water_work 用到的表空间
代码集(codeset) 视图:debt_cons,debt_notice 存储过程:
fee_xx,fee_xx1,meter_xx,meter_xx1,sing_fee_history,singfee,sum_fee,sum
_fee1,sum_water,sum_water1,unit_fee_history,unitfee触发器:
add_consumer,check_fee,add_debt
(HA, etc) 安装考虑
备份/恢复策略 数据库创建之后进行了备份，然后恢复使用
4
第 5 页 共 26 页 2. 数据库表设计
2.2 实体列表( Entity List)
序号 实体名称 别名 描述 备注 PT1 charger 学员基本信息表 记录学员的信息 PT2 consumer 请假信息表 记录请假的详细信息 PT3 fee 课程信息表 描述所有课程的
详细信息
PT4 Meter_work 缴费信息表 缴费的详细信息
及缴费情况的记录 PT5 worker 选课信息表 学员选择课程的
详细记录及信息
2.2.1 <水费管理E-R图>
用户
缴费 抄表
收费人员
5
第 6 页 共 26 页
2.2.2 <收费员基本信息E-R图>
编姓
号 名
身电 份话 收费员基本信息 证
号
备
注
6
第 7 页 共 26 页
2.2.3 <用户信息E-R图>
用用 户户
编名
号 称
2.2.4 <缴费信息E-R图>
7
第 8 页 共 26 页
用
户
编
号
缴费
缴 用费 水应缴 状量 付费 态 费时
用 间
8
第 9 页 共 26 页
2.2.5 <抄表E-R图>
用课编
户程号
编名
号 称
水本
表 期抄表 编读
号 数
上抄
期表
读员 数 号
9
第 10 页 共 26 页
2.2.6 <抄表员信息ER图>
姓编
名 号
抄表员信息
电备
话 注
10
第 11 页 共 26 页
2.3 实体定义
2.3.1
在序号处应提供超级连接到相应的数据字典
别名 收费员基本信息表
描述 记录收费员的基本信息
到逻辑数据模型(LDM) 1对多
的映射
目的 记录基本信息
事件 修改查询基本信息
charger (收费员基本信息表) 列名 数据类型 允许空 备注 是否为主键 Charger_num char(2) 收费员编号 是 charger_name nvarchar(4) 姓名 Charger_tel Char(12) 是 电话 Id Char(20) 是 身份证号 remark nvarchar(50) 是 备注
11
第 12 页 共 26 页
2.3.2 < consumer >
别名 客户信息表
描述 客户账户的的详细信息
到逻辑数据模型 1对多
(LDM)的映射
目的 记录修改客户账户信息
事件 修改，查询，添加，删除客户信息
consumer (客户信息表) 列名 数据类型 允许空 备注 是否为主键
Cons_num Char(8) 客户编号 是 Cons_name nvarchar(4)
客户名称 Cons_addr Nvarchar(50) 客户地址 unit Nchar(10) 允许 单位名称 kind float 水价类型 Meter_num Char(8) 水表编号 debt money 欠费额 reamarks Nvarchar(50) 允许 备注
2.3.3 < fee>
别名 缴费信息表
描述 记录缴费的详细情况
到逻辑数据模型(LDM)多对多
的映射
目的 记录更改缴费信息
事件 修改，查询，添加，删除缴费信息
12
第 13 页 共 26 页
fee (缴费信息表) 列名 数据类型 允许空 备注 是否为主键 Num Int 编号 是 cons_num Char(8) 用户编号 Water_month int 月份 water float 本月用水量 fee Money 允许 应缴费用 fee_time Datetime 允许 缴费时间 status Bit 允许 缴费状态 Charger_num Char(2) 允许 操作员编号
2.3.4 < meter_work>
别名 抄表信息表
描述 抄表的详细信息
到逻辑数据模型(LDM) 多对多
的映射
目的 记录抄表信息
事件 修改，查询，添加，删除抄表信息
13
第 14 页 共 26 页
Meter_work(抄表信息表)
列名 数据类型 允许空 备注 是否为主键 num Int 编号 是 cons_num Char(8) 客户编号 Meter_num char(8) 水表编号 Meter_start float 上期读数 Meter_end float 本期读数 Meter_time datetime 抄表时间 Worker_num Char(3) 允许 抄表员编号
2.3.5 < worker>
抄表员基本信息表
别名
描述 记录抄表员的基本信息
到逻辑数据模型 1对多
(LDM)的映射
目的 记录修改抄表基本信息
事件 修改，查询，添加，删除基本信息
14
第 15 页 共 26 页
worker(抄表员信息)
列名 数据类型 允许空 备注 是否为主键 worker_Num Char(3) 编号 是 worker_name nvarchar(4) 姓名
Worker_tel Char(12) 是 电话
remarks Varchar(50) 是 备注
2.4 数据库其他对象设计
本节是可选的。
15
第 16 页 共 26 页 2.4.1 存储过程设计
存储过程名称 功能描述
Fee_xx /***按年月查询用户收费信息*********/
Fee_xx1 /***按单位查询用户收费信息*********/
Meter_xx /***按年月查询用户抄表信息*********/
Meter_xx1 /***按单位查询用户抄表信息*********/
Sing_fee_history /********查询个人缴费历史***************/
Singfee 个人或工厂缴费,并打印缴费单
Sum_fee /***按年月统计用户收费信息*********/
Sum_fee1 /***按单位统计用户收费信息*********/
Sum_water /***统计某年某月的总用水量*********/
Sum_water1 /***统计某单位的某年某月的总用水量*********/
Unit_fee_history /********查询单位缴费历史***************/
unitfee 单位统一缴费
2.4.2 触发器设计
触发器名称 触发事件 功能描述 Add_consumer 插入 用户注册时，要初始化用户信息(填
写姓名，地址，单位，水价类型，水
表编号),
并在抄表信息中添加表的初始信息
Check_fee 更新 当用户缴费时，更新缴费信息，将
要缴月份的缴费状态设为true，同时
记录缴费时间和操作员，并将“用户
表”中的欠费额数减少相应的钱数。
16
第 17 页 共 26 页
Add_deb 插入 当插入一条抄表信息(户号，表号，止码，抄表日期，抄表员)时，同时更新起码(即变为上一次的止码) ，将在缴费信息中插入一条缴费信息(包括此用户的本月用水量、本月应缴水费),缴费主状态设为false
17
第 18 页 共 26 页 数据库容量及安全性设计
2.5 数据量估计
序号 实体名称 实体含义 平均估计数据访问频度% 当前容量 年增长量
大小 增加 修改 删除
1 charger 收费员信息 2 consumer 客户账户基本
信息
3 fee 缴费信息 4 Meter_wo抄表信息
rk
5 worker 抄表员信息 2.6 数据库安全性设计
2.6.1 用户设置
用户名 描述
Dbo 对数据库进行备份
consumer 查看数据
admin 对数据库进行管理
sa 拥有数据库所有的权限
2.6.2 用户组设置
组名 组成员 描述 sa sa 拥有数据库所有的权限 admin admin 拥有相应的管理权限 consumer consumer 可以查看自己的信息
18
第 19 页 共 26 页 2.6.3 数据库权限设计
表名 组名 用户名 权限描述
sa sa 可以对表进行修改、添charger
加、删除、查询
sa sa 可以对表进行修改、添consumer
加、删除、查询
sa sa 可以对表进行修改、添fee
加、删除、查询
sa sa 可以对表进行修改、添Meter_work
加、删除、查询
sa sa 可以对表进行修改、添worker
加、删除、查询
2.7 附录1
use 水费管理
/****用户注册时，要初始化用户信息(填写姓名，地址，单位，水价类型，水表编号), 并在抄表信息中添加表的初始信息**/
alter trigger add_comsumer on consumer
instead of insert
as
declare @cons_num char(8),
@cons_name nvarchar(8),
@cons_addr nvarchar(50),
@num int,
@unit nchar(10),
@kind float,
@meter_num char(8)
select
@cons_num=cons_num,@cons_name=cons_name,@cons_addr=cons_addr,@unit=unit,@kind=kind,@meter_num=meter_num
from inserted
select @num=max(num) from meter_work
set @num=@num+1
insert consumer(cons_num,cons_name,cons_addr,unit,kind,meter_num,debt,remarks)
19
第 20 页 共 26 页 values(@cons_num,@cons_name,@cons_addr,@unit,@kind,@meter_num,0,null) insert meter_work(num,cons_num,meter_num,meter_end,meter_time,worker_num) values(@num,@cons_num,@meter_num,0,getdate(),null)
go
insert consumer(cons_num,cons_name,cons_addr,unit,kind,meter_num) values('00000024','c24','aaaa2','bbbb1',1,'00000024')
/****当插入一条抄表信息(户号，表号，止码，抄表日期，抄表员)时，同时更新起码(即变为上一次的止码)
，将在缴费信息中插入一条缴费信息(包括此用户的本月用水量、本月应缴水费),缴费主状态设为false**/
alter trigger add_debt on meter_work
instead of insert
as
declare @cons_num char(8),@meter_num char(8),@meter_start float,@meter_end float,@meter_time datetime,@worker char(3),
@num int,@month int,@water float,@fee money,@status bit,@num1 int
select
@cons_num=cons_num,@meter_num=meter_num,@meter_end=meter_end,@meter_time=meter_time,@worker=worker_num from inserted
select @meter_start=max(meter_end) from meter_work where cons_num=@cons_num select @num1=max(num)from meter_work
select @num=max(num) from fee
set @num1=@num1+1
set @num=@num+1
if @meter_end=0
set @water=0
else
set @water=@meter_end-@meter_start
set @month=cast(month(@meter_time)as int)-1
if @month < 0
set @month=0
set @fee=@water*(select kind from consumer where cons_num=@cons_num) set @status=0
insert fee(num,cons_num,water_month,water,fee,status,fee_time,charger_num) values(@num,@cons_num,@month,@water,@fee,@status,null,null) insert meter_work
20
第 21 页 共 26 页 values(@num1,@cons_num,@meter_num,@meter_start,@meter_end,@meter_time,@worker) update consumer
set debt=debt+@fee where cons_num=@cons_num
go
insert meter_work(cons_num,meter_num,meter_end,meter_time,worker_num) values('00000024','00000024',10,'2011-2-1','002')
/****当用户缴费时，更新缴费信息，将要缴月份的缴费状态设为true，同时记录缴费时间和操作员， 并将“用户表”中的欠费额数减少相应的钱数************/
create trigger check_fee on fee
for update
as
declare @cons_num char(8),@cons_num2 char(8),@fee money,@status bit,@status2 bit select @cons_num=cons_num ,@status=status from deleted
select @cons_num2=cons_num,@status2=status from inserted select @fee=fee from fee where cons_num=@cons_num2
if @status2=1
begin
update consumer
set debt=debt-@fee where cons_num=@cons_num
end
/***个人或工厂缴费,并打印缴费单**/
alter procedure singfee
@cons_num char(8),@water_month int,@fee_time datetime,@status bit,@charger_num char(2) as
update fee
set status=@status,fee_time=@fee_time,
charger_num=@charger_num
where cons_num=@cons_num and water_month = @water_month select 收费编号=num,户号=cons_num,月份=water_month,
月用水量=water,月水费=fee,缴费状态=status,缴费时间=fee_time,
收费员=charger_num from fee where cons_num=@cons_num and water_month = @water_month
21
第 22 页 共 26 页
/*****填写户号，所要缴费的月份，缴费时间，缴费状态，操作员**/ exec singfee '00000022',2,'2011-3-2',1,'01'
/****单位统一缴费*******************************/ alter procedure unitfee
@unit nchar(10),@water_month int,@fee_time datetime,@status bit,@charger_num char(2) as
declare huh cursor for select cons_num from consumer where unit=@unit open huh
declare @cons_num char(8)
if @@error = 0
begin
while 1=1
begin
fetch next from huh into @cons_num
if @@fetch_status = 0
begin
update fee
set status=@status,fee_time=@fee_time,
charger_num=@charger_num
where cons_num=@cons_num and water_month = @water_month
select 收费编号=num,户号=cons_num,月份=water_month,
月用水量=water,月水费=fee,缴费状态=status,缴费时间=fee_time,
收费员=charger_num from fee where cons_num=@cons_num and water_month = @water_month
end
else
break
end
end
close huh
deallocate huh
go
/*****填写单位名称，所要缴费的月份，缴费时间，缴费状态，操作员**/ exec unitfee 'bbbb1',1,'2011-2-2',1,'01'
/********欠费大于的，打印催费通知单******************/ alter view debt_notice
as
22
第 23 页 共 26 页 select 户号=a.cons_num,户名=a.cons_name,地址=a.cons_addr,单位=a.unit,水价单价=a.kind, 水表编号=a.meter_num,月用水量=b.water,应缴费用=b.fee,缴费状态=b.status from consumer as a,fee as b
where a.cons_num=b.cons_num and b.fee >0 and b.status=0
select * from debt_notice
order by 户号
compute sum(月用水量),sum(应缴费用) by 户号
/******查询、统计和报表****/
/***按年月查询用户抄表信息*********/
create procedure meter_xx
@year int ,@month int
as
select * from meter_work
where cast(year(meter_time) as int)=@year and cast(month(meter_time)as int )=@month
exec meter_xx 2011,2
/***按单位查询用户抄表信息*********/
alter procedure meter_xx1
@unit nchar(10),@year int ,@month int
as
select
b.cons_num,b.unit,b.cons_num,b.cons_name,a.meter_num,a.meter_start,a.meter_end,a.meter_time,a.worker_num from meter_work as a,consumer as b
where cast(year(a.meter_time) as int)=@year and cast(month(a.meter_time)as int )=@month and a.cons_num=b.cons_num and b.unit=@unit
exec meter_xx1 'bbbb1',2011,2
/***按年月查询用户收费信息*********/
create procedure fee_xx
@year int ,@month int
as
select * from fee
where cast(year(fee_time) as int)=@year and cast(month(fee_time)as int )=@month
23
第 24 页 共 26 页 exec fee_xx 2011,2
/***按单位查询用户收费信息*********/
create procedure fee_xx1
@unit nchar(10),@year int ,@month int
as
select a.water_month,b.cons_num,b.unit,b.cons_num,b.cons_name, a.water,a.fee,a.status,a.fee_time,a.charger_num from fee as a,consumer as b where cast(year(a.fee_time) as int)=@year and cast(month(a.fee_time)as int )=@month and a.cons_num=b.cons_num and b.unit=@unit
exec fee_xx1 'bbbb1',2011,2
/****查看未缴用户****/
create view debt_cons
as
select * from consumer where debt>0
select * from debt_cons
/***统计某年某月的总用水量*********/
alter procedure sum_water
@year int ,@month int
as
set @month=@month+1
select 年=cast(@year as char(4)) ,月=cast((@month-1) as char(2)),总用水量=sum(meter_end-meter_start) from meter_work
where cast(year(meter_time) as int)=@year and cast(month(meter_time)as int )=@month
exec sum_water 2011,1
/***统计某单位的某年某月的总用水量*********/
alter procedure sum_water1
@unit nchar(10),@year int ,@month int
as
set @month=@month+1
select 单位名称=@unit, 年=cast(@year as char(4)) ,月=cast((@month-1) as char(2)), 总用水量=sum(a.meter_end-a.meter_start) from meter_work as a,consumer as b where cast(year(a.meter_time) as int)=@year and cast(month(a.meter_time)as int )=@month and a.cons_num=b.cons_num and b.unit=@unit
24
第 25 页 共 26 页 exec sum_water1 'bbbb1',2011,1
/***按年月统计用户收费信息*********/
alter procedure sum_fee
@year int ,@month int
as
select 年=cast(@year as char(4)) ,月=cast((@month) as char(2)),总收费=sum(fee)from fee where cast(year(fee_time) as int)=@year and cast(month(fee_time)as int )=@month and status=1 select 年=cast(@year as char(4)) ,月=cast((@month) as char(2)),未收费=sum(fee)from fee where cast(year(fee_time) as int)=@year and cast(month(fee_time)as int )=@month and status=0 exec sum_fee 2011,2
/***按单位统计用户收费信息*********/
alter procedure sum_fee1
@unit nchar(10),@year int ,@month int
as
select 单位名称=@unit,年=cast(@year as char(4)) ,月=cast((@month) as char(2)), 总收费=sum(a.fee) from fee as a,consumer as b
where cast(year(a.fee_time) as int)=@year and cast(month(a.fee_time)as int )=@month and a.cons_num=b.cons_num and b.unit=@unit and a.status=1
select 单位名称=@unit,年=cast(@year as char(4)) ,月=cast((@month) as char(2)), 未收费=sum(a.fee) from fee as a,consumer as b
where cast(year(a.fee_time) as int)=@year and cast(month(a.fee_time)as int )=@month and a.cons_num=b.cons_num and b.unit=@unit and a.status=0
exec sum_fee1 'bbbb1',2011,2
/********查询个人缴费历史***************/
create procedure sing_fee_history
@cons_num char(8)
as
select * from fee where cons_num=@cons_num
exec sing_fee_history '00000001'
/********查询单位缴费历史***************/
alter procedure unit_fee_history
@unit nchar(10)
as
select 月份=a.water_month,本月总用水量=sum(a.water),
25
第 26 页 共 26 页
本月应缴费用=sum(a.fee), 缴费状态=1 from fee as a,consumer as b where a.cons_num=b.cons_num and b.unit=@unit and a.status=1 group by a.water_month
select 月份=a.water_month,本月总用水量=sum(a.water), 本月未缴费用=sum(a.fee), 缴费状态=0 from fee as a,consumer as b where a.cons_num=b.cons_num and b.unit=@unit and a.status=0 group by a.water_month
exec unit_fee_history 'bbbb1'
26
浙公网安备 33021202002483