Create TableAdmin (Aidvarchar2(Ten) not NULL, Apasswordvarchar2(8) not NULL, Aphonevarchar2( One) not NULL, Anamevarchar2(8) not NULL); Comment on TableAdmin is 'Administrator Login'; Comment on columnAdmin.aid is 'Admin number (primary key)'; Comment on columnAdmin.apassword is 'Login Password'; Comment on columnAdmin.aphone is 'Admin Phone'; Comment on columnAdmin.aname is 'Administrator Name';Alter TableAdminAdd constraintPk_adminPrimary Key(aid);
Drop TableUuser;Create TableUuser (UoNvarchar2( the) not NULL, unamevarchar2(8) not NULL, Uphonevarchar2( One) not NULL, Upasswordvarchar2(8) not NULL, Uaddressvarchar2( -) not NULL); Comment on TableUuser is 'User Login'; Comment on columnUuser.uon is 'user number (primary key)'; Comment on columnUuser.uname is 'User name'; Comment on columnUuser.uphone is 'User Phone'; Comment on columnUuser.upassword is 'User Login Password'; Comment on columnUuser.uaddress is 'User Address';Alter TableUuserAdd constraintPk_uuserPrimary Key(UoN);
Drop TableDianfei;Create TableDianfei (UoNvarchar2(Ten) not NULL, Mmonthvarchar2(6) not NULL, DDF Number(6,2) not NULL, Djftime date not NULL, Djfztvarchar2(3) not NULL, DSYJF date not NULL); Comment on TableDianfei is 'Electric Meter'; Comment on columnDianfei.uon is 'user number (from key)'; Comment on columnDianfei.mmonth is 'Payment month (from key)'; Comment on columnDianfei.ddf is 'amount of electricity'; Comment on columnDianfei.djftime is 'Payment Date'; Comment on columnDianfei.djfzt is 'Payment Status'; Comment on columnDianfei.dsyjf is 'last month payment date';Alter TableDianfeiAdd constraintPk_dianfeiForeign Key(UoN)ReferencesUuser (uon);Alter TableDianfeiAdd constraintPk_dianfeiaForeign Key(Mmonth)References Money(Mmonth);
Drop TableShuifei;Create TableShuifei (UoNvarchar2(Ten) not NULL, Mmonthvarchar2(6) not NULL, SDF Number(6,2) not NULL, Sjftime date not NULL, Sjfztvarchar2(3) not NULL, SSYJF date not NULL); Comment on TableShuifei is 'Water Meter'; Comment on columnShuifei.uon is 'user number (from key)'; Comment on columnShuifei.mmonth is 'Payment month (from key)'; Comment on columnShuifei.sdf is 'amount of water charges'; Comment on columnShuifei.sjftime is 'Payment Date'; Comment on columnShuifei.sjfzt is 'Payment Status'; Comment on columnShuifei.ssyjf is 'last month payment date';Alter TableShuifeiAdd constraintPk_shuifeiForeign Key(UoN)ReferencesUuser (uon);Alter TableShuifeiAdd constraintPk_shuifeiaForeign Key(Mmonth)References Money(Mmonth);
Drop Table Money;Create Table Money(Mmonthvarchar2(6) not NULL, Mwater Number(4,2) not NULL, MPower Number(4,2) not NULL); Comment on column Money. Mmonth is 'Payment Month (primary key)'; Comment on column Money. Mwater is 'Price of water charges'; Comment on column Money. mpower is 'Electricity Price'; Alter Table Money Add constraintPk_moneyPrimary Key(Mmonth);
Water and electricity Payment management system-database table creation