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

1、银行ATM存取款机系统设计与实现数据库技术与开发项目实训设计报告 项目名称：银行ATM存取款机系统设计与实现姓 名：范涛 学 号：21专 业：软件工程12-2 指导教师：刘立新 完成日期：2014-11-20银行ATM存取款机系统设计与实现第1章：项目背景1、项目任务创建数据库、创建表、创建约束使用触发器和插入测试数据模拟常规业务、创建视图使用存储过程实现业务处理利用事务实现较复杂的数据更新2、项目技能目标 使用PowerDesigner完成数据库概念模型和数据库物理模型设计。 使用T-SQL语句创建数据库、表和各种约束。 使用T-SQL语句编程实现常见业务。 使用触发器实现多表之间的级联更新

2、。 使用事务和存储过程封装业务逻辑。 使用视图简化复杂的数据查询。 使用游标技术实现结果集的行集操作。3、需求概述某银行是一家民办的小型银行企业，现有十多万客户，公司将为该银行开发一套ATM存取款机系统，对银行日常的存取款业务进行计算机管理，以便保证数据的安全性，提高工作效率。要求根据银行存取款业务需求设计出符合第三范式的数据库结构，使用T-SQL语言创建数据库和表，并添加表约束，进行数据的增删改查，运用逻辑结构语句、事务、视图和存储过程，按照银行的业务需求，实现各项银行日常存款、取款和转账业务。4、开发环境 数据库：SQL SERVER 2008开发版 数据库建模工具：PowerDesign

3、er15第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_

4、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 (ri

5、ght(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_curren

6、cy 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 101

7、0 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_bcopendat

8、e 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

9、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 bank

10、cardadd 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.创建

11、级联触发器（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_dealacou

12、nt,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(l

13、trim(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_DeldealInfogo

14、create 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_BankDeali

15、nfo 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,

16、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 curs

17、or_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 wher

18、e 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 bankbusines

19、stype values(活期,无固定存期，可随时存取，存取金额不限的一种比较灵活的存款),(定活两便,事先不约定存期，一次性存入，一次性支取的存款),(通知,不约定存期，支取时需提前通知银行，约定支取日期和金额方能支取的存款),(整存整取1年,整笔存入，到期提取本息),(整存整取2年,整笔存入，到期提取本息),(整存整取3年,整笔存入，到期提取本息),(零存整取1年,事先原定金额，逐月按约定金额存入，到期支付本息),(零存整取2年,事先原定金额，逐月按约定金额存入，到期支付本息),(零存整取3年,事先原定金额，逐月按约定金额存入，到期支付本息),(自助转账,银行ATM机上办理银行卡之间互相划

20、转);select * from bankbusinesstype 结果如下图所示：图12.BankCustomer表的测试数据insert into bankcustomervalues(小明,包头市昆都仑区包钢五中),(小黑,54,包头昆区阿尔丁大街);select * from bankcustomer 结果如下图所示：图23.BankCard表的测试数据declare date datetimeselect date=cast(DATEADD(DD,-(rand()*30),GETDATE() as datetime)-前30天的日期insert into BankCardvalues

21、(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

22、(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

23、),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)in

24、sert 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 BankDealInfo

25、values(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 12

26、34 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 * fro

27、m 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_RegLos

28、s 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_

29、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,银行流通余额：+rt