水电缴费管理系统 需求分析与设计

系统功能概述

    本系统主要实现了管理员查询、添加、删除用户信息；水电费录入、查询、修改和删除。用户缴纳水电费和查询水电信息等功能。

其中涉及到的模块有：

(1)登录模块功能：主要是输入的用户名和密码保存并且与数据库中的数据进行比较，只有完全符合才可以登录系统。

(2)管理员模块功能：可以操作水电价格、用户信息和水电录入、缴纳。

①用户信息子模块功能：可以查询、操作用户信息。

②价格子模块功能：主要是对水电费价格的管理。

③水费子模块功能：添加、修改和删除水费缴纳信息。

④电费子模块功能：添加、修改和删除电费缴纳信息。

(3)用户模块：能查看个人信息和缴费信息。

建表：

管理员表：

-- Create table
create table ADMIN
(
admin_no NVARCHAR2(4) not null,
admin_name NVARCHAR2(12) not null,
admin_sex NVARCHAR2(3),
admin_age NUMBER(4),
admin_cardid VARCHAR2(19) not null,
admin_depart VARCHAR2(21),
admin_logname VARCHAR2(12) not null,
admin_password VARCHAR2(10) not null
)
tablespace ADMIN
pctfree 10
initrans 1
maxtrans 255;
-- Add comments to the columns
comment on column ADMIN.admin_no
is '工号（主键）';
comment on column ADMIN.admin_name
is '姓名';
comment on column ADMIN.admin_sex
is '性别';
comment on column ADMIN.admin_age
is '年龄';
comment on column ADMIN.admin_cardid
is '身份证号';
comment on column ADMIN.admin_depart
is '部门';
comment on column ADMIN.admin_logname
is '管理员登录名';
comment on column ADMIN.admin_password
is '管理员登陆密码';
-- Create/Recreate primary, unique and foreign key constraints
alter table ADMIN
add constraint PK_ANO primary key (ADMIN_NO)
using index
tablespace ADMIN
pctfree 10
initrans 2
maxtrans 255;
-- Create/Recreate check constraints
alter table ADMIN
add constraint AA_SEX
check (Admin_SEX='男'or Admin_SEX='女');

用户表：

-- Create table
create table U_USER
(
user_idno
VARCHAR2(5) not null,
user_name
VARCHAR2(12) not null,
user_logname
VARCHAR2(15) not null,
user_password VARCHAR2(10) not null,
user_sex
VARCHAR2(3),
user_address
VARCHAR2(21) not null,
user_phone
NUMBER(11) not null
)
tablespace ADMIN
pctfree 10
initrans 1
maxtrans 255;
-- Add comments to the columns
comment on column U_USER.user_idno
is '用户编号';
comment on column U_USER.user_name
is '用户姓名';
comment on column U_USER.user_logname
is '用户登录名';
comment on column U_USER.user_password
is '用户密码';
comment on column U_USER.user_sex
is '用户性别';
comment on column U_USER.user_address
is '用户住址';
comment on column U_USER.user_phone
is '用户电话';
-- Create/Recreate primary, unique and foreign key constraints
alter table U_USER
add constraint PK_UNO primary key (USER_IDNO)
using index
tablespace ADMIN
pctfree 10
initrans 2
maxtrans 255;
-- Create/Recreate check constraints
alter table U_USER
add constraint USER_SEX
check (user_sex='男'or user_sex='女');

水表：

-- Create table
create table WEATER
(
user_idno VARCHAR2(10) not null,
water_sum NUMBER(6,2) not null,
water_befor_sum NUMBER(6,2) not null,
water_paymony VARCHAR2(9) not null,
water_price VARCHAR2(3) not null,
water_mony NUMBER(6,2) not null,
water_nomony NUMBER(10,2) not null
)
tablespace ADMIN
pctfree 10
initrans 1
maxtrans 255;
-- Add comments to the columns
comment on column WEATER.user_idno
is '用户ID号(主键)';
comment on column WEATER.water_sum
is '用户本月的水表总数';
comment on column WEATER.water_befor_sum
is '用户上月的水表总数';
comment on column WEATER.water_paymony
is '用户的缴费状态';
comment on column WEATER.water_price
is '水费价格';
comment on column WEATER.water_mony
is '用户水费缴费钱数';
comment on column WEATER.water_nomony
is '用户水费欠费钱数';
-- Create/Recreate primary, unique and foreign key constraints
alter table WEATER
add constraint PK_WATER_USER_ID primary key (USER_IDNO)
using index
tablespace ADMIN
pctfree 10
initrans 2
maxtrans 255;

 

 

 

电表：

-- Create table
create table POWER
(
user_idno VARCHAR2(10) not null,
power_sum NUMBER(6,2) not null,
power_befor_sum NUMBER(6,2) not null,
power_paymony VARCHAR2(9) not null,
power_price VARCHAR2(3) not null,
power_mony NUMBER(10,2) not null,
power_nomony NUMBER(10,2) not null
)
tablespace ADMIN
pctfree 10
initrans 1
maxtrans 255;
-- Add comments to the columns
comment on column POWER.user_idno
is '用户ID号(主键)';
comment on column POWER.power_sum
is '用户本月的电表总数';
comment on column POWER.power_befor_sum
is '用户上月的电表总数';
comment on column POWER.power_paymony
is '用户的缴费状态';
comment on column POWER.power_price
is '电费价格';
comment on column POWER.power_mony
is '用户电费缴费钱数';
comment on column POWER.power_nomony
is '用户电费欠费钱数';
-- Create/Recreate primary, unique and foreign key constraints
alter table POWER
add constraint PK_POWER_USER_IDNO primary key (USER_IDNO)
using index
tablespace ADMIN
pctfree 10
initrans 2
maxtrans 255;

 

 

价格表：

-- Create table
create table PRICE
(
price_no
VARCHAR2(9) not null,
price_water
NUMBER(4,2) not null,
price_power
NUMBER(4,2) not null,
price_date_water DATE,
price_date_power DATE
)
tablespace ADMIN
pctfree 10
initrans 1
maxtrans 255;
-- Add comments to the columns
comment on column PRICE.price_no
is '价格表编号(主键)';
comment on column PRICE.price_water
is '水费单价';
comment on column PRICE.price_power
is '电费单价';
comment on column PRICE.price_date_water
is '水费的价格更新日期';
comment on column PRICE.price_date_power
is '电费的价格更新日期';
-- Create/Recreate primary, unique and foreign key constraints
alter table PRICE
add constraint PK_PRICE_NO primary key (PRICE_NO)
using index
tablespace ADMIN
pctfree 10
initrans 2
maxtrans 255;