东北大学数据库应用程序设计实践报告

课程编号：B080109004数据库应用程序设计实践报告姓名学号班级指导教师开设学期2016-2017第一学期开设时间第13周——第15周报告日期2016/12/16评定成绩评定人评定日期东北大学软件学院数据库应用程序设计实践报告11.问题定义银行代收费系统给电力公司开发的一套缴费系统，方便用户通过网银支付电费。主要的用例图：图1银行代收费系统用例图根据用例图得出主要的业务需求：（1）抄表系统管理员把抄表记录录入系统，抄表记录包括当前电表数、抄表日期、抄表人等信息，根据抄表记录，系统自动计算每个计费设备当月的应收电费。每个计费设备有唯一编号。（2）查询用户随时查询欠费金额。一个用户名下可能多个计费设备，查询欠费时，将所有计费设备欠费总和输出。需要考虑设备的余额问题。如果余额大于欠费，则欠费为0，更新余额，修改receivable中flag标志。（3）缴费在当月电费清单生成完毕后，用户可进行电费缴纳，缴纳金额可是任意金额。系统将缴费金额存入设备余额中，再次查询则欠费应该减少。（4）冲正用户在缴费过程中如果给其他用户缴费了，在当日0点前可以冲正，即把钱收回，放入余额，向payfee表中添加一个负数金额、相同银行流水号的记录。并且修改设备余额，此时查询欠费应该有改变。（5）对帐每个银行每日凌晨给电力公司的代缴费系统发送对账信息，代缴费系统记录对账结果，对账明细，对账异常信息进行存储。错误信息为100银行没有此记录。101企业没有此流水号.102银行企业金额不等。2．数据库设计数据库应用程序设计实践报告2（1）ER图设计：自己设计的ER图：经过老师修正统一的ER图：客户teladdressname设备deviceid拥有费用（应收）产生费用（实缴）缴纳银行缴给idnameflagidyearmonthpaydatebankserialtypepaymoneyidcodeidtypebalance抄表记录产生idyearmonthsnumbasicfee银行记录银行对账总表银行对账异常表idpayfeebankserialcheckdatebanktotalcountbanktotalmoneyourtotalcountourtotalmoneyidexceptiontypeourmoneyidcheckdatebankserialbankmoney（2）建表语句--CreatetablecreatetableBank(idnumber(4),namevarchar2(20),codechar(2))数据库应用程序设计实践报告3;--Create/Recreateprimary,uniqueandforeignkeyconstraintsaltertableBankaddconstraintPK_BANK_IDprimarykey(ID);altertableBANKaddconstraintPK_BANK_CODEunique(CODE);--Createtablecreatetableclient(idnumber(4),namevarchar2(20),addressvarchar2(80),telvarchar2(20));--Create/Recreateprimary,uniqueandforeignkeyconstraintsaltertableclientaddconstraintPK_CLIENT_IDprimarykey(ID);--Createtablecreatetabledevice(deviceidnumber(4),clientidnumber(4),typechar(2),balancenumber(7,2));--Create/Recreateprimary,uniqueandforeignkeyconstraintsaltertabledeviceaddconstraintPK_DEVICE_DEVICEIDprimarykey(DEVICEID);altertabledeviceaddconstraintFK_DEVICE_CLIENTIDforeignkey(CLIENTID)referencesclient(ID);--Createtablecreatetableelectricity(idnumber(4),deviceidnumber(4),yearmonthchar(6),snumnumber(10));--Create/Recreateprimary,uniqueandforeignkeyconstraintsaltertableelectricityaddconstraintPK_ELECTRICITY_IDprimarykey(ID);altertableelectricityaddconstraintFK_ELECTRICITY_DEVICEIDforeignkey(DEVICEID)referencesdevice(DEVICEID);--CreatetablecreatetableRECEIVABLES(idnumber(4),yearmonthchar(6),deviceidnumber(4),basicfeenumber(7,2),数据库应用程序设计实践报告4flagchar(1));--Create/Recreateprimary,uniqueandforeignkeyconstraintsaltertableRECEIVABLESaddconstraintPK_RECEIVABLES_IDprimarykey(ID);altertableRECEIVABLESaddconstraintFK_RECEIVABLES_DEVICEIDforeignkey(DEVICEID)referencesdevice(DEVICEID);--CreatetablecreatetablePAYFEE(idnumber(4),deviceidnumber(4),paymoneynumber(7,2),paydatedate,bankcodechar(2),typechar(4),bankserialvarchar2(20));--Create/Recreateprimary,uniqueandforeignkeyconstraintsaltertablePAYFEEaddconstraintPK_PAYFEE_IDprimarykey(ID);altertablePAYFEEaddconstraintFK_PAYFEE_DEVICEIDforeignkey(DEVICEID)referencesdevice(DEVICEID);altertablePAYFEEaddconstraintFK_PAYFEE_BANKCODEforeignkey(BANKCODE)referencesBANK(CODE);--CreatetablecreatetableBANKRECORD(idnumber(4),payfeenumber(7,2),bankcodechar(2),bankserialvarchar2(20));--Create/Recreateprimary,uniqueandforeignkeyconstraintsaltertableBANKRECORDaddconstraintPK_BANKRECORD_IDprimarykey(ID);altertableBANKRECORDaddconstraintFK_BANKRECORD_BANKCODEforeignkey(BANKCODE)referencesBANK(CODE);--CreatetablecreatetableCHECKRESULT(idnumber(4),checkdatedate,bankcodechar(2),banktotalcountnumber(4),banktotalmoneynumber(10,2),ourtotalcountnumber(4),ourtotalmoneynumber(10,2))数据库应用程序设计实践报告5;--Create/Recreateprimary,uniqueandforeignkeyconstraintsaltertablecHECKRESULTaddconstraintPK_CHECKRESULT_IDprimarykey(ID);altertableCHECKRESULTaddconstraintFK_CHECKRESULT_BANKCODEforeignkey(BANKCODE)referencesBANK(CODE);--Createtablecreatetablecheck_exception(idnumber(4),checkdatedate,bankcodechar(2),bankserialvarchar2(20),bankmoneynumber(7,2),ourmoneynumber(7,2),exceptiontypechar(3));--Create/Recreateprimary,uniqueandforeignkeyconstraintsaltertablecheck_exceptionaddconstraintPK_CHECKEXCEPTION_IDprimarykey(ID);altertableCHECK_EXCEPTIONaddconstraintFK_CHECKEXCEPTION_BANKCODEforeignkey(BANKCODE)referencesBANK(CODE);3．数据库端的系统实现1.十条sql语句（1）查询出所有欠费用户。（为了使测试方便，修改添加了一些数据，见附录）selecta.clientid,c.name,a.deviceid,b.yearmonthfromdeviceajoinreceivablesbona.deviceid=b.deviceidjoinclientcona.clientid=c.idwhereb.flag=0orderby1,3,4（2）查询出拥有超过2个设备的用户SELECTclientid,name数据库应用程序设计实践报告6FROM(SELECTclientid,COUNT(*)CTFROMdeviceGROUPBYclientid)joinclientonclient.id=clientidWHERECT2（3）统计电力企业某个月的总应收费用，实收费用selectmonth,sum(paymoney)from(selectto_char(paydate,'yyyymm')asMonth,paymoneyfrompayfeepwhereto_char(paydate,'yyyymm')='201608')groupbymonth--实收费用selectyearmonth,sum(basicfee)asreceivableMoneyfromreceivablesgroupbyyearmonthhavingyearmonth='201608'--应收费用数据库应用程序设计实践报告7（4）查询出所有欠费超过半年的用户withsas(selectb.deviceid,count(b.deviceid)fromreceivablesbwhereflag=0groupbyb.deviceidhavingcount(b.deviceid)1--我将题目修改成超过一个月)selectdevice.clientid,device.deviceidfromdevicejoinsondevice.deviceid=s.deviceidorderby1,2（5）查询任意用户的欠费总额selectclientid,sum(b.basicfee)fromdeviceajoinreceivablesbona.deviceid=b.deviceidwhereclientid=1groupbyclien