Database water and electricity payment system

Water and Electricity Payment management system data Sheet Design

SQL Build Table Script:

--Build a table
--Administrator Table admin
CREATE TABLE Admin (
admin_id VARCHAR2 (3) NOT NULL,
Admin_loginname VARCHAR2 (8) NOT NULL,
Admin_password VARCHAR2 (6) NOT NULL,
Admin_username VARCHAR2 (4) NOT NULL,
Constraint Pk_admin primary KEY (ADMIN_ID)
);
Comment on column admin.admin_id is ' Administrator number ';
Comment on column admin.admin_loginname is ' administrator login user name ';
Comment on column Admin.admin_password is ' admin login password ';
Comment on column admin.admin_username is ' administrator name ';
--User basic information table
CREATE TABLE Users (
USER_ID VARCHAR2 (Ten) NOT NULL,
User_loginname VARCHAR2 (8) NOT NULL,
User_password VARCHAR2 (6) NOT NULL,
User_username VARCHAR2 (4) NOT NULL,
User_address varchar2 () NOT NULL,
User_phone number (one) is not NULL,
Constraint Pk_users primary KEY (USER_ID)
);
Comment on column users.user_id is ' user number ';
Comment on column users.user_loginname is ' user login name ';
Comment on column users.user_password is ' user login password ';
Comment on column users.user_username is ' user name ';
Comment on column users.user_address is ' user address ';
Comment on column users.user_phone is ' user phone ';
--Water meter water
CREATE TABLE Water (
water_id VARCHAR2 (Ten) NOT NULL,
UserID VARCHAR2 (TEN) is not NULL,
Water_count Number (3,2) is not NULL,
Water_time date NOT NULL,
Water_status Number (1) is not NULL,
Water_priceid VARCHAR2 (Ten) NOT NULL,
Water_beforecount Number (3,2) is not NULL,
Constraint Pk_water primary KEY (WATER_ID)
);
Comment on column water.water_id is ' water meter number ';
Comment on column water.userid is ' user number ';
Comment on column water.water_count is ' water meter running number ';
Comment on column water.water_time is ' water meter time ';
Comment on column water.water_status is ' water fee status ';
Comment on column Water.water_priceid is ' water price number ';
Comment on column Water.water_beforecount was ' last month water meter run ';
--Water meter power
CREATE TABLE Power (
power_id VARCHAR2 (Ten) NOT NULL,
UserID VARCHAR2 (TEN) is not NULL,
Power_count Number (3,2) is not NULL,
Power_time date NOT NULL,
Power_status Number (1) is not NULL,
Power_priceid VARCHAR2 (Ten) NOT NULL,
Power_beforecount Number (3,2) is not NULL,
Constraint Pk_power primary KEY (POWER_ID)
);
Comment on column power.power_id is ' meter number ';
Comment on column water.userid is ' user number ';
Comment on column power.power_count is ' meter run ';
Comment on column power.power_time is ' meter time ';
Comment on column power.power_status is ' electricity bill status ';
Comment on column Power.power_priceid is ' electricity price number ';
Comment on column Power.power_beforecount was ' last month meter run ';
--Price List money
CREATE TABLE Price (
price_id VARCHAR2 (Ten) NOT NULL,
Price_time date NOT NULL,
IsActive Number (1) is not NULL,
Waterprice Number (2,2) is not NULL,
Powerprice Number (2,2) is not NULL,
Constraint Pk_price primary KEY (PRICE_ID)
);
Comment on column price.price_id is ' Price number ';
Comment on column price.price_time is ' price date ';
Comment on column price.isactive is ' price status ';
Comment on column price.waterprice is ' water price ';
Comment on column price.powerprice is ' tariff price ';

Administrator table:

Water Tariff:

Electricity meter:

Price list:

Database water and electricity payment system