银行ATM存取款机系统设计与实现.doc

数据库技术与开发项目实训设计报告 项目名称：银行ATM存取款机系统设计与实现姓 名：范涛学 号：1267159221专 业：软件工程12-2指导教师：刘立新 完成日期：2014-11-20目录项目名称：银行ATM存取款机系统设计与实现1银行ATM存取款机系统设计与实现3第一章 ：项目背景31、项目任务32、项目技能目标33、需求概述34、开发环境3第二章 ：创建数据库41.创建数据库42.创建各个数据表及相关的约束53.添加外键约束和生成数据库7第三章 ：创建触发器和插入测试数据81.创建触发器82.插入数据表的测试数据12第四章：模拟常规业务151.修改客户密码162.办理银行卡挂失163.统计银行资金流通余额和盈利结算174.查询本周开户信息185.查询本月单次交易金额最高的卡号和总交易金额最高的卡号196.查询挂失客户207.催款提醒业务20第五章：创建、使用视图201.输出银行客户记录视图VW_userInfo212.输出银行卡记录视图VW_CardInfo223.输出银行卡交易记录视图VW_TransInfo224.根据客户登录名查询该客户账户信息VW_OneUserInfo23第六章：存储过程实现业务处理231.完成存款或取款业务242.产生随机卡号283.完成开户业务294.分页显示查询交易数据335.打印客户对账单346.统计未发生交易的账户377.统计银行卡交易量和交易额40第七章：利用事务实现转账42附录48银行ATM存取款机系统设计与实现第1章 ：项目背景1、项目任务创建数据库、创建表、创建约束使用触发器和插入测试数据模拟常规业务、创建视图使用存储过程实现业务处理利用事务实现较复杂的数据更新2、项目技能目标 使用PowerDesigner完成数据库概念模型和数据库物理模型设计。 使用T-SQL语句创建数据库、表和各种约束。 使用T-SQL语句编程实现常见业务。 使用触发器实现多表之间的级联更新。 使用事务和存储过程封装业务逻辑。 使用视图简化复杂的数据查询。 使用游标技术实现结果集的行集操作。3、需求概述某银行是一家民办的小型银行企业，现有十多万客户，公司将为该银行开发一套ATM存取款机系统，对银行日常的存取款业务进行计算机管理，以便保证数据的安全性，提高工作效率。要求根据银行存取款业务需求设计出符合第三范式的数据库结构，使用T-SQL语言创建数据库和表，并添加表约束，进行数据的增删改查，运用逻辑结构语句、事务、视图和存储过程，按照银行的业务需求，实现各项银行日常存款、取款和转账业务。4、开发环境 数据库：SQL SERVER 2008开发版 数据库建模工具：PowerDesigner15第2章 ：创建数据库1.创建数据库代码如下：create database bankdbon primary(name=Nbankdb, filename=NG:数据库课设bankdb.mdf, size=5mb, maxsize=30mb, filegrowth=15%)log on(name=Nbankdb_log, filename=NG:数据库课设bankdb_log.ldf, size=2mb, maxsize=30mb, filegrowth=15%)2.创建各个数据表及相关的约束（1）.创建银行业务类型表create table bankbusinesstype(bbt_id int identity(1,1) primary key,bbt_name char(20) not null,bbt_comment varchar(100);（2）.创建用户信息表create table bankcustomer( bc_id int identity(1,1) primary key, bc_name char(20) not null, bc_icno char(18) not null check(left(bc_icno,17) like 0-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-9 and (right(bc_icno,1) like 0-9 or right(bc_icno,1) like x), bc_tel varchar(20) not null check(bc_tel like 0-90-90-90-9_0-90-90-90-90-90-90-9) or (bc_tel like 0-90-90-90-90-90-90-90-90-90-90-9), bc_addr varchar(100);（3）.创建银行卡信息create table bankcard(bc_no char(19) not null, bc_pwd char(6) not null, bc_currency char(5) not null, bc_bbtid int not null, bc_opendate date not null, bc_openamount money not null, bc_regloss char(2), bc_bcid int not null, bc_existbalance money not null)alter table bankcardadd constraint sp_bcno1 primary key(bc_no)alter table bankcardadd constraint sp_bcno2 check(bc_no like 1010 3576 0-90-90-90-9 0-90-90-90-9)alter table bankcardadd constraint sp_bcopenamount check(bc_openamount=1)alter table bankcardadd constraint sp_bcpwd default(888888) for bc_pwdalter table bankcardadd constraint sp_bccurrency default(RMB) for bc_currencyalter table bankcardadd constraint sp_bcopendate default(getdate() for bc_opendatealter table bankcardadd constraint sp_bcregloss default(否) for bc_regloss（4）.创建交易信息表create table bankdealinfo(bd_no int identity(1,1), bd_bcno char(19) not null, bd_dealdate date not null, bd_dealacount money not null, bd_dealtype char(10) not null, bd_dealcomment char(100)alter table bankdealinfoadd constraint sp_bdno primary key(bd_no)alter table bankdealinfoadd constraint sp_bddealdate default(getdate() for bd_dealdatealter table bankdealinfoadd constraint sp_bddealtype check(bd_dealtype=存入 or bd_dealtype=支取)3.添加外键约束和生成数据库代码如下：use bankdb goalter table bankcardadd constraint fk_bc_bbt foreign key(bc_bbtid) references bankbusinesstype(bbt_id);alter table bankcardadd constraint fk_bc_bc foreign key(bc_bcid) references BankCustomer(bc_id);alter table bankDealInfoadd constraint fk_bdi_bc foreign key(bd_bcno) references BankCard(bc_no);第3章 ：创建触发器和插入测试数据1.创建级联触发器（1）.创建Insert触发器：if (object_id(tr_InsertdealInfo,tr) is not null) drop trigger tr_InsertdealInfogocreate trigger tr_InsertdealInfoon bankdealinfofor insertas declare type char(10),sum money,bdbcno char(19); -创建一个游标，指向inserted表 declare cursor_bankdealinfo cursor for select bd_dealtype,bd_dealacount,bd_bcno from inserted -打开游标 open cursor_BankDealinfo -取游标中各个字段的值复制给各个变量 fetch next from cursor_BankDealinfo into type,sum,BDBCNo while fetch_status=0 begin -判断交易记录里是存入还是支取，及时更新银行卡表的存款余额 if(rtrim(ltrim(type)=存入) update bankcard set BC_ExistBalance=BC_ExistBalance+sum where BC_No=BDBCNo; if(rtrim(ltrim(type)=支取) update bankcard set BC_ExistBalance=BC_ExistBalance-sum where BC_No=BDBCNo; fetch next from cursor_BankDealinfo into type,sum,BDBCNo end close cursor_BankDealinfo deallocate cursor_BankDealinfoGo（2).创建Delete触发器if (object_id(tr_DeldealInfo,tr) is not null) drop trigger tr_DeldealInfogocreate trigger tr_DeldealInfoon bankdealinfofor deleteas declare type char(10),sum money,BDBCNo char(19); -创建一个游标，指向deleted表 declare cursor_BankDealinfo cursor for select BD_DealType,BD_DealAcount,BD_BCNo from deleted -打开游标 open cursor_BankDealinfo -取游标中各个字段的值复制给各个变量 fetch next from cursor_BankDealinfo into type,sum,BDBCNo while fetch_status=0 begin if(rtrim(ltrim(type)=存入) update bankcard set BC_ExistBalance=BC_ExistBalance-sum where BC_No=BDBCNo; if(rtrim(ltrim(type)=支取) update bankcard set BC_ExistBalance=BC_ExistBalance+sum where BC_No=BDBCNo; fetch next from cursor_BankDealinfo into type,sum,BDBCNo end close cursor_BankDealinfo deallocate cursor_BankDealinfoGo(3).创建update触发器：if (object_id(tr_DeldealInfo,tr) is not null) drop trigger tr_DeldealInfogocreate trigger tr_DeldealInfoon bankdealinfofor updateasdeclare type char(10),sum money,BDBCNo char(19); -创建一个游标，指向deleted表 declare cursor_BankDealinfo cursor for select BD_DealType,BD_DealAcount,BD_BCNo from updated -打开游标 open cursor_BankDealinfo -取游标中各个字段的值复制给各个变量 fetch next from cursor_BankDealinfo into type,sum,BDBCNo while fetch_status=0 begin if(rtrim(ltrim(type)=存入) update bankcard set BC_ExistBalance=BC_ExistBalance-sum where BC_No=BDBCNo; if(rtrim(ltrim(type)=支取) update bankcard set BC_ExistBalance=BC_ExistBalance+sum where BC_No=BDBCNo; fetch next from cursor_BankDealinfo into type,sum,BDBCNo end close cursor_BankDealinfo deallocate cursor_BankDealinfogo2.插入数据表的测试数据1.BankBusinessType表的测试数据代码如下：insert into bankbusinesstype values(活期,无固定存期，可随时存取，存取金额不限的一种比较灵活的存款),(定活两便,事先不约定存期，一次性存入，一次性支取的存款),(通知,不约定存期，支取时需提前通知银行，约定支取日期和金额方能支取的存款),(整存整取1年,整笔存入，到期提取本息),(整存整取2年,整笔存入，到期提取本息),(整存整取3年,整笔存入，到期提取本息),(零存整取1年,事先原定金额，逐月按约定金额存入，到期支付本息),(零存整取2年,事先原定金额，逐月按约定金额存入，到期支付本息),(零存整取3年,事先原定金额，逐月按约定金额存入，到期支付本息),(自助转账,银行ATM机上办理银行卡之间互相划转);select * from bankbusinesstype 结果如下图所示：图12.BankCustomer表的测试数据insert into bankcustomervalues(小包头市昆都仑区包钢五中),(小黑,150203199202352254包头昆区阿尔丁大街);select * from bankcustomer 结果如下图所示：图23.BankCard表的测试数据declare date datetimeselect date=cast(DATEADD(DD,-(rand()*30),GETDATE() as datetime)-前30天的日期insert into BankCardvalues(1010 3576 1234 5678,197611,RMB,1,date,1000.00,否,1,1000.00);select date=cast(DATEADD(DD,-(rand()*30),GETDATE() as datetime)insert into BankCardvalues(1010 3576 1234 5688,197711,RMB,2,date,1500.00,否,2,1500.00);select * from BankCard结果如下图所示：图34.BankDealInfo表的测试数据declare date1 datetimeselect date1=cast(DATEADD(DD,-(rand()*15),GETDATE() as datetime)insert into BankDealInfovalues(1010 3576 1234 5678,date1,500.00,存入,单位1月工资)select date1=cast(DATEADD(DD,-(rand()*15),GETDATE() as datetime)insert into BankDealInfovalues(1010 3576 1234 5678,date1,1500.00,存入,单位2月工资)select date1=cast(DATEADD(DD,-(rand()*15),GETDATE() as datetime)insert into BankDealInfovalues(1010 3576 1234 5678,date1,600.00,支取,支付宝付款)select date1=cast(DATEADD(DD,-(rand()*15),GETDATE() as datetime)insert into BankDealInfovalues(1010 3576 1234 5678,date1,700.00,支取,刷卡消费);select date1=cast(DATEADD(DD,-(rand()*15),GETDATE() as datetime)insert into BankDealInfovalues(1010 3576 1234 5688,date1,3000.00,存入,单位1月工资)select date1=cast(DATEADD(DD,-(rand()*15),GETDATE() as datetime)insert into BankDealInfovalues(1010 3576 1234 5688,date1,2800.00,存入,单位2月工资)select date1=cast(DATEADD(DD,-(rand()*15),GETDATE() as datetime)insert into BankDealInfovalues(1010 3576 1234 5688,date1,1600.00,支取,支付宝付款)select date1=cast(DATEADD(DD,-(rand()*15),GETDATE() as datetime)insert into BankDealInfovalues(1010 3576 1234 5688,date1,900.00,支取,刷卡消费);select * from BankDealInfo结果如下图所示：图4第四章：模拟常规业务1. 修改客户密码update BankCard set BC_Pwd=123456 where BC_No=1010 3576 1234 5678;update BankCard set BC_Pwd=123123 where BC_No=1010 3576 1234 5688;-修改密码后的查询显示select bc_no as 卡号,bc_pwd as 密码,BC_Currency as 货币类型,BC_BBTId as 储蓄类型,BC_OpenDate as 开户日期,BC_OpenAmount as 开户金额,BC_RegLoss as 是否挂失,BC_BBTId as 客户编号,BC_ExistBalance as 存款余额 from BankCard结果如下图所示：图52.办理银行卡挂失select * from BankCard where BC_No=1010 3576 1234 5678;update BankCard set bc_regloss =是 where BC_No=1010 3576 1234 5678;select BankCard.BC_No as 卡号,BankCard.BC_Pwd as 密码,BankCard.BC_Currency as 货币类型,BankBusinessType.BBT_Name as 储蓄类型,BankCard.BC_OpenDate as 开户日期,BankCard.BC_OpenAmount as 开户金额,BankCard.BC_RegLoss as 是否挂失,BankCustomer.BC_Name as 客户姓名,BankCard.BC_ExistBalance as 存款余额 from BankCard inner join BankBusinessType on BankCard.BC_BBTId=BankBusinessType.BBT_Idinner join BankCustomer on BankCard.BC_BCId=BankCustomer.BC_IdGo结果如下图所示：图63.统计银行资金流通余额和盈利结算if exists(select * from sysobjects where name=proc_staticsBanlanceAndProfit ) drop procedure proc_staticsBanlanceAndProfit gocreate procedure proc_staticsBanlanceAndProfitasdeclare YU money;declare YING money;declare IN money;declare OUT money select IN=sum(bd_dealacount ) from BankDealInfo where rtrim(ltrim(BD_DealType)=存入;select OUT =sum(BD_DealAcount) from BankDealInfo where rtrim(ltrim(BD_DealType)=支取;set YU=IN-OUT;set YING =IN *0.008-OUT *0.003;print 存入总金额：+rtrim(ltrim(str(IN )+RMB,支取总金额：+rtrim(ltrim(str(OUT)+RMB,银行流通余额：+rtrim(ltrim(str(YU)+RMB,盈利余额为：+Rtrim(Ltrim(str(YING)+RMBgoexec proc_staticsBanlanceAndProfit;use BankDBGO结果如下图所示：图74.查询本周开户信息select BankCard.bc_no as 卡号,BankCustomer.BC_Name as 姓名,BankCard.BC_Currency as 货币,BankBusinessType.BBT_Name as 存款类型,BankCard.BC_OpenDate as 开户日期,BankCard.BC_OpenAmount as 开户金额,BankCard.BC_ExistBalance as 存款余额, case when BankCard.BC_RegLoss=是 then 挂失状态else 非挂失状态 end as 账户状态 from BankCard inner join BankBusinessType on BankCard.BC_BBTId=BankBusinessType.BBT_Idinner join BankCustomer on BankCard.BC_BCId=BankCustomer.BC_Idwhere datediff(dd,datepart(dayofyear,BankCard.BC_OpenDate),datepart(dayofyear,getdate()=7;结果如下图所示：图85.查询本月单次交易金额最高的卡号和总交易金额最高的卡号select distinct BankCard.BC_No as 卡号,BankCard.BC_OpenDate as 开户日期,BankCard.BC_OpenAmount as 开户金额from BankCardwhere BankCard.BC_No in(select BankDealInfo.BD_BCNo from BankDealInfo where BankDealInfo.BD_DealAcount = (select max(BankDealInfo.BD_DealAcount) from BankDealInfo WHERE DATEDIFF(mm,BankDealInfo.BD_DealDate,GETDATE()=0)Go结果如下图所示：图96.查询挂失客户-自查询select BankCustomer.bc_name as 客户姓名,BankCustomer.BC_Tel as 客户电话from BankCard inner join BankCustomer on BankCard.BC_BCId=BankCustomer.BC_Idwhere BankCard.BC_RegLoss=是;-内部查询select BankCustomer.BC_Name as 客户姓名,BankCustomer.BC_Tel as 客户电话from BankCustomer where BC_Id in(select BC_BCId from BankCard where BC_RegLoss=是);结果如下图所示：图107.催款提醒业务select BankCustomer.bc_name as 客户姓名,BankCustomer.BC_Tel as 客户电话,BankCard.BC_ExistBalance as 存款余额from BankCardinner join BankCustomer on BankCard.BC_BCId=BankCustomer.BC_Idwhere BankCard.BC_ExistBalance5000.00;Go结果如下图所示：图11第五章：创建、使用视图1.输出银行客户记录视图VW_userInfoIF EXISTS(SELECT 1 FROM sys.views WHERE name=VW_userInfo) DROP VIEW VW_userInfogocreate view VW_userInfoas select bc_id as 客户编号,BC_Name as 开户名,BC_ICNo as 身份证号,BC_Tel as 电话号码,BC_Addr as 居住地址 from BankCustomergoselect * from VW_userInfo结果如下图所示：图122.输出银行卡记录视图VW_CardInfoIF EXISTS(SELECT 1 FROM sys.views WHERE name=VW_CardInfo) DROP VIEW VW_CardInfo gocreate view VW_CardInfoas select bankcard.bc_no as 卡号,bankcustomer.bc_name as 开户名,bankcard.bc_currency as 货币类型,bankbusinesstype.bbt_name as 存款类型,bankcard.bc_opendate as 开户日期, bankcard.bc_existbalance as 存款余额,bankcard.bc_pwd as 密码 from bankcard inner join bankbusinesstype on bankcard.bc_bbtid =bankbusinesstype.bbt_id inner join bankcustomer on bankcard.bc_bcid =bankcustomer.bc_id goselect * from VW_CardInfo结果如下图所示：图133.输出银行卡交易记录视图VW_TransInfoIF EXISTS(SELECT 1 FROM sys.views WHERE name=VW_TransInfo) DROP VIEW VW_TransInfogocreate view VW_TransInfoas select bd_dealdate as 交易日期,bd_dealtype as 交易类型,bd_bcno as 卡号,bd_dealacount as 交易金额,bd_dealcomment as 交易备注 from bankdealinfo goselect * from VW_TransInfo结果如下图所示：图144.根据客户登录名查询该客户账户信息VW_OneUserInfoIF EXISTS(SELECT 1 FROM sys.views WHERE name=VW_OneUserInfo) DROP VIEW VW_OneUserInfo go create view VW_OneUserInfoas select bc_id as 客户编号,bc_name as 开户名,bc_icno as 身份证号,bc_tel as 电话号码,bc_addr as 记住地址 from bankcustomer goselect * from VW_OneUserInfo结果如下图所示：图15第六章：存储过程实现业务处理1.完成存款或取款业务if exists(select * from sysobjects where name=proc_TakeMoney)drop procedure proc_TakeMoneygo-创建存取款业务的存储过程create procedure proc_TakeMoneybcno char(19),money money,pwd char(6)=nullas-不返回受影响的行数set nocount on-声明一变量存放指定卡号的存款余额declare existBanlance money-启动事务机制begin transelect existBanlance=BC_ExistBalance from BankCard where BC_No=bcno print 交易前，卡号+bcno+，余额为：+ltrim(str(existBanlance)print 交易正进行，请稍后.-如果输入参数pwd为空，则为取款业务，否则为存款业务if (pwd is not null)-办理取款业务begin-判断指定卡号和密码是否存在，若存在，则可以取款，否则不能办理取款业务if exists(select * from BankCard where BC_No=bcno and BC_Pwd=pwd)begin-判断取款金额是否小于等于存款余额-1，若条件成立，则可以取款，否则不能取款if(money=ExistBanlance-1) begin set existBanlance = existBanlance - money insert into BankDealInfo values( bcno,GETDATE(),money,支取,通过存储) endelseprint 取款交易失败，余额不足，请减少取款endelseprint 取款交易失败，密码有错误endelse-办理存款业务 begin set existBanlance = existBanlance + money insert into BankDealInfo values(bcno,GETDATE(),money,存入,通过存储)end-判断事务处理里是否有异常，若没有异常，则提交，若有异常，则回滚if (ERROR0)beginprint 交易失败rollback tranendelsebegincommit tranprint 交易成功，交易金额为：+ltrim(str(money)-判断该交易为何种类型业务，若是存款，则现有余额等于原有余额加上存款金额print 卡号+bcno+，余额为：+ltrim(str(existBanlance) end -显示银行卡用户详情和交易详情select BankCard.bc_no as 卡号,BankCustomer.BC_Name as 开户名,BankCard.BC_Currency as 货币类型,BankBusinessType.BBT_Name as 存款类型,BankCard.BC_ExistBalance as 存款余额,BankCard.BC_Pwd as 密码,BankCard.BC_RegLoss as 是否挂失 from BankCardinner join BankBusinessType on BankCard.BC_BBTId=BankBusinessType.BBT_Idinner join BankCustomer on BankCard.BC_BCId=BankCustomer.BC_Idwhere BC_No=bcnoselect BD_DealDate as 交易日期, BD_DealType as 交易类型 ,BD_BCNo as 卡号, BD_DealAcount as 交易金额,BD_DealComment as 备注 from BankDealInfo where BD_BCNo=bcnogo-执行存款存储过程exec proc_TakeMoney 1010 3576 1234 5678,2100-执行取款存储过程exec proc_TakeMoney 1010 3576 1234 5688,100,123456结果如下图所示：图162.产生随机卡号if exists(select * from sysobjects where name=proc_randCardID)drop procedure Proc_randCardIDgocreate procedure Proc_randCardID(myCardId1 varchar(19) output)asdeclare m int;declare n int;declare D3 varchar(50);declare D4 varchar,tmpstr char(10);set m=0;set n=9;select D3=cast(cast(rand()*(m-n)+n) as int) as char(1) + cast(cast(rand()*(m-n)+n) as int) as char(1) + cast(cast(rand()*(m-n)+n) as int) as char(1) + cast(cast(rand()*(m-n)+n) as int) as char(1)+cast(cast(rand()*(m-n)+n) as int) as char(1) + cast(cast(rand()*(m-n)+n) as int) as char(1) + cast(cast(rand()*(m-n)+n) as int) as char(1) + cast(cast(rand()*(m-n)+n) as int) as char(1);set tmpstr=convert(char(10),D3)set myCardId1=1010 3576 +SUBSTRING(tmpstr,1,4)+ +SUBSTRING(tmpstr,5,8)godeclare myCardId1 varchar(19)exec proc_randCardId myCardId1 outputprint 产生随机卡号为:+myCardId1结果如下图所示：图173.完成开户业务use BankDBgoif exists(select * from sysobjects where name=usp_openAccount)drop procedure usp_openAccountgo-创建开户存储过程usp_openAccount，输入参数分别是开户名、身份证号、电话号码、开户金额、存款类型和地址create proc usp_openAccount BCName varchar(50),BCICNo char(18),BCTel varchar(20),BCOpenamount money, BBTN