数据库课程设计银行储蓄管理系统

1、数据库系统概论课程设计报告设计题目：银行储蓄管理系统姓名：学号：班级：设计起止时间：需求分析：在对软件工程相关知识学习之后，我们对设计软件有了基本的认识和一些应 用技能。在数据库的课程设计中，我们计划做一个小型的银行储蓄管理系统，包 括了基本的存取转，和管理员对日常工作的管理功能。功能设计：(1) .客户：包括存款，取款，转账，查询余额，查看流水，密码修改功能(2) 管理员：新增用户，删除用户，查看用户，员工绩效，VIP用户判别， 储备金预警分析。3.功能流程图:银行管理系统户 客员 S3开户销户查看员工绩效P1 rnu rnuV労另资金储备金判别存款取款转账余额显示查看流水修改密码详细设计：

2、1.E-R图模型CpassCn amecustomCRmoneyCtimeCphoneoperate1bankstaffBmoneySphoneBn ame2.根据E-R图设计关系表（1）.银行信息表（bank）字段名字段类型及长度允许空主键说明Bidn char(9)noPK银行号Bn amen char (20)no银行名Bmoneynumeric (20,3)no银行余额（2）.客户信息表（custom）字段名字段类型及长度允许空主键说明Cidn char (9)noPK客户ID:Cn amen char (10)no客户姓名Cpassn char (10)no密码Ctimen char

3、 (20)no注册时间Bidn char (9)no所在银行行号 外码（Bank（ Bid）Crmoneynumeric (10,3)no账户余额Cphonen char (11)no客户电话.员工（管理员）表（staff ）字段名字段类型及长度允许空主键说明Sidn char (9)noPK员工IDSn amen char (10)no员工姓名:Spassn char (10)no登陆密码SItimen char (20)no入行时间Sphonen char (11)no联系电话|（4）.流水信息表字段名字段类型及长度允许空主键说明Oidn char (9)noPK流水号Cidn char

4、(9)no客户 ID 外码(Custom(Cid)Bidn char (9)no银行 ID 外码（Bank（ Bid）Sidn char (9)no员工 ID 外码（Staff （Sid）Otypesmalli ntno操作类型Otimen char (20)no操作时间Omoneynumeric (10,3)yes交易金额OBmoneynumeric (10,3)yes上次余额OAm oneynumeric (10,3)yes账户余额三个实体： ban k,staff,custom一个联系：operate关系图：KUN-PC.master - dbo.Staff列名SidSnanneEpas

5、wSEtime SphoneKUN-PC.master - dbo.Oprat#? CidBidV sidOtypeOtimeOmoneyOBrnoneyOAmcneyKUN-PC. master - dbo.Custom列窖CidCnameCpassCtimeBidCrmontyCphoneSQL语句：/*建表*/create table Bank(Bid n char(9) primary key,Bn ame n char(20) not nu II,Bmoney nu meric(20,3) not n ull)create table Custom(Cid nchar(9) not

6、null,Cn ame n char(10) not n ull,Cpass nchar(10) not null,Ctype small int not n ull,Ctime nchar(20) n ot null,Ccode nchar(18) not null,Bid n char(9) n ot null,Crmoney nu meric(10,3) not n ull,Cphone n char(11) not nu II, primary key(Cid), foreig n key(Bid) refere nces Ban k(Bid)外键，并对他进行级联更新*/on upda

7、te cascade,)create table Staff(Sid n char(9) primary key,Sn ame n char(10) not nu II,Spass nchar(10) not null,SItime nchar(20) not null,Spho ne n char(11) not n ull)create table Operate(Oid n char(9) not null,Cid n char(9) n ot null,Bid nchar(9) not null,Sid n char(9) n ot null,Otype nchar(10) not n

8、ull,Otime nchar(20) not null,Oflag small int not n ull,Omoney nu meric(10,3),OBmo ney numeric(10,3),OAmo ney numeric(10,3),primary key(Oid,Cid,Sid),foreig n key (Cid) refere nces Custom(Cid)联删除*/on delete cascade,foreig n key(Sid) referen ces Staff(Sid)引起冲突的时候，拒绝删除*/on delete no acti on,foreig n key

9、 (Bid) refere nces Ban k(Bid)on update cascade/*在客户表中以Bank表的主码作为一个/*在列级定义主码*/*以用户表主码为一个外键， 进行级/*以员工表的主码作为外键，当删除insert into Bank values(00001,中国银行小寨分行,10000)update Ba nk set Bn ame=中国银行经开分行where Bid=00002 select * from Bank;delete from Ba nk where Bid=1 or Bid=2;in sertintoCustomvalues(6505001,花花,111

10、,0,2012/12/10/08:26:0000001,1500insert into Staff values(7985000,自助服务,111,2002/01/07,12331654613)delete from Custom where Bid=2;insert into Operate values(2406002,6505001,00001,7985001,哈哈,2012 年 12 月 18 日 14 时 12 分,0,0,2900,2900)insert into Operate values(2406005,650

11、5007,00001,7985001,嘿嘿,2012 年 12 月 18 日 14 时 12 分,0,0,2900,2900) select * from Custom;select * from Operate;select * from Staff;select * from Bank;delete from Operatedrop table Customdrop table Bankdrop table Staff;drop table Operate;select * from Bank;delete from Operate where 0id=6505001drop view B

12、Mon ey;create view BMoneyasselect Omoneyfrom Operatewhere Oflag = 0 and Omoney2000 and Otype =取款；create view BlnMoneyasfrom Operatewhere Oflag = 0 and Omoney2000 and Otype=存款；create view VIPasselect Ctypefrom Customwhere Ctype=1;select coun t(*) from BMon ey;select coun t(*) from BlnMon ey;select co

13、u nt(*) from VIP;update Bank set Bmo ney=10000 where Bid=OOOO1：update Bank set Bmon ey=+bm on ey where Bid=+Bid+;update Bank set Bmo ney=10200.000000 where Bid=OOOO1程序代码：客户部分：a. void CClientDlg:OnButtonln()/ 存款函数/ TODO: Add your con trol no tificati on han dler code hereCI nDlg In Dlg;if (In Dlg.DoM

14、odal()=IDOK)double temp,temp1;ADOC onn ado;CStri ng sql = select * from Custom where Cname=+Cname+; _RecordsetPtr ResultSet = ado.GetRecordSet(_bstr_t)sql);CStri ng str = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Crmo ney);CStri ng bid = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Bid);CStri ng str4 = (LPCT

15、STR)(_bstr_t)ResultSet-GetCollect(Cid);CStri ng str2= str;/ 将交易前钱数暂存temp=atof(str); temp1=(double)I nDlg.m_ InNum;temp+=temp1;str.Format(%f,temp);CStri ng str3 = str;/ 暂存交易后金额sql = update Custom set Crmoney=+str+ +where Cname=+Cname+; ado.ExecuteSQL(_bstr_t)sql);sql = select coun t(*) num from Opera

16、te;ResultSet = ado.GetRecordSet(_bstr_t)sql);int num = atoi(LPCTSTR)(_bstr_t)ResultSet-GetCollect( nu m);str = 240600;CStri ng Oid,Cid,Sid,Bid,Otime,Omo ney,OAmo ney,OBmo ney; Oid.Format(%d,+num); Oid=str+Oid;Sid=7985001;Bid = bid; double temp2;sql = select * from Ba nk where Bid=+Bid+;ResultSet = a

17、do.GetRecordSet(_bstr_t)sql);CStri ng bmoney = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Bmo ney); temp2 = atof(bm on ey); temp2+=temp1; bmon ey.Format(%f,temp2); sql = update Bank set Bmo ney=+bmo ney+ where Bid=+Bid+; ado.ExecuteSQL(_bstr_t)sql);CString Otype =存款;CString m_time; CTime time;time = CTi

18、me:GetCurre ntTime();m_time = time.Format(%Y 年%m 月 %d 日 X);Otime = m_time; int flag = 0; Omo ney.Format(%f,temp1); OAm oney = str2;OBmo ney = str3;sql.Format(i nsertintoOperatevalues(%s,%s,%s,%s,%s,%s,%d,%s,%s,%s),Oid,str4,Bid,Sid,Otype,m_ time,flag,O mon ey,OAm on ey,OBm on ey);ado.ExecuteSQL(_bstr

19、_t)sql);ado.E xitCo nn ect(); b. void CClie ntDlg:O nButto nGet()/ 取款函数/ TODO: Add your con trol no tificati on han dler code hereCGetDlg GetDlg;if (GetDlg.DoModal()=IDOK)double temp,temp1;ADOC onn ado;CStri ng sql = select * from Custom where Cname=+Cname+; _RecordsetPtr ResultSet = ado.GetRecordSe

20、t(_bstr_t)sql);CStri ngstr=(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Crmo ney);CStri ng bid = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Bid);CStri ngstr4=(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Cid);CStri ng str2= str; 将交易前钱数暂存 temp=atof(str);temp1=(double)GetDlg.m_GetNum;if (temptemp1)temp-=temp1; str.For

21、mat(%f,temp); CStri ng str3 = str;暂存交易后金额sql = update Custom set Crmoney=+str+whereCname=+Cname+;ado.ExecuteSQL(_bstr_t)sql);sql = select coun t(*) num from Operate;ResultSet = ado.GetRecordSet(_bstr_t)sql) int num=atoi(LPCTSTR)(_bstr_t)ResultSet-GetCollect( nu m);str = 240600;CStri ng Oid,Cid,Sid,B

22、id,Otime,O mon ey,OAmo ney,OBmo ney; Oid.Format(%d,+num);Oid=str+Oid; Sid=7985001Bid = bid; double temp2; sql = select * from Bank where Bid=+Bid+;ResultSet = ado.GetRecordSet(_bstr_t)sql);CStri ngbmoney=(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Bmo ney);temp2 = atof(bm on ey); temp2-=temp1;bmo ney.For

23、mat(%f,temp2);sql = update Bank set Bmon ey=+bm on ey+whereBid=”+Bid+”;ado.ExecuteSQL(_bstr_t)sql);CString Otype =取款;CString m_time; CTime time;time = CTime:GetCurre ntTime();m_time = time.Format(%Y 年 %m 月 %d 日 %X);Otime = m_time; int flag = 0;Omon ey.Format(%f,temp1);OAmo ney = str2;OBmoney = str3;

24、 sql.Format(i nsert into Operate values(%s,%s,%s,%s,%s,%s,%d,%s,%s,%s),Oid,str4,Bid,Sid,Otype,m_ time,flag,O mon ey,OAm on ey,OBm on ey); ado.ExecuteSQL(_bstr_t)sql); ado.ExitConnect();elseAfxMessageBox(账户余额不足！)；c. void CClie ntDlg:O nButto nTurn()/ TODO: Add your con trol no tificati on han dler co

25、de hereCTur nDlg TurnDlg;if (TurnDlg.DoModal()=IDOK)ADOC onn ado;CStri ng sql = select * from Custom;_RecordsetPtr ResultSet = ado.GetRecordSet(_bstr_t)sql); int flag = 0;while (!ResultSet-adoEOF)CStri ngTCusId=(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Cid);CStri ngTCusM on=(LPCTSTR)(_bstr_t)ResultSet-

26、GetCollect(Crmo ney);收钱客户在操作前的余额CStri ng str5 = TCusMo n; TCusId.Remove();if (TCusld=TurnDlg.m_Turnld)double temp,temp1;sql = select * from Custom where Cname=+Cname+;ResultSet = ado.GetRecordSet(_bstr_t)sql);CStri ng str = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Crmo ney);CStri ng bid = (LPCTSTR)(_b

27、str_t)ResultSet-GetCollect(Bid);CStri ng str4 = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Cid);CStri ng str2= str;/ 将交易前钱数暂存temp=atof(str);temp1=(double)TurnDlg.m_TurnNum; if (temptemp1) flag = 1;temp-=temp1; 住客户钱数减少 str.Format(%f,temp);CStri ng str3 = str;/暂存交易后金额sql = update Custom set Crmoney=+str+

28、+where Cname=+Cname+; do.ExecuteSQL(_bstr_t)sql); temp=atof(TCusMo n);收钱客户钱数增加 temp+=temp1;TCusMo n.Format(%f,temp);sql = update Custom setCrmoney=”+TCusMon+ +where Cid=+TCusId+”; ado.ExecuteSQL(_bstr_t)sql); sql = select coun t(*) num from Operate; ResultSet = ado.GetRecordSet(_bstr_t)sql);int num

29、= atoi(LPCTSTR)(_bstr_t)ResultSet-GetCollect(” nu m);str=240600;CStri ngOid,Cid,Sid,Bid,Otime,Omo ney,OAmo ney,OBmo ney;int Otype; Oid.Format(%d,+num); Oid=str+Oid;Sid=7985001;Bid = bid; Otype = 2; CStri ng m_time; CTime time;time = CTime:GetCurrentTime();m_time= time.Format(%Y 年 %m 月 %d日 %X);Otime

30、= m_time; Omoney.Format(%f,temp1);OAm oney = str2; OBmoney = str3;sql.Format(i nsertintoOperatevalues(%s,%s,%s,%s,%d,%s,%s,%s,%s),Oid,str4,Bid,Sid,Otype,m_time ,Omon ey,OAm on ey,OBm on ey); ado.ExecuteSQL(_bstr_t)sql);sql.Format(i nsertintoOperatevalues(%s,%s,%s,%s,%d,%s,%s,%s,%s),TCusld,str4,Bid,S

31、id,Otype,m_t ime,O mon ey,str5,TCusM on); ado.ExecuteSQL(_bstr_t)sql);ado.ExitConnect();break; elseAfxMessageBox( 账户余额不足 r)； break; ResultSet-MoveNext();if(flag=0)AfxMessageBox(该账户不存在!); d. void CClientDlg:OnButtonHistoy()/ 历史记录查询/ TODO: Add your con trol no tificati on han dler code hereCShowHistor

32、y showDlg;showDlg.C name = Cn ame;showDlg.DoModal();e. void CClie ntDlg:O nButto nAlter()/ TODO: Add your con trol no tificati on han dler code hereCAlterDlg alteDlg;if(alteDlg.DoModal()=IDOK) if (alteDlg.m_AItem=0)if (alteDlg.m_Alter1=alteDlg.m_Alter2) ADOC onn ado; CStri ng sql = updateCustom setC

33、n ame=+alteDlg.m_Alter1+whereCname=+Cname+;ado.ExecuteSQL(_bstr_t)sql);AfxMessageBox(姓名修改成功!); else AfxMessageBox(两次姓名输入不一致，请重新输入); else if(alteDlg.m_AItem=1)if (alteDlg.m_Alter1=alteDlg.m_Alter2) ADOC onn ado;CString sql = update Custom set Cpass=+alteDlg.m_Alter1+ where Cname=+Cname+;ado.ExecuteSQ

34、L(_bstr_t)sql); AfxMessageBox(密码 修改成功!); elseAfxMessageBox(两次密码输入不一致，请重新输入)；elseif (alteDlg.m_Alter1=alteDlg.m_Alter2)if (alteDlg.m_Alter1.GetAt(0)=1&alteDlg.m_Alter1.GetLe ngth()=11)ADOC onn ado;CStri ngsql = updateCustom setCpho ne=+alteDlg.m_Alter1+ where Cname=+Cname+; ado.ExecuteSQL(_bstr_t)sql

35、); AfxMessageBox(电话号码修改成功!); else AfxMessageBox(电话号码格式不对!); elseAfxMessageBox(两次电话输入不一致，请重新输入!); 管理员部分：a. void CStaffDlg:OnButtonlnsert()/ 添加新成员/ TODO: Add your con trol no tificati on han dler code hereCln sertDlg in sertDlg;if (in sertDlg.DoModal()=IDOK)if (in sertDlg.m_Pho ne.GetAt(0)=1&in sertDl

36、g.m_Pho ne.GetLe ngth()=11)ADOC onn ado;CStri ngsql = select coun t(*) Num from Custom whereCcode=+insertDlg.m_Code+;_RecordsetPtr ResultSet = ado.GetRecordSet(_bstr_t)sql);int Num = atoi(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Num);if (Num=0)CStri ng str = 650500;sql = select coun t(*) num from Custo

37、m;ResultSet = ado.GetRecordSet(_bstr_t)sql);Num = atoi(LPCTSTR)(_bstr_t)ResultSet-GetCollect( nu m);CStri ng Cid,Cpass,Bid,Pm on ey; double dou;Cid.Format(%d,+Num); Cid = str+Cid;Cpass = 000000;CStri ng m_time; Bid= 00001;CTime time; time = CTime:GetCurre ntTime();m_time = time.Format(%Y 年 %m 月 %d 日

38、 X);dou = (double)i nsertDlg.m_Pm on ey;Pmon ey.Format(%f,dou);/Otime = m_time;sql.Format(i nsertin toCustomvalues(%s,%s,%s,%s,%s,%s,%s,%s),Cid,i nsertDlg.m_Name,Cpass,m_time,i n sertDlg.m_Code,Bid,Pm on ey,i nsertDlg.m_Ph on e);ado.ExecuteSQL(_bstr_t)sql);sql = select coun t(*) num from Operate;Res

39、ultSet = ado.GetRecordSet(_bstr_t)sql);Num = atoi(LPCTSTR)(_bstr_t)ResultSet-GetCollect( nu m);CString Oid,OAmoney,Otype; Otype =新注册;Oid.Format(%d,+Num);str = 240600;Oid = str+Oid; Bid= 00001;i nt flag = 0; OAmoney = 0;sql.Format(i nsertintoOperatevalues(%s,%s,%s,%s,%s,%s,%d,%s,%s,%s),Oid,Cid,Bid,Si

40、d,Otype,m_ time,flag,Pm on ey,OAm on ey,Pm on ey); ado.ExecuteSQL(_bstr_t)sql); AfxMessageBox(新增客户成功); elseAfxMessageBox(该证件号已经注册); b. void CStaffDlg:OnButtonDelete()/ 注销客户/ TODO: Add your con trol no tificati on han dler code hereCDelDlg delDlg;if (delDlg.DoModal()=IDOK)if (delDlg.m_Delld1=delDlg.m

41、_Delld2)ADOC onn ado;coun t(*) Num from Custom where Cid=+delDlg.m_DelId1+;_RecordsetPtr ResultSet = ado.GetRecordSet(_bstr_t)sql);int Num = atoi(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Num);if (Num!=0)CStri ng str =650500;sql= delete from Custom whereCid=+delDlg.m_DelId1+”;ado.ExecuteSQL(_bstr_t)sql)

42、; CStri ng Bid,Cid;Cid = 6505000;CStri ng m_time;CTime time; time = CTime:GetCurre ntTime();m_time = time.Format(%Y 年%m 月 %d 日 %X);sql = select count(*) num from Operate;ResultSet = ado.GetRecordSet(_bstr_t)sql);Num = atoi(LPCTSTR)(_bstr_t)ResultSet-GetCollect(” nu m);CStri ngOid,OAmo ney,Otype;Otyp

43、e =注 销 用 户”;Oid.Format(%d,+Num);tr = 24O6OO;Oid = str+Oid; Bid= 00001;i nt flag = 2; OAm oney = 0; sql.Format(i nsertintoOperatevalues(%s,%s,%s,%s,%s,%s,%d,%s,%s,%s),Oid,Cid,Bid,Sid,Otype,m_ time,flag,OAm on ey,OAm on ey,OAm on ey); ado.ExecuteSQL(_bstr_t)sql); AfxMessageBox(注销客户成功); elseAfxMessageB

44、ox(不存在该用户); else AfxMessageBox(两次输入用户ID不一致，请重新输入！);c. void CStaffDlg:O nButto nResher()查询客户/ TODO: Add your con trol no tificati on han dler code hereCAlterDlg altDlg; if (altDlg.DoModal()=IDOK)if (altDlg.m_AItem=0) if (altDlg.m_Alter1=altDlg.m_Alter2) ADOC onn ado;CString sql = update Staff set Sna

45、me=+altDlg.m_Alter1+whereSname=+Sname+;ado.ExecuteSQL(_bstr_t)sql);AfxMessageBox(姓名修改成功!);elseAfxMessageBox(两次姓名输入不一致，请重新输入!); else if(altDlg.m_AItem=1)if (altDlg.m_Alter1=altDlg.m_Alter2)ADOC onn ado;CString sql = update Custom set Spass=+altDlg.m_Alter1+ where Sname=+Sname+;ado.ExecuteSQL(_bstr_t)

46、sql); AfxMessageBox(密码修 改成功!); elseAfxMessageBox(两次密码输入不一致，请重新输入); elseif (altDlg.m_Alter1=altDlg.m_Alter2)if (altDlg.m_Alter1.GetAt(0)=1&altDlg.m_Alter1.GetLe ngth()=11)ADOC onn ado;CString sql = update Custom set Sphone=+altDlg.m_Alter1+whereSname=+Sname+;ado.ExecuteSQL(_bstr_t)sql);AfxMessageBox(

47、电话号码修改成功!); elseAfxMessageBox(电话号码格式不对!); elseAfxMessageBox(两次电话输入不一致，请重新输入!); d. void CStaffDlg:OnButtonMoneyctrl()/ 资金管理/ TODO: Add your con trol no tificati on han dler code hereCSerchDlg serDlg;if (serDlg.DoModal()=IDOK)ADOC onn ado;CStri ng sql = select cou nt(*) Num from Custom where Cid=+serD

48、lg.m_Cid+;_RecordsetPtr ResultSet = ado.GetRecordSet(_bstr_t)sql);int Num = atoi(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Num);if (Num!=0)CCusI nfoDIg cusDlg;cusDlg.Cid = serDlg.m_Cid ADOC onn ado;CStri ng sql = select * from Custom where Cid=+cusDlg.Cid+;_RecordsetPtr ResultSet = ado.GetRecordSet(_bst

49、r_t)sql);cusDlg.m_C name = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(C name); cusDlg.m_Cid = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Cid);cusDlg.m_Code = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Ccode); cusDlg.m_Pho ne = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Cpho ne); cusDlg.m_Rmo ney = (LPCTSTR)(_bstr_t

50、)ResultSet-GetCollect(Crmo ney); cusDlg.DoModal();ado.ExitCo nn ect();elseAfxMessageBox(不存在该客户！)；e. void CStaffDlg:OnButtonAlter() / 信息修改CMon eyDlg mon Dlg; ADOC onn ado;CStri ng sql = select cou nt(*) Num from BMo ney ;_RecordsetPtr ResultSet = ado.GetRecordSet(_bstr_t)sql);mo nDlg.m_GetNum = atoi(

51、LPCTSTR)(_bstr_t)ResultSet-GetCollect(Num); sql = select cou nt(*) Num from BI nMon ey;ResultSet = ado.GetRecordSet(_bstr_t)sql);mon Dlg.mnNum = atoi(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Num); sql = select cou nt(*) Num from VIP;ResultSet = ado.GetRecordSet(_bstr_t)sql);mon Dlg.m_VIPNum= atoi(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Num); sql = select * from Bank where Bid=OOOO1：ResultSet = ado.GetRecordSet(_bstr_t)sql);mo nDlg.m_Cash = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Bmo ney); ado.ExitC onn ect();mon Dlg.DoModal();f. void CStaffDlg:OnButtonAnyse