水电费管理题目

管理员表：

-- Create table
create table ADMIN
(
  nadminid   VARCHAR2(11) not null,
  nloginname VARCHAR2(5) not null,
  npassword  VARCHAR2(11) not null,
  ntime      VARCHAR2(11) not null
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255;
-- Add comments to the columns 
comment on column ADMIN.nadminid
  is '管理员ID（主键）';
comment on column ADMIN.nloginname
  is '管理员姓名';
comment on column ADMIN.npassword
  is '管理员密码';
comment on column ADMIN.ntime
  is '管理员姓名';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ADMIN
  add constraint ADMIN primary key (NADMINID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255;

用户表：

-- Create table
create table USERR
(
  uloginname VARCHAR2(11) not null,
  upassword  VARCHAR2(11) not null,
  uusername  VARCHAR2(5) not null,
  uaddress   VARCHAR2(18) not null,
  uphone     NUMBER(11) not null,
  userid     VARCHAR2(11) not null
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255;
-- Add comments to the columns 
comment on column USERR.uloginname
  is '用户名（主键）';
comment on column USERR.upassword
  is '用户密码';
comment on column USERR.uusername
  is '用户姓名';
comment on column USERR.uaddress
  is '用户地址';
comment on column USERR.uphone
  is '用户电话';
comment on column USERR.userid
  is '用户名id（主键）';
-- Create/Recreate primary, unique and foreign key constraints 
alter table USERR
  add constraint USERR primary key (USERID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255;

水表：

-- Create table
create table WATER
(
  wwaterid     VARCHAR2(6) not null,
  userid       VARCHAR2(5) not null,
  wcount       VARCHAR2(11),
  wtime        DATE not null,
  wprize       NUMBER(11) not null,
  wbeforecount VARCHAR2(11),
  wmoney       VARCHAR2(10)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255;
-- Add comments to the columns 
comment on column WATER.wwaterid
  is '水表ID';
comment on column WATER.userid
  is '用户名';
comment on column WATER.wcount
  is '用水量';
comment on column WATER.wtime
  is '缴费时间（主键）';
comment on column WATER.wprize
  is '水费价格';
comment on column WATER.wbeforecount
  is '上月用水量';
comment on column WATER.wmoney
  is '水费金额';
-- Create/Recreate primary, unique and foreign key constraints 
alter table WATER
  add constraint WATER primary key (WTIME)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255;

电表：

-- Create table
create table POWER
(
  ppowerid     VARCHAR2(6) not null,
  userid       VARCHAR2(5) not null,
  pcount       VARCHAR2(11) not null,
  ptime        DATE not null,
  pprize       NUMBER(11) not null,
  pbeforecount VARCHAR2(11),
  pmoney       VARCHAR2(10) not null
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255;
-- Add comments to the columns 
comment on column POWER.ppowerid
  is '电表ID';
comment on column POWER.userid
  is '用户名';
comment on column POWER.pcount
  is '用电量';
comment on column POWER.ptime
  is '缴费时间（主键）';
comment on column POWER.pprize
  is '电费价格';
comment on column POWER.pbeforecount
  is '上月用电量';
comment on column POWER.pmoney
  is '电费金额';
-- Create/Recreate primary, unique and foreign key constraints 
alter table POWER
  add constraint POWER primary key (PTIME)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255;

水电费总表：

-- Create table
create table MONEY
(
  userid      VARCHAR2(11) not null,
  ymoney      VARCHAR2(6),
  ywtime      DATE not null,
  ywaterprize VARCHAR2(10),
  ypowerprize VARCHAR2(10),
  yptime      DATE not null
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255;
-- Add comments to the columns 
comment on column MONEY.userid
  is '用户名';
comment on column MONEY.ymoney
  is '水电费总额';
comment on column MONEY.ywtime
  is '水费缴费时间（主键）';
comment on column MONEY.ywaterprize
  is '水费';
comment on column MONEY.ypowerprize
  is '电费';
comment on column MONEY.yptime
  is '电费缴费时间（主键）';
-- Create/Recreate primary, unique and foreign key constraints 
alter table MONEY
  add constraint MONEY primary key (YWTIME, YPTIME)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255;