《数据库技术与应用课程设计报告.docx》由会员分享，可在线阅读，更多相关《数据库技术与应用课程设计报告.docx（49页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、优质文本?数据库技术与应用?课程设计报告学 号：1467159124姓 名：张喜泉专 业：软件工程指导教师：康懿完成日期：2016-12-24目录?银行ATM存取款机系统设计与实现?3一、工程背景31、工程任务32、工程技能目标33、需求概述34、开发环境35、 问题分析36、 实训进度安排5二、工程实训内容5制定?数据库设计与编程标准?51、 实训一：创立数据库52、 实训二：创立触发器和随机卡号的存储过程(2学时)93、 实训三：生成各个表的测试数据(4学时)114、实训四：模拟常规业务165、实训五：查询统计226、实训六：创立、使用视图257、实训七：存储过程实现业务处理28358、实
2、训八：利用事务实现转账35三：心得体会41?银行ATM存取款机系统设计与实现?一、工程背景1、工程任务 创立数据库、创立表、创立约束 使用触发器和插入测试数据 模拟常规业务、创立视图 使用存储过程实现业务处理 利用事务实现较复杂的数据更新2、工程技能目标 使用T-SQL语句创立数据库、表和各种约束。 使用T-SQL语句编程实现常见业务。 使用触发器实现多表之间的级联更新。 使用事务和存储过程封装业务逻辑。 使用视图简化复杂的数据查询。 使用游标技术实现结果集的行集操作。3、需求概述某银行是一家民办的小型银行企业，现有十多万客户，公司将为该银行开发一套ATM存取款机系统，对银行日常的存取款业务进
3、行计算机管理，以便保证数据的平安性，提高工作效率。要求根据银行存取款业务需求设计出符合第三范式的数据库结构，使用T-SQL语言创立数据库和表，并添加表约束，进行数据的增删改查，运用逻辑结构语句、事务、视图和存储过程，按照银行的业务需求，实现各项银行日常存款、取款和转账业务。4、开发环境 数据库：SQL SERVER 2016开发版5、 问题分析该工程的ATM存取款机业务如下：(1) 银行存取款业务介绍 银行为客户提供了各种银行存取款业务。详见表1表1 银行存取款业务业务描述活期无固定存期，可随时存取，存取金额不限的一种比拟灵活的存款定活两便事先不约定存期，一次性存入，一次性支取的存款通知不约定
4、存期，支取时需提前通知银行，约定支取日期和金额方能支取的存款整存整取选择存款期限，整笔存入，到期提取本息的一种定期储蓄。银行提供的存款期限有1年、2年和3年零存整取一种事先原定金额，逐月按约定金额存入，到期支取本息的定期储蓄。银行提供的存款期限由1年、2年和3年自助转账在ATM存取款机上办理同一币种账户的银行卡之间互相划转(2) 客户信息 每个客户凭个人身份证在银行可以开设多个银行卡账户，开设账户时，客户需要提供的开户数据如表2所示：表2 开设银行卡账户的客户信息数据描述姓名必须提供身份证号唯一确定客户，是由17位数字和1位数字或者字符X构成。联系 号码：由11位数字构成，且前2位必须是13或
5、者15、18开头。居住地址可以选择(3) 开户网点信息表3 开户网点信息数据描述网点编号编号由6位数字构成。网点名称开户行中文名称网点地址开户行所在地址信息。(4) 银行卡账户信息 银行为每个账户提供一个银行卡，每个银行卡可以存入一种币种的存款，银行保存账户如表3所示：表4 银行卡账户信息数据描述卡号银行的卡号由16位数字组成，其中：一般前8位代表特俗含义，如代表某总行某支行等，假定该行要求其营业厅的卡号格式为1010 3576 XXXX XXXX，后8位必须是随机产生且唯一，每4位号码后有空格。密码由6位数字构成，开户时默认为“888888币种默认为RMB，目前该银行尚未开设其他币种存款业务
6、。存款类型必须选择开户日期客户开设银行卡账户的日期，默认为当日开户金额客户开设银行卡账户时存入的金额，规定不得小于1元。是否挂失默认为“否网点编号客户网点编号 客户持银行卡在ATM机上输入密码，经系统验证身份后办理存款、取款和转账等银行业务。银行规定，每个账户当前的存款金额不得小于1元。(5) 银行卡交易信息 银行在为客户办理业务时，需要记录每一笔账目，账目交易信息如表4所示：表1 银行卡交易信息数据描述卡号银行的卡号由16位数字组成交易日期默认为当日交易金额必须大于0元金额,必须为100元的整数倍交易类型包括：存款、取款、转入或者转出4种备注对每笔交易做必要的说明6、 实训进度安排实训进度安
7、排如下表所示：表2 实训进度安排实训内容所需学时提交文档实训一：制定数据库设计与编程标准41份数据库设计与编程标准实训二：创立数据库4T-SQL源文件实训三：创立触发器和插入测试数据4T-SQL源文件实训五：模拟常规业务4T-SQL源文件实训六：创立和使用视图4T-SQL源文件实训七：存储过程实现业务处理6T-SQL源文件实训八：利用事务实现转账业务6T-SQL源文件二、工程实训内容实训内容由5个实训工程构成，建议在参考代码和实现步骤根底上进行改良，每个实训子工程的T-SQL语句写成1个T-SQL源文件，如item1.sql。 制定?数据库设计与编程标准? 参考技术文档：?数据库设计标准 (1
8、)?、?数据库设计标准(修订)?、?数据库设计及编写标准?、?编程标准(T-SQL)?、?Transact-SQL_数据库编程命名标准?、?SQL_Server数据库编程标准?等技术文档， 制定一份3-5页，不少于1500字的?数据库设计与SQL编程标准?，要求至少包含各个数据库对象的命名标准、编程标准及注释标准。 该实训工程的设计与编程要求遵循该实训制定的?数据库设计与编程标准?。1、 实训一：创立数据库 阅读数据库结构相关描述表名：BankBusinessType银行业务类型表序号列名数据类型长度小数位标识主键外键允许空默认值说明1BBTIdint40是是否银行业务类型编号，自动增长列2B
9、BTNamechar200否银行业务类型名称3BBTCommentvarchar1000是银行业务描述表名：BankCard银行卡序号列名数据类型长度小数位标识主键外键允许空默认值说明1BCNochar190是否卡号2BCPwdchar60否888888密码3BCCurrencychar50否RMB币种4BCBBTIdint40是否业务类型5BCOpenDatedate30否getdate开户日期6BCOpenAmountmoney84否开户金额7BCRegLosschar20是否是否挂失8BCBCIdint40否客户编号9BCExistBalancemoney84否账户余额10BCBDIDc
10、har60是是开户行编号表名：BankCustomer客户信息序号列名数据类型长度小数位标识主键外键允许空默认值说明1BCIdint40是是否客户编号2BCNamechar200否客户名称3BCICNochar180否客户身份证号4BCTelvarchar200否客户 号5BCAddrvarchar1000是客户地址表名：BankDealInfo交易信息序号列名数据类型长度小数位标识主键外键允许空默认值说明1BDNoint40是是否交易编号2BDBCNochar190是否银行卡号3BDDealDatedate30否getdate交易日期4BDDealAcountmoney84否交易金额5BDD
11、ealTypechar100否交易类型6BDDealCommentvarchar1000是描述表名：BankDesposit开户网点信息序号列名数据类型长度小数位标识主键外键允许空默认值说明1BDIDchar60是否网点编号2BDNamechar200否网点名称3BDAddresschar500是网点地址使用T-SQL语句完成数据库、数据表和各种约束的创立，并保存为item1.sql文件。按下述推荐步骤，在4学时内完成下述实训内容：(1) 创立数据库1学时使用Create DataBase语句创立“ATM存取款机系统数据库BankDB，数据文件和日志文件保存在指定目录下建议建立一个文件夹，用于
12、存放该实训工程的所有相关T-SQL源文件，文件增长率为15%。(2) 创立各个数据表及相关的约束2学时根据数据表结构，使用Create Table语句创立表结构。根据银行业务，分析表中每个列相应的约束要求，为每个表添加各种约束。要求创立表时要求检测是否存在表结构，如果存在，那么先删除再创立。2、 实训二：创立触发器和随机卡号的存储过程(2学时)使用T-SQL语句完成触发器和随机卡号存储过程的创立，并保存为item2.sql文件。(1) 创立级联触发器 创立Insert触发器 在交易信息表BankDealInfo中创立一个Insert触发器，当增加一条交易信息时，修改相应银行卡的存款余额。建议使
13、用游标，实现批量增加的级联更新。 创立Delete触发器在交易信息表创立一个Delete触发器，当删除一条交易信息时，修改相应银行卡的存款余额。(2) 创立产生随机卡号的存储过程Proc_randCardID创立存储过程产生8位随机数字，与前8位固定数字“1010 3576连接，生成一个由16位数字组成的银行卡号，并输出。要求： 产生随机卡号的存储过程名为Proc_randCardID。 利用下面的代码调用存储过程进行测试declare myCardId1 char(19)exec proc_randCardId myCardId1 outputprint 产生随机卡号为+myCardId1
14、结果如下图：3、 实训三：生成各个表的测试数据(4学时)使用T-SQL语句向每个表插入如下所示测试数据，要保证业务数据的一致性和完整性，保存为item3.sql文件(1) BankBusinessType表的测试数据 使用T-SQL向已经创立的BankBusinessType表插入如下数据：由于该表的数据已经给出，故直接使用向导导入。2) BankDesposit表的测试数据 使用T-SQL语句插入不少于10条开户网点信息，参考数据如下所示：BankCustomer表的测试数据BankCustomer表，产生10000条记录，如下列图所示：导入数据bankcustomerBankCard表的测
15、试数据使用T-SQL语句向BankCard表中插入15000条数据，卡号调用存储过程Proc_randCardID随机产生，开户日期设置为当前日期近3年的随机某一天使用相应的日期函数和随机函数完成，开户金额为100-2,000之间的正数100的倍数。BankDealInfo表的测试数据使用T-SQL语句向BankDealInfo表中插入20万条数据，要求交易日期设置为当前日期近3年的随机某一天使用相应的日期函数和随机函数完成，要求交易日期晚于该卡的开户日期，交易金额为100-2,000之间的正数100的倍数。实训四：模拟常规业务使用T-SQL语句实现银行的日常业务，并保存为item4.sql文
16、件。按下述推荐步骤，在4学时内完成下述实训内容：(1) 修改存款类型为活期的银行卡密码将存款类型为活期的银行卡密码改为身份证号后6位，并查询本周开户的银行卡信息，如下图。写出更新密码和查询修改密码效果图的T-SQL语句。修改客户密码效果图只显示本周开户的银行卡(2) 办理银行卡挂失 将账户余额小于0的银行卡办理为挂失状态，并显示如下查询结果按银行卡开户日期排序：由于设计表数据时就限制了余额不能小于0，所以查询为空(3) 统计某个指定客户的资金流通余额和盈利结算存入代表资金流入，支取代表资金流出挂失的银行卡不参与计算。计算公式：资金流通余额=总存入金额-总支取金额假定存款利率为千分之三，贷款利率
17、为千分之八。计算公式：盈利结算=总支取金额*0.008-总存入金额*0.003。要求创立一个存储过程proc_staticsBanlanceAndProfit，执行该存储过程运行结果如下列图所示：-执行统计银行资金流通余额和盈利结算的存储过程exec proc_staticsBanlanceAndProfit 张建军;图1 统计某客户的资金流通余额和盈利结算(4) 查询某个开户网点本季度开户信息查询本季度某个开户网点的银行卡开户相关信息，如查询大连新型支行的本季度开户信息。结果如下列图所示，按开户日期排序：图2 本季度某个开户网点的开户信息 (5) 查询本周开户且本周单次交易金额最高的交易信息
18、查询本周开户的银行卡中单次交易金额最高的信息。结果如下列图所示： 查询本季度各银行卡交易总额的信息，结果如下列图所示：图1 本季度各银行卡的交易信息：卡号，交易总额 查询本季度交易总金额最高的银行卡信息。图1 本季度银行卡交易额最高的银行卡信息：卡号、开户日期、开户金额(6) 查询挂失客户信息查询挂失账号的客户信息，利用子查询in的方式，查询结果如下列图所示：图3 查询挂失客户由于所有卡余额均大于等于0，所以并没有挂失账户，此查询为空实训五：查询统计使用T-SQL语句实现银行的日常业务，并保存为item5.sql文件。按下述推荐步骤，在4学时内完成下述实训内容：(1) 催款提醒业务根据某种业务
19、如代缴 费、代缴 费或房贷等的需要，每个月末，查询出各个开户网点中客户账户上余额少于200元的客户总数，由银行统一致电催款。查询结果如下列图所示：图4 催款提醒业务 查询没有开户的客户信息查询没有开户的客户信息，查询结果如下列图所示：图5 查询没有开户的客户信息由于在设计表数据时为每一位客户都至少开了一张卡，故不存在未开户客户(3) 统计各个开户网点营业情况 显示各开户网点的本周开户数只统计账户余额在5000元以上的银行卡，交易总金额，交易总笔数。查询结果如下列图所示：图6 查询本周各开户网点的营业情况(4) 查询客户开卡数量 显示开卡数量等于或者超过5张的客户信息。查询结果如下列图所示：图7
20、 查询开卡数量5张及以上的客户信息(5) 统计本月开户的银行卡支取情况 显示本月开户的银行卡支取情况，查看各个银行卡的开户金额+收入总额-支出总额是否和账户余额相符。查询结果如下列图所示：图8 查询本月开户的银行卡支取情况6、实训六：创立、使用视图使用T-SQL语句创立如下视图，并保存为item6.sql文件。按下述推荐步骤，在2学时内完成下述实训内容：为向客户提供友好的用户界面，使用T-SQL语句创立下面几个视图，并使用这些视图输出各表信息。(1) 输出银行客户记录视图VW_userInfo 显示的列名全为中文，显示银行卡没有挂失的客户记录视图，要求先判断该视图是否存在，假设存在，那么先删除
21、。结果如下列图所示： 图9 输出银行客户记录其对应的银行卡状态为未挂失(2) 输出银行卡记录视图VW_CardInfo建议使用内部连接Inner Join语句，结果如下列图所示：图10 输出银行卡记录(3) 输出银行卡交易记录视图VW_TransInfo查询该视图，视图按交易日期排序，结果如下列图所示：由于要在视图中使用order by 因此使用top 图11 输出银行卡的交易记录(4) 查询本季度没有交易记录的客户信息 创立视图vw_searchCustomerNoDeal，查询本季度没有交易记录的客户信息。图12 本季度没有交易记录的客户信息vw_searchCustomerNoDeal7
22、、实训七：存储过程实现业务处理 使用T-SQL语句创立如下视图，并保存为item7.sql文件(1) 完成存款或取款业务 描述： 根据银行卡号和交易金额实现银行卡的存款和取款业务。 每一笔存款，取款业务都要计入银行交易账，并同时更新客户的存款余额。 如果是取款业务，在记账之前，要完成下面两项数据的检查验证工作，如果检查不合格，那么中断取款业务，给出提示信息后退出。 检查客户输入的密码是否正确。 账户取款金额是否大于当前存款额加1。要求： 取款或存款存储过程名为usp_takeMoney。 编写一个存储过程完成存款和取款业务，并调用存储过程取钱或者存钱进行测试。 假设是存取款过程成功，那么结果窗
23、口显示如图19的信息，包括卡号信息，以及当天的该卡号交易信息。假设是存取款不成功，那么给出错误提示信息。 结果如下列图所示 下述两图为存取款正确时的结果窗口： 图13 执行存储过程的结果窗口存取款图14 执行存储过程的输出消息 提示： 鉴于存款时客户不需要提供密码，在编写存储过程中，为输入参数“密码列设置默认值为Null。 在存储过程中使用事务，以保证数据操作的一致性。 use BankDB1 go -7-1 if exists(select * from sysobjects where id=OBJECT_ID(Nproc_TakeMoney) drop proc proc_TakeMon
24、ey go create proc proc_TakeMoney BCNo varchar(20),money money,pwd varchar(10)=null as -不返回受影响的行数 set nocount on -声明一个变量存放指定卡号的存款余额 declare existBanlance money -启动事务机制 begin tran select existBanlance=BCExistBalance from BankCard where BCNo=BCNo print(交易前，卡号+bcno+,余额为:+convert(varchar(20),existBanlanc
25、e) print(交易正进行，请稍后.) -如果输入参数pwd为空，那么为取款业务，否那么为存款业务 if(pwd is not null) -办理取款业务 begin -判断指定卡号和密码是否存在，假设存在，那么可以取款，否那么失败 if exists(select BCNo,BCPwd from BankCard where (BCNo=BCNo and BCPwd=pwd) begin if(select BCRegLoss from BankCard where BCNo=BCNo)=是) begin print 该卡已挂失,无法办理相应业务 rollback end else beg
26、in -判断取款金额是否小于等于余额，假设条件成立，那么可以取款，否那么失败 if(money=existBanlance) begin insert into BankDealInfo values(BCNo,GETDATE(),money,取款,通过存储过程) if(ERROR0) begin print 交易失败 rollback tran end else begin commit tran print 交易成功,交易金额为:+convert(varchar(10),money)+,余额为：+convert(varchar(10),(existBanlance-money) end e
27、nd else begin print 余额缺乏,取款失败 rollback tran end end end else begin print 取款失败,卡号或用户名错误 rollback tran end end else begin if(select BCRegLoss from BankCard where BCNo=BCNo)=是) begin print 该卡已挂失,无法办理相应业务 rollback end else begin -办理存款业务 insert into BankDealInfo values(BCNo,GETDATE(),money,存款,通过存储过程) -判断
28、事物处理是否有异常，没有那么提交事务，否那么回滚 if(ERROR0) begin print 交易失败 rollback tran end else begin commit tran print 交易成功,交易金额为:+convert(varchar(10),money) -判断交易类型,显示余额 print 卡号+BCNo+,余额为：+convert(varchar(10),existBanlance+money) end endendgo-存款exec proc_TakeMoney1010 3576 0001 8539,100-取款exec proc_TakeMoney1010 357
29、6 0001 8539,100,888888-假设用户操作已挂失的卡exec proc_TakeMoney1010 3576 0000 7359,100go2.完成开户业务描述： 利用存储过程为客户开设2个银行卡账户，开户时需要提供客户的信息有：开户名、身份证号、 号码、开户金额、存款类型和地址、开户网点。客户的信息见表所示： 为成功开户的客户提供银行卡，且银行卡号唯一。要求： 开户的存储过程名为usp_openAccount。 使用下面的数据执行该存储过程，进行测试：调用此存储过程开户。表3 两位客户的开户信息姓名身份证联系 开户金额存款类型地址开户网点周公旦1502031975100743
30、39130884488221200定活两便内蒙古包头包头乐园支行姬昌150203197610174339158345678091100活期内蒙古包头包钢三中支行 结果如下列图所示：图15 执行开户存储过程的结果图16 测试开户存储过程的输出信息 use BankDB1 go if exists(select * from sysobjects where name=usp_openAccount) drop proc usp_openAccount go -创立开户存储过程usp_openAccount， -输入参数分别是开户名、身份证号、 号码、开户金额、存款类型和地址 create pro
31、c usp_openAccount BCName varchar(12),BDBCNo varchar(20),BDTel varchar(12), OpenAccount money,BCtype varchar(6),address varchar(100) as declare BCBBTId int,BCNO varchar(19),BCId int,BCOpenDate date if (exists(select * from BankBusinessType WHERE BBTName=BCtype) begin set nocount on begin tran set BCI
32、d=(select count(*) from BankCustomer)+1 select BCBBTId=BBTId from BankBusinessType where BBTName=BCtype exec randCardId BCNo output while(exists(select * from BankCard where BCNo=BCNO) exec randCardId BCNo output insert into BankCustomer values(BCName,BDBCNo,BDTel,address) insert into BankCard value
33、s(BCNO,BCBBTId,GETDATE(),OpenAccount,否,BCId,OpenAccount,100001) -判断事物操作是否有异常 if(ERROR0) begin print 尊敬的用户,开户失败，所有操作均撤销 rollback end else begin commit tran set BCOpenDate=(select BCOpenDate from BankCard where BCNo=BCNO) print 尊敬的客户,开户成功,系统为你产生的随机卡号是+BCNo print 开户日期:+convert(varchar(12),BCOpenDate)+开
34、户金额:+convert(varchar(10),OpenAccount) select * from BankCustomer where BCId=BCId select * from BankCard where BCNo=BCNO end end else print 尊敬的客户，未能成功开户，存款类型不正确，请重新输入 go exec usp_openAccount李鑫,150202196210030491,13904721843,1000,活期,内蒙古科技大学支行 测试失败情况： exec usp_openAccount李鑫,150202196210030491,139047218
35、43,1000,123,内蒙古科技大学支行(4) 分页显示查询交易数据根据指定的页数和每页的记录数分页显示交易数据。要求： 存储过程名称是usp_PagingDisplay. 测试数据是输出第2页，每页10行交易数据，结果如下列图所示：图17 每页10行方式输出第2页交易数据 8、实训八：利用事务实现转账使用存储过程和事务实现转账业务，操作步骤如下所示：(1) 从某一个账户支取一定金额的存款。(2) 将支取金额存入到另一个指定的账户中。(3) 分别打印此笔业务的转出账单和转入账单。 要求：(1) 存储过程名称是usp_transfer。(2) 要求使用事务机制实现转账业务。(3) 结果如下图：
36、 use BankDB1 go if exists(select * from sysobjects where name=usp_transfer) drop proc usp_transfer go -创立存储过程，传递转出账号、密码，转入账号以及转账金额 create proc usp_transfer outzh varchar(20),inzh varchar(20),pwd varchar(8),dealAcount money as -不返回受影响的行数 set nocount on -转账之后账户余额 declare outbalance money,inbalance mon
37、ey -转出账号的姓名，货币类型，存款类型，开户日期 declare outname varchar(8),outCurr char(3),outType varchar(30),outOpenDate date -转入账号的姓名，货币类型，存款类型，开户日期 declare inname varchar(8),inCurr char(3),inType varchar(30),inOpenDate date -判断转出账户是否存在 if(not exists(select BCNo from BankCard where BCNo=outzh) or (pwd(select BCPwd fr
38、om BankCard where BCNo=outzh) begin print转出账户不存在或者密码错误，转账失败 return end else begin print开始转账，请稍后. -判断转出账户余额是否大于等于转账金额 if(exists(select * from BankCard where BCNo=outzh and BCExistBalance=dealAcount) begin -判断转入账户是否存在 if(not exists(select BCNo from BankCard where BCNo=inzh) begin print转入账户不存在，转账失败 ret
39、urn end else begin begin tran print交易正在进行，请稍后. -增加一条转出交易记录 insert into BankDealInfo values(outzh,GETDATE(),dealAcount,转出,通过存储过程) -增加一条转入交易记录 insert into BankDealInfo values(inzh,GETDATE(),dealAcount,转入,通过存储过程) -取得转账后两个账户的余额 select outbalance=(select BCExistBalance from BankCard where BCNo=outzh) select inbalance=(select BCExistBalance from BankCard where BCNo=inzh)