水电缴费管理系统 需求分析与设计
系统功能概述
本系统主要实现了管理员查询、添加、删除用户信息；水电费录入、查询、修改和删除。用户缴纳水电费和查询水电信息等功能。
其中涉及到的模块有：
(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;
