数据库课程设计作业
ATM 系统（数据库实现部分）设计报告
一、基本表的建立与完整性约束
右图展示了经由逻辑结
构设计讨论得到的带有
具体的关系模式的细化
ER 图。
参照此图，建立名为 atm 的数据库，在库内建立 6 个基本表，各个表之间通过外
键联系：
1.1 客户信息表
CREATE TABLE `客户信息` (
`客户名` TINYTEXT NOT NULL COLLATE 'utf8mb4_0900_ai_ci',
`身份证号` VARCHAR(20) NOT NULL COLLATE 'utf8mb4_0900_ai_ci',
`客户状态` INT(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`身份证号`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
客户信息表的核心包含客户名和身份证号
以身份证号作为主键；
客户名为 TINYTEXT 类型，能够兼容较长的姓名。
客户状态原先是设计用于记录客户信用相关信息的，但是更多的设计没有继
续，就默认置 0，本项目中没有用到；
客户信息表独立于银行卡密表，这使得表的扩展性得到保证，未来若有增设客户
信息字段(如地址，联系电话)的需要将会比较方便。
1.2 银行卡密表
CREATE TABLE `银行卡密` (
`卡号` BIGINT(19) NOT NULL AUTO_INCREMENT,
`密码` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_0900_ai_ci',
`身份证号` VARCHAR(20) NOT NULL COLLATE 'utf8mb4_0900_ai_ci',
PRIMARY KEY (`卡号`) USING BTREE,
INDEX `FK_银行卡密_客户信息` (`身份证号`) USING BTREE,
CONSTRAINT `FK_银行卡密_客户信息` FOREIGN KEY (`身份证号`) REFERENCES `atm`.`客
户信息` (`身份证号`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8mb4_0900_ai_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1036
;
在项目设计上一名客户最多可持有 3 张银行卡，银行卡密基本表记录了相关信
息：
该表以卡号为主键，卡号以自增 bigint 类型为默认值，确保了卡号不重复，
实现了卡号的自动生成方法。
该表的密码虽然在实际前端要求用户输入六位数字密码，但是这个密码的传
输存储具有一个加解密的过程，为了防止出错就设置为 char 类型了。
身份证号作为与客户信息联系的外键。
1.3 临时会话表
CREATE TABLE `临时会话` (
`会话号` BIGINT(19) NOT NULL,
`卡号` BIGINT(19) NOT NULL,
`会话过期时间` TIMESTAMP NOT NULL,
`会话建立时间` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`会话号`) USING BTREE,
INDEX `FK_session_users` (`卡号`) USING BTREE,
CONSTRAINT `FK_临时会话_银行卡密` FOREIGN KEY (`卡号`) REFERENCES `atm`.`银行卡
密` (`卡号`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COMMENT='会话记录表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
临时会话表用于建立 ATM 系统端登录后的会话与登录卡号的联系。
其中以会话号为主键；
卡号为映射当前所操作的银行卡信息，作为外键与银行卡密表相联系；
会话过期时间和建立时间将作为临时会话信息定时清理的依据。
1.4 卡内账户表
CREATE TABLE `卡内账户` (
`账户号` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8mb4_0900_ai_ci',
`卡号` BIGINT(19) NOT NULL DEFAULT '0',
`账户类型` VARCHAR(2) NOT NULL COLLATE 'utf8mb4_0900_ai_ci',
PRIMARY KEY (`账户号`) USING BTREE,
INDEX `卡号` (`卡号`) USING BTREE,
CONSTRAINT `FK_卡内账户_银行卡密` FOREIGN KEY (`卡号`) REFERENCES `atm`.`银行卡
密` (`卡号`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8mb4_0900_ai_ci'
ENGINE=InnoDB
;
卡内账户表以账户号作为主键，该账户号由开户时的调用过程决定。而不选
择自增序列，主要是希望开户过程生成与对应卡号相关联的前后缀或相关编
码规则作为账户号，方便查验和关联。
卡号作为外键构建了单张银行卡与卡内账户的联系，
账户类型在本设计中主要包含“活期”，“定期”和“信用”三类，另外 ATM
系统账户设有一个“系统”账户类型。
1.5 账户流水表
CREATE TABLE `账户流水` (
`交易编号` BIGINT(19) NOT NULL AUTO_INCREMENT,
`交易时间` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`账户号` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_0900_ai_ci',
`交易类型` TINYTEXT NOT NULL COLLATE 'utf8mb4_0900_ai_ci',
`映射账户` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
`映射交易号` BIGINT(19) NULL DEFAULT NULL,
`变动金额` DECIMAL(20,2) NOT NULL DEFAULT '0.00',
`余额` DECIMAL(20,2) NOT NULL DEFAULT '0.00',
`包含手续费` DECIMAL(20,2) NOT NULL DEFAULT '0.00',
`交易备注` TINYTEXT NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
PRIMARY KEY (`交易编号`) USING BTREE,
INDEX `FK_活期_users` (`账户号`) USING BTREE,
INDEX `FK_活期_users_2` (`映射账户`) USING BTREE,
INDEX `操作时间` (`交易时间`) USING BTREE,
INDEX `FK_账户流水_账户流水` (`映射交易号`) USING BTREE,
CONSTRAINT `FK_账户流水_卡内账户` FOREIGN KEY (`账户号`) REFERENCES `atm`.`卡内
账户` (`账户号`) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT `FK_账户流水_卡内账户_2` FOREIGN KEY (`映射账户`) REFERENCES `atm`.`
卡内账户` (`账户号`) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT `FK_账户流水_账户流水` FOREIGN KEY (`映射交易号`) REFERENCES `atm`.`
账户流水` (`交易编号`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8mb4_0900_ai_ci'
ENGINE=InnoDB
AUTO_INCREMENT=182
;
账户流水表是本设计中关系最为复杂的基本表，该表记录了不同客户之间所有账
户的交易流水。
以自增序列的交易编号为主键，规避了以操作时间或（操作时间，账户号）
为主键时，同时间（1 秒内）同账户操作带来的冲突。
交易时间默认以执行插入操作时的系统当前时间，不需要特别指定；账户号
作为外键与卡内账户表联系。
交易类型用于简短记录此次交易的内容，如“存款”、“取款”、“转出”、“转
入”等。
映射账户表示与此次交易内容相关的操作对方或发起方账户号，在存取款时，
映射账户为自身账户号；在转出操作时，映射账户号为转账到对方的账户号，
在转入操作时，映射账户号为转账操作发起人对方的账户号；
映射交易号反映了具有关联的交易对，例如转入和转出操作两条记录是成对
进行的，因此两个操作的映射交易号互为彼此。映射交易号的设计目的在于
有效的针对转账操作进行定位溯源，在本设计中暂未实际使用到；
变动金额代表此次操作的涉及款项，取款，转出等操作的变动金额为负数，
存款，转入等操作的变动金额为正数。该变动金额是包含手续费在内的金额。
余额表征了该账户在该笔交易后账户的剩余可交易额度，在信用账户中，该
值透支至-2000 元。余额只通过变动金额进行计算。
包含手续费仅作为手续费的展示，不参与账户金额变动的计算。例如用户转
出 500 元，手续费收取 1%，则操作记录显示变动金额 505.00，包含手续费
5.00，此时计算余额方式为最近一笔交易的余额减去 505.00，而不会计算为
最近一笔交易的余额-505.00-5.00。
交易备注反映了更为详细和必要的交易信息，如扣除手续费的原因等。
1.6 定期存款表
CREATE TABLE `定期存款` (
`交易编号` BIGINT(19) NOT NULL AUTO_INCREMENT,
`交易时间` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`账户号` VARCHAR(50) NOT NULL DEFAULT '0' COLLATE 'utf8mb4_0900_ai_ci',
`存入金额` DECIMAL(20,2) NOT NULL DEFAULT '0.00',
`到期时间` TIMESTAMP NOT NULL,
`日利率` DECIMAL(20,6) NOT NULL DEFAULT '0.000000',
`存款状态` INT(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`交易编号`) USING BTREE,
INDEX `FK_定期存款_卡内账户` (`账户号`) USING BTREE,
CONSTRAINT `FK_定期存款_卡内账户` FOREIGN KEY (`账户号`) REFERENCES `atm`.`卡内
账户` (`账户号`) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT `FK_定期存款_账户流水` FOREIGN KEY (`交易编号`) REFERENCES `atm`.`账
户流水` (`交易编号`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8mb4_0900_ai_ci'
ENGINE=InnoDB
AUTO_INCREMENT=160
;
定期存款是在账户流水的基础上额外维护的一个表，其建立的根本目的是适
应业务需求，记录定期存款的到期时间，利率和是否已取出（存款状态）这三个
信息，如果三个核心属性也写在账户流水表内会造成账户流水表的臃肿且利用率
低。
定期存款表以账户流水表中涉及到定期存入的每条交易号为主键，并有意引
入了如交易时间，账户号和交易金额这类冗余量，方便后台数据的查看校对，当
然这些字段也可以进一步优化除去。
二、存储过程的编写
本项目后端没有显式的服务端封装，即不是传统的（客户端<->服务端<->数
据库端）的模式，这带来了安全性相关的问题，如果在前端直接登录具有权限的
账户执行 select,insert,update 等语句，那么如果前端软件遭到破坏后将会造
成数据库管理权限的外泄，造成数据库入侵，带来威胁。
经过讨论，我们决定以数据库账户和权限为切入点，借由存储过程，模拟后
端的接口封装。前端连接到数据库端的子账户仅能调用注册，登录，存取款等存
储过程，实现更加细化的功能权限，而没有对任何基本表的通用增删改查权限。
从而在一定程度上解决了软件被破坏数据库账号泄露等问题下的安全性问题。按
照实验设计需求，数据库共设置有 8 个存储过程。
2.1 注册功能（signup）
delimiter $
CREATE DEFINER=`root`@`%` PROCEDURE `signup`(
IN `name` TINYTEXT,
IN `pwd` VARCHAR(50),
IN `pid` BIGINT,
OUT `id` INT,
OUT `info` TEXT
)
BEGIN
DECLARE num INT ;
DECLARE acc TEXT;
DECLARE ACCOUNT VARCHAR(50);
SELECT COUNT(*) INTO num FROM 客户信息 WHERE 身份证号=pid;
if num > 2 then
