数据库课程设计银行ATM存取款机系统设计与实现.docx

数据库课程设计银行ATM存取款机系统设计与实现.docx

《数据库课程设计银行ATM存取款机系统设计与实现.docx》由会员分享，可在线阅读，更多相关《数据库课程设计银行ATM存取款机系统设计与实现.docx（61页珍藏版）》请在冰豆网上搜索。

数据库课程设计银行ATM存取款机系统设计与实现

给学弟学妹的福利,数据库这个难度还算可以吧.也不是很大.大家多多思考一下.代码仅供参考.最好不要复制.手敲一遍也是好的.

《数据库技术与开发》

项目实训设计报告

项目名称：

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

姓名：

谭海兵

专业：

软件工程

指导教师：

余金林

完成日期：

2015-11-22

一、项目背景

1、项目任务

Ø使用PowerDesigner完成数据库设计

Ø创建数据库、创建表、创建约束

Ø使用触发器和插入测试数据

Ø模拟常规业务、创建视图

Ø使用存储过程实现业务处理

Ø利用事务实现较复杂的数据更新

2、项目技能目标

Ø使用PowerDesigner完成数据库概念模型和数据库物理模型设计。

Ø使用T-SQL语句创建数据库、表和各种约束。

Ø使用T-SQL语句编程实现常见业务。

Ø使用触发器实现多表之间的级联更新。

Ø使用事务和存储过程封装业务逻辑。

Ø使用视图简化复杂的数据查询。

Ø使用游标技术实现结果集的行集操作。

3、需求概述

某银行是一家民办的小型银行企业，现有十多万客户，公司将为该银行开发一套ATM存取款机系统，对银行日常的存取款业务进行计算机管理，以便保证数据的安全性，提高工作效率。

要求根据银行存取款业务需求设计出符合第三范式的数据库结构，使用T-SQL语言创建数据库和表，并添加表约束，进行数据的增删改查，运用逻辑结构语句、事务、视图和存储过程，按照银行的业务需求，实现各项银行日常存款、取款和转账业务。

4、开发环境

Ø数据库：

SQLSERVER2008开发版

Ø数据库建模工具：

PowerDesigner15

5、问题分析

该项目的ATM存取款机业务如下：

（1）银行存取款业务介绍

银行为客户提供了各种银行存取款业务。

详见表1

表1．银行存取款业务

业务

描述

活期

无固定存期，可随时存取，存取金额不限的一种比较灵活的存款

定活两便

事先不约定存期，一次性存入，一次性支取的存款

通知

不约定存期，支取时需提前通知银行，约定支取日期和金额方能支取的存款

整存整取

选择存款期限，整笔存入，到期提取本息的一种定期储蓄。

银行提供的存款期限有1年、2年和3年

零存整取

一种事先原定金额，逐月按约定金额存入，到期支取本息的定期储蓄。

银行提供的存款期限由1年、2年和3年

自助转账

在ATM存取款机上办理同一币种账户的银行卡之间互相划转

（2）客户信息

每个客户凭个人身份证在银行可以开设多个银行卡账户，开设账户时，客户需要提供的开户数据如表2所示：

表2．开设银行卡账户的客户信息

数据

描述

姓名

必须提供

身份证号

唯一确定客户，如果是二代身份证，则是由17位数字和1位数字或者字符X构成。

如果是一代身份证，则身份证号全部是15位数字。

联系电话

分为座机号码和手机号码：

座机号码由数字和“-”构成，由以下两种格式：

XXX-XXXXXXXX或者XXXX-XXXXXXX。

手机号码由11位数字构成，且前2位必须是13或者15开头。

居住地址

可以选择

（3）银行卡账户信息

银行为每个账户提供一个银行卡，每个银行卡可以存入一种币种的存款，银行保存账户如表3所示：

表3．银行卡账户信息

数据

描述

卡号

银行的卡号由16位数字组成，其中：

一般前8位代表特俗含义，如代表某总行某支行等，假定该行要求其营业厅的卡号格式为10103576XXXXXXXX，后8位必须是随机产生且唯一，每4位号码后有空格。

密码

由6位数字构成，开户时默认为“888888”

币种

默认为RMB，目前该银行尚未开设其他币种存款业务。

存款类型

必须选择

开户日期

客户开设银行卡账户的日期，默认为当日

开户金额

客户开设银行卡账户时存入的金额，规定不得小于1元。

是否挂失

默认为“否”

Ø客户持银行卡在ATM机上输入密码，经系统验证身份后办理存款、取款和转账等银行业务。

银行规定，每个账户当前的存款金额不得小于1元。

（4）银行卡交易信息

银行在为客户办理业务时，需要记录每一笔账目，账目交易信息如表4所示：

表4．银行卡交易信息

数据

描述

卡号

银行的卡号由16位数字组成

交易日期

默认为当日

交易金额

必须大于0元

交易类型

包括：

存入和支取两种

备注

对每笔交易做必要的说明

（5）银行卡手工账户和存取款单据信息

该银行要求这套软件实现银行客户的开户、存款、取款、转账和余额查询等业务，使得银行储蓄业务方便、快捷，同事保证银行业务数据的安全性。

为使开发人员尽快了解银行业务，该银行提供了银行卡手工账户和存取款单据的样本数据，以供项目开发时参考，参加表5和表6。

表5．银行卡手工账户样本数据

表6．存取款单据样本数据

二、项目实训内容

1、实训一：

制定《数据库设计与编程规范》

长度规范

凡是需要命名的对象其标识符均控制在30个字符以内，也即：

SQLServer中的表名、字段名、函数名、存储过程、触发器、视图等名字长度要尽量不超过30个字符长度。

构成规范

数据库各种名称必须以字母开头，但严禁以系统关键字开头，名称只能含有字母、数字以及下划线“_”三类字符，“_”用于间隔名称中的各语义字段；不要使用系统保留字作表名。

大小写规范

构成SQLServer数据库中的各种名称（表名、字段名、过程名、视图名等所有命名符的首字母需要使用大写，也即每个命名单词的首字母大写，其它字符小写。

但对于简写或缩写的短单词，如ID、UI可以全为大写。

主键规范

除临时表、流水表以及日志表外，其它表都要建立主键。

主键最好设计成单一主键，尽量不要用复合主键，尽量使用没有业务语义的字段作为主键，

如采用按顺序自增的数值型字段为主键

注释规范

每个表，每个字段都要有注释，说明其含义，对于冗余字段还要特别说明其

维护方法，外键字段要说明参照于那个表，另外对于存储过程、视图、触发器、函数等代码均要增加注释，以保持代码的可读性以及后续的可维护性。

行大小规范

SQLServer的1页的大小是8K，因此一行的数据要控制到8K之内，如果超过8K要想办法将表进行拆分成多个子表。

数据保留策略

大表由于数据量较大，往往是系统的性能瓶颈所在，因此对于大表的设计好考虑到今后的数据转移、分片、Partition等，并且对大表以及其相关表

的数据保留时间也要有一个提前规划，以免今后出现性能问题束手无策。

必备字段要求

每个大表都应该添加以下几个有用的字段，分别为创建日期、修改日期、操

作人以及版本标记，创建这些字段的目的是为今后的数据转移以及分片或分

区做准备，同时也有利于今后的数据审计等。

注意事项

基于大表的任何操作都要慎重思考，通常情况下要禁止在大表上创建触发器，禁止在大表上做频繁的批量更新或删除动作，禁止在生产时间对大表

做DDL操作，禁止在大表上做全表扫描（FullScan）等。

临时表功能

SQLServer分为全局临时表和局部临时表，临时表在很多场合下能带来意想不到的效果，尤其是需要中转的数据记录集采用临时表能提升系统性能。

临时表支持索引、约束、排序等实体表具有的功能。

存储特点

临时表的数据存储在tempdb数据库中，因此过于频繁的创建临时表会增加tempdb库的负荷，尤其是数据量超过10W条记录的临时表更是会影响tempdb库的性能，由此在某些情况下可用CTE替代临时表的使用。

注意事项

临时表执行完毕后，要及时的手工Drop掉，及时释放资源，减轻系统的Loading，另外特别注意的是要尽量禁止使用全局临时表，全局适合多个session间的数据交互，但往往会引起数据的串值。

命名规范

尽量采用有意义的字段名，使描述尽可能清楚，如采用缩写，尽量使用通用的缩写语言，如addr代表address，避免出现只有自己理解的缩写。

日期字段

时效性数据应包括“创建时间/修改时间”字段，时间标记对查找数据、清理数据、排序合并特别有用，另外要根据具体业务考量时间字段的类型，如在Smalldatetime和Datetime类型进行选择。

注意保留词

对于字段的命名，要确保字段名没有和保留词、数据库系统或者常用方法冲突，比如master、CROSS、address、substring、len、sysobjects等词就不适合用来做字段的独立命名。

数值规范

数值型的主要有INT、BIGINT、TINYINT、SMALLINT、FLOAT、NUMERIC、MONEY等类型，要根据实际应用选择合适的类型，如字段的数据为小于255的整形数字，那么就要选择TINYINT；如字段数据小于32767的整形数字，那么就要选择SMALLINT，以此类推。

文本规范

文本类型主要有CHAR、VARCHAR、TEXT等类型，要根据实际应用选择合适的类型，如字段文本长度固定为8位，那么就要用CHAR（8）；如文本长度最大为100，并且大小是非定长的，那么就要设为VARCHAR（100）。

并且以上文本若为汉字，那么就要设为NVARCHAR和NCHAR。

字段命名技巧

字段命名要统一规范，同一个字段在不同的表中命名要一致，另外字段名一般都要带上业务模块的前缀，如订单（Order）价格字段命名为Or_Price,部门（Department）编号为Dep_No。

命名规范没有那个是最合理的，只有定义好最适合自己的统一规范即可。

外键建立索引

外键不建立索引，将有可能导致两个严重的问题。

1.更新相关的表产生死锁。

2.两表关联查询时性能低下。

因此通常情况下都必须要求外键建立索引。

联合索引规范

当数据对某表经常要多条件查询时，可能就需要建立联合索引，联合索引的第一个引导列字段非常重要，引导列字段通常要能过滤掉大部分数据，这样方能减少IO的读写，提高性能。

非引导列字段在引导列的查询数据基础上继续过滤数据，以提高查询速度。

联合索引对更新会产生一定的性能影响。

禁用多余索引

数据库索引能提高查询速度，但会增加写操作的开销，因此对一些几月或者从没有使用过的索引要删除掉，以免增大数据库的负荷。

重复索引问题

一般情况下，尽量避免重复索引的出现，重复索引很容易引起死锁，减低数据库的并发访问。

重复索引也会造成索引的维护困难。

索引数量限制

数据库索引主要用来解决读的性能瓶颈，但是会增加写操作的负荷，因此过多的索引会造成更新速度变慢，甚至会引起不要的死锁。

一般情况下表中的索引不要超过5个。

注意事项

建立索引前，要充分了解表的使用及数据特性，要了解表的查询条件和查询频率，甚至随着业务的变化而引起表数据使用状况的变化，带之而来的是索引也需要相应调整。

命名规范

存储过程命名遵守统一的规范，对于业务存储过程要以p或proc开头，接着加上”_”，然后再加上模块名称简写和具体的业务词，最后加上执行类型。

数据库的存储过程名严禁以sp开头，sp通常表示系统数据库存储过程名的前缀。

触发器以Tr开头，接着加上”_”，然后加上表名。

书写规范

关键字建议用大写，同样的代码书写格式保持一致，SQL脚本采用缩进风格，风格一致，缩进格式一致，使用空格。

INSERT规范

通常情况下，INSERT语句要给出具体的字段列表，避免采用“INSERTINTOTB_1VALUES（‘值1’,’值2’,’值3’）”用法，此种用法往往会由于表结构变迁而导致语句不可执行

避免隐式转换

书写时，必须明确表结构及表中各个字段的数据类型，特别是查询条件中的字段，要避免由于类型的不同导致数据类型转换的发生，从而减少因为

数据类型转换产生的系统开销。

NULL陷阱

NULL不要直接用来进行运算符的比较，也不要和其它值进行连接操作，判断一个值是否为NULL值时，要采用ISNULL来进行比较。

LIKE规范

LIKE子句应尽量前段匹配，要避免通配符在前段，以免导致全索引扫描的发生。

参数化代码

SQL中常量的直接使用，会导致SQL语句频繁的硬解析，进而严重影响数据库的性能，基于这些原因，代码中要尽量采用参数绑定，以减少语句硬解析的次数，从而提高语句执行性能。

动态SQL

动态SQL是在运行时才进行解析的，相当于是硬解析，因此会损失一些系统性能，但是动态SQL写法灵活，因此在某些情况下需要以性能换灵活，但对于用静态语句就能简单实现的SQL，就不要用动态SQL语句。

嵌套层级限制

嵌套查询尽量少使用，尤其是对于超过3层的嵌套查询更要慎用，对于复杂的嵌套语句要根据业务进行拆分为多条SQL来实现，或者通过临时表来取代一部分嵌套层级。

排序规范

SQL语句中要尽量减少排序，对查询结果进行的排序会大大降低系统的性能，并且会增加tempdb数据库的负荷，因此在开发时间宽松情况下，要尽量将排序动作放到应用程序层去完成。

代码注释要求

注释是指程序中会被编译器忽略掉的部分，目的是描述代码的用途及更新时间，合理的添加注释可以使得程序结构清晰，可以使代码更好理解，便于系统后续的维护。

一般情况下，注释要不少于代码的十分之一。

静态SQL

SQL语句要尽可能采用静态SQL，静态SQL第一次执行时会将编译器解析的结果存储在缓存中，下次执行该静态SQL时会直接从缓存中获取其执行计划，相当于是软解析，因此采用静态SQL可以减少语句的解析时间，提升了数据库的性能。

最小事务原则

数据库事务用来保持数据的一致性，但是对于一个执行时间较长的大事务，会造成数据库锁的增加，当锁越积越多的时候就会从行锁升级到页锁，从业锁升级到表锁，从而严重影响数据库的性能。

因此，在能满足数据一致性的前提下，要尽量将非一致性要求的语句代码从事务中移除，以便提升数据库的并发访问。

顺序提交

顺序提交是一个好的代码编写习惯，顺序提交可以减少死锁的发生，并且还能增加代码的可读性及可维护性。

2、实训二：

数据库分析设计与建模

（1）分析银行ATM存取款系统实体

图1．银行存取款系统ER图

（2）规范数据库结构设计

第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。

如在银行客户表BankCustomer中，不能将客户信息都放在一列中显示，也不能将其中的两列或多列在一列中显示；客户信息表的每一行只表示一个员工的信息，一个客户的信息在表中只出现一次。

简而言之，第一范式就是无重复的列。

第二范式（2NF）要求实体的属性完全依赖于主关键字。

如银行交易信息表BankDealInfo中，不能把卡号设为主键因为一个卡号可以发生多条交易记录。

要确定唯一的一条信息，必须重新定义一个和其它属性无关的交易编号。

这样要查询一条交易信息。

就可以用交易编号。

简而言之，第二范式就是属性完全依赖于主键。

第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

如银行卡表BankCard中，有了用户Id后。

不能还添加用户姓名等相关的用户信息。

否则就会有大量的数据冗余。

简而言之，第三范式就是属性不依赖于其它非主属性。

（3）绘制CDM模型、生成PDM模型

图2．银行存取款系统数据概念模型CDM

图3．银行存取款系统数据物理模型PDM

3、实训三：

创建数据库

（1）创建数据库

使用CreateDataBase语句创建“ATM存取款机系统”数据库BankDB，数据文件和日志文件保存在指定目录下文件增长率为15%。

--创建BankDB数据库，数据库文件和日志文件均保存在

--文件夹d:

\sql下

--文件增长率均为%，数据文件起始大小为MB，日志文件起始大小为MB

CREATEDATABASEBankDB

ONPRIMARY

（

NAME=N'BankDB',

FILENAME=N'd:

\sql\BankDB.mdf',

SIZE=5MB,

FILEGROWTH=15%

）

LOGON

（

NAME=N'BankDB_log',

FILENAME=N'd:

\sql\BankDB_log.ldf',

SIZE=2MB,

FILEGROWTH=15%

）

（2）创建各个数据表及相关的约束

根据银行业务，分析表中每个列相应的约束要求，为每个表添加各种约束。

要求创建表时要求检测是否存在表结构，如果存在，则先删除再创建。

USEBankDB

GO

--判断银行业务类型是否存在,若存在则删除

--sysobjects系统对象表。

保存当前数据库的对象。

--OBJECT_ID（）根据对象名称返回该对象的id

IFEXISTS（SELECT*FROMsysobjectsWHEREid=OBJECT_ID（N'BankBusinessType'））

DROPTABLEBankBusinessType

--创建银行业务类型表

CREATETABLEBankBusinessType

（

--银行业务类型编号

BBTIdINTIDENTITY（1,1）PRIMARYKEY,

--银行业务类型名称

BBTNameCHAR（20）NOTNULL,

--银行业务描述

BBTCommentVARCHAR（100）

）

GO

--判断银行卡客户是否存在,若存在则删除

IFEXISTS（SELECT*FROMsysobjectsWHEREid=OBJECT_ID（N'BankCustomer'））

DROPTABLEBankCustomer

--创建银行客户信息表

CREATETABLEBankCustomer

（

--客户编号

BCIdINTIDENTITY（1,1）PRIMARYKEY,

--客户姓名

BCNameCHAR（20）NOTNULL,

--客户身份证,定义身份证号前位必须是数字，后位可以是数字或者X。

BCICNoCHAR（18）NOTNULLCHECK（LEFT（BCICNo,17）LIKE'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

AND（RIGHT（BCICNo,1）LIKE'[0-9]'ORRIGHT（BCICNo,1）LIKE'X'））,

--客户联系电话,必须是固定电话号码或者手机号

BCTelVARCHAR（20）NOTNULLCHECK（BCTellike'[0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'or

BCTellike'[0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'or

BCTellike'1[358][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'）,

--客户居住地址

BCAddrvarchar（100）

）

GO

--判断银行卡是否存在，若存在。

则删除BankCard

IFEXISTS（SELECT*FROMsysobjectsWHEREid=OBJECT_ID（N'BankCard'））

DROPTABLEBankCard

--建立银行卡信息

CREATETABLEBankCard

（

--银行卡号

BCNoCHAR（19）PRIMARYKEYCHECK（BCNoLIKE'10103576[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'）,

--密码

BCPwdCHAR（6）NOTNULLDEFAULT（'888888'）,

--币种

BCCurrencyCHAR（5）NOTNULLDEFAULT（'RMB'）,

--存款类型

BCBBTIdINTNOTNULL,

--开户日期

BCOpenDateDATENOTNULLDEFAULT（GETDATE（））,

--开户金额

BCOpenAmountMONEYNOTNULLCHECK（BCOpenAmount>=1）,

--是否挂失

BCRegLossCHAR

（2）DEFAULT（'否'）,

--客户编号

BCBCIdINTNOTNULL,

--余额

BCExistBalanceMONEYNOTNULL

）

GO

--判断交易信息BankDealInfo是否存在,若存在则删除

IFEXISTS（SELECT*FROMsysobjectsWHEREid=OBJECT_ID（N'BankDealInfo'））

DROPTABLEBankDealInfo

--创建交易信息表

CREATETABLEBankDealInfo

（

--交易编号

BDNoINTIDENTITY（1,1）PRIMARYKEY,

--卡号

BDBCNoCHAR（19）NOTNULL,

--交易日期

BDDealDateDATENOTNULL,

--交易金额

BDDealAcountMONEYNOTNULL,

--交易类型

BDDealTypeCHAR（10）NOTNULLCHECK（BDDealType='存入'ORBDDealType='支取'）,

--交易备注

BDDealCommentvarchar（100）

）

（3）添加外键约束和生成数据库关系图

添加子表外键约束及生成数据库关系图

ALTERTABLEBankCard

ADDCONSTRAINTFK_BC_BBTFOREIGNKEY（BCBBTId）REFERENCESBankBusinessType（BBTId）

ALTERTABLEBankCard

ADDCONSTRAINTFK_BC_BCFOREIGNKEY（BCBCId）REFERENCESBankCustomer（BCId）

ALTERTABLEBankDealInfo

ADDCONSTRAINTFK_BDI_BCFOREIGNKEY（BDBCNo）REFERENCESBankCard（BCNo）

在SQLSERVER里自动生成数据库关系图，如下图所示：

图4．BankDb数据库关系图

4、实训四：

创建触发器和插入测试数据

（1）创建级联触发器

Ø创建Insert触发器

在交易信息表创建一个Insert触发器，当增加一条交易信息时，修改相应银行卡的存款余额。

--在交易信息表插入触发器.使用游标当新增一个交易信息.修改银行卡的存款余额

--检查触发器是否存在.存在则删除

IF（OBJECT_ID（'tr_InsertDealInfo','TR'）ISNOTnull）

DROPTRIGGERtr_InsertDealInfo

GO

--创建Insert触发器

CREATETRIGGERtr_InsertDealInfoONBankDea