资源简介

(共46张PPT)
第十三章 项目案例
银行业务系统
训练的技能点
■使用T-SQL语句建数据库和表结构
■使用T-SQL语句编程实现用户业务
■使用事务和存储过程封装业务逻辑
■使用视图简化复杂的数据查询
任务描述
◎系统概述
某银行需要开发业务系统，实现如下功能：
◇开户（到银行填写开户申请单，卡号自动生成）
◇取款
◇存款
◇查询余额
◇转账
◎系统角色
◇数据库设计员、数据库程序员、数据库维护员
问题分析：整体开发思路
◎系统开发步骤
■明确需求
■设计数据库
◇绘制E-R图
◇绘制数据库模型图
◇使用三大范式规范数据库结构设计
■编码实现需求
■测试
问题分析：数据库设计与创建
◎银行业务处理
■每个用户可以开设多个银行卡账户
■每个银行卡账户可以有多笔交易
◎设计符合第三范式的数据库结构
■根据银行业务绘制E-R图
■绘制数据库模型图
■使用三大范式规范数据库结构
◎实现“银行业务系统”的相关业务
难点分析
◎数据库设计步骤
◎E-R图要素
■实体
■关系
◇一对一关系
◇一对多关系
◇多对多关系
◎数据库设计的三大规范
■第一范式
■第二范式
■第三范式
难点分析
◎银行业务系统
■卡号构成
◇假设某行由16位数字构成，每4位数字一组，中间用空格隔开
●如：6227 2666 1001 1202
◇前8位数字固定的，表示发卡银行识别号（BIN）
●如：前8位是6227 2666
◇后8位随机产生
■客户用身份证开设账户
◇开户金额不得小于1元
◇存款余额不得小于1元
难点分析
◎ 银行取款业务
■存款
◇记录交易发生额，增加账户余额
■取款
◇记录交易发生额，减少账户余额
■转账
◇关系到二个账户，总账平衡
◇转出账户：记录交易转入金额，减少该账户的余额
◇转入账户：记录交易转出金额（同转入金额），增加该账户余额
难点分析
◎ 银行业务系统
■修改密码
◇变更客户密码
■挂失
◇客户填写特殊业务申请单（挂失申请书），停止该账户发生存款支取交易
◇在客户信息中设置列记录客户当前账户状态
开发计划
◎用例1：数据库设计
◎用例2：建库、建表、建约束
◎用例3：插入测试数据
◎用例4：模拟常规业务
◎用例5：利用视图实现较复杂的数据查询
◎用例6：使用存储过程实现业务处理
◎用例7：利用事务实现较复杂的数据更新
用例1：数据库设计
◎需求说明
■根据银行ATM取款机业务处理流程设计数据库
◇绘制E-R图
◇将E-R图转为关系
◇使用第三范式规范数据库表结构
用例1：数据库设计
◎思路分析
■按照数据库设计步骤确定实体及实体关系
■绘制E-R图
■将E-R图转换为表
■使用第三范式规范数据库设计
◎难点提示
■寻找、确定银行业务系统的实体及实体间关系
◇根据业务确定实体
◇确定实体之间关系
■使用三大范式规范数据库设计
◇表内的每一个值都只能被表达一次
◇表内的每一行都应该被唯一的标识（主键）
◇表内不应该存储依赖于其他键的非键信息
◎绘制E-R图
用例1：数据库设计
◎客户表（userInfo）结构
用例1：数据库设计
◎银行卡表（cardInfo）结构
用例1：数据库设计
◎交易表（tradeInfo）结构
用例1：数据库设计
◎存款类型表（deposit）结构
用例1：数据库设计
用例2：建库、建表、建约束
◎需求说明
■使用SQL语言创建数据库BankDB
◇数据库保存在D:\bank目录下，文件增长率为15%
■使用SQL语言创建表
◇存款类型表
◇客户表
◇银行卡表
◇交易表
■使用SQL语言在每个表上添加约束
◇主键约束、外键约束、CHECK约束、默认约束、非空约束
◎难点提示
■CREATE语句与ALTER语句
用例2：建库、建表、建约束
◎参考代码
--建库语句：
CREATE DATABASE bankDB
ON
(
NAME='…',
FILENAME='…',
SIZE=…,
FILEGROWTH=…
)
LOG ON
(
… …
)
--建表语句：
CREATE TABLE 表名
(
customerID INT IDENTITY(1,1),
customerName CHAR(8)
NOT NULL,
… …
)
文件增长率
数据文件
日志文件
自动编号，从1开始
非空/必填
◎加约束、建关系部分
--建约束语句：
ALTER TABLE cardInfo
ADD CONSTRAINT PK_cardID PRIMARY KEY(cardID),
CONSTRAINT CK_cardID CHECK(cardID LIKE ‘6227 2666 [0-9]…'),
CONSTRAINT DF_curType DEFAULT('RMB') FOR curType
CONSTRAINT FK_customerID FOREIGN KEY(customerID)
REFERENCES userInfo(customerID),
CONSTRAINT UQ_PID UNIQUE(PID),
…..
主键约束
检查约束
外键约束（建关系）
唯一约束
默认约束
用例2：建库、建表、建约束
用例3：插入测试数据
◎需求说明
■使用SQL语言向每个表中插入至少5条记录
◎思路分析
■先增加主表的记录，后增加子表的记录
◎难点提示
■插入数据表的先后顺序
■插入客户记录时，先要查询是否已存在这个客户，如果已存在，就不要再插入。
◎张三和李四开户：
SET NOCOUNT ON --不显示受影响的条数信息
INSERT INTO userInfo(customerName,PID,telephone,address )
VALUES('张三','130101601221321 ','010-12345678 ','北京海淀')
INSERT INTO cardInfo(cardID,savingType,openMoney
,balance,customerID) VALUES(6227 2666 1234 5678 ', '活期’,1000,1000,1)
INSERT INTO userInfo(customerName,PID,telephone)
VALUES('李四','213456197506133178 ','0478-24322123 ')
INSERT INTO cardInfo(cardID,savingType,openMoney,balance,
customerID) VALUES(6227 2666 5678 1234 ','定期一年 ',1,1,2)
SELECT * FROM userInfo
SELECT * FROM cardInfo
GO
用例3：插入测试数据
◎张三的卡号取款900元，李四的卡号存款5000元
/*--------------交易信息表插入交易记录--------------------------*/
INSERT INTO transInfo(transType,cardID,transMoney)
VALUES('支取', 6227 2666 1234 5678 ',900)
/*-------------更新银行卡信息表中的现有余额-------------------*/
UPDATE cardInfo SET balance=balance-900 WHERE cardID='6227 2666 1234 5678 '
/*--------------交易信息表插入交易记录--------------------------*/
INSERT INTO transInfo(transType,cardID,transMoney)
VALUES('存入', 6227 2666 5678 1234 ',5000)
/*-------------更新银行卡信息表中的现有余额-------------------*/
UPDATE cardInfo SET balance=balance+5000 WHERE cardID='6227 2666 5678 1234‘
GO
用例3：插入测试数据
用例3：插入测试数据
◎功能测试
■使用查询语句测试插入的数据是否正确
◎代码审查
■审查插入测试数据的SQL代码
互相测试完成的程序功能
测试出的缺陷记录在“常见问题列表中”
单元测试
用例4：模拟常规业务
◎需求说明
■修改客户密码
■办理银行卡挂失
■统计银行资金流通余额和盈利结算
◇银行资金流通余额=总存入金额-总支取金额
◇盈利结算=总支取金额 * 0.008 – 总存入金额 * 0.003
■查询本周开户的卡号，显示该卡相关信息
■查询本月交易金额最高的卡号
■查询挂失账号的客户信息
■催款提醒业务
用例4：模拟常规业务
◎思路分析
■修改客户密码
■办理银行卡挂失
◇使用UPDATE语句实现密码变更和卡挂失
UPDATE … WHERE …
■统计银行资金流通余额和盈利结算
◇使用聚合函数SUM()和数据类型转换函数CONVERT()
SELECT @inMoney=SUM() FROM … WHERE …
■查询本周开户的卡号，显示该卡相关信息
◇日期函数DATEDIFF()和DATEPART()
用例4：模拟常规业务
◎思路分析
■查询本月交易金额最高的卡号
◇使用子查询和DISTINCT关键字去掉重复的卡号
SELECT … FROM transInfo WHERE transMoney = (
SELECT … FROM … )
■查询挂失账号的客户信息
◇使用子查询IN 或内联接查询INNER JOIN
SELECT … FROM userInfo WHERE customerID IN (
SELECT … FROM … )
■催款提醒业务
◇使用子查询IN 或内联接查询INNER JOIN
SELECT … FROM userInfo INNER JOIN …
用例4：模拟常规业务
◎功能测试
■检查点
◇正确修改客户密码
◇正确办理银行卡挂失，查询获得挂失账号的客户信息
◇正确查询本月交易金额最高的卡号
◇正确统计银行资金流通余额和盈利结算
◇正确查询本周开户的卡号，显示该卡相关信息
◇正确催款提醒业务
互相测试完成的程序功能
测试出的缺陷记录在“常见问题列表中”
单元测试
用例5：利用视图实现数据查询
◎需求说明
■为客户提供以下3个视图供其查询该客户数据
◇客户基本信息：vw_userInfo
◇银行卡信息：vw_cardInfo
◇银行卡交易信息：vw_tradeInfo
■提供友好界面，要求各列名称为中文描述
■调用创建的视图获得查询结果
◎思路分析
■创建视图
■调用视图
CREATE VIEW …
AS
…
GO
SELECT … FROM …
用例5：利用视图实现数据查询
◎功能测试
■检查点
◇正确创建视图
◇正确使用视图获得查询数据
互相测试完成的程序功能
测试出的缺陷记录在“常见问题列表中”
单元测试
用例6：用存储过程实现业务处理
◎需求说明
■产生随机卡号
■完成开户业务
■完成取款或存款业务
■根据卡号打印对账单
■查询、统计指定时间段内没有发生交易的账户信息
■统计指定时间段内某地区客户在银行卡交易量和交易额。如果不指定地区，则查询所有客户的交易量和交易额
用例6：用存储过程实现业务处理
◎思路分析
■完成取款或存款业务
◇创建存储过程
CREATE PROCEDURE proc_takeMoney
@card char(19)，
@type char(4) ,
@inputPass char(6)=' '
AS
…--SQL语句
IF(…) …
…
GO
存储过程的参数
有默认值的参数，放在最后
◇调用存储过程
EXEC proc_takeMoney ‘6227 2666 5678 1234 ’, ‘支取’,’123456’
检测支取金额是否大于当前存款金额加1
用例6：用存储过程实现业务处理
◎思路分析
■产生随机卡号
◇创建存储过程
CREATE PROCEDURE proc_randCardID
@randCardID char(19) OUTPUT
…
SELECT @r = RAND (随机种子 )
…
SET @randCardID =…SUBSTRING(@tempStr,3,4)…..
GO
◇调用存储过程
DECLARE @mycardID char(19)
EXECUTE proc_randCardID @mycardID OUTPUT
OUTPUT表示传出的参数
产生0-1的随机数
例如：0. 0823 5678 1234
截取小数点后8位作为卡号的后八位数
卡号(4位一组，用空格隔开)：6227 2666 5678 1234
字符串截取函数
调用带output输出参数的存储过程
用例6：用存储过程实现业务处理
◎思路分析
■完成开户业务
◇创建存储过程
CREATE PROCEDURE proc_openAccount
…
AS
…--SQL语句
WHILE EXISTS( … )
EXEC PROCEDURE proc_randCardID …
…
GO
◇调用存储过程
EXEC proc_openAccount '王五 ','110101731011612 ',
'2222-63598978 ',1,'定期'
调用产生随机卡号存储过程，获得唯一的卡号
存储过程的参数
包括：开户名、身份证号、电话号码、开户金额、存款类型和地址
用例6：用存储过程实现业务处理
◎思路分析
■根据卡号打印对账单
◇创建存储过程
CREATE PROCEDURE proc_CheckSheet
@cardID varchar(19)
AS
…
SELECT … FROM … WHERE cardID = @cardID
…
PRINT …
…
GO
◇调用存储过程
EXEC proc_CheckSheet '6227 2666 1234 5678 '
根据输入的卡号查找相关交易记录
用例6：用存储过程实现业务处理
◎思路分析
■统计指定时间段内某地区客户在银行卡交易量和交易额
■如果不指定地区，则查询所有客户的交易量和交易额
◇创建存储过程
CREATE PROCEDURE proc_getTradeInfo
…
AS
…
SELECT …COUNT(…), … SUM(…) FROM … WHERE …
…
GO
◇调用存储过程
EXEC proc_getTradeInfo …
调用聚合函数
用例6：用存储过程实现业务处理
◎功能测试
■检查点1
◇正确创建存储过程
■检查点2
◇正确调用存储过程完成相关的数据操作
互相测试完成的程序功能
测试出的缺陷记录在“常见问题列表中”
单元测试
◎需求说明
■使用事务和存储过程实现转账业务
◎思路分析
■指定的客户取款
■指定的客户存钱
■打印对账单
用例7：实现较复杂的数据更新
■创建存储过程
■调用存储过程
CREATE PROCEDURE proc_tradefer
@card1 char(19),
@pwd char(6),
@card2 char(19),
@outmoney money
AS
BEGIN TRAN
…
EXEC proc_takeMoney @card, @outmoney, '支取', @pwd
… --转入处理
IF @error>0
… ROLLBACK TRAN
ELSE
… COMMIT TRAN
EXEC proc_CheckSheet @card1
…
GO
用例7：实现较复杂的数据更新
从卡号@card1转出300元
SELECT @card1=cardID FROM cardInfo …
WHERE customerName='李四'
…
EXEC proc_tradefer @card1, '123123', @card2, 2000
向卡号@card2转入同等金额
打印对账单
用例7：实现较复杂的数据更新
◎功能测试
■检查点1
◇正确创建、调用存储过程实现数据库数据的更新操作
■检查点2
◇正确使用事务以保证数据的完整性
互相测试完成的程序功能
测试出的缺陷记录在“常见问题列表中”
单元测试
用例8：建数据库访问账户设置权限
◎需求分析
■使用SQL语句添加系统维护帐号sysAdmin，并授权
◎思路分析
■使用系统存储过程创建数据库登录名
■使用系统存储过程创建用户名
■为数据库用户设置访问权限
■为登录用户创建查询账户的视图
◎添加系统维护帐号appAdmin，并授权
--1.添加SQL登录帐号
CREATE LOGIN appAdmin WITH PASSWORD='bank1234'
GO
--2.创建数据库用户
USE bankDB
GO
CREATE USER appAdmin FOR LOGIN appAdmin
GO
--3. 给数据库用户appAdmin授权(增删改查的权限)
GRANT select,insert,update,delete,select ON transInfo
TO appAdmin
GRANT select,insert,update,delete,select ON userInfo
TO appAdmin
GRANT select,insert,update,delete,select ON cardInfo
TO appAdmin
GO
用例8：建数据库访问账户设置权限
获得登录用户名称
用例8：建数据库访问账户设置权限
◎功能测试
■检查点
◇正确创建登录名、用户
◇使用创建的登录名能够正确登录SQL Server
互相测试完成的程序功能
测试出的缺陷记录在“常见问题列表中”
单元测试
集成测试
系统集成后，重新测试系统所有程序功能
测试出的缺陷记录在“常见问题列表中”
修正缺陷后返测，并更新“常见问题列表中”
集成测试
项目总结
讲解要点：
完成情况、技能总结、经验分享、项目收获
表达要求：
清晰流畅、有条理、重点突出
项目展示
技能总结
◎巩固的知识点：
■SQL语句：建库、建表、加约束、建关系
■常用的约束类型：主键、外键、非空、默认值、检查约束
■高级查询：内部连接、子查询、索引、视图
■存储过程：带参数的存储过程、带返回值的存储过程
■事务：显示事务的应用

展开更多......

收起↑