ATM取款機(jī)系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)ppt課件

1、ATM取款機(jī)系統(tǒng)數(shù)據(jù)庫設(shè)計(jì) 問題描述問題描述某銀行擬開發(fā)一套某銀行擬開發(fā)一套ATM取款機(jī)系統(tǒng)，實(shí)現(xiàn)如下功能：取款機(jī)系統(tǒng)，實(shí)現(xiàn)如下功能：1、開戶到銀行填寫開戶申請單，卡號自動生成）、開戶到銀行填寫開戶申請單，卡號自動生成）2、取錢、取錢3、存錢、存錢4、查詢余額、查詢余額5、轉(zhuǎn)賬如使用一卡通代繳手機(jī)話費(fèi)、個(gè)人股票交易等）、轉(zhuǎn)賬如使用一卡通代繳手機(jī)話費(fèi)、個(gè)人股票交易等）現(xiàn)要求對現(xiàn)要求對“ATM柜員機(jī)系統(tǒng)進(jìn)行數(shù)據(jù)庫的設(shè)計(jì)并實(shí)現(xiàn)，柜員機(jī)系統(tǒng)進(jìn)行數(shù)據(jù)庫的設(shè)計(jì)并實(shí)現(xiàn)，數(shù)據(jù)庫保存在數(shù)據(jù)庫保存在D:bank目錄下，文件增長率為目錄下，文件增長率為15% 。問題分析-1字段名稱說 明customerID顧客編號

2、自動編號（標(biāo)識列），從1開始，主鍵customerName開戶名必填PID身份證號必填，只能是18位或15位，身份證號唯一約束telephone聯(lián)系電話必填，格式為xxxx-xxxxxxxx或手機(jī)號13位address居住地址可選輸入用戶信息表：用戶信息表：userInfo ：問題分析-2銀行卡信息表：銀行卡信息表：cardInfo字段名稱說 明cardID卡號必填，主健，銀行的卡號規(guī)則和電話號碼一樣，一般前8位代表特殊含義，如某總行某支行等。假定該行要求其營業(yè)廳的卡號格式為：1010 3576 xxxx xxx開始,每4位號碼后有空格，卡號一般是隨機(jī)產(chǎn)生。curType貨幣種類必填，默認(rèn)為R

3、MBsavingType存款類型活期/定活兩便/定期openDate開戶日期必填，默認(rèn)為系統(tǒng)當(dāng)前日期openMoney開戶金額必填，不低于1元balance余額必填，不低于1元,否則將銷戶pass密碼必填，6位數(shù)字，開戶時(shí)默認(rèn)為6個(gè)“8”IsReportLoss是否掛失必填，是/否值，默認(rèn)為”否”customerID顧客編號外鍵，必填，表示該卡對應(yīng)的顧客編號，一位顧客允許辦理多張卡號問題分析-3字段名稱說 明transDate 交易日期必填，默認(rèn)為系統(tǒng)當(dāng)前日期cardID卡號必填，外健，可重復(fù)索引transType 交易類型必填，只能是存入/支取transMoney交易金額必填，大于0rema

4、rk備注可選輸入，其他說明交易信息表：交易信息表：transInfo 難點(diǎn)分析-1 設(shè)計(jì)ER圖、建庫、建表、加約束、建關(guān)系部分 建庫語句：建庫語句：CREATE DATABASE bankDB ON ( NAME=, FILENAME=, SIZE=, FILEGROWTH=) LOG ON ( . )建表語句：建表語句：CREATE TABLE 表名表名 ( customerID INT IDENTITY(1,1), customerName CHAR(8) NOT NULL, .)文件增長率文件增長率數(shù)據(jù)文件數(shù)據(jù)文件日志文件日志文件自動編號，自動編號，從從1開始開始非空非空/必填必填難點(diǎn)分

5、析-2 設(shè)計(jì)ER圖、建庫、建表、加約束、建關(guān)系部分 建約束語句：建約束語句：ALTER TABLE cardInfo ADD CONSTRAINT PK_cardID PRIMARY KEY(cardID), CONSTRAINT CK_cardID CHECK(cardID LIKE 1010 3576 0-9), CONSTRAINT DF_curType DEFAULT(RMB) FOR curType CONSTRAINT FK_customerID FOREIGN KEY(customerID) REFERENCES userInfo(customerID), CONSTRAINT

6、UQ_PID UNIQUE(PID), .主鍵約束主鍵約束檢查約束檢查約束外鍵約束建關(guān)系）外鍵約束建關(guān)系）唯一約束唯一約束默認(rèn)約束默認(rèn)約束難點(diǎn)分析-3字段名稱說 明customerID顧客編號自動編號（標(biāo)識列），從1開始，主鍵customerName開戶名必填PID身份證號必填，只能是18位或15位，身份證號唯一約束telephone聯(lián)系電話必填，格式為xxxx-xxxxxxxx或手機(jī)號13位address居住地址可選輸入建表建表時(shí)時(shí):IDENTITY(1,1)check約束：約束：len( )函數(shù)函數(shù)建表時(shí)建表時(shí):NOT NULLcheck約束：約束： like 0-90-9 設(shè)計(jì)ER圖、建

7、庫、建表、加約束、建關(guān)系部分 子查詢：子查詢：SELECT .FROM WHERE transMoney=(SELECT FROM )內(nèi)部連接：內(nèi)部連接：SELECT FROM userInfo INNER JOIN cardInfo ON. SQL編程：編程：DECLARE inMoney moneySELECT inMoney=sum(transMoney) from .where (transType=存入存入)視圖：視圖：CREATE VIEW view_userInfo AS -SQL語句語句GO難點(diǎn)分析-4聲明變量聲明變量 插入測試數(shù)據(jù)、常規(guī)業(yè)務(wù)模擬、創(chuàng)建索引視圖部分 給變量賦值的

8、兩種方法：給變量賦值的兩種方法：SELECT或或SET測試視圖：測試視圖：SELECT . FROM view_userInfo 創(chuàng)建存儲過程：創(chuàng)建存儲過程： CREATE PROCEDURE proc_takeMoney card char(19)， type char(4) , inputPass char(6)= AS .SQL語句語句GO調(diào)用存儲過程：調(diào)用存儲過程：EXEC proc_openAccount 李四李四,321245678912345678,1,定期定期難點(diǎn)分析-5 存儲過程部分1： 存儲過程的參數(shù)存儲過程的參數(shù)有默認(rèn)值的參數(shù)，放在最后有默認(rèn)

9、值的參數(shù)，放在最后創(chuàng)建存儲過程：創(chuàng)建存儲過程： CREATE PROCEDURE proc_randCardID randCardID char(19) OUTPUT SELECT r=RAND (隨機(jī)種子隨機(jī)種子 ) 例如：例如：0. 08233262 3215 . set randCardID =.SUBSTRING(tempStr,3,4).GO 調(diào)用存儲過程：調(diào)用存儲過程：DECLARE mycardID char(19)EXECUTE proc_randCardID mycardID OUTPUT難點(diǎn)分析-6 存儲過程部分2：OUTPUT表示表示傳出的參數(shù)傳出的參數(shù)產(chǎn)生產(chǎn)生0-1的隨

10、機(jī)數(shù)的隨機(jī)數(shù)截取小數(shù)點(diǎn)后截取小數(shù)點(diǎn)后8位作為卡號的后八為數(shù)位作為卡號的后八為數(shù)卡號卡號(4位一組，用空格隔開位一組，用空格隔開)：1010 3576 0823 3262調(diào)用帶調(diào)用帶output輸出參數(shù)的存儲過程輸出參數(shù)的存儲過程字符串截取函數(shù)字符串截取函數(shù)階段劃分第一階段30分鐘） 利用Visio設(shè)計(jì)數(shù)據(jù)庫的E-R圖第二階段（ 50分鐘） 利用SQL語句實(shí)現(xiàn)建庫、建表、加約束、建關(guān)系第三階段30分鐘） 利用SQL語句插入測試數(shù)據(jù)、模擬常規(guī)業(yè)務(wù)操作 第四階段30分鐘） 利用SQL語句創(chuàng)建索引和視圖第五階段60分鐘） 利用SQL語句創(chuàng)建3個(gè)存儲過程并測試 第六階段30分鐘） 利用SQL語句創(chuàng)建轉(zhuǎn)賬

11、事務(wù)并測試 第七階段20分鐘） 利用SQL語句創(chuàng)建系統(tǒng)維護(hù)帳號并授權(quán) 第一階段 第一階段30分鐘） 利用Visio設(shè)計(jì)數(shù)據(jù)庫的E-R圖 要求學(xué)員自己動手操作，教員巡視，解答學(xué)員提出的問題階段檢查 針對第一階段抽查學(xué)員的操作結(jié)果 教員給出點(diǎn)評或集中演示難點(diǎn)部分第一階段結(jié)果演示1 第一階段操作的結(jié)果：第二階段 第二階段50分鐘） 利用SQL語句實(shí)現(xiàn)建庫、建表、加約束、建關(guān)系 要求學(xué)員自己動手編寫SQL語句，教員巡視，解答學(xué)員提出的問題階段檢查 針對第二階段抽查學(xué)員的編碼結(jié)果 教員給出點(diǎn)評或集中演示難點(diǎn)部分第二階段標(biāo)準(zhǔn)代碼演示-1 建庫IF exists(SELECT * FROM sysdatab

12、ases WHERE name=bankDB) DROP DATABASE bankDBGOCREATE DATABASE bankDB ON ( NAME=bankDB_data, FILENAME=d:bankbankDB_data.mdf, SIZE=3mb, FILEGROWTH=15% ) LOG ON (.檢驗(yàn)數(shù)據(jù)庫是否存在，如果為檢驗(yàn)數(shù)據(jù)庫是否存在，如果為真，刪除此數(shù)據(jù)庫真，刪除此數(shù)據(jù)庫 創(chuàng)建建庫創(chuàng)建建庫bankDB第二階段標(biāo)準(zhǔn)代碼演示-2 建表：USE bankDBGOCREATE TABLE userInfo 用戶信息表用戶信息表 ( customerID INT IDENT

13、ITY(1,1), customerName CHAR(8) NOT NULL, PID CHAR(18) NOT NULL, telephone CHAR(13) NOT NULL, address VARCHAR(50)GOCREATE TABLE cardInfo -銀行卡信息表銀行卡信息表( cardID CHAR(19) NOT NULL, curType CHAR(5) NOT NULL, savingType CHAR(8) NOT NULL, openDate DATETIME NOT NULL, openMoney MONEY NOT NULL, balance MONEY

14、NOT NULL, pass CHAR(6) NOT NULL, IsReportLoss BIT NOT NULL, customerID INT NOT NULL)CREATE TABLE transInfo -交易信息表交易信息表( transDate DATETIME NOT NULL, transType CHAR(4) NOT NULL, cardID CHAR(19) NOT NULL, transMoney MONEY NOT NULL, remark TEXT )GO第三階段 第三階段30分鐘）： 利用SQL語句實(shí)現(xiàn)插入測試數(shù)據(jù)、常規(guī)業(yè)務(wù)操作 要求學(xué)員自己動手操作，教員巡視，

15、解答學(xué)員提出的問題階段檢查 針對第三階段抽查學(xué)員的編碼結(jié)果 抽查學(xué)員編寫的完整代碼，要求學(xué)員上臺講解，并演示運(yùn)行結(jié)果 教員給出點(diǎn)評第三階段標(biāo)準(zhǔn)代碼演示-1 張三和李四開戶：SET NOCOUNT ON -不顯示受影響的條數(shù)信息不顯示受影響的條數(shù)信息INSERT INTO userInfo(customerName,PID,telephone,address ) VALUES(張三張三,123456789012345北京海淀北京海淀)INSERT INTO cardInfo(cardID,savingType,openMoney ,balance,customerID

16、) VALUES(1010 3576 1234 5678, 活期活期,1000,1000,1)INSERT INTO userInfo(customerName,PID,telephone) VALUES(李四李四,321245678912345678INSERT INTO cardInfo(cardID,savingType,openMoney,balance, customerID) VALUES(1010 3576 1212 1134,定期定期,1,1,2)SELECT * FROM userInfoSELECT * FROM cardInfoGO第三階段標(biāo)

17、準(zhǔn)代碼演示-2 張三的卡號取款900元，李四的卡號存款5000元/*-交易信息表插入交易記錄交易信息表插入交易記錄-*/INSERT INTO transInfo(transType,cardID,transMoney) VALUES(支取支取,1010 3576 1234 5678,900) /*-更新銀行卡信息表中的現(xiàn)有余額更新銀行卡信息表中的現(xiàn)有余額-*/UPDATE cardInfo SET balance=balance-900 WHERE cardID=1010 3576 1234 5678/*-交易信息表插入交易記錄交易信息表插入交易記錄-*/INSERT INTO transI

18、nfo(transType,cardID,transMoney) VALUES(存入存入,1010 3576 1212 1134,5000) /*-更新銀行卡信息表中的現(xiàn)有余額更新銀行卡信息表中的現(xiàn)有余額-*/UPDATE cardInfo SET balance=balance+5000 WHERE cardID=1010 3576 1212 1134GO第三階段標(biāo)準(zhǔn)代碼演示-3 修改密碼和掛失賬號/*-修改密碼修改密碼-*/-1.張三卡號為張三卡號為1010 3576 1234 5678修改銀行卡密碼為修改銀行卡密碼為123456-2.李四卡號為李四卡號為1010 3576 1212 11

19、34修改銀行卡密碼為修改銀行卡密碼為123123update cardInfo set pass=123456 WHERE cardID=1010 3576 1234 5678 update cardInfo set pass=123123 WHERE cardID=1010 3576 1212 1134 SELECT * FROM cardInfo/*- 李四的卡號掛失李四的卡號掛失 -*/update cardInfo set IsReportLoss=1 WHERE cardID=1010 3576 1212 1134 SELECT * FROM cardInfoGO第三階段標(biāo)準(zhǔn)代碼演示

20、-4 統(tǒng)計(jì)銀行的資金流通余額和盈利結(jié)算DECLARE inMoney moneyDECLARE outMoney moneyDECLARE profit moneySELECT inMoney=sum(transMoney) FROM transInfo WHERE (transType=存入存入)SELECT outMoney=sum(transMoney) FROM transInfo WHERE (transType=支取支取)print 銀行流通余額總計(jì)為銀行流通余額總計(jì)為:+ convert(varchar(20), inMoney-outMoney)+RMBset profit=o

21、utMoney*0.008-inMoney*0.003print 盈利結(jié)算為盈利結(jié)算為:+ convert(varchar(20),profit)+RMBGO第三階段標(biāo)準(zhǔn)代碼演示-5 其他操作其他操作/*-查詢本周開戶的卡號查詢本周開戶的卡號,顯示該卡相關(guān)信息顯示該卡相關(guān)信息-*/SELECT * FROM cardInfo WHERE (DATEDIFF(Day,getDate(),openDate)DATEPART(weekday,openDate)/*-查詢本月交易金額最高的卡號查詢本月交易金額最高的卡號-*/SELECT DISTINCT cardID FROM transInfo W

22、HERE transMoney=(SELECT Max(transMoney) FROM transInfo)/*-查詢掛失賬號的客戶信息查詢掛失賬號的客戶信息-*/SELECT customerName as 客戶姓名客戶姓名,telephone as 聯(lián)系電話聯(lián)系電話 FROM userInfo WHERE customerID IN (SELECT customerID FROM cardInfo WHERE IsReportLoss=1)/*-催款提醒：催款提醒： 如果發(fā)現(xiàn)用戶賬上余額少于如果發(fā)現(xiàn)用戶賬上余額少于200元，將致電催款。元，將致電催款。-*/SELECT,., FROM

23、userInfo INNER JOIN cardInfo ON userInfo.customerID =cardInfo.customerID WHERE balance200第四階段 第四階段30分鐘）： 利用SQL語句創(chuàng)建索引和視圖 要求學(xué)員自己動手操作，教員巡視，解答學(xué)員提出的問題階段檢查 針對第三階段抽查學(xué)員的編碼結(jié)果 抽查學(xué)員編寫的完整代碼，要求學(xué)員上臺講解，并演示運(yùn)行結(jié)果 教員給出點(diǎn)評第四階段標(biāo)準(zhǔn)代碼演示-1 創(chuàng)建索引和視圖：-1.創(chuàng)建索引：給交易表的卡號創(chuàng)建索引：給交易表的卡號cardID字段創(chuàng)建重復(fù)索引字段創(chuàng)建重復(fù)索引create NONCLUSTERED INDEX ind

24、ex_cardID ON transInfo(cardID)WITH FILLFACTOR=70GO-2.按指定索引查詢按指定索引查詢 張三卡號為張三卡號為1010 3576 1212 1134的交易記錄的交易記錄SELECT * FROM transInfo (INDEX=index_cardID) WHERE cardID=1010 3576 1234 5678GO-3.創(chuàng)建視圖：查詢各表要求字段全為中文字段名。創(chuàng)建視圖：查詢各表要求字段全為中文字段名。create VIEW view_userInfo -銀行卡信息表視圖其他表同理）銀行卡信息表視圖其他表同理） AS select cus

25、tomerID as 客戶編號客戶編號,customerName as 開戶名開戶名, PID as 身份身份證號證號, telephone as 電話號碼電話號碼,address as 居住地址居住地址 from userInfoGO第五階段 第五階段60分鐘）： 利用SQL語句創(chuàng)建3個(gè)存儲過程并測試 要求學(xué)員自己動手操作，教員巡視，解答學(xué)員提出的問題階段檢查 針對第三階段抽查學(xué)員的編碼結(jié)果 抽查學(xué)員編寫的完整代碼，要求學(xué)員上臺講解，并演示運(yùn)行結(jié)果 教員給出點(diǎn)評第五階段標(biāo)準(zhǔn)代碼演示-1 取錢或存錢的存儲過程create procedure proc_takeMoney card char(1

26、9),m money,type char(4),inputPass char(6)= AS print 交易正進(jìn)行交易正進(jìn)行,請稍后請稍后. if (type=支取支取) if (SELECT pass FROM cardInfo WHERE cardID=card)inputPass ) begin raiserror (密碼錯(cuò)誤密碼錯(cuò)誤!,16,1) return endDECLARE myTransType char(4),outMoney MONEY,myCardID char(19) SELECT myTransType=transType,outMoney=transMoney ,

27、myCardID=cardID FROM transInfo where cardID=card DECLARE mybalance money SELECT mybalance=balance FROM cardInfo WHERE cardID=card （未完待續(xù)）（未完待續(xù)）-2.調(diào)用存儲過程取錢或存錢調(diào)用存儲過程取錢或存錢 張三取張三取300，李四存，李四存500 現(xiàn)實(shí)中的現(xiàn)實(shí)中的ATM依靠讀卡器讀出張三的卡號依靠讀卡器讀出張三的卡號,這里根據(jù)張三的名字查出考號模擬這里根據(jù)張三的名字查出考號模擬declare card char(19)select card=cardID from

28、cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID where customerName=張三張三EXEC proc_takeMoney card,300 ,支取支取,123456 GO 李四同理李四同理declare card char(19)select card=cardID from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID where customerName=李四李四EXEC proc_ta

29、keMoney card,500 ,存入存入select * from view_cardInfoselect * from view_transInfoif (type=支取支取) if (mybalance=m+1) update cardInfo set balance=balance-m WHERE cardID=myCardID else begin raiserror (交易失??！余額不足！交易失??！余額不足！,16,1) print 卡號卡號+card+ 余額：余額：+convert(varchar(20),mybalance) return end else update ca

30、rdInfo set balance=balance+m WHERE cardID=card print 交易成功！交易金額：交易成功！交易金額：+convert(varchar(20),m) SELECT mybalance=balance FROM cardInfo WHERE cardID=card print 卡號卡號+card+ 余額：余額：+convert(varchar(20),mybalance) INSERT INTO transInfo(transType,cardID,transMoney) VALUES(type,card,m) GO第五階段標(biāo)準(zhǔn)代碼演示-2 產(chǎn)生隨機(jī)卡

31、號的存儲過程create procedure proc_randCardID randCardID char(19) OUTPUT AS DECLARE r numeric(15,8) DECLARE tempStr char(10) SELECT r=RAND(DATEPART(mm, GETDATE() * 100000 ) + (DATEPART(ss, GETDATE() * 1000 ) + DATEPART(ms, GETDATE() ) set tempStr=convert(char(10),r) set randCardID=1010 3576 +SUBSTRING(temp

32、Str,3,4)+ +SUBSTRING(tempStr,7,4) GO-測試產(chǎn)生隨機(jī)卡號測試產(chǎn)生隨機(jī)卡號DECLARE mycardID char(19) EXECUTE proc_randCardID mycardID OUTPUTprint 產(chǎn)生的隨機(jī)卡號為：產(chǎn)生的隨機(jī)卡號為：+mycardIDGO測試：產(chǎn)生隨機(jī)卡號測試：產(chǎn)生隨機(jī)卡號代碼：代碼：DECLARE mycardID char(19) EXECUTE proc_randCardID mycardID OUTPUTprint 產(chǎn)生的隨機(jī)卡號為：產(chǎn)生的隨機(jī)卡號為：+mycardIDGO 第五階段標(biāo)準(zhǔn)代碼演示-3 開戶的存儲過程cr

33、eate procedure proc_openAccount customerName char(8),PID char(18),telephone char(13) ,openMoney money,savingType char(8),address varchar(50)= AS DECLARE mycardID char(19),cur_customerID int -調(diào)用產(chǎn)生隨機(jī)卡號的存儲過程獲得隨機(jī)卡號調(diào)用產(chǎn)生隨機(jī)卡號的存儲過程獲得隨機(jī)卡號 EXECUTE proc_randCardID mycardID OUTPUT while exists(SELECT * FROM car

34、dInfo WHERE cardID=mycardID) EXECUTE proc_randCardID mycardID OUTPUT print 尊敬的客戶尊敬的客戶,開戶成功開戶成功!系統(tǒng)為您產(chǎn)生的隨機(jī)卡號為系統(tǒng)為您產(chǎn)生的隨機(jī)卡號為:+mycardID print 開戶日期開戶日期+convert(char(10),getdate(),111)+ 開戶金額開戶金額:+convert(varchar(20),openMoney)(未完待續(xù)未完待續(xù))IF not exists(select * from userInfo where PID=PID) INSERT INTO userInfo

35、(customerName,PID,telephone,address ) VALUES(customerName,PID,telephone,address) select cur_customerID=customerID from userInfo where PID=PID INSERT INTO cardInfo(cardID,savingType,openMoney,balance,customerID)VALUES(mycardID,savingType,openMoney,openMoney,cur_customerID)GO-調(diào)用存儲過程開戶調(diào)用存儲過程開戶EXEC proc

36、_openAccount 王五王五,334456889012678,2222-63598978,1000,活期活期,河南新鄉(xiāng)河南新鄉(xiāng) EXEC proc_openAccount 李四李四,2134456789123422221,定期定期 第六階段 第六階段30分鐘）： 利用SQL語句創(chuàng)建轉(zhuǎn)賬事務(wù)的存儲過程并測試 要求學(xué)員自己動手操作，教員巡視，解答學(xué)員提出的問題階段檢查 針對第三階段抽查學(xué)員的編碼結(jié)果 抽查學(xué)員編寫的完整代碼，要求學(xué)員上臺講解，并演示運(yùn)行結(jié)果 教員給出點(diǎn)評第六階段標(biāo)準(zhǔn)代碼演示-1 轉(zhuǎn)賬事務(wù)的存儲過程create procedure proc_tra

37、nsfer card1char(19),card2char(19),outmoney money AS begin tran print 開始轉(zhuǎn)賬開始轉(zhuǎn)賬,請稍后請稍后. DECLARE errors int set errors=0 EXEC proc_takeMoney card1,outmoney ,支取支取,123123 set errors=errors+error EXEC proc_takeMoney card2,outmoney ,存入存入 set errors=errors+error if (errors0) begin print 轉(zhuǎn)賬失??！轉(zhuǎn)賬失敗！ rollback