銀行核心業(yè)務系統(tǒng)案例和DB基礎(chǔ)

銀行關(guān)鍵業(yè)務系統(tǒng)案例張英第1頁任務 現(xiàn)有一商業(yè)銀行需要用計算機完成其關(guān)鍵業(yè)務系統(tǒng)第2頁完成任務步驟準備工作需求分析限定業(yè)務范圍業(yè)務流程及數(shù)據(jù)調(diào)查

建立模型物理實現(xiàn)測試、運行與維護第3頁需求分析-業(yè)務限定業(yè)務限定： 存款賬戶開戶、存款、取款、行內(nèi)轉(zhuǎn)賬、掛失、銷戶；存款及客戶信息查詢和統(tǒng)計。第4頁開戶業(yè)務流程開戶業(yè)務流程：用戶填寫基本信息依據(jù)用戶身份證號查詢該用戶是否已經(jīng)是本行客戶假如不是，先登記基本信息，再開戶。假如是，則直接開戶。開戶：確定存款賬戶類型及金額；填寫賬戶相關(guān)信息；用戶預留密碼；打印存折或發(fā)卡第5頁開戶需要數(shù)據(jù)項客戶身份證編號

客戶姓名客戶所在城市聯(lián)絡電話性別職業(yè)帳號開戶日期賬戶余額賬戶類型銀行操作人員開戶銀行分支機構(gòu)第6頁存款業(yè)務流程存款業(yè)務流程用戶提供賬號（經(jīng)過卡或存折）和存款數(shù)額信息查詢指定賬號是否存在，假如不存在，則提醒錯誤并終止業(yè)務；不然繼續(xù)。收取對應數(shù)量現(xiàn)金，更改對應賬戶余額，統(tǒng)計此次業(yè)務明細信息。第7頁存款數(shù)據(jù)項帳號存款日期存款額賬戶余額銀行操作人員第8頁取款業(yè)務流程取款業(yè)務流程:用戶提供賬戶，輸入密碼，取款數(shù)額查詢指定賬號是否存在，假如不存在，則提醒錯誤并終止業(yè)務；不然繼續(xù)。查詢用戶輸入密碼與系統(tǒng)預留密碼是否一致，假如不一致，則重新輸入密碼；不然繼續(xù)。查詢指定賬戶是否為正常賬戶，是則繼續(xù)，不然提醒錯誤并終止業(yè)務。查詢指定賬戶余額是否足夠支取，假如不夠，則提醒錯誤并終止業(yè)務；不然繼續(xù)。給用戶支取對應數(shù)量現(xiàn)金，更改對應賬戶余額，統(tǒng)計此次業(yè)務明細信息。第9頁取款數(shù)據(jù)項帳號

取款日期取款額賬戶余額銀行操作人員第10頁行內(nèi)轉(zhuǎn)賬業(yè)務流程行內(nèi)轉(zhuǎn)賬業(yè)務流程：用戶提供轉(zhuǎn)出賬戶，輸入密碼、對方賬戶和轉(zhuǎn)賬數(shù)額信息查詢上述指定賬號是否都存在，假如任一個賬戶不存在，則提醒錯誤并終止業(yè)務；不然繼續(xù)。查詢用戶輸入密碼與系統(tǒng)預留密碼是否一致，假如不一致，則提醒重新輸入密碼；不然繼續(xù)。查詢轉(zhuǎn)出賬戶是否為正常賬戶，是則繼續(xù)，不然提醒錯誤并終止業(yè)務。查詢轉(zhuǎn)出賬戶余額是否足夠轉(zhuǎn)賬，假如不夠，則提醒錯誤并終止業(yè)務；不然繼續(xù)。更改轉(zhuǎn)出賬戶與對方賬戶余額，統(tǒng)計此次業(yè)務明細信息。第11頁行內(nèi)轉(zhuǎn)賬數(shù)據(jù)項轉(zhuǎn)出帳號對方賬號轉(zhuǎn)賬金額轉(zhuǎn)賬日期銀行操作人員第12頁其它業(yè)務流程其它業(yè)務流程請同學們討論掛失銷戶查詢余額、交易明細需要哪些數(shù)據(jù)項？第13頁需求數(shù)據(jù)匯總開戶：客戶身份證編號，客戶姓名，客戶所在城市，聯(lián)絡電話，性別，職業(yè)、帳號，開戶日期，賬戶余額，賬戶類型，銀行操作人員，開戶銀行分支機構(gòu)存款：帳號，存款日期，存款額，賬戶余額，銀行操作人員取款：帳號，取款日期，取款額，賬戶余額，銀行操作人員轉(zhuǎn)賬：轉(zhuǎn)出帳號，對方賬號，金額，轉(zhuǎn)賬日期，銀行操作人員銷戶：帳號，賬戶余額，銷戶日期，賬戶狀態(tài)掛失：帳號，掛失日期，賬戶狀態(tài)客戶查詢：賬號，余額，存取款歷史（時間，業(yè)務類型，發(fā)生額，當初余額）第14頁問題 這些業(yè)務數(shù)據(jù)代表現(xiàn)實世界怎樣在計算機內(nèi)表示？用幾張表存放？第15頁數(shù)據(jù)模型在數(shù)據(jù)庫中用數(shù)據(jù)模型這個工具來抽象、表示和處理現(xiàn)實世界中數(shù)據(jù)和信息。通俗地講數(shù)據(jù)模型就是現(xiàn)實世界模擬。數(shù)據(jù)模型應滿足三方面要求能比較真實地模擬現(xiàn)實世界輕易為人所了解便于在計算機上實現(xiàn)這三方面要求是否很輕易都滿足？第16頁數(shù)據(jù)模型層次概念模型也稱信息模型 按用戶觀點對數(shù)據(jù)和信息建模。(2)結(jié)構(gòu)模型

按計算機系統(tǒng)觀點對數(shù)據(jù)建模。主要包含網(wǎng)狀模型、層次模型、關(guān)系模型，面向?qū)ο竽Ｐ偷?。信息世界概念化形式化概念?shù)據(jù)模型結(jié)構(gòu)數(shù)據(jù)模型現(xiàn)實世界計算機世界面向用戶面向計算機客觀對象抽象過程第17頁概念模型中基本概念

(1)實體（Entity）客觀存在并可相互區(qū)分事物稱為實體。(2)屬性（Attribute）實體所含有某一特征稱為屬性。一個實體能夠由若干個屬性來刻畫。(3)域（Domain）屬性取值范圍稱為該屬性域。

(4)超碼與侯選碼、主碼唯一標識實體屬性集稱為超碼。唯一標識實體最小屬性集稱為侯選碼。依據(jù)需要，選擇一個候選碼作為主碼。主屬性：包含在任意一個候選碼中屬性。非主屬性第18頁概念模型中基本概念(續(xù))實體型（EntityType）與實體集（EntitySet）聯(lián)絡（Relationship）反應實體集之間關(guān)聯(lián)。一對一聯(lián)絡（1:1）銀行和正行長之間聯(lián)絡一對多聯(lián)絡（1:n）客戶和賬戶之間聯(lián)絡多對多聯(lián)絡（m:n）客戶和銀行之間聯(lián)絡第19頁E-R圖實體型（或?qū)嶓w集）用矩形表示，矩形框內(nèi)寫明實體名。屬性用橢圓形表示，并用無向邊將其與對應實體連接起來；候選碼：屬性下加下劃線客戶身份證號性別姓名出生日期第20頁E-R圖支行管理行長111:1聯(lián)絡客戶服務銀行mnm:n聯(lián)絡支行隸屬存款賬戶1n1:n聯(lián)絡第21頁案例E-R圖m1擁有姓名身份證號電話賬號類型余額狀態(tài)開戶日期客戶存款賬戶職業(yè)對應1n金額類型日期名稱編碼銀行分支機構(gòu)性別城市經(jīng)手人管理n1交易對方賬號經(jīng)手人第22頁邏輯模型—關(guān)系模型銀行表客戶表屬性名含義類型長度要求Branch_id機構(gòu)編碼字符4主碼Branch_name機構(gòu)名稱變長字符40非空屬性名含義類型長度要求Customer_id身份證號字符18主碼Cust_name姓名變長字符20非空Cust_phone電話變長字符13Cust_gender性別字符1“M”男”F“女Cust_job職業(yè)變長字符30Cust_city所在城市變長字符30第23頁賬戶表屬性名含義類型長度要求Customer_id身份證號字符18外碼Branch_id機構(gòu)編碼字符4外碼Account_no賬號字符8主碼balance余額數(shù)值14,2build_date開戶日期日期10Acct_type賬戶類型字符1“0”活期，”數(shù)字“表示定時月數(shù)status賬戶狀態(tài)字符1”0“正常，”1”掛失，”2“銷戶Suspend_date掛失日期日期Close_date銷戶日期日期Account_pwd密碼字符6Staff_id經(jīng)手人字符18外碼第24頁存、取款、轉(zhuǎn)賬明細表deposit屬性名含義類型長度要求Serial_no流水號數(shù)值10,0主碼Account_no賬號字符8外碼Oper_date發(fā)生日期日期amount金額數(shù)值11,2balance余額數(shù)值11,2oper_type類型字符1“C”存款”Q“取款”S“轉(zhuǎn)入”Z“轉(zhuǎn)出Account_vs往來賬號字符8外碼Staff_id經(jīng)手人字符18外碼第25頁銀行職員表

屬性名含義類型長度要求staff_id職員編號字符18主碼Staff_name姓名變長字符15Branch_id機構(gòu)編碼字符4Staff_role職位字符10staff_pwd密碼字符15第26頁二維表（或關(guān)系）元組屬性主碼：customer_id第27頁關(guān)系完整性約束新增一個客戶，身份證號能夠不填嗎？身份證號能夠重復嗎？實體完整性（主屬性非空）客戶性別欄能夠填寫什么？用戶定義完整性賬戶表中customer_id欄是否能夠出現(xiàn)一個在客戶表customer_id欄中不存在值？參考完整性第28頁外碼Customer主鍵？Account主鍵？注意：一個客戶能夠有多個賬號Cust_id稱為account表外碼第29頁參考完整性外碼取值：或者取空值或者等于它引用主碼一個值。第30頁關(guān)系操作類型

查詢、增加、刪除、修改語言SQL第31頁查詢操作找出成都客戶------選擇查看全部客戶身份證號和姓名-------投影查看全部客戶身份證號、姓名和存款賬戶信息------連接第32頁連接舉例學號計算機會計學號金融000180900001860002769800026600038870000390rsR.學號計算機會計S.學號金融000180900001860002769800026600038870000390學號計算機會計金融000180908600027698660003887090等值連接自然連接第33頁SQL概述SQL(StructuredQueryLanguage)是關(guān)系數(shù)據(jù)庫標準語言，最早由IBM企業(yè)研制，并在其SYSTEMR系統(tǒng)中實現(xiàn)。SQL標準:SQL-86(ANSI1986)(ISO1987)SQL-89(ANSI1989)SQL-92orSQL2(ANSI1992)SQL-99orSQL3(ANSI1999)SQL第34頁SQL命令動詞第35頁相關(guān)sql語句說明SQL是大小寫無關(guān)每個SQL語句用半角分號“；”結(jié)束在SQL語句中字符數(shù)據(jù)用半角單引號“‘”括起第36頁ORACLESQL中慣用基本數(shù)據(jù)類型字符型char(n)固定長度字符串varchar2(n)變長長度字符串數(shù)值型Int整數(shù)number(p,d)小數(shù)，p為有效數(shù)位，d為小數(shù)位數(shù)。日期時間型date.日期型（4位年）。普通格式：yyyy-mm-dd比如：-1-27第37頁定義基本表CREATETABLE<表名>

（<列名><數(shù)據(jù)類型>[<列級完整性約束條件>][，．．．]

[，<表級完整性約束條件>]）；第38頁慣用完整性約束條件慣用列級完整性約束條件NOTNULLPRIMARYKEY僅適合用于單屬性主碼UNIQUECHECK（）定義域慣用表級完整性約束條件PRIMARYKEY(主碼)CHECK(條件)FOREIGNKEY(外碼)REFERENCES表名（與外碼對應主碼）UNIQUE（屬性）第39頁創(chuàng)建系統(tǒng)中表Customer表CREATETABLEcustomer(cust_idCHAR(18)primarykey,cust_namevarCHAR2(20)notnull,cust_cityvarchar2(30),cust_phonevarCHAR2(13),cust_genderCHAR(1),cust_jobvarCHAR2(30));ACCOUNT表查看表結(jié)構(gòu)第40頁向表中添加數(shù)據(jù)插入數(shù)據(jù)：向指定表中插入一條或多條元組；INSERTINTO<表名>(<屬性列表>)

VALUES(<常量列表>]…);INTO子句屬性列次序可與表定義中次序不一致能夠只指定部分屬性列（必須包含主屬性）假如不指定屬性列：表示要插入是一條完整元組，且屬性列次序與表定義中次序一致（不提議使用）練習：插入一個客戶及其開戶數(shù)據(jù)第41頁SQL查詢查詢語句普通格式：SELECT<目標列表示式>FROM<表名>[，<表名>][WHERE<條件表示式>][GROUPBY<列名>[HAVING<條件表示式>]][ORDERBY<列名>[ASC|DESC]]；

第42頁各子句含義SELECT子句：指定要顯示屬性列表示式（即結(jié)果關(guān)系屬性）FROM子句：指定查詢需要表WHERE子句：指定查詢條件

GROUPBY子句：分組統(tǒng)計。對表中滿足查詢條件行按指定列分組，值相等行為一個組。通常會在每組中使用集函數(shù)（即完成統(tǒng)計功效函數(shù)）。HAVING短語：篩選出滿足指定條件組ORDERBY子句：對查詢結(jié)果按指定次序排序第43頁簡單查詢

從表Customer中查詢Cust_id,cust_name。selectcust_id,cust_namefromcustomer;查詢表Customer全部數(shù)據(jù)?！?”表示表中全部字段。SELECT*FROMCustomer查詢表Account中有開戶統(tǒng)計客戶身份證號。SELECTDISTINCTcust_idFROMaccount第44頁條件查詢

第45頁

條件查詢

查詢”成都“客戶信息select*fromcustomerwherecust_city='成都‘;表Customer中查詢”成都”“教師”信息select*fromcustomerwherecust_city='成都'andcust_job='教師‘查詢余額大于100000或小于0帳戶信息select*fromaccountwherebalance<0orbalance>1000第46頁條件查詢

從表account中查詢balance在元到10000元統(tǒng)計。select*fromaccountwherebalancebetween0and100000從表customer中查詢cust_job是‘教師’‘工人’‘工程師’統(tǒng)計。

select*fromcustomerwherecust_jobin('教師','工程師','工人')從表Customer中查詢Cust_phone不為空統(tǒng)計。select*fromcustomerwherecust_phoneisnotnull第47頁統(tǒng)計查詢

第48頁統(tǒng)計查詢

計算表Account中全部帳戶最高Balance、最低balanceselectmax(balance)最高余額,min(balance)最低余額fromaccount計算表Customer成都客戶數(shù)。selectcount(*)fromcustomerwherecust_city='成都‘統(tǒng)計Account中有開戶統(tǒng)計客戶數(shù)。selectcount(distinctcust_id)fromaccount思索：這里DISTINCT為何不能省略？

第49頁分組查詢

關(guān)鍵:確定分類字段、匯總字段,

匯總函數(shù)查詢每個客戶帳戶數(shù)。selectcust_id,count(*)as開戶數(shù)fromaccountgroupbycust_id;查詢每個客戶帳戶余額累計。selectcust_id,sum(balance)as余額累計fromaccountgroupbycust_id;第50頁查詢結(jié)果排序從表customer查詢結(jié)果按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