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

表的设计

--管理员表
create table admin
(
   a_loginname  varchar2(9)  not null,
   a_password   varchar2(6)  not null
);
comment on column admin.a_loginname is'管理员账号(主键)';
comment on column admin.a_password is '密码';
alter table admin
add constraint pk_a primary key(a_loginname);
add constraint pk_s uniqe  (a_password);

--用户表
create table users
(
  u_id        number(4)    not null,
  u_name      varchar2(9)  not null,
  u_password  varchar2(6)  not null,
  u_phone     varchar2(11) not null,
  u_email     varchar2(18),
  u_address   varchar2(27)
);
comment on column users.u_id is '编号（主键）';
comment on column users.u_name is '用户姓名';
comment on column users.u_password is '用户密码';
comment on column users.u_phone is '用户电话';
comment on column users.u_email is'邮件地址';
comment on column users.u_address is'家庭地址';
alter table users 
add constraint pk_u primary key(u_id);
add constraint PK_P unique (U_PASSWORD)

--水费表
create  table water 
(
  u_id   number(4)    not null,
  u_name varchar2(9)  not null,
  w_month number(4,2) not null,
  w_price number(4,2) not null,
  w_status varchar(6) not null
);
comment on column water.u_id is '编号（主键）';
comment on column water.u_name is'用户姓名';
comment on column water.w_month is'本月用水量';
comment on column water.w_price is'本月应交水费';
comment on column water.w_status is'水费缴付情况';
alter table water 
add constraint  pk_w primary key(u_id);

create table dianfei
(
  u_id   number(4)      not null,
  u_name varchar(9)     not null,
  d_month number(4,2)   not null,
  d_price number(4,2)   not null,
  d_status varchar2(6)  not null
);
comment on column dianfei.u_id is'编号（主键）';
comment on column dianfei.u_name is'用户姓名';
comment on column dianfei.d_month is'本月用电量';
comment on column dianfei.d_price is'本月应交电费';
comment on column dianfei.d_status is'电费缴付情况';
alter table dianfei
add constraint pk_d primary key(u_id);