信息0801081204112黃露銀行儲(chǔ)蓄管理數(shù)據(jù)庫課程設(shè)計(jì)

1、 數(shù)據(jù)庫課程設(shè)計(jì)·銀行儲(chǔ)蓄管理系統(tǒng)數(shù)據(jù)庫原理及應(yīng)用課程設(shè)計(jì)報(bào)告 專業(yè)： 信息管理與信息系統(tǒng) 班級(jí)： 信息0801 學(xué)號(hào)： 081204112 姓名： 黃露 題目名稱：銀行儲(chǔ)蓄業(yè)務(wù)管理系統(tǒng) 完成日期： 2011.3.11 目錄一、系統(tǒng)定義.3二、需求分析.3（一）系統(tǒng)綜合需求（二）系統(tǒng)邏輯模型1數(shù)據(jù)流圖：2數(shù)據(jù)字典三、系統(tǒng)設(shè)計(jì).5（一）概念結(jié)構(gòu)設(shè)計(jì)E-R圖：（二）邏輯結(jié)構(gòu)設(shè)計(jì)四、詳細(xì)設(shè)計(jì)6（一）開發(fā)平臺(tái)及工具（二）數(shù)據(jù)庫分析五、源程序清單13六、設(shè)計(jì)心得18一、系統(tǒng)定義：銀行是一個(gè)國家正常運(yùn)轉(zhuǎn)必不可缺的機(jī)構(gòu)，當(dāng)今社會(huì)，幾乎是每個(gè)人都會(huì)涉及到儲(chǔ)蓄業(yè)務(wù)，為此，為方便用戶查詢和使用各種業(yè)務(wù)，

2、可用計(jì)算機(jī)為工具對(duì)查詢管理為一體的各種服務(wù)。當(dāng)然，這樣的一個(gè)銀行儲(chǔ)蓄業(yè)務(wù)系統(tǒng)就應(yīng)運(yùn)而生了。本系統(tǒng)是一個(gè)簡單的儲(chǔ)蓄系統(tǒng)，可以對(duì)儲(chǔ)戶的信息進(jìn)行查詢修改以及刪除。二、需求分析：（一）系統(tǒng)綜合需求 本系統(tǒng)是銀行用戶的存取款系統(tǒng)。主要功能是管理各個(gè)用戶存取款的相關(guān)數(shù)據(jù)。 儲(chǔ)戶填寫的存款單或取款單輸入系統(tǒng)，如果是存款，則系統(tǒng)記錄存款人姓名，住址（或電話號(hào)碼），身份證號(hào)碼，存款類型，存款金額，存款日期等信息，并打印存單給儲(chǔ)戶；如果是取款，則需要輸入帳號(hào)、取款金額等信息，核對(duì)正確后系統(tǒng)打印出清單給儲(chǔ)戶。（二）系統(tǒng)邏輯模型1數(shù)據(jù)流圖：圖1-1 系統(tǒng)數(shù)據(jù)流圖2數(shù)據(jù)字典數(shù)據(jù)名字：注冊(cè)申請(qǐng)表數(shù)據(jù)來源：儲(chǔ)戶數(shù)據(jù)目的：儲(chǔ)

3、戶信息數(shù)據(jù)組成：姓名+密碼+住址+身份證號(hào)碼數(shù)據(jù)名字：儲(chǔ)戶信息表數(shù)據(jù)來源：儲(chǔ)戶數(shù)據(jù)目的：身份驗(yàn)證數(shù)據(jù)組成：姓名+密碼+住址+身份證號(hào)碼+現(xiàn)有金額數(shù)據(jù)名字：利息清單數(shù)據(jù)來源：系統(tǒng)數(shù)據(jù)目的：儲(chǔ)戶數(shù)據(jù)組成：利息+姓名+取款時(shí)間數(shù)據(jù)名字：存單數(shù)據(jù)來源：存款信息表數(shù)據(jù)目的：儲(chǔ)戶數(shù)據(jù)組成：姓名+到期時(shí)間+存入類型+存入時(shí)間+利率+存入金額 數(shù)據(jù)名字：取款單數(shù)據(jù)來源：儲(chǔ)戶 數(shù)據(jù)目的：儲(chǔ)戶信息表數(shù)據(jù)組成：姓名+到期時(shí)間+存入類型+存入時(shí)間+利率+取款金額+身份證號(hào)數(shù)據(jù)名字：存款單數(shù)據(jù)來源：儲(chǔ)戶數(shù)據(jù)目的： 存款信息表數(shù)據(jù)組成：姓名+到期時(shí)間+存入類型+存入時(shí)間+利率+存入金額+身份證號(hào)表1.1 系統(tǒng)數(shù)據(jù)字典三、

4、系統(tǒng)設(shè)計(jì)：（一）概念結(jié)構(gòu)設(shè)計(jì)E-R圖：（二）邏輯結(jié)構(gòu)設(shè)計(jì)四、詳細(xì)設(shè)計(jì)（一）開發(fā)平臺(tái)及工具l 開發(fā)工具M(jìn)icrosoft Visual C+ 6.0l DBMS：Microsoft SQL Server 2000l 建模工具：Microsoft Visio,word編輯器說明：為了方便的實(shí)現(xiàn)系統(tǒng)以及個(gè)人熟悉程度的因素，選擇了VC+ 6.0的開發(fā)平臺(tái)。在Windows平臺(tái)上我們所學(xué)過的就只有MS SQL Server了，所以采用SQL Server2000來進(jìn)行開發(fā)。（二）銀行儲(chǔ)蓄業(yè)務(wù)數(shù)據(jù)庫分析1）本系統(tǒng)由三張表組成，具體如下：表名屬性(字段)名儲(chǔ)戶表賬號(hào)、身份證號(hào)、姓名、性別、身高、地址、存款余

5、額存款單表存款單號(hào)、金額、存款方式、賬號(hào)、存款日期取款單表取款單號(hào)、金額、取款方式、賬號(hào)、取款日期系統(tǒng)表基本信息2）表間關(guān)系如下：一個(gè)存款單或取款單屬于一個(gè)用戶，一個(gè)用戶可以擁有多次存款單或取款單。用戶通過存款操作與存款單建立聯(lián)系，通過取款操作與取款單建立聯(lián)系。3）數(shù)據(jù)準(zhǔn)備前期準(zhǔn)備工作：以system賬戶登錄，創(chuàng)建新賬戶create user dy identified by lyjn;grant dba to dy;connect dy/dyorcl;各個(gè)表的具體信息和創(chuàng)建表的代碼如下：儲(chǔ)戶表(luser)字段(屬性)含義字段(屬性)名字段(屬性)類型賬號(hào)acnumVARCHAR(5)身份證

6、號(hào)idNUMBER姓名nameVARCHAR2(20)性別sexVARCHAR2(2)身高h(yuǎn)eightNUMBER(3,2)地址addressVARCHAR2(20)存款余額(億)Balance(billion)INT創(chuàng)建儲(chǔ)戶表的代碼如下：CREATE TABLE luser(acnum VARCHAR2(5) NOT NULL ,id NUMBER NOT NULL ,name VARCHAR2(20) NOT NULL ,sex VARCHAR2(2) CHECK(sex IN ('男','女') ) , height NUMBER(3,2) NOT NUL

7、L,address VARCHAR2(20) NOT NULL ,balance INT NOT NULL );ALTER TABLE luser添加主鍵約束ADD CONSTRAINT XPKluser PRIMARY KEY (acnum);存款單表(depositslip)字段(屬性)含義字段(屬性)名字段(屬性)類型存款單號(hào)dps_numNUMBER賬號(hào)acnumVARCHAR2(5)存款時(shí)間dps_timeTIMESTAMP金額(億)moneyINT存款方式dps_wayVARCHAR2(10)創(chuàng)建存款單表的代碼如下：CREATE TABLE depositslip(dps_num

8、NUMBER NOT NULL ,money INT CHECK(money>=0) ,dps_way VARCHAR2(10) NOT NULL ,acnum VARCHAR2(5) NOT NULL ,dps_time TIMESTAMP NULL );ALTER TABLE depositslip 添加主鍵約束ADD CONSTRAINT XPKdepositslip PRIMARY KEY (dps_num,acnum);ALTER TABLE depositslip在depositslip表中的acnum字段中添加外鍵約束ADD (CONSTRAINT R_5 FOREIGN

9、KEY (acnum) REFERENCES luser(acnum);取款單表(drawslip)字段(屬性)含義字段(屬性)名字段(屬性)類型取款單號(hào)dw_numNUMBER賬號(hào)acnumVARCHAR2(5)取款時(shí)間dw_timeTIMESTAMP金額(億)MoneyINT取款方式dw_wayVARCHAR2(10)創(chuàng)建取款單表的代碼如下：CREATE TABLE drawslip(dw_num NUMBER NOT NULL ,money INT CHECK(money>=0),dw_way VARCHAR2(10) NOT NULL ,acnum VARCHAR2(5) NOT

10、 NULL ,dw_time TIMESTAMP NULL );ALTER TABLE drawslip 添加主鍵約束ADD CONSTRAINT XPKdrawslip PRIMARY KEY (dw_num,acnum);ALTER TABLE drawslip 在drawslip表中的acnum字段中添加外鍵約束ADD (CONSTRAINT R_6 FOREIGN KEY (acnum) REFERENCES luser(acnum);4）插入(insert)數(shù)據(jù)1、插入儲(chǔ)戶表(luser)數(shù)據(jù)insert into luser (ACNUM, ID, NAME, SEX,HEIGHT

11、, ADDRESS, BALANCE)values ('00001', 3422101, '劉忠田', '男',1.72 ,'北京市朝陽區(qū)', 345);insert into luser (ACNUM, ID, NAME, SEX,HEIGHT, ADDRESS, BALANCE)values ('00002', 3422102, '周正義', '男',1.78, '天津市寶坻區(qū)', 123);insert into luser (ACNUM, ID, NAME,

12、SEX,HEIGHT, ADDRESS, BALANCE)values ('00003', 3422103, '陳光標(biāo)', '男',1.80, '南京市新街口', 333);insert into luser (ACNUM, ID, NAME, SEX,HEIGHT, ADDRESS, BALANCE)values ('00004', 3422104, '張茵', '女',1.65, '上海市黃浦區(qū)', 234);insert into luser (ACNUM, I

13、D, NAME, SEX,HEIGHT, ADDRESS, BALANCE)values ('00005', 3422105, '陳麗華', '女',1.70, '香港市旺角區(qū)', 111);2、插入存款單表(depositslip)數(shù)據(jù)insert into depositslip (DPS_NUM, ACNUM, MONEY, DPS_TIME, DPS_WAY)values (98001,'00001', 120,to_date('08-08-2009','dd-mm-yyyy'

14、;), '整存整取');insert into depositslip (DPS_NUM, ACNUM, MONEY, DPS_TIME, DPS_WAY)values (98002,'00001', 300,to_date('10-08-2009','dd-mm-yyyy'), '整存整取');insert into depositslip (DPS_NUM, ACNUM, MONEY, DPS_TIME, DPS_WAY)values (98003,'00002', 100,to_date(&#

15、39;12-08-2009','dd-mm-yyyy'), '整存零取');insert into depositslip (DPS_NUM, ACNUM, MONEY, DPS_TIME, DPS_WAY)values (98004,'00002', 150,to_date('12-09-2009','dd-mm-yyyy'), '整存零取');insert into depositslip (DPS_NUM, ACNUM, MONEY, DPS_TIME, DPS_WAY)values

16、(98005,'00003', 200,to_date('15-08-2009','dd-mm-yyyy'), '零存整取');insert into depositslip (DPS_NUM, ACNUM, MONEY, DPS_TIME, DPS_WAY)values (98006,'00003', 100,to_date('14-09-2009','dd-mm-yyyy'), '零存整取');insert into depositslip (DPS_NUM, A

17、CNUM, MONEY, DPS_TIME, DPS_WAY)values (98007,'00003', 80,to_date('23-09-2009','dd-mm-yyyy'), '零存整取');insert into depositslip (DPS_NUM, ACNUM, MONEY, DPS_TIME, DPS_WAY)values (98008,'00004', 80,to_date('23-08-2009','dd-mm-yyyy'), '零存零取')

18、;insert into depositslip (DPS_NUM, ACNUM, MONEY, DPS_TIME, DPS_WAY)values (98009,'00004', 180,to_date('24-09-2009','dd-mm-yyyy'), '零存零取');insert into depositslip (DPS_NUM, ACNUM, MONEY, DPS_TIME, DPS_WAY)values (98010,'00005', 190,to_date('24-08-2009',

19、'dd-mm-yyyy'), '通知存款');insert into depositslip (DPS_NUM, ACNUM, MONEY, DPS_TIME, DPS_WAY)values (98011,'00005', 210,to_date('30-09-2009','dd-mm-yyyy'), '通知存款');3、插入取款單表(drawslip)數(shù)據(jù)insert into drawslip (DW_NUM, ACNUM, MONEY, DW_TIME, DW_WAY)values (5200

20、1,'00001', 75,to_date('08-08-2010','dd-mm-yyyy'), '柜臺(tái)');insert into drawslip (DW_NUM, ACNUM, MONEY, DW_TIME, DW_WAY)values (52002,'00002', 27,to_date('08-09-2010','dd-mm-yyyy'), '取款機(jī)');insert into drawslip (DW_NUM, ACNUM, MONEY, DW_TIME

21、, DW_WAY)values (52003,'00002',100,to_date('12-10-2010','dd-mm-yyyy'), '柜臺(tái)');insert into drawslip (DW_NUM, ACNUM, MONEY, DW_TIME, DW_WAY)values (52004,'00003',47,to_date('15-08-2010','dd-mm-yyyy'), '取款機(jī)');insert into drawslip (DW_NUM, A

22、CNUM, MONEY, DW_TIME, DW_WAY)values (52005,'00004',26,to_date('12-09-2010','dd-mm-yyyy'), '取款機(jī)');insert into drawslip (DW_NUM, ACNUM, MONEY, DW_TIME, DW_WAY)values (52006,'00005',100,to_date('23-09-2010','dd-mm-yyyy'), '柜臺(tái)');insert into

23、 drawslip (DW_NUM, ACNUM, MONEY, DW_TIME, DW_WAY)values (52007,'00005',150,to_date('10-10-2010','dd-mm-yyyy'), '柜臺(tái)');insert into drawslip (DW_NUM, ACNUM, MONEY, DW_TIME, DW_WAY)values (52008,'00005',39,to_date('31-10-2010','dd-mm-yyyy'), '取

24、款機(jī)');5）索引建立索引遵循的規(guī)律：1.建立在where子句經(jīng)常引用的列上，2.經(jīng)常需要排序的列上，3.連接屬性列上等在存款單表的存款單號(hào)字段和賬號(hào)字段建立組合索引CREATE UNIQUE INDEX XPKdepositslip ON depositslip(dps_num ASC,acnum ASC);在存款單表的賬號(hào)字段建立索引CREATE INDEX XIF1depositslip ON depositslip(acnum ASC);在取款單表的取款單號(hào)字段和賬號(hào)字段建立組合索引CREATE UNIQUE INDEX XPKdrawslip ON drawslip(dw_n

25、um ASC,acnum ASC);在取款單表的賬號(hào)字段建立索引CREATE INDEX XIF1drawslip ON drawslip(acnum ASC);在儲(chǔ)戶表的賬戶字段上建立索引，方便查詢CREATE UNIQUE INDEX XPKluser ON luser(acnum ASC);6）觸發(fā)器1、插入存款，對(duì)應(yīng)儲(chǔ)戶表的相應(yīng)儲(chǔ)戶的余額(balance)增加插入記錄包含的金額數(shù)(money)CREATE OR REPLACE TRIGGER OperatingDps AFTER INSERT ON depositslip FOR EACH ROWDECLAREn_ac VARCHAR

26、2(5);n_mo INT;BEGINn_ac:=:new.acnum;n_mo:=:new.money;update luser set balance=balance+n_mo where luser.acnum=n_ac;END OperatingDps;2、刪除存款，對(duì)應(yīng)儲(chǔ)戶表的相應(yīng)儲(chǔ)戶的余額(balance)減去所刪除記錄的金額數(shù)(money)CREATE OR REPLACE TRIGGER OperatingDps2 AFTER DELETE ON depositslip FOR EACH ROWDECLAREo_ac VARCHAR2(5);o_mo INT;BEGINo_ac

27、:=:old.acnum;o_mo:=:old.money;update luser set balance=balance-o_mo where luser.acnum=o_ac;END OperatingDps2;3、插入取款，對(duì)應(yīng)儲(chǔ)戶表的相應(yīng)儲(chǔ)戶的余額(balance)減少插入記錄包含的金額數(shù)(money)CREATE OR REPLACE TRIGGER OperatingDw AFTER INSERT ON drawslip FOR EACH ROWDECLAREn_ac VARCHAR2(5);n_mo INT;BEGINn_ac:=:new.acnum;n_mo:=:new.mo

28、ney;update luser set balance=balance-n_mo where luser.acnum=n_ac;END OperatingDw;4、刪除取款，對(duì)應(yīng)儲(chǔ)戶表的相應(yīng)儲(chǔ)戶的余額(balance)加上所刪除記錄的金額數(shù)(money)CREATE OR REPLACE TRIGGER OperatingDw2 AFTER DELETE ON drawslip FOR EACH ROWDECLAREo_ac VARCHAR2(5);o_mo INT;BEGINo_ac:=:old.acnum;o_mo:=:old.money;update luser set balance

29、=balance+o_mo where luser.acnum=o_ac;END OperatingDw2;5、插入的取款金額(money)大于對(duì)應(yīng)用戶的余額(balance)時(shí)，系統(tǒng)報(bào)錯(cuò)CREATE OR REPLACE TRIGGER OperatingDw3 AFTER insert ON drawslip FOR EACH ROWDECLAREn_ac VARCHAR2(5);n_mo INT;n_ba INT;BEGINn_ac:=:new.acnum;n_mo:=:new.money;select balance into n_ba from luser where acnum=n

30、_ac;if n_mo>n_ba thenRAISE_APPLICATION_ERROR(-20000, '存款余額不足！請(qǐng)重新插入存款！');end if;END OperatingDw3;五、源程序清單#include<iostream>#include<fstream>#include<string>using namespace std;const int Max=100;void Wait();class User /用戶類的聲明public: User() CurrentAcount=0; /初始化當(dāng)前的賬戶 void Re

31、sert1(User *u,double A) u->CurrentAcount=u->CurrentAcount+A; void Resert2(User *u,double A) u->CurrentAcount=u->CurrentAcount-A; char *getname() /取得用戶名 return name; long getNum() /取得帳戶號(hào) return Num; double getCurrentAcount() return CurrentAcount; void output() /輸出相關(guān)信息 cout<<"用戶

32、名："<<name<<endl; cout<<"賬 號(hào)："<<Num<<endl; cout<<"余 額："<<CurrentAcount<<endl; void addUser(char *name,long Num) strcpy(this->name,name); this->Num=Num; void Deposit(User *u,double Acount) /存款 u->Resert1(u,Acount); void

33、out(User *u,double acount) /取款 if(acount>u->getCurrentAcount() cout<<"余額不足.n" else cout<<"請(qǐng)及時(shí)取走你的鈔票.n" u->Resert2(u,acount); void Locate(User *u) /查詢余額 u->output(); private: char name20; long Num; double CurrentAcount; ;class DoBank /銀行管理類的聲明public: DoBank

34、() User a; top=-1; fstream file("f:User.txt",ios:in); /以讀的方式打開文件 while(1) file.read(char*)&a,sizeof(a); if(!file) break; top+; btop=a; file.close(); /關(guān)閉文件 void Work(); void addUser(char *name,int Num) User *p=equal(Num); if(p=NULL) top+; btop.addUser(name,Num); cout<<"成功載入nn

35、n" else cout<<"該用戶已存在.n" User *equal(long Num) int i; for(i=0;i<top;i+) if(bi.getNum()=Num) return &bi; return NULL; DoBank() /析構(gòu)函數(shù) fstream file("f:User.txt",ios:out); /以寫的方式打開文件 for (int i=0;i<=top;i+) file.write(char *)&bi,sizeof(bi); file.close(); /關(guān)閉文

36、件 private: User bMax; int top;void Wait() int i=0; while(i<999999999) i+; void Menu() /顯示功能菜單 cout<<"nnnn" cout<<" 1、用戶維護(hù)n" cout<<" 2、存款n" cout<<" 3、取款n"cout<<" 4、查詢n" cout<<" 0、退出n"void DoBank:Work()

37、Menu(); User *us; User *us1; char name20; long Num; long Num1; long TAcount; char number; cin>>number; while(number!='0') if(int)number<47|(int)number>53) cout<<"對(duì)不起，你的輸入有誤。n" else switch(number) /輸入功能代號(hào) case '1': cout<<"請(qǐng)輸入姓名:" cin>>

38、name; cout<<"請(qǐng)輸入賬號(hào):" cin>>Num; addUser(name,Num); break; case '2': cout<<"請(qǐng)輸入賬號(hào)" cin>>Num; us=equal(Num); if(us=NULL) cout<<"不存在該用戶.n" else cout<<"請(qǐng)輸入存款金額：" cin>>TAcount; us->Deposit(us,TAcount); cout<<"成功存入.nnn" break; case '3': cout<<"請(qǐng)輸入賬號(hào)：" cin>>Num; us=equal(Num); if(us=NULL) cout<<"不存在該用戶.n" else cout<<"請(qǐng)輸入取款金額：" cin>>TAcount; us->out(us,TAcount); cout<<"nnn" bre