银行核心业务系统案例和DB基础

银行关键业务系统案例张英第1页任务 现有一商业银行需要用计算机完成其关键业务系统第2页完成任务步骤准备工作需求分析限定业务范围业务流程及数据调查

建立模型物理实现测试、运行与维护第3页需求分析-业务限定业务限定： 存款账户开户、存款、取款、行内转账、挂失、销户；存款及客户信息查询和统计。第4页开户业务流程开户业务流程：用户填写基本信息依据用户身份证号查询该用户是否已经是本行客户假如不是，先登记基本信息，再开户。假如是，则直接开户。开户：确定存款账户类型及金额；填写账户相关信息；用户预留密码；打印存折或发卡第5页开户需要数据项客户身份证编号

客户姓名客户所在城市联络电话性别职业帐号开户日期账户余额账户类型银行操作人员开户银行分支机构第6页存款业务流程存款业务流程用户提供账号（经过卡或存折）和存款数额信息查询指定账号是否存在，假如不存在，则提醒错误并终止业务；不然继续。收取对应数量现金，更改对应账户余额，统计此次业务明细信息。第7页存款数据项帐号存款日期存款额账户余额银行操作人员第8页取款业务流程取款业务流程:用户提供账户，输入密码，取款数额查询指定账号是否存在，假如不存在，则提醒错误并终止业务；不然继续。查询用户输入密码与系统预留密码是否一致，假如不一致，则重新输入密码；不然继续。查询指定账户是否为正常账户，是则继续，不然提醒错误并终止业务。查询指定账户余额是否足够支取，假如不够，则提醒错误并终止业务；不然继续。给用户支取对应数量现金，更改对应账户余额，统计此次业务明细信息。第9页取款数据项帐号

取款日期取款额账户余额银行操作人员第10页行内转账业务流程行内转账业务流程：用户提供转出账户，输入密码、对方账户和转账数额信息查询上述指定账号是否都存在，假如任一个账户不存在，则提醒错误并终止业务；不然继续。查询用户输入密码与系统预留密码是否一致，假如不一致，则提醒重新输入密码；不然继续。查询转出账户是否为正常账户，是则继续，不然提醒错误并终止业务。查询转出账户余额是否足够转账，假如不够，则提醒错误并终止业务；不然继续。更改转出账户与对方账户余额，统计此次业务明细信息。第11页行内转账数据项转出帐号对方账号转账金额转账日期银行操作人员第12页其它业务流程其它业务流程请同学们讨论挂失销户查询余额、交易明细需要哪些数据项？第13页需求数据汇总开户：客户身份证编号，客户姓名，客户所在城市，联络电话，性别，职业、帐号，开户日期，账户余额，账户类型，银行操作人员，开户银行分支机构存款：帐号，存款日期，存款额，账户余额，银行操作人员取款：帐号，取款日期，取款额，账户余额，银行操作人员转账：转出帐号，对方账号，金额，转账日期，银行操作人员销户：帐号，账户余额，销户日期，账户状态挂失：帐号，挂失日期，账户状态客户查询：账号，余额，存取款历史（时间，业务类型，发生额，当初余额）第14页问题 这些业务数据代表现实世界怎样在计算机内表示？用几张表存放？第15页数据模型在数据库中用数据模型这个工具来抽象、表示和处理现实世界中数据和信息。通俗地讲数据模型就是现实世界模拟。数据模型应满足三方面要求能比较真实地模拟现实世界轻易为人所了解便于在计算机上实现这三方面要求是否很轻易都满足？第16页数据模型层次概念模型也称信息模型 按用户观点对数据和信息建模。(2)结构模型

按计算机系统观点对数据建模。主要包含网状模型、层次模型、关系模型，面向对象模型等。信息世界概念化形式化概念数据模型结构数据模型现实世界计算机世界面向用户面向计算机客观对象抽象过程第17页概念模型中基本概念

(1)实体（Entity）客观存在并可相互区分事物称为实体。(2)属性（Attribute）实体所含有某一特征称为属性。一个实体能够由若干个属性来刻画。(3)域（Domain）属性取值范围称为该属性域。

(4)超码与侯选码、主码唯一标识实体属性集称为超码。唯一标识实体最小属性集称为侯选码。依据需要，选择一个候选码作为主码。主属性：包含在任意一个候选码中属性。非主属性第18页概念模型中基本概念(续)实体型（EntityType）与实体集（EntitySet）联络（Relationship）反应实体集之间关联。一对一联络（1:1）银行和正行长之间联络一对多联络（1:n）客户和账户之间联络多对多联络（m:n）客户和银行之间联络第19页E-R图实体型（或实体集）用矩形表示，矩形框内写明实体名。属性用椭圆形表示，并用无向边将其与对应实体连接起来；候选码：属性下加下划线客户身份证号性别姓名出生日期第20页E-R图支行管理行长111:1联络客户服务银行mnm:n联络支行隶属存款账户1n1:n联络第21页案例E-R图m1拥有姓名身份证号电话账号类型余额状态开户日期客户存款账户职业对应1n金额类型日期名称编码银行分支机构性别城市经手人管理n1交易对方账号经手人第22页逻辑模型—关系模型银行表客户表属性名含义类型长度要求Branch_id机构编码字符4主码Branch_name机构名称变长字符40非空属性名含义类型长度要求Customer_id身份证号字符18主码Cust_name姓名变长字符20非空Cust_phone电话变长字符13Cust_gender性别字符1“M”男”F“女Cust_job职业变长字符30Cust_city所在城市变长字符30第23页账户表属性名含义类型长度要求Customer_id身份证号字符18外码Branch_id机构编码字符4外码Account_no账号字符8主码balance余额数值14,2build_date开户日期日期10Acct_type账户类型字符1“0”活期，”数字“表示定时月数status账户状态字符1”0“正常，”1”挂失，”2“销户Suspend_date挂失日期日期Close_date销户日期日期Account_pwd密码字符6Staff_id经手人字符18外码第24页存、取款、转账明细表deposit属性名含义类型长度要求Serial_no流水号数值10,0主码Account_no账号字符8外码Oper_date发生日期日期amount金额数值11,2balance余额数值11,2oper_type类型字符1“C”存款”Q“取款”S“转入”Z“转出Account_vs往来账号字符8外码Staff_id经手人字符18外码第25页银行职员表

属性名含义类型长度要求staff_id职员编号字符18主码Staff_name姓名变长字符15Branch_id机构编码字符4Staff_role职位字符10staff_pwd密码字符15第26页二维表（或关系）元组属性主码：customer_id第27页关系完整性约束新增一个客户，身份证号能够不填吗？身份证号能够重复吗？实体完整性（主属性非空）客户性别栏能够填写什么？用户定义完整性账户表中customer_id栏是否能够出现一个在客户表customer_id栏中不存在值？参考完整性第28页外码Customer主键？Account主键？注意：一个客户能够有多个账号Cust_id称为account表外码第29页参考完整性外码取值：或者取空值或者等于它引用主码一个值。第30页关系操作类型

查询、增加、删除、修改语言SQL第31页查询操作找出成都客户------选择查看全部客户身份证号和姓名-------投影查看全部客户身份证号、姓名和存款账户信息------连接第32页连接举例学号计算机会计学号金融000180900001860002769800026600038870000390rsR.学号计算机会计S.学号金融000180900001860002769800026600038870000390学号计算机会计金融000180908600027698660003887090等值连接自然连接第33页SQL概述SQL(StructuredQueryLanguage)是关系数据库标准语言，最早由IBM企业研制，并在其SYSTEMR系统中实现。SQL标准:SQL-86(ANSI1986)(ISO1987)SQL-89(ANSI1989)SQL-92orSQL2(ANSI1992)SQL-99orSQL3(ANSI1999)SQL第34页SQL命令动词第35页相关sql语句说明SQL是大小写无关每个SQL语句用半角分号“；”结束在SQL语句中字符数据用半角单引号“‘”括起第36页ORACLESQL中惯用基本数据类型字符型char(n)固定长度字符串varchar2(n)变长长度字符串数值型Int整数number(p,d)小数，p为有效数位，d为小数位数。日期时间型date.日期型（4位年）。普通格式：yyyy-mm-dd比如：-1-27第37页定义基本表CREATETABLE<表名>

（<列名><数据类型>[<列级完整性约束条件>][，．．．]

[，<表级完整性约束条件>]）；第38页惯用完整性约束条件惯用列级完整性约束条件NOTNULLPRIMARYKEY仅适合用于单属性主码UNIQUECHECK（）定义域惯用表级完整性约束条件PRIMARYKEY(主码)CHECK(条件)FOREIGNKEY(外码)REFERENCES表名（与外码对应主码）UNIQUE（属性）第39页创建系统中表Customer表CREATETABLEcustomer(cust_idCHAR(18)primarykey,cust_namevarCHAR2(20)notnull,cust_cityvarchar2(30),cust_phonevarCHAR2(13),cust_genderCHAR(1),cust_jobvarCHAR2(30));ACCOUNT表查看表结构第40页向表中添加数据插入数据：向指定表中插入一条或多条元组；INSERTINTO<表名>(<属性列表>)

VALUES(<常量列表>]…);INTO子句属性列次序可与表定义中次序不一致能够只指定部分属性列（必须包含主属性）假如不指定属性列：表示要插入是一条完整元组，且属性列次序与表定义中次序一致（不提议使用）练习：插入一个客户及其开户数据第41页SQL查询查询语句普通格式：SELECT<目标列表示式>FROM<表名>[，<表名>][WHERE<条件表示式>][GROUPBY<列名>[HAVING<条件表示式>]][ORDERBY<列名>[ASC|DESC]]；

第42页各子句含义SELECT子句：指定要显示属性列表示式（即结果关系属性）FROM子句：指定查询需要表WHERE子句：指定查询条件

GROUPBY子句：分组统计。对表中满足查询条件行按指定列分组，值相等行为一个组。通常会在每组中使用集函数（即完成统计功效函数）。HAVING短语：筛选出满足指定条件组ORDERBY子句：对查询结果按指定次序排序第43页简单查询

从表Customer中查询Cust_id,cust_name。selectcust_id,cust_namefromcustomer;查询表Customer全部数据。“*”表示表中全部字段。SELECT*FROMCustomer查询表Account中有开户统计客户身份证号。SELECTDISTINCTcust_idFROMaccount第44页条件查询

第45页

条件查询

查询”成都“客户信息select*fromcustomerwherecust_city='成都‘;表Customer中查询”成都”“教师”信息select*fromcustomerwherecust_city='成都'andcust_job='教师‘查询余额大于100000或小于0帐户信息select*fromaccountwherebalance<0orbalance>1000第46页条件查询

从表account中查询balance在元到10000元统计。select*fromaccountwherebalancebetween0and100000从表customer中查询cust_job是‘教师’‘工人’‘工程师’统计。

select*fromcustomerwherecust_jobin('教师','工程师','工人')从表Customer中查询Cust_phone不为空统计。select*fromcustomerwherecust_phoneisnotnull第47页统计查询

第48页统计查询

计算表Account中全部帐户最高Balance、最低balanceselectmax(balance)最高余额,min(balance)最低余额fromaccount计算表Customer成都客户数。selectcount(*)fromcustomerwherecust_city='成都‘统计Account中有开户统计客户数。selectcount(distinctcust_id)fromaccount思索：这里DISTINCT为何不能省略？

第49页分组查询

关键:确定分类字段、汇总字段,

汇总函数查询每个客户帐户数。selectcust_id,count(*)as开户数fromaccountgroupbycust_id;查询每个客户帐户余额累计。selectcust_id,sum(balance)as余额累计fromaccountgroupbycust_id;第50页查询结果排序从表customer查询结果按cust_city升序排序，cust_city相同按照cust_job降序排序。select*fromcustomerorderbycust_city,cust_jobdesc第51页多表连接查询连接From表1,表2…where连接条件From子句中：表1join表2on连接条件表1naturaljoin表2第52页连接查询查询每个账户账号、客户姓名和账户余额。selectcust_name,account_no,balancefromcustomer,accountwherecustomer.cust_id=account.cust_id;注意:对于两个表都有同名字段,必须用表名或别名加以限制。selectcust_name,account_no,balancefromcustomerjoinaccountoncustomer.cust_id=account.cust_id;selectcust_name,account_no,balancefromcustomernatural