数据库课程设计-ATM存取款机系统.doc

数据库技术与开发项目实训设计报告项目名称：ATM存取款机系统姓 名：*学 号：*专 业：软件2012-1指导教师： 于金林完成日期：2014-11-19目录一、创建数据库11.1创建数据库11.2 创建各个数据表及相关的约束21.3添加外键约束和生成数据库关系图5二、创建触发器和插入测试数据72.1 创建级联触发器72.2 插入数据表的测试数据12三、模拟常规业务183.1 修改客户密码183.2 办理银行卡挂失183.3 统计银行资金流通余额和盈利结算193.4 查询本周开户信息213.5 查询本月单次交易金额最高的卡号和总交易金额最高的卡号213.6 查询挂失客户223.7 催款提醒业务23四、创建、使用视图244.1 输出银行客户记录视图VW_userInfo244.2 输出银行卡记录视图VW_CardInfo254.3 输出银行卡交易记录视图VW_TransInfo264.4根据客户登录名查询该客户账户信息VW_OneUserInfo27五、存储过程实现业务处理285.1完成存款或取款业务285.2 产生随机卡号325.3 完成开户业务345.4 分页显示查询交易数据375.5 打印客户对账单395.6 统计未发生交易的账户435.7 统计银行卡交易量和交易额46六、利用事务实现转账52七、心得体会59一、创建数据库1.1创建数据库 使用Create DataBase语句创建“ATM存取款机系统”数据库BankDB代码如下：-创建BankDB数据库create database BankDB on primary(name=NBankDB,filename=NC:2014年工程案例项目银行ATM存取款机系统BankDB.mdf, size=5MB, filegrowth=15%)log on(name=NBankDB_log,filename=NC:2014年工程案例项目银行ATM存取款机系统BankDB_log.ldf, size=2MB, filegrowth=15%)GO1.2 创建各个数据表及相关的约束使用Create Table语句创建表结构。根据银行业务，分析表中每个列相应的约束要求，为每个表添加各种约束。要求创建表时要求检测是否存在表结构，如果存在，则先删除再创建。 代码如下use BankDB;go-判断银行业务类型表是否存在,若存在则删除if exists(select * from sysobjects where id=OBJECT_ID(NBankBusinessType) drop table BankBusinessType-创建银行业务类型表create table BankBusinessType(BBTId int identity(1,1) primary key,-银行业务类型编号BBTName char(20) not null,-银行业务类型名称BBTComment varchar(100)-银行业务类型描述);go-判断银行卡客户是否存在,若存在则删除if exists(select * from sysobjects where id=OBJECT_ID(NBankCustomer) drop table BankCustomer-创建银行客户信息表create table BankCustomer(BCuId int identity(1,1) primary key,-银行客户编号BCuName char(20) not null,-银行客户名字BCuICNo char(18) not null -银行客户身份证号check(left(BCuICNo,17) like 0-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-9 and (right(BCuICNo,1) like 0-9 or right(BCuICNo,1) like X),-定义身份证号前位必须是数字，后位可以是数字或者X。BCuTel varchar(20) not null -银行客户联系方式check(BCuTel like 0-90-90-90-9_0-90-90-90-90-90-90-9 orBCuTel like 0-90-90-9_0-90-90-90-90-90-90-90-9 or BCuTel like 13580-90-90-90-90-90-90-90-90-9),-定义联系方式，必须是固定电话号码或者手机号，手机前2位必须是13或15BCuAddress varchar(100)-银行客户地址);go-判断银行卡是否存在，若存在，则删除银行卡BankCardif exists(select * from sysobjects where id=object_id(NBankCard) drop table BankCard-建立银行卡信息create table BankCard(-卡号必须符合16位数字构成,前8位为1010 3576,后位是随机产生且唯一,每4位必须有一个空格BCNo char(19) primary key check(BCNo like 1010 3576 0-90-90-90-9 0-90-90-90-9),BCPassword char(6) not null default(888888),-密码,开户默认为888888BCCurrency char(5) not null default(RMB),-币种,为RMB类型BCBBTId int not null,-存款类型BCOpenDate date not null default(getdate(),-开户日期,默认当日BCOpenAmount money not null check(BCOpenAmount=1),-开户金额,不得小于元BCRegLoss char(2) default(否),-是否挂失,默认为否BCBCuId int not null,-客户编号BCExistBalance money not null-余额);-判断交易信息BankDealInfo是否存在,若存在则删除if exists(select * from sysobjects where id=OBJECT_ID(NBankDealInfo) drop table BankDealInfo-创建交易信息表create table BankDealInfo(BDNo int identity(1,1) primary key,-交易编号为自动增长列BDBCNo char(19) not null,-卡号BDDealDate Date not null default(getdate(),-交易日期BDDealAcount money not null,-交易金额BDDealType Char(10) not null check(BDDealType=存入 or BDDealType=支取),-交易类型，有种存入和支取BDDealComment varchar(100)-交易描述);1.3添加外键约束和生成数据库关系图添加子表外键约束及生成数据库关系图代码如下：-建立表之间的外键约束关系alter table bankcard add constraint fk_BC_BBT foreign key(BCBBTId) references bankbusinesstype(bbtid);-BC表与BTT表alter table bankcard add constraint fk_BC_BCu foreign key(BCBCuId) references BankCustomer(BCuId);-BC表与BCu表alter table bankDealInfoadd constraint fk_BD_BC foreign key(BDBCNo) references BankCard(BCNo);-BD表与BC表在SQL SERVER里自动生成数据库关系图，如下所示：二、创建触发器和插入测试数据2.1 创建级联触发器 2.1.1创建Insert触发器 在交易信息表BankDealInfo中创建一个Insert触发器，当增加一条交易信息时，修改相应银行卡的存款余额。 建议使用游标，实现批量增加的级联更新。-创建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); declare cursor_BankDealinfo cursor for select BDDealType,BDDealAcount,BDBCNo from inserted-创建一个游标，指向inserted表 open cursor_BankDealinfo-打开游标 fetch next from cursor_BankDealinfo into type,sum,BDBCNo-取游标中各个字段的值复制给各个变量 while fetch_status=0-fetch语句成功-=-1是语句失败或此行不在结果集中；=-2是被提取的行不存在 begin -判断交易记录里是存入还是支取，及时更新银行卡表的存款余额 if(rtrim(ltrim(type)=存入) -去除多余空格 update bankcard set BCExistBalance=BCExistBalance+sum where BCNo=BDBCNo; if(rtrim(ltrim(type)=支取) update bankcard set BCExistBalance=BCExistBalance-sum where BCNo=BDBCNo; fetch next from cursor_BankDealinfo into type,sum,BDBCNo end close cursor_BankDealinfo-关闭游标 deallocate cursor_BankDealinfo-释放游标Go2.1.2 创建Delete触发器在交易信息表创建一个Delete触发器，当删除一条交易信息时，修改相应银行卡的存款余额。建议使用游标，实现批量删除的级联更新。-创建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); declare cursor_BankDealinfo cursor for select BDDealType,BDDealAcount,BDBCNo from deleted-创建一个游标，指向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 BCExistBalance=BCExistBalance-sum where BCNo=BDBCNo; if(rtrim(ltrim(type)=支取) update bankcard set BCExistBalance=BCExistBalance+sum where BCNo=BDBCNo; fetch next from cursor_BankDealinfo into type,sum,BDBCNo end close cursor_BankDealinfo deallocate cursor_BankDealinfo go2.1.3创建Update触发器 创建一个Update触发器，当更新交易信息表的记录时，更新银行卡表的相应卡号的余额。-创建update触发器 -在交易信息表中插入一个触发器，使用游标,当删除一个交易信息，修改银行卡的存款余额if (object_id(tr_updatedealInfo,tr) is not null) drop trigger tr_updatedealInfogocreate trigger tr_updatedealInfoon bankdealinfofor updateas declare type char(10),sum money,BDBCNo char(19); declare cursor_BankDealinfo cursor for select BDDealType,BDDealAcount,BDBCNo from inserted -创建一个游标，指向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 BCExistBalance=BCExistBalance+sum where BCNo=BDBCNo; if(rtrim(ltrim(type)=支取) update bankcard set BCExistBalance=BCExistBalance-sum where BCNo=BDBCNo; fetch next from cursor_BankDealinfo into type,sum,BDBCNo end close cursor_BankDealinfo deallocate cursor_BankDealInfo; print(插入记录); declare cursor_BankDealinfo cursor for select BDDealType,BDDealAcount,BDBCNo from deleted -创建一个游标，指向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 BCExistBalance=BCExistBalance-sum where BCNo=BDBCNo; if(rtrim(ltrim(type)=支取) update bankcard set BCExistBalance=BCExistBalance+sum where BCNo=BDBCNo; fetch next from cursor_BankDealinfo into type,sum,BDBCNo end close cursor_BankDealinfo deallocate cursor_BankDealInfo; print(删除记录);2.2 插入数据表的测试数据使用T-SQL语句向每个表插入如测试数据，要保证业务数据的一致性和完整性。2.2.1 BankBusinessType表的测试数据代码如下：-向已经创建的BankBusinessType表插入测试数据 insert into bankbusinessType(BBTName,BBTComment) values(活期,无固定存期,可随时存取,存取金额不限的一种比较灵活的存款),-加上逗号 (定活两便,事先不约定存期，一次性存入，一次性支取的存款), (通知,不约定存期，支取时应提前通知银行，约定支取日期和金额方能支取的存款), (整存整取1年,整笔存入，到期提取本息), (整存整取2年,整笔存入，到期提取本息), (整存整取3年,整笔存入，到期提取本息),(零存整取1年,事先原定金额，逐月按约定金额存入，到期支付本息),(零存整取2年,事先原定金额，逐月按约定金额存入，到期支付本息),(零存整取3年,事先原定金额，逐月按约定金额存入，到期支付本息),(自助转账,银行ATM存款机上办理银行卡之间互相划转)-不加逗号select * from bankbusinesstype 运行结果如下：2.2.2 BankCustomer表的测试数据 代码如下：-向已经创建的BankCustomer表中插入测试数据insert into bankcustomer (BCuName,BCuICNo,BCuTel,BCuAddress) values(张包头市昆区包钢五中), (关羽0472_2315490,包头市昆区阿尔丁大街), (林俊杰,140203197616547894沈阳市连山区), (张翰,21564876630154788X,0456_6561423,大连市幸福街)select * from BankCustomer 运行结果如下：2.2.3 BankCard表的测试数据使用T-SQL语句向BankCard表中插入数据，要求开户日期设置为当前日期近一个月的随机某一天（使用相应的日期函数和随机函数完成）。 代码如下：insert into BankCard(BCNo,BCPassword,BCCurrency,BCBBTId,BCOpenDate,BCOpenAmount,BCRegLoss,BCBCuId,BCExistBalance)values(1010 3576 1234 5678,000000,RMB,1,cast(dateadd(day,-cast(rand()*30 as int),getdate() as date),2000,否,1,2000), (1010 3576 1234 5688,000000,RMB,2,cast(dateadd(day,-cast(rand()*30 as int),getdate() as date),1500,否,2,1500), (1010 3576 1234 5698,000000,RMB,1,cast(dateadd(day,-cast(rand()*30 as int),getdate() as date),1000,否,1,1000);goselect * from BankCard;go运行结果如下：2.2.4 BankDealInfo表的测试数据使用T-SQL语句向BankDealInfo表中插入数据，要求交易日期设置为当前日期近半个月的随机某一天（使用相应的日期函数和随机函数完成）。代码如下：insert into BankDealInfo(BDBCNo,BDDealDate,BDDealAcount,BDDealType,BDDealComment)values(1010 3576 1234 5678,cast(dateadd(day,-cast(rand()*15 as int),getdate() as date),400,存入,单位1月工资), (1010 3576 1234 5678,cast(dateadd(day,-cast(rand()*15 as int),getdate() as date),500,存入,单位2月工资), (1010 3576 1234 5678,cast(dateadd(day,-cast(rand()*15 as int),getdate() as date),600,支取,支付宝付款), (1010 3576 1234 5688,cast(dateadd(day,-cast(rand()*15 as int),getdate() as date),700,支取,刷卡消费), (1010 3576 1234 5688,cast(dateadd(day,-cast(rand()*15 as int),getdate() as date),800,存入,单位1月工资), (1010 3576 1234 5688,cast(dateadd(day,-cast(rand()*15 as int),getdate() as date),900,存入,单位2月工资), (1010 3576 1234 5688,cast(dateadd(day,-cast(rand()*15 as int),getdate() as date),1000,支取,支付宝付款), (1010 3576 1234 5688,cast(dateadd(day,-cast(rand()*15 as int),getdate() as date),900.,支取,刷卡消费);select * from BankDealInfo;Go运行结果如下：三、模拟常规业务3.1 修改客户密码根据卡号修改指定2个客户的银行密码，其中第一个客户1010 3576 1234 5678密码修改为123456，第二个客户1010 3576 1234 5688修改为123123代码如下：-修改客户密码update BankCard set BCPassword=123456 where BCNo=1010 3576 1234 5678;update BankCard set BCPassword=123123 where BCNo=1010 3576 1234 5688;goselect BCNo as 银行卡卡号,BCPassword as 密码,BCCurrency as 币种,BCBBTId as 银行业务类型号,BCOpenDate as 开户日期,BCOpenAmount as 开户金额,BCRegLoss as 是否挂失,BCBCuId as 客户编号,BCExistBalance as 余额from BankCard;go运行结果如下：3.2 办理银行卡挂失卡号为1010 3576 1234 5678的银行卡丢失，申请挂失代码如下：-办理银行卡挂失update BankCard set BCRegLoss=是 where BCNo=1010 3576 1234 5688;goselect BCNo as 银行卡卡号,BCPassword as 密码,BCCurrency as 币种,BBTName as 银行业务类型名,BCOpenDate as 开户日期,BCOpenAmount as 开户金额,BCRegLoss as 是否挂失,BCuName as 银行客户姓名,BCExistBalance as 余额from BankCard inner join BankCustomer on BankCard.BCBCuid=BankCustomer.BCuId-银行卡和客户进行内连接inner join BankBusinessType on BankCard.BCBBTId=BankBusinessType.BBTIdgo运行结果如下：3.3 统计银行资金流通余额和盈利结算存入代表资金流入，支取代表资金流出。计算公式：资金流通余额=总存入金额-总支取金额假定存款利率为千分之三，贷款利率为千分之八。计算公式：盈利结算=总支取金额*0.008-总存入金额*0.003。要求创建一个存储过程pro_staticsBanlanceAndProfit代码如下：-统计银行资金流通余额和盈利结算if exists(select * from sysobjects where name=proc_staticsBanlanceAndProfit ) drop procedure proc_staticsBanlanceAndProfit; go -创建存储过程create procedure proc_staticsBanlanceAndProfitasdeclare InMoney money,OutMoney money;select InMoney=SUM(BDDealAcount) from BankDealInfo where rtrim(ltrim(BDDealType)=存入;select OutMoney=SUM(BDDealAcount) from BankDealInfo where rtrim(ltrim(BDDealType)=支取;print 存入总金额:+rtrim(ltrim(str(InMoney)+RMB,支取总金额:+rtrim(ltrim(str(OutMoney)+RMB,银行流通余额:+rtrim(ltrim(str(InMoney-OutMoney)+RMB;-资金流通余额=总存入金额-总支取金额print 盈利余额为:+rtrim(ltrim(str(OutMoney*0.008-InMoney*0.003)+RMB-盈利结算=总支取金额*0.008-总存入金额*0.003exec proc_staticsBanlanceAndProfit;运行结果如下：3.4 查询本周开户信息查询本周开户的卡号，显示该卡的相关信息， 提示：求时间差使用日期函数DateDiff()，求星期几使用日期函数DatePart()，账户状态列使用Case语句转换。代码如下：-查询本周开户信息select BCNo as 银行卡卡号,BCOpenDate as 开户日期,BCOpenAmount as 开户金额 from BankCard whereDATEDIFF(day,BCOpenDate,GETDATE()DATEPART(WEEKDAY,GETDATE();go运行结果如下：3.5 查询本月单次交易金额最高的卡号和总交易金额最高的卡号查询本月存、取款中单次交易金额最高的卡号信息，提示：要求使用子查询和distinct去掉重复的卡号,使用DateDiff()和DataPart()函数得到本月内的交易信息。代码如下：-查询本月存、取款中单次交易金额最高的卡号信息select distinct BDBCNo as 银行卡卡号,BCOpenDate as 开户日期,BCOpenAmount as 开户金额 from BankDealInfo,BankCard where BankDealInfo.BDBCNo=BankCard.BCNo and BDDealAcount=(select Max(BDDealAcount) from BankDealInfo) and datediff(day,BCOpenDate,getdate()datepart(month,getdate(); 运行结果如下：3.6 查询挂失客户查询挂失账号的客户信息，分别利用子查询in的方式或者内部连接inner join。代码如下：-查询挂失客户select BCuName as 客户姓名,bcuicno as 客户身份证号,BCuTel as 联系电话,bcuaddress as 客户地址 from BankCard inner join BankCustomer on BankCard.BCBCuId=BankCustomer.BCuId whereBCRegLoss=是;Go运行结果如下:3.7 催款提醒业务根据某种业务（如代缴电话费、代缴手机费或房贷等）的需要，每个月末，查询出客户账户上余额少于2000元，由银行统一致电催款。代码如下：-催款提醒业务select BCuName as 客户姓名,bcuicno as 客户身份证号, BCuTel as 联系电话,BCExistBalance as 账上余额 from BankCard inner join BankCustomer on BankCard.BCBCuId=BankCustomer.BCuId whereBankCard.BCExistBalance2000;go运行结果如下：四、创建、使用视图4.1 输出银行客户记录视图VW_userInfo显示的列名全为中文，要求先判断该视图是否存在，若存在，则先删除代码如下：-输出银行客户记录视图VW_userInfoIF EXISTS (SELECT * FROM sys.views WHERE name =VW_userInfo)drop view VW_userInfo;gocreate view VW_userInfo as-指定视图要执行的操作select BCuId as 客户号,BCuName as 客户姓名,BCuICNo as 客户身份证号,BCuTel as 客户电话,BCuAddress as 客户地址 from BankCustomer;gouse bankdb select * from VW_userInfo;运行结果如下：4.2 输出银行卡记录视图VW_CardInfo建议使用内部连接Inner Join语句代码如下：-输出银行卡记录视图VW_CardInfoIF EXISTS (SELECT * FROM sys.views WHERE name =VW_CardInfo)drop view VW_CardInfo;gocreate view VW_CardInfo asselect BCNo as 卡号,BCPassword as 密码,BCCurrency as 币种,BBTName as 存储类型,BCOpenDate as 开户日期,BCExistBalance as 余额,BCRegLoss as 是否挂失from BankCard inner join BankCustomer on BankCard.BCBCuId=BankCustomer.BCuIdinner join BankBusinessType on BankCard.BCBBTId=BankBusinessType.BBTId;gouse bankdbselect * from VW_CardInfo;运行结果如下：4.3 输出银行卡交易记录视图VW_TransInfo 代码如下：-输出银行卡交易记录视图VW_TransInfoIF EXISTS (SELECT * FROM sys.views WHERE name =VW_TransInfo)drop view VW_TransInfo;gocreate view VW_TransInfo asselect BDBCNo as 卡号, BDDealDate as 交易日期,BDDealType as 交易类型,BDDealAcount as 交易金额,BDDealComment as 交易描述 from BankDealInfo;gouse bankdb select * from VW_TransInfo;运行结果如下：4.4根据客户登录名查询该客户账户信息VW_OneUserInfo 根据客户登录名（采用实名制访问银行系统）查询该客户账户信息的视图，利用SQL SERVER系统函数system_user获得数据库用户名。代码如下：-根据客户登录名查询该客户账户信息VW_OneUserInfoIF EXISTS(SELECT 1 FROM sys.views WHERE name=VW_OneUserInfo) DROP VIEW VW_OneUserInfo go CREATE VIEW VW_OneUserInfo as SELECT BCuId AS 客户编号,BCuName AS 开户名,BCuICNo AS 身份证号,BCuTel AS 电话号码,BCuAddress AS 居住地址 from BankCustomerWHERE BCuName=system_userSELECT * FROM VW_OneUserInfo运行结果如下：五、存储过程实现业务处理5.1完成存款或取款业务 描述： 根据银行卡号和交易金额实现银行卡的存款和取款业务。 每一笔存款，取款业务都要计入银行交易账，并同时更新客户的存款余额。 如果是取款业务，在记账之前，要完成下面两项数据的检查验证工作，如果检查不合格，那么中断取款业务，给出提示信息后退出。 检查客户输入的密码是否正确。 账户取款金额是否大于当前存款额加1。要求： 取款或存款存储过程名为pro_takeMoney。 编写一个存储过程完成存款和取款业务，并调用存储过程取钱或者存钱进行测试，测试数据是张飞的卡号支取100元（密码123456），关羽的卡号存入2100元。代码如下：- 完成存款或取款业务if exists(select * from sysobjects where name=pro_TakeMoney)drop procedure pro_TakeMoneygo-创建存取款业务的存储过程create procedure pro_TakeMoneybcno char(19),money money,password char(6)=nullasset nocount on;declare bcexistBalance money;begin transactionselect bcexistBalance=BCexistBalance from BankCard where BCNo=bcno;print 交易之前，卡号+bcno+，余额为：+ltrim(str(bcexistBalance);print 交易正在进行，请耐心等候.;if (password is not null)-如果密码不空，则为取款beginif exists(select * from BankCard where BCNo=bcno and BCPassword=password)beginif(money=bcExistBalance-1)insert into BankDealInfo(BDBCNo,BDDealDate,BDDealAcount,BDDealType,BDDealComment)values(bcno,getdate(),money,支取,通过ATM取款);elseprint 取款交易失败，余额不足，请减少取款;endelseprint 取款交易失败，密码有错误;endelse-如果密码为空，则为存款insert into BankDealInfo(BDBCNo,BDDealDate,BDDealAcount,BDDealType,BDDealComment)values(bcno,getdate(),money,存入,通过ATM存款);-办理存款业务 if (ERROR0)-事务处理时异常，则回滚beginprint 交易失败;rollback transaction;endelse-没有异常，则提交commit transaction;print 交易成功，交易金额为：+ltrim(str(money);select bcexistBalance=BCExistBalance from BankCard where BCNo=bcno;print 卡号+bcno+，余额为：+cast(bcexistBalance as char(20);select * from bankcard;select * from bankdealinfo;Go exec pro_TakeMoney 1010 3576 1234 5678,2100; go exec pro_TakeMoney 1010 3576 1234 5678,100,123456; go张飞卡号存入2100元的运行结果：关羽卡号取款100元的运行结果：5.2 产生随机卡号创建存储过程产生8位随机数字，与前8位固定数字“1010 3576”连接，生成一个由16位数字组成的银行卡号，并输出。要求： 产生随机卡号的存储过程名为Pro_randCardID。 使用随机函数生成银行卡后8位的数字，随机函数的用法：rand(随机种子)将产生01的随机数，要求每次的随机种子不一样。 为了保证随机种子每次都不相同，一般采用的算法是：随机种子=当前的月份数*100000+当前的秒数*1000+当前的毫秒数产生了01的随机数后，取小数点后8位，即：0.XX