《银行ATM存取款机系统设计与实现》课程设计报告(共97页).doc

精选优质文档-倾情为你奉上数据库技术与开发课程设计报告项目名称：银行ATM存取款机系统设计与实现姓 名：贺英杰专 业：软件工程指导教师：余金玲完成日期：2015-11-21目录实训一：制定数据库设计与编程规范一、 总则1、目的为了规范开发过程中SQL语句的编写工作，提高SQL 语句共享，避免语义或语句相同的SQL语句重复解析，优化SQL查询，培养良好的代码风格。2、基本原则以大小写敏感编写SQL语句。 尽量使用Unicode 数据类型。 优先使用连接代替子查询或嵌套查询。 尽量使用参数化SQL查询代替语句拼接SQL查询。 禁止使用拼音+英语的方式来命名SQL对象或变量。 尽量使用存储过程代替SQL语句。3、基本设计规范(1) 长度规范凡是需要命名的对象其标识符均控制在30个字符以内，也即：SQL Server中的表名、字段名、函数名、存储过程、触发器、视图等名字长度要尽量不超过30个字符长度。 (2) 构成规范 数据库各种名称必须以字母开头，但严禁以系统关键字开头，名称只能含有字母、数字以及下划线“_”三类字符，“_”用于间隔名称中的各语义字段；不要使用系统保留字作表名。(3) 大小写规范 构成SQL Server数据库中的各种名称(表名、字段名、过程名、视图名等所有命名符的首字母需要使用大写，也即每个命名单词的首字母大写，其它字符小写。但对于简写或缩写的短单词，如ID、 UI可以全为大写。(4) 主键规范除临时表、流水表以及日志表外，其它表都要建立主键。主键最好设计成单一主键，尽量不要用复合主键，尽量使用没有业务语义的字段作为主键，如采用按顺序自增的数值型字段为主键。(5) 注释规范 每个表，每个字段都要有注释，说明其含义，对于冗余字段还要特别说明其维护方法，外键字段要说明参照于那个表，另外对于存储过程、视图、触发器、函数等代码均要增加注释，以保持代码的可读性以及后续的可维护性。(6) 行大小规范SQL Server的1页的大小是8K，因此一行的数据要控制到8K之内，如果超过8K要想办法将表进行拆分成多个子表。二、 数据库设计规范1、 表设计规范(1) 第一范式 1NF是最基本的范式，用以确保保持每列原子性，如数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。(2) 第二范式 2NF需要确保表中的每一列都和主键相关，而不能只与主键的某一部分相关。也即在一个表中只能保存一种数据，不可以把多种数据保存在一张表中。(3) 第三范式 3NF需要确保表中的每一列数据都和主键直接相关，而不能间接相关。比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系，而不可以在订单表中添加关于客户其它信息(比如姓名、所属公司等)的字段。2、反规范化表设计(1) 原因 数据库规范化设计目前占数据库设计主导地位，规范化设计达到了数据存储空间最佳利用和控制数据冗余，但是也带来了查询性能的降低，因此在适当的条件下，对数据库进行反规范化设计也是很有必要的。(2) 反规范化设计的目的反规范化设计通过增加数据冗余，数据分片等策略来改进原有规范设计，以达到改善数据库性能的目的，也即以空间换性能。需要注意的是：数据库设计没有一层不变的规范，也没有百分百完美的模式，因此要结合实际的应用需要统一考量设计，未来的发展方向将会是多种设计规范的混搭模式，即数据库中即有规范化设计又有反规范化设计。(3) 遵循原则全面了解系统，确信对系统的逻辑设计有全面的了解，当改变数据库时，会对其它模块带来哪些影响。采取索引、数据存储等技术提升性能，分析系统的数据量，对于数据量不大，响应时间不高的尽量通过数据库本身技术来提升性能数据完整性维护，使用反规范化带来的最大问题是数据完整性问题，在程序设计中要充分考虑到数据库事务的处理，增删改查保证数据库操作一致性；完整性约束还可以通过批处理及触发器实现(尽量少用触发器)。(4) 反规范化表设计方法两大方法增加冗余列 关系表中的冗余 层次关系中的冗余数据分片 水平分片 垂直分片 混合分片3、大表设计规范(1) 数据保留策略大表由于数据量较大，往往是系统的性能瓶颈所在，因此对于大表的设计好考虑到今后的数据转移、分片、 Partition等，并且对大表以及其相关表的数据保留时间也要有一个提前规划，以免今后出现性能问题束手无策。(2) 必备字段要求每个大表都应该添加以下几个有用的字段，分别为创建日期、修改日期、操作人以及版本标记，创建这些字段的目的是为今后的数据转移以及分片或分区做准备，同时也有利于今后的数据审计等。(3) 注意事项基于大表的任何操作都要慎重思考，通常情况下要禁止在大表上创建触发器，禁止在大表上做频繁的批量更新或删除动作，禁止在生产时间对大表做DDL操作，禁止在大表上做全表扫描(Full Scan)等。4、临时表设计规范(1) 临时表功能SQL Server分为全局临时表和局部临时表，临时表在很多场合下能带来意想不到的效果，尤其是需要中转的数据记录集采用临时表能提升系统性能。临时表支持索引、约束、排序等实体表具有的功能。(2) 存储特点临时表的数据存储在tempdb数据库中，因此过于频繁的创建临时表会增加tempdb库的负荷，尤其是数据量超过10W条记录的临时表更是会影响tempdb库的性能，由此在某些情况下可用CTE替代临时表的使用。(3) 注意事项临时表执行完毕后，要及时的手工Drop掉，及时释放资源，减轻系统的Loading，另外特别注意的是要尽量禁止使用全局临时表，全局适合多个session间的数据交互，但往往会引起数据的串值。5、分区表设计规范(1) 功能定义目前SQL Server 2005/2008/2012的企业版均支持表分区，表分区的目的是将一个大表的数据从物理上分散存储到不同的物理磁盘或文件组，从而提高该表的读写性能。对于小于1000W条记录的表无需做表分区。(2) 分区规范 当表的数据量超过1000W条记录时，要考虑做表分区。 SQL Server目前仅支持范围分区，因此可以以时间或数值型代码作为分区字段，分区的总体原则是将数据尽可能平均分布到各个分区文件组，以减少热点访问数据。(3) 注意规范正确的使用分区表能带来数据库性能的提升，但运用不当也可能会适得其反，导致系统速度变慢，甚至引起系统宕机。因此对于分区表，在应用程序代码中的增删改查必须要加上分区字段条件，否则系统会遍历所有分区文件组。6、列（字段）设计规范(1) 命名规范尽量采用有意义的字段名，使描述尽可能清楚，如采用缩写，尽量使用通用的缩写语言，如addr代表address，避免出现只有自己理解的缩写。(2) 日期字段时效性数据应包括“创建时间/修改时间”字段，时间标记对查找数据、清理数据、排序合并特别有用，另外要根据具体业务考量时间字段的类型，如在Smalldatetime和Datetime类型进行选择。(3) 注意保留词 对于字段的命名，要确保字段名没有和保留词、数据库系统或者常用方法冲突，比如master、 CROSS、 address、 substring、 len、 sysobjects等词就不适合用来做字段的独立命名。(4) 数值规范数值型的主要有INT、 BIGINT、 TINYINT、 SMALLINT、 FLOAT、NUMERIC、 MONEY等类型，要根据实际应用选择合适的类型，如字段的数据为小于255的整形数字，那么就要选择TINYINT；如字段数据小于32767的整形数字，那么就要选择SMALLINT，以此类推。(5) 文本规范文本类型主要有CHAR、 VARCHAR、 TEXT等类型，要根据实际应用选择合适的类型，如字段文本长度固定为8位，那么就要用CHAR(8)；如文本长度最大为100，并且大小是非定长的，那么就要设为VARCHAR(100)。并且以上文本若为汉字，那么就要设为NVARCHAR和NCHAR。(6) 字段命名技巧字段命名要统一规范，同一个字段在不同的表中命名要一致，另外字段名一般都要带上业务模块的前缀，如订单(Order)价格字段命名为Or_Price,部门(Department)编号为Dep_No。命名规范没有那个是最合理的，只有定义好最适合自己的统一规范即可。7、索引设计规范(1) 外键建立索引 外键不建立索引，将有可能导致两个严重的问题：1).更新相关的表产生死锁；2).两表关联查询时性能低下。因此通常情况下都必须要求外键建立索引。(2) 联合索引规范当数据对某表经常要多条件查询时，可能就需要建立联合索引，联合索引的第一个引导列字段非常重要，引导列字段通常要能过滤掉大部分数据，这样方能减少IO的读写，提高性能。非引导列字段在引导列的查询数据基础上继续过滤数据，以提高查询速度。联合索引对更新会产生一定的性能影响。(3) 禁用多余索引数据库索引能提高查询速度，但会增加写操作的开销，因此对一些几月或者从没有使用过的索引要删除掉，以免增大数据库的负荷。(4) 重复索引问题一般情况下，尽量避免重复索引的出现，重复索引很容易引起死锁，减低数据库的并发访问。重复索引也会造成索引的维护困难。(5) 索引数量限制数据库索引主要用来解决读的性能瓶颈，但是会增加写操作的负荷，因此过多的索引会造成更新速度变慢，甚至会引起不要的死锁。一般情况下表中的索引不要超过5个。(6) 注意事项建立索引前，要充分了解表的使用及数据特性，要了解表的查询条件和查询频率，甚至随着业务的变化而引起表数据使用状况的变化，带之而来的是索引也需要相应调整。8、命名规范(1) 存储过程命名遵守统一的规范，对于业务存储过程要以p或proc开头，接着加上”_”，然后再加上模块名称简写和具体的业务词，最后加上执行类型。例如：proc_DepDetailSelect： 获取部门详细信息的存储过程；proc_DepDetailInsert： 增加部门详细信息的存储过程；proc_DepDetailUpdate：修改部门详细信息的存储过程；proc_DepDetailDelete： 删除部门详细信息的存储过程;(2) 公共存储过程以p或proc开头，接着加上“_”，然后加上Common的缩写词Comm，最后加上执行类型。如：proc_CommMaxIDSelect： 获取最大ID的存储过程；(3) 数据库的存储过程命名严禁以sp开头，sp通常表示系统数据库存储过程名的前缀。(4) 表命名遵守统一的规范，对于业务表要以模块名称简写词开头，接着加上“_”，最后加上具体的业务名词。例如：Dep_Detail： 部门明细表；Dep_Master： 部门主表；PO_Scrap： 订单报废表；(5) 触发器以Tri开头，接着加上”_”，然后加上表名，最后加上“_”+执行动作。例如：Tri_PO_Scrap_Insert： 基于表PO_Scrap的插入触发器；Tri_PO_Scrap_Update： 基于表PO_Scrap的更新触发器；Tri_PO_Scrap_Delete： 基于表PO_Scrap的删除触发器；(6) 视图我们约定，字段由前缀和实际名字组成，中间用下划线连接。前缀：使用小写字母vi，表示视图。因此，合法的视图名类似如下。vi_User vi_UserInfo(7) 索引普通索引以IDX开头，接着加上”_”，然后加上表名，最后加上“_”+字段名。例如表PO_Scrap以Update_Time字段建立的索引命名为：IDX_PO_Scrap_Update_Time： 索引字段为Update_Time；IDX_PO_Scrap_WH_Code_Buyer_： 索引字段分别为WH_Code和Buyer；主键索引以PK开头，接着加上”_”，然后加上表名，最后加上“_”+字段名。例如表PO_Scrap以ID字段建立的主键命名为：PK_PO_Scrap_ID： 主键字段为ID；唯一索引以UX开头，接着加上”_”，然后加上表名，最后加上“_”+字段名。例如表PO_Scrap以Serial_No和Barcode两个字段建立的唯一索引命名为：UX_PO_Scrap_Serial_No_Barcode： 以Serial_No和Barcode字段建立的唯一索引。外键以fk_为前缀，如:FK_PO_Scrap_Gen_Code;函数以fn_为前缀，如:fn_Data_Split三、 数据库编程规范(1) 书写规范关键字建议用大写，同样的代码书写格式保持一致，SQL脚本采用缩进风格，风格一致，缩进格式一致，使用空格。如：SELECT Col_1,Col_2FROM TB_1WHERE COL_2>GETDATE()(2) 避免SELECT *避免使用SELECT *语句，应给出字段列表，如：SELECT Col_1,Col_2,Col_3FROM TB_1(3) INSERT规范通常情况下，INSERT语句要给出具体的字段列表，避免采用” INSERTINTO TB_1 VALUES(值1,值2,值3) “用法，此种用法往往会由于表结构变迁而导致语句不可执行。(4) 避免隐式转换书写时，必须明确表结构及表中各个字段的数据类型，特别是查询条件中的字段，要避免由于类型的不同导致数据类型转换的发生，从而减少因为数据类型转换产生的系统开销。(5) NULL陷阱NULL不要直接用来进行运算符的比较，也不要和其它值进行连接操作，判断一个值是否为NULL值时，要采用IS NULL来进行比较。(6) LIKE规范 LIKE子句应尽量前段匹配，要避免通配符在前段，以免导致全索引扫描的发生。(7) 参数化代码SQL中常量的直接使用，会导致SQL语句频繁的硬解析，进而严重影响数据库的性能，基于这些原因，代码中要尽量采用参数绑定，以减少语句硬解析的次数，从而提高语句执行性能。(8) 动态SQL动态SQL是在运行时才进行解析的，相当于是硬解析，因此会损失一些系统性能，但是动态SQL写法灵活，因此在某些情况下需要以性能换灵活，但对于用静态语句就能简单实现的SQL，就不要用动态SQL语句。(9) 嵌套层级限制 嵌套查询尽量少使用，尤其是对于超过3层的嵌套查询更要慎用，对于复杂的嵌套语句要根据业务进行拆分为多条SQL来实现，或者通过临时表来取代一部分嵌套层级。(10) 排序规范SQL语句中要尽量减少排序，对查询结果进行的排序会大大降低系统的性能，并且会增加tempdb数据库的负荷，因此在开发时间宽松情况下，要尽量将排序动作放到应用程序层去完成。(11) HINT使用HINT是数据库的一种特别用法，通过它能影响语句的执行，改变SQL的执行计划，但HINT用法有时会导致错误的结果，因此正式运行环境一般不建议使用。(12) 代码注释要求 注释是指程序中会被编译器忽略掉的部分，目的是描述代码的用途及更新时间，合理的添加注释可以使得程序结构清晰，可以使代码更好理解，便于系统后续的维护。一般情况下，注释要不少于代码的十分之一。(13) 静态SQLSQL语句要尽可能采用静态SQL，静态SQL第一次执行时会将编译器解析的结果存储在缓存中，下次执行该静态SQL时会直接从缓存中获取其执行计划，相当于是软解析，因此采用静态SQL可以减少语句的解析时间，提升了数据库的性能。(14) 最小事务原则数据库事务用来保持数据的一致性，但是对于一个执行时间较长的大事务，会造成数据库锁的增加，当锁越积越多的时候就会从行锁升级到页锁，从业锁升级到表锁，从而严重影响数据库的性能。因此，在能满足数据一致性的前提下，要尽量将非一致性要求的语句代码从事务中移除，以便提升数据库的并发访问。(15) 顺序提交 顺序提交是一个好的代码编写习惯，顺序提交可以减少死锁的发生，并且还能增加代码的可读性及可维护性。实训二：数据库分析设计与建模一、 分析银行ATM存取款系统实体图1 银行ATM全局ER图二、 规范数据库结构设计1. 第一范式1NF是最基本的范式，用以确保保持每列原子性，如数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。如在银行客户表BankCustomer中，不能将客户信息都放在一列中显示，也不能将其中的两列或多列在一列中显示；客户信息表的每一行只表示一个员工的信息，一个员工的信息在表中只出现一次。简而言之，第一范式就是无重复的列。2. 第二范式2NF需要确保表中的每一列都和主键相关，而不能只与主键的某一部分相关。也即在一个表中只能保存一种数据，不可以把多种数据保存在一张表中。如在银行业务类型表BankBusinessType中，银行业务类型名称BBTName，银行业务描述BBTComment，银行业务类型表中的每一个字段都与主键相关。3. 第三范式3NF需要确保表中的每一列数据都和主键直接相关，而不能间接相关。比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系，而不可以在订单表中添加关于客户其它信息(比如姓名、所属公司等)的字段。如在银行业务类型表BankBusinessType中，银行业务类型名称BBTName，银行业务描述BBTComment，银行业务类型表中的每一个字段都与主键直接相关。三、 绘制CDM模型、生成PDM模型和生成T-SQL语句图2 银行存取款系统数据概念模型CDM图3 银行存取款系统数据物理模型PDM/*=*/* DBMS name: Sybase SQL Anywhere 11 */* Created on: 2015/11/23 22:47:47 */*=*/if exists(select 1 from sys.sysforeignkey where role='FK_BANKCARD_A_BANKBUSI') then alter table BankCard delete foreign key FK_BANKCARD_A_BANKBUSIend if;if exists(select 1 from sys.sysforeignkey where role='FK_BANKCARD_JJ_BANKCUST') then alter table BankCard delete foreign key FK_BANKCARD_JJ_BANKCUSTend if;if exists(select 1 from sys.sysforeignkey where role='FK_BANKDEAL_W_BANKCARD') then alter table BankDealInfo delete foreign key FK_BANKDEAL_W_BANKCARDend if;if exists( select 1 from sys.sysindex i, sys.systable t where i.table_id=t.table_id and i.index_name='BankBusinessType_PK' and t.table_name='BankBusinessType') then drop index BankBusinessType.BankBusinessType_PKend if;if exists( select 1 from sys.systable where table_name='BankBusinessType' and table_type in ('BASE', 'GBL TEMP') then drop table BankBusinessTypeend if;if exists( select 1 from sys.sysindex i, sys.systable t where i.table_id=t.table_id and i.index_name='a_FK' and t.table_name='BankCard') then drop index BankCard.a_FKend if;if exists( select 1 from sys.sysindex i, sys.systable t where i.table_id=t.table_id and i.index_name='jj_FK' and t.table_name='BankCard') then drop index BankCard.jj_FKend if;if exists( select 1 from sys.sysindex i, sys.systable t where i.table_id=t.table_id and i.index_name='BankCard_PK' and t.table_name='BankCard') then drop index BankCard.BankCard_PKend if;if exists( select 1 from sys.systable where table_name='BankCard' and table_type in ('BASE', 'GBL TEMP') then drop table BankCardend if;if exists( select 1 from sys.sysindex i, sys.systable t where i.table_id=t.table_id and i.index_name='BankCustomer_PK' and t.table_name='BankCustomer') then drop index BankCustomer.BankCustomer_PKend if;if exists( select 1 from sys.systable where table_name='BankCustomer' and table_type in ('BASE', 'GBL TEMP') then drop table BankCustomerend if;if exists( select 1 from sys.sysindex i, sys.systable t where i.table_id=t.table_id and i.index_name='w_FK' and t.table_name='BankDealInfo') then drop index BankDealInfo.w_FKend if;if exists( select 1 from sys.sysindex i, sys.systable t where i.table_id=t.table_id and i.index_name='BankDealInfo_PK' and t.table_name='BankDealInfo') then drop index BankDealInfo.BankDealInfo_PKend if;if exists( select 1 from sys.systable where table_name='BankDealInfo' and table_type in ('BASE', 'GBL TEMP') then drop table BankDealInfoend if;/*=*/* Table: BankBusinessType */*=*/create table BankBusinessType ( BBTId integer not null, BBTName char(20) not null, BBTComment varchar(100) null, constraint PK_BANKBUSINESSTYPE primary key (BBTId);/*=*/* Index: BankBusinessType_PK */*=*/create unique index BankBusinessType_PK on BankBusinessType (BBTId ASC);/*=*/* Table: BankCard */*=*/create table BankCard ( BCNo char(19) not null, BBTId integer null, BCId integer null, BCPwd char(6) not null, BCCurrency char(5) not null, BCOpenDate date not null, BCOpenAmount numeric(8,2) not null, BCRegLoss char(2) not null, BCExistBalance numeric(8,2) null, constraint PK_BANKCARD primary key (BCNo);/*=*/* Index: BankCard_PK */*=*/create unique index BankCard_PK on BankCard (BCNo ASC);/*=*/* Index: jj_FK */*=*/create index jj_FK on BankCard (BCId ASC);/*=*/* Index: a_FK */*=*/create index a_FK on BankCard (BBTId ASC);/*=*/* Table: BankCustomer */*=*/create table BankCustomer ( BCId integer not null, BCName char(20) null, BCICNo char(18) null, BCTel varchar(20) null, BCAddr varchar(100) null, constraint PK_BANKCUSTOMER primary key (BCId);/*=*/* Index: BankCustomer_PK */*=*/create unique index BankCustomer_PK on BankCustomer (BCId ASC);/*=*/* Table: BankDealInfo */*=*/create table BankDealInfo ( BDNo integer not null, BCNo char(19) null, BDDealDate date null, BDDealAcount numeric(8,2) null, BDDealType char(10) null, BDDealComment varchar(100) null, constraint PK_BANKDEALINFO primary key (BDNo);/*=*/* Index: BankDealInfo_PK */*=