水电费管理题目
管理员表:
-- 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;