数据库技术与应用课程设计报告.doc

1、数据库技术与应用课程设计报告学 号：1467159124姓 名：张喜泉专 业：软件工程指导教师：康懿完成日期：2016-12-24目录银行 ATM 存取款机系统设计与实现 .3一、项目背景 31、项目任务 32、项目技能目标 33、需求概述 34、开发环境 35、 问题分析 36、 实训进度安排 5二、项目实训内容 5制定数据库设计与编程规范 51、 实训一：创建数据库 52、 实训二：创建触发器和随机卡号的存储过程(2 学时) 93、 实训三：生成各个表的测试数据(4 学时) 114、 实训四：模拟常规业务 165、 实训五：查询统计 226、实训六：创建、使用视图 257、实训七：存储过程

2、实现业务处理 28358、实训八：利用事务实现转账 35三：心得体会 41银行 ATM 存取款机系统设计与实现一、项目背景1、项目任务 创建数据库、创建表、创建约束 使用触发器和插入测试数据 模拟常规业务、创建视图 使用存储过程实现业务处理 利用事务实现较复杂的数据更新2、项目技能目标 使用 T-SQL 语句创建数据库、表和各种约束。 使用 T-SQL 语句编程实现常见业务。 使用触发器实现多表之间的级联更新。 使用事务和存储过程封装业务逻辑。 使用视图简化复杂的数据查询。 使用游标技术实现结果集的行集操作。3、需求概述某银行是一家民办的小型银行企业，现有十多万客户，公司将为该银行开发一套 A

3、TM 存取款机系统，对银行日常的存取款业务进行计算机管理，以便保证数据的安全性，提高工作效率。要求根据银行存取款业务需求设计出符合第三范式的数据库结构，使用 T-SQL 语言创建数据库和表，并添加表约束，进行数据的增删改查，运用逻辑结构语句、事务、视图和存储过程，按照银行的业务需求，实现各项银行日常存款、取款和转账业务。4、开发环境 数据库：SQL SERVER 2008 开发版5、问题分析该项目的 ATM 存取款机业务如下：(1) 银行存取款业务介绍银行为客户提供了各种银行存取款业务。详见表 1表 1银行存取款业务业务 描述活期 无固定存期，可随时存取，存取金额不限的一种比较灵活的存款定活两

4、便 事先不约定存期，一次性存入，一次性支取的存款通知 不约定存期，支取时需提前通知银行，约定支取日期和金额方能支取的存款整存整取 选择存款期限，整笔存入，到期提取本息的一种定期储蓄。银行提供的存款期限有 1 年、2 年和 3 年零存整取 一种事先原定金额，逐月按约定金额存入，到期支取本息的定期储蓄。银行提供的存款期限由 1 年、2 年和 3 年自助转账 在 ATM 存取款机上办理同一币种账户的银行卡之间互相划转(2) 客户信息每个客户凭个人身份证在银行可以开设多个银行卡账户，开设账户时，客户需要提供的开户数据如表 2 所示：表 2开设银行卡账户的客户信息数据 描述姓名 必须提供身份证号 唯一确

5、定客户，是由 17 位数字和 1 位数字或者字符 X 构成。联系电话 手机号码：由 11 位数字构成，且前 2 位必须是 13 或者 15、18 开头。居住地址 可以选择(3) 开户网点信息表 3开户网点信息数据 描述网点编号 编号由 6 位数字构成。网点名称 开户行中文名称网点地址 开户行所在地址信息。(4) 银行卡账户信息银行为每个账户提供一个银行卡，每个银行卡可以存入一种币种的存款，银行保存账户如表 3 所示：表 4银行卡账户信息数据 描述卡号 银行的卡号由 16 位数字组成，其中：一般前 8 位代表特俗含义，如代表某总行某支行等，假定该行要求其营业厅的卡号格式为1010 3576 XX

6、XX XXXX，后 8 位必须是随机产生且唯一，每 4 位号码后有空格。密码 由 6 位数字构成，开户时默认为“888888”币种 默认为 RMB，目前该银行尚未开设其他币种存款业务。存款类型 必须选择开户日期 客户开设银行卡账户的日期，默认为当日开户金额 客户开设银行卡账户时存入的金额，规定不得小于 1 元。是否挂失 默认为“否”网点编号 客户网点编号 客户持银行卡在 ATM 机上输入密码，经系统验证身份后办理存款、取款和转账等银行业务。银行规定，每个账户当前的存款金额不得小于 1 元。(5) 银行卡交易信息银行在为客户办理业务时，需要记录每一笔账目，账目交易信息如表 4 所示：表 1银行卡

7、交易信息数据 描述卡号 银行的卡号由 16 位数字组成交易日期 默认为当日交易金额 必须大于 0 元金额,必须为 100 元的整数倍交易类型 包括：存款、取款、转入或者转出 4 种备注 对每笔交易做必要的说明6、实训进度安排实训进度安排如下表所示：表 2实训进度安排实训内容 所需学时 提交文档实训一：制定数据库设计与编程规范4 1 份数据库设计与编程规范实训二：创建数据库 4 T-SQL 源文件实训三：创建触发器和插入测试数据4 T-SQL 源文件实训五：模拟常规业务 4 T-SQL 源文件实训六：创建和使用视图 4 T-SQL 源文件实训七：存储过程实现业务处理 6 T-SQL 源文件实训八

8、：利用事务实现转账业务 6 T-SQL 源文件二、项目实训内容实训内容由 5 个实训项目构成，建议在参考代码和实现步骤基础上进行改进，每个实训子项目的 T-SQL 语句写成 1 个 T-SQL 源文件，如 item1.sql。制定数据库设计与编程规范 参考技术文档：数据库设计规范 (1) 、 数据库设计规范 (修订) 、 数据库设计及编写规范 、 编程规范(T-SQL) 、 Transact-SQL_数据库编程命名规范 、 SQL_Server 数据库编程规范等技术文档， 制定一份 3-5 页，不少于 1500 字的数据库设计与 SQL 编程规范 ，要求至少包含各个数据库对象的命名规范、编程规

9、范及注释规范。 该实训项目的设计与编程要求遵循该实训制定的数据库设计与编程规范 。1、实训一：创建数据库阅读数据库结构相关描述表名：BankBusinessType 银行业务类型表序号 列名 数据类型长度 小数位 标识 主键 外键 允许空默认值说明1 BBTId int 4 0 是 是 否 银行业务类型编号，自动增长列2 BBTName char 20 0 否 银行业务类型名称3 BBTComment varchar 100 0 是 银行业务描述表名：BankCard 银行卡序号 列名 数据类型 长度 小数位标识 主键 外键 允许空默认值 说明1 BCNo char 19 0 是 否 卡号2

10、BCPwd char 6 0 否 888888 密码3 BCCurrency char 5 0 否 RMB 币种4 BCBBTId int 4 0 是 否 业务类型5 BCOpenDate date 3 0 否 getdate 开户日期6 BCOpenAmount money 8 4 否 开户金额7 BCRegLoss char 2 0 是 否 是否挂失8 BCBCId int 4 0 否 客户编号9 BCExistBalance money 8 4 否 账户余额10 BCBDID char 6 0 是 是 开户行编号表名：BankCustomer 客户信息序号 列名 数据类型 长度 小数位

11、标识 主键 外键 允许空默认值 说明1 BCId int 4 0 是 是 否 客户编号2 BCName char 20 0 否 客户名称3 BCICNo char 18 0 否 客户身份证号4 BCTel varchar 20 0 否 客户电话号5 BCAddr varchar 100 0 是 客户地址表名：BankDealInfo 交易信息序号 列名 数据类型 长度 小数位标识 主键 外键 允许空默认值 说明1 BDNo int 4 0 是 是 否 交易编号2 BDBCNo char 19 0 是 否 银行卡号3 BDDealDate date 3 0 否 getdate 交易日期4 BDD

12、ealAcount money 8 4 否 交易金额5 BDDealType char 10 0 否 交易类型6 BDDealComment varchar 100 0 是 描述表名：BankDesposit 开户网点信息序号 列名 数据类型 长度 小数位 标识 主键 外键 允许空默认值说明1 BDID char 6 0 是 否 网点编号2 BDName char 20 0 否 网点名称3 BDAddress char 50 0 是 网点地址使用 T-SQL 语句完成数据库、数据表和各种约束的创建，并保存为item1.sql 文件。按下述推荐步骤，在 4 学时内完成下述实训内容：(1) 创建数

13、据库（1 学时）使用 Create DataBase 语句创建“ATM 存取款机系统”数据库 BankDB，数据文件和日志文件保存在指定目录下（建议建立一个文件夹，用于存放该实训项目的所有相关 T-SQL 源文件） ，文件增长率为 15%。(2) 创建各个数据表及相关的约束（2 学时）根据数据表结构，使用 Create Table 语句创建表结构。根据银行业务，分析表中每个列相应的约束要求，为每个表添加各种约束。要求创建表时要求检测是否存在表结构，如果存在，则先删除再创建。2、实训二：创建触发器和随机卡号的存储过程(2 学时)使用 T-SQL 语句完成触发器和随机卡号存储过程的创建，并保存为i

14、tem2.sql 文件。(1) 创建级联触发器 创建 Insert 触发器在交易信息表 BankDealInfo 中创建一个 Insert 触发器，当增加一条交易信息时，修改相应银行卡的存款余额。建议使用游标，实现批量增加的级联更新。 创建Delete触发器在交易信息表创建一个 Delete 触发器，当删除一条交易信息时，修改相应银行卡的存款余额。(2) 创建产生随机卡号的存储过程 Proc_randCardID创建存储过程产生 8 位随机数字，与前 8 位固定数字“1010 3576”连接，生成一个由 16 位数字组成的银行卡号，并输出。要求： 产生随机卡号的存储过程名为 Proc_rand

15、CardID。 利用下面的代码调用存储过程进行测试declare myCardId1 char(19)exec proc_randCardId myCardId1 outputprint 产生随机卡号为+myCardId1 结果如图所示：3、实训三：生成各个表的测试数据(4 学时)使用 T-SQL 语句向每个表插入如下所示测试数据，要保证业务数据的一致性和完整性，保存为 item3.sql 文件(1) BankBusinessType 表的测试数据使用 T-SQL 向已经创建的 BankBusinessType 表插入如下数据：由于该表的数据已经给出，故直接使用向导导入。2) BankDesp

16、osit 表的测试数据使用 T-SQL 语句插入不少于 10 条开户网点信息，参考数据如下所示：BankCustomer 表的测试数据BankCustomer 表，产生 10000 条记录，如下图所示：导入数据 bankcustomerBankCard 表的测试数据使用 T-SQL 语句向 BankCard 表中插入 15000 条数据，卡号调用存储过程Proc_randCardID 随机产生，开户日期设置为当前日期近 3 年的随机某一天（使用相应的日期函数和随机函数完成） ，开户金额为 100-2,000 之间的正数（100的倍数） 。BankDealInfo 表的测试数据使用 T-SQL

17、语句向 BankDealInfo 表中插入 20 万条数据，要求交易日期设置为当前日期近 3 年的随机某一天（使用相应的日期函数和随机函数完成） ，要求交易日期晚于该卡的开户日期，交易金额为 100-2,000 之间的正数（100 的倍数） 。实训四：模拟常规业务使用 T-SQL 语句实现银行的日常业务，并保存为 item4.sql 文件。按下述推荐步骤，在 4 学时内完成下述实训内容：(1) 修改存款类型为活期的银行卡密码将存款类型为活期的银行卡密码改为身份证号后 6 位，并查询本周开户的银行卡信息，如图所示。写出更新密码和查询修改密码效果图的 T-SQL 语句。修改客户密码效果图（只显示本

18、周开户的银行卡）(2) 办理银行卡挂失将账户余额小于 0 的银行卡办理为挂失状态，并显示如下查询结果（按银行卡开户日期排序）：由于设计表数据时就限制了余额不能小于 0，所以查询为空(3) 统计某个指定客户的资金流通余额和盈利结算存入代表资金流入，支取代表资金流出（挂失的银行卡不参与计算） 。计算公式：资金流通余额=总存入金额-总支取金额假定存款利率为千分之三，贷款利率为千分之八。计算公式：盈利结算=总支取金额*0.008-总存入金额*0.003。要求创建一个存储过程 proc_staticsBanlanceAndProfit，执行该存储过程运行结果如下图所示：-执行统计银行资金流通余额和盈利结

19、算的存储过程exec proc_staticsBanlanceAndProfit 张建军;图 1统计某客户的资金流通余额和盈利结算(4) 查询某个开户网点本季度开户信息查询本季度某个开户网点的银行卡开户相关信息，如查询大连新型支行的本季度开户信息。结果如下图所示，按开户日期排序：图 2本季度某个开户网点的开户信息(5) 查询本周开户且本周单次交易金额最高的交易信息查询本周开户的银行卡中单次交易金额最高的信息。结果如下图所示：查询本季度各银行卡交易总额的信息，结果如下图所示：图 1本季度各银行卡的交易信息：卡号，交易总额查询本季度交易总金额最高的银行卡信息。图 1本季度银行卡交易额最高的银行卡信

20、息：卡号、开户日期、开户金额(6) 查询挂失客户信息查询挂失账号的客户信息，利用子查询 in 的方式，查询结果如下图所示：图 3查询挂失客户由于所有卡余额均大于等于 0，所以并没有挂失账户，此查询为空实训五：查询统计使用 T-SQL 语句实现银行的日常业务，并保存为 item5.sql 文件。按下述推荐步骤，在 4 学时内完成下述实训内容：(1) 催款提醒业务根据某种业务（如代缴电话费、代缴手机费或房贷等）的需要，每个月末，查询出各个开户网点中客户账户上余额少于 200 元的客户总数，由银行统一致电催款。查询结果如下图所示：图 4催款提醒业务查询没有开户的客户信息查询没有开户的客户信息，查询结

21、果如下图所示：图 5查询没有开户的客户信息由于在设计表数据时为每一位客户都至少开了一张卡，故不存在未开户客户(3) 统计各个开户网点营业情况显示各开户网点的本周开户数（只统计账户余额在 5000 元以上的银行卡） ，交易总金额，交易总笔数。查询结果如下图所示：图 6查询本周各开户网点的营业情况(4) 查询客户开卡数量显示开卡数量等于或者超过 5 张的客户信息。查询结果如下图所示：图 7查询开卡数量 5 张及以上的客户信息(5) 统计本月开户的银行卡支取情况显示本月开户的银行卡支取情况，查看各个银行卡的开户金额+收入总额-支出总额是否和账户余额相符。查询结果如下图所示：图 8查询本月开户的银行卡支取情况6、实训六：创建、使用视图使用 T-SQL 语句创建如下视图，并保存为 item6.sql 文件。按下述推荐步骤，在 2 学时内完成下述实训内容：为向客户提供友好的用户界面，使用 T-SQL 语句创建下面几个视图，并使用这些视图输出各表信息。(1) 输出银行客户记录视图 VW_userInfo显示的列名全为中文，显示银行卡没有挂失的客户记录视图，要求先判断该视图是否存在，若存在，则先删除。结果如下图所示：