东北大学数据库应用程序设计实践报告.docx
- 文档编号:26594223
- 上传时间:2023-06-20
- 格式:DOCX
- 页数:28
- 大小:26.70KB
东北大学数据库应用程序设计实践报告.docx
《东北大学数据库应用程序设计实践报告.docx》由会员分享,可在线阅读,更多相关《东北大学数据库应用程序设计实践报告.docx(28页珍藏版)》请在冰豆网上搜索。
东北大学数据库应用程序设计实践报告
课程编号:
数据库应用程序设计实践报告
姓名
学号
班级
指导教师
开设学期
2016-2017第一学期
开设时间
第13周——第15周
报告日期
2016/12/16
评定成绩
评定人
评定日期
东北大学软件学院
1.问题定义
银行代收费系统给电力公司开发的一套缴费系统,方便用户通过网银支付电费。
主要的用例图:
图1银行代收费系统用例图
根据用例图得出主要的业务需求:
(1)抄表
系统管理员把抄表记录录入系统,抄表记录包括当前电表数、抄表日期、抄表人等信息,根据抄表记录,系统自动计算每个计费设备当月的应收电费。
每个计费设备有唯一编号。
(2)查询
用户随时查询欠费金额。
一个用户名下可能多个计费设备,查询欠费时,将所有计费设备欠费总和输出。
需要考虑设备的余额问题。
如果余额大于欠费,则欠费为0,更新余额,修改receivable中flag标志。
(3)缴费
在当月电费清单生成完毕后,用户可进行电费缴纳,缴纳金额可是任意金额。
系统将缴费金额存入设备余额中,再次查询则欠费应该减少。
(4)冲正
用户在缴费过程中如果给其他用户缴费了,在当日0点前可以冲正,即把钱收回,放入余额,向payfee表中添加一个负数金额、相同银行流水号的记录。
并且修改设备余额,此时查询欠费应该有改变。
(5)对帐
每个银行每日凌晨给电力公司的代缴费系统发送对账信息,代缴费系统记录对账结果,对账明细,对账异常信息进行存储。
错误信息为100银行没有此记录。
101企业没有此流水号.102银行企业金额不等。
2.数据库设计
(1)ER图设计:
自己设计的ER图:
经过老师修正统一的ER图:
(2)建表语句
--Createtable
createtableBank
(
idnumber(4),
namevarchar2(20),
codechar
(2)
)
;
--Create/Recreateprimary,uniqueandforeignkeyconstraints
altertableBank
addconstraintPK_BANK_IDprimarykey(ID);
altertableBANK
addconstraintPK_BANK_CODEunique(CODE);
--Createtable
createtableclient
(
idnumber(4),
namevarchar2(20),
addressvarchar2(80),
telvarchar2(20)
)
;
--Create/Recreateprimary,uniqueandforeignkeyconstraints
altertableclient
addconstraintPK_CLIENT_IDprimarykey(ID);
--Createtable
createtabledevice
(
deviceidnumber(4),
clientidnumber(4),
typechar
(2),
balancenumber(7,2)
)
;
--Create/Recreateprimary,uniqueandforeignkeyconstraints
altertabledevice
addconstraintPK_DEVICE_DEVICEIDprimarykey(DEVICEID);
altertabledevice
addconstraintFK_DEVICE_CLIENTIDforeignkey(CLIENTID)
referencesclient(ID);
--Createtable
createtableelectricity
(
idnumber(4),
deviceidnumber(4),
yearmonthchar(6),
snumnumber(10)
)
;
--Create/Recreateprimary,uniqueandforeignkeyconstraints
altertableelectricity
addconstraintPK_ELECTRICITY_IDprimarykey(ID);
altertableelectricity
addconstraintFK_ELECTRICITY_DEVICEIDforeignkey(DEVICEID)
referencesdevice(DEVICEID);
--Createtable
createtableRECEIVABLES
(
idnumber(4),
yearmonthchar(6),
deviceidnumber(4),
basicfeenumber(7,2),
flagchar
(1)
)
;
--Create/Recreateprimary,uniqueandforeignkeyconstraints
altertableRECEIVABLES
addconstraintPK_RECEIVABLES_IDprimarykey(ID);
altertableRECEIVABLES
addconstraintFK_RECEIVABLES_DEVICEIDforeignkey(DEVICEID)
referencesdevice(DEVICEID);
--Createtable
createtablePAYFEE
(
idnumber(4),
deviceidnumber(4),
paymoneynumber(7,2),
paydatedate,
bankcodechar
(2),
typechar(4),
bankserialvarchar2(20)
)
;
--Create/Recreateprimary,uniqueandforeignkeyconstraints
altertablePAYFEE
addconstraintPK_PAYFEE_IDprimarykey(ID);
altertablePAYFEE
addconstraintFK_PAYFEE_DEVICEIDforeignkey(DEVICEID)
referencesdevice(DEVICEID);
altertablePAYFEE
addconstraintFK_PAYFEE_BANKCODEforeignkey(BANKCODE)
referencesBANK(CODE);
--Createtable
createtableBANKRECORD
(
idnumber(4),
payfeenumber(7,2),
bankcodechar
(2),
bankserialvarchar2(20)
)
;
--Create/Recreateprimary,uniqueandforeignkeyconstraints
altertableBANKRECORD
addconstraintPK_BANKRECORD_IDprimarykey(ID);
altertableBANKRECORD
addconstraintFK_BANKRECORD_BANKCODEforeignkey(BANKCODE)
referencesBANK(CODE);
--Createtable
createtableCHECKRESULT
(
idnumber(4),
checkdatedate,
bankcodechar
(2),
banktotalcountnumber(4),
banktotalmoneynumber(10,2),
ourtotalcountnumber(4),
ourtotalmoneynumber(10,2)
)
;
--Create/Recreateprimary,uniqueandforeignkeyconstraints
altertablecHECKRESULT
addconstraintPK_CHECKRESULT_IDprimarykey(ID);
altertableCHECKRESULT
addconstraintFK_CHECKRESULT_BANKCODEforeignkey(BANKCODE)
referencesBANK(CODE);
--Createtable
createtablecheck_exception
(
idnumber(4),
checkdatedate,
bankcodechar
(2),
bankserialvarchar2(20),
bankmoneynumber(7,2),
ourmoneynumber(7,2),
exceptiontypechar(3)
)
;
--Create/Recreateprimary,uniqueandforeignkeyconstraints
altertablecheck_exception
addconstraintPK_CHECKEXCEPTION_IDprimarykey(ID);
altertableCHECK_EXCEPTION
addconstraintFK_CHECKEXCEPTION_BANKCODEforeignkey(BANKCODE)
referencesBANK(CODE);
3.数据库端的系统实现
1.十条sql语句
(1)查询出所有欠费用户。
(为了使测试方便,修改添加了一些数据,见附录)
selecta.clientid,c.name,a.deviceid,b.yearmonth
fromdeviceajoinreceivablesbona.deviceid=b.deviceidjoinclientcona.clientid=c.id
whereb.flag=0
orderby1,3,4
(2)查询出拥有超过2个设备的用户
SELECTclientid,name
FROM(SELECTclientid,COUNT(*)CTFROMdeviceGROUPBYclientid)joinclientonclient.id=clientid
WHERECT>2
(3)统计电力企业某个月的总应收费用,实收费用
selectmonth,sum(paymoney)
from(
selectto_char(paydate,'yyyymm')asMonth,paymoney
frompayfeep
whereto_char(paydate,'yyyymm')='201608'
)
groupbymonth
--实收费用
selectyearmonth,sum(basicfee)asreceivableMoney
fromreceivables
groupbyyearmonth
havingyearmonth='201608'
--应收费用
(4)查询出所有欠费超过半年的用户
withsas(
selectb.deviceid,count(b.deviceid)
fromreceivablesb
whereflag=0
groupbyb.deviceid
havingcount(b.deviceid)>1--我将题目修改成超过一个月
)
selectdevice.clientid,device.deviceid
fromdevicejoinsondevice.deviceid=s.deviceid
orderby1,2
(5)查询任意用户的欠费总额
selectclientid,sum(b.basicfee)
fromdeviceajoinreceivablesbona.deviceid=b.deviceid
whereclientid=1
groupbyclientid,flag
havingflag=0
(6)查询出某个月用电量最高的3名用户
withsas(
selectsum(b.snum)assum_num,a.clientid
fromdevicea
innerjoinelectricitybona.deviceid=b.deviceid
whereb.yearmonth='201608'--月份条件
groupbya.clientid
)
selects0.*
from(
selects.clientid,s.sum_num
froms
orderbys.sum_numdesc)s0
whererownum<=3
(7)查询出电力企业某个月哪天的缴费人数最多
selectday,num
from
(selectcount(id)asnum,to_char(paydate,'yyyymmdd')asday
frompayfee
whereto_char(paydate,'yyyymm')='201608'
groupbyto_char(paydate,'yyyymmdd')
orderbycount(bankserial)desc
)
whererownum<2;--查询8月份付款人数最多的一天
(8)按设备类型使用人数从高到低排序查询列出设备类型,使用人数。
selectdevice.type,count(*)asnum
fromdevice
groupbydevice.type
orderbycount(*)desc
(9)统计每个月各银行缴费人次,从高到低排序。
selectto_char(paydate,'yyyymm')yearmonth,bank.name,count(payfee.id)num
frombankjoinpayfeeonbank.code=payfee.bankcode
groupbyto_char(paydate,'yyyymm'),bank.name
orderbyyearmonth,numdesc;
--增加了一条记录,修改了两条记录
(10)查询出电力企业所有新增用户(使用设备不足半年)。
selectclient.id,device.deviceid
fromclientjoindeviceonclient.id=device.clientid
joinelectricityondevice.deviceid=electricity.deviceid
groupbyclient.id,device.deviceid
havingcount(yearmonth)<6;
--如果某个设备的抄表记录数小于6,则其使用不足半年
附录:
添加修改的数据:
1.在device表下,加入数据如图:
2.在receivables表中,加入四个设备两个月的应收记录如图:
3.在payfee里加入1201设备201609的付款记录
4.向electricity中插入数据,如下图,比较用户。
5.将payfee中的部分记录的bankcode更改,订单日期也进行更改
6.增加记录到bank表中。
2.事物存储过程
(1)查询
代码1:
(按设备号进行查询,在代码2中被调用)
createorreplaceprocedurequeryfee1(devicenoinnumber,smoneyoutnumber)is
basicfeenumber(7,2);
yearmonthreceivables.yearmonth%type;
dtypedevice.type%type;
daysnumber;
days2number;
d_balancenumber;
ridnumber;
chargedatedate;
cursortemp_cursoris
selectr.basicfee,r.yearmonth,d.type,r.id
fromdeviced,receivablesr
whered.deviceid=r.deviceid
andr.flag=0
andd.deviceid=deviceno;
begin
smoney:
=0;
opentemp_cursor;
loop
fetchtemp_cursorintobasicfee,yearmonth,dtype,rid;
exitwhentemp_cursor%notfound;
smoney:
=smoney+basicfee;
smoney:
=smoney+basicfee*0.08;
ifdtype='01'then
smoney:
=smoney+basicfee*0.1;
else
smoney:
=smoney+basicfee*0.15;
endif;
selectround(sysdate-add_months(to_date(yearmonth,'yyyymm'),1))
intodays
fromdual;
selectTO_CHAR(SYSDATE,'DDD')intodays2fromdual;
ifdays>0then
ifdtype='01'then--居民违约金跨年与不跨年违约金比例相同
smoney:
=smoney+basicfee*0.001*days;
else
ifdays smoney: =smoney+basicfee*0.002*days; else--其他,跨年 smoney: =smoney+basicfee*0.002*(days-days2)+basicfee*0.003*(days2); endif; endif; endif; endloop; selectbalanceintod_balancefromdevicewheredeviceid=deviceno; if(smoney<=d_balanceandsmoney! =0)then--如果设备余额大于欠费余额更新、欠费置0 selecttrunc(sysdate)intochargedatefromdual;--截取到日 insertintodevicerecordvalues(deviceno,smoney,rid,chargedate);--把设备扣费记录保存保存 updatedevicesetbalance=balance-smoneywheredeviceid=deviceno; updatereceivablessetflag=2whereid=rid; smoney: =0; elsif(smoney! =0)then--设备余额不够缴费 smoney: =smoney-d_balance; endif; endqueryfee1; 代码2: (按用户号获得设备号,将设备号传入代码1的存储过程中) createorreplaceprocedureQueryFee(clientnoinclient.id%type,smoneyoutnumber)is devicenonumber; d_smoneynumber; cursortemp_cursoris selectd.deviceid fromclientc,deviced wherec.id=d.clientid andc.id=clientno; begin smoney: =0; opentemp_cursor; loop fetchtemp_cursorintodeviceno; exitwhentemp_cursor%notfound; queryfee1(deviceno,d_smoney); smoney: =smoney+d_smoney; endloop; endQueryFee; 测试截图: 设备6的应收费用表: 查询设备6的欠费金额: 主要创新点: 1.我将修改标志位flag和扣费的过程写在了此存储过程中。 查询时如果设备余额大于欠费数,则用余额对设备进行缴费,更新flag=2(第二天凌晨所有的flag=2更改为1)是为了标志是今天的扣费修改过程,方便冲正。 2.我新设置了一个表,bankrecord用来记录扣费记录,方便冲正的时候将设备金额变回来。 3.在计算跨年费用时,我使用了selectTO_CHAR(SYSDATE,'DDD')intodays2fromdual; 首先判断当前时间是一年中的第几天,再根据老师的代码,设备欠费天数days作比较。 如果days>days2,说明存在跨年的欠费,否则不存在。 2.缴费 代码: (添加记录到payfee表中,并更改设备余额) createorreplaceprocedurepayfee1(devicenoinnumber,paymoneyinnumber,resultsoutvarchar)is paydatedate; begin ifpaymoney>0then selecttrunc(sysdate)intopaydatefromdual;--截取到日 insertintopayfeevalues(paysequence.nextval,deviceno,paymoney,paydate,19,2001,bankserial.nextval); updatedevicesetbalance=balance+paymoneywheredeviceid=deviceno; results: ='成功'; else results: ='缴费失败,缴费金额不能少于0'; end
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 东北大学 数据库 应用 程序设计 实践 报告