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

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

2、 转账 余额显示 开户 详细设计： 图模型1.E-R CidCpassBidCtimeCnameCphonecustomCRmoney1SpassSphoneBioperatSnamstafbanBmoneSiBnamSItime 2.根据E-R图设计关系表 (1)银行信息表ban 字段名 字段类型及长度 允许空 主键 说明 银行号 PK Bid no (9) nchar 银行名Bname no (20) nchar 银行余额 no Bmoney (20,3)numeric （ 2）.客户信息表（custom） 说明 主键字段类型及长度 允许空 字段名 客户 PK Cid no ID (9)n

3、char Cname no 客户姓名 (10) nchar Cpass 密码 no (10) nchar no 注册时间 Ctime (20)nchar 所在银行行号Bid no (9)nchar外码（Bank（Bid） no Crmoney 账户余额 (10,3)numeric Cphone no 客户电话 (11)nchar ）staff (3).员工（管理员）表（ 说明 字段名字段类型及长度允许空 主键 Sid 员工 no ID PK (9)nchar 员工姓名 no Sname (10)nchar Spass 登陆密码 no (10)nchar 入行时间SItime no (20)nc

4、har Sphone 联系电话 no (11)nchar 流水信息表 (4). 字段名 字段类型及长度 允许空 主键 说明 Oid (9)nchar no PK 流水号 Cid (9)nchar no 客户ID 外码（Custom（Cid） Bid (9)nchar no 银行ID 外码（Bank（Bid） Sid (9)nchar no 员工ID 外码（Staff（Sid） Otype smallint no 操作类型 Otime (20)nchar no 操作时间 Omoney numeric(10,3) yes 交易金额 OBmoney (10,3) numeric yes 上次余额 O

5、Amoney (10,3) numeric yes 账户余额 bank,staff,custom 三个实体：operate 一个联系： 关系图： SQL语句： /*建表*/ create table Bank (Bid nchar(9) primary key, Bname nchar(20) not null, Bmoney numeric(20,3) not null ) create table Custom ( Cid nchar(9) not null, Cname nchar(10) not null, Cpass nchar(10) not null, Ctype smallin

6、t not null, Ctime nchar(20) not null, Ccode nchar(18) not null, Bid nchar(9) not null, Crmoney numeric(10,3) not null, Cphone nchar(11) not null, primary key(Cid), foreign key(Bid) references Bank(Bid) /*在客户表中以Bank表的主码作为一个 */ 外键，并对他进行级联更新 on update cascade, ) create table Staff /*在列级定义主码*/ (Sid ncha

7、r(9) primary key, Sname nchar(10) not null, Spass nchar(10) not null, SItime nchar(20) not null, Sphone nchar(11) not null ) create table Operate (Oid nchar(9) not null, Cid nchar(9) not null, Bid nchar(9) not null, Sid nchar(9) not null, Otype nchar(10) not null, Otime nchar(20) not null, Oflag sma

8、llint not null, Omoney numeric(10,3), OBmoney numeric(10,3), OAmoney numeric(10,3), primary key(Oid,Cid,Sid), 进行级/* 以用户表主码为一个外键， foreign key (Cid) references Custom(Cid) */ 联删除 on delete cascade, foreign key(Sid) references Staff(Sid) 以员工表的主码作为外键，当删除/* */ 引起冲突的时候，拒绝删除on delete no action, foreign key

9、 (Bid) references Bank(Bid) on update cascade ) insert into Bank values(00001,中国银行小寨分行,10000) update Bank set Bname=中国银行经开分行 where Bid=00002 select * from Bank; delete from Bank where Bid=1 or Bid=2; insert into Custom values(6505001,花花,111,0,2012/12/10/08:26:0000001,1500,1434567

10、8912) insert 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,6505007,00001,7985001,嘿嘿,2012年12月18日14时12分,0,0,2900,2900) select * f

11、rom Custom; select * from Operate; select * from Staff; select * from Bank; delete from Operate drop table Custom drop table Bank drop table Staff; drop table Operate; select * from Bank; delete from Operate where Oid=6505001 drop view BMoney; create view BMoney as select Omoney from Operate where O

12、flag = 0 and Omoney2000 and Otype = 取款; create view BInMoney as from Operate where Oflag = 0 and Omoney2000 and Otype=存款; create view VIP as select Ctype from Custom where Ctype=1; select count(*) from BMoney; select count(*) from BInMoney; select count(*) from VIP; update Bank set Bmoney=10000 wher

13、e Bid=00001; update Bank set Bmoney=+bmoney where Bid=+Bid+; update Bank set Bmoney=10200.000000 where Bid=00001 程序代码： 客户部分： a. void CClientDlg:OnButtonIn() /存款函数 / TODO: Add your control notification handler code here CInDlg InDlg; if (InDlg.DoModal()=IDOK) double temp,temp1; ADOConn ado; CString s

14、ql = select * from Custom where Cname=+Cname+; _RecordsetPtr ResultSet = ado.GetRecordSet(_bstr_t)sql); CString str = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Crmoney); CString bid = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Bid); CString str4 = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Cid); CString str2=

15、str; /将交易前钱数暂存 temp=atof(str); temp1=(double)InDlg.m_InNum; temp+=temp1; str.Format(%f,temp); CString str3 = str; /暂存交易后金额 sql = %update Custom set Crmoney=+str+ +where Cname=+Cname+; ado.ExecuteSQL(_bstr_t)sql); sql = select count(*) num from Operate; ResultSet = ado.GetRecordSet(_bstr_t)sql); int

16、num = atoi(LPCTSTR)(_bstr_t)ResultSet-GetCollect( um); str = ?600;CString Oid,Cid,Sid,Bid,Otime,Omoney,OAmoney,OBmoney; Oid.Format(%d,+num); Oid=str+Oid; Sid=_x0007_985001;Bid = bid; double temp2; sql = select * from Bank where Bid=+Bid+; ResultSet = ado.GetRecordSet(_bstr_t)sql); CString bmoney = (

17、LPCTSTR)(_bstr_t)ResultSet-GetCollect(Bmoney); temp2 = atof(bmoney); temp2+=temp1; bmoney.Format(%f,temp2); sql = %update Bank set Bmoney=+bmoney+ where Bid=+Bid+; ado.ExecuteSQL(_bstr_t)sql); 千牴湩?瑏灹? 存款;CString m_time; CTime time; time = CTime:GetCurrentTime(); m_time = time.Format(%Y年%m月%d 日 %X);O

18、time = m_time; int flag = 0; Omoney.Format(%f,temp1); OAmoney = str2;OBmoney = str3; sql.Format(insert into Operate values(%s,%s,%s,%s,%s,%s,%d,%s,%s,%s),Oid,str4,Bid,Sid,Otype,m_time,flag,Omoney,OAmoney,OBmoney);ado.ExecuteSQL(_bstr_t)sql);ado.ExitConnect(); b. void CClientDlg:OnButtonGet() /取款函数 /

19、 TODO: Add your control notification handler code here CGetDlg GetDlg; if (GetDlg.DoModal()=IDOK) double temp,temp1; ADOConn ado; CString sql = select * from Custom where Cname=+Cname+; _RecordsetPtr ResultSet = ado.GetRecordSet(_bstr_t)sql); CString str = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Crmo

20、ney); CString bid = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Bid); CString str4 = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Cid);CString str2= str; /将交易前钱数暂存temp=atof(str); temp1=(double)GetDlg.m_GetNum; if (temptemp1) temp-=temp1; str.Format(%f,temp); CString str3 = str; /暂存交易后金额 sql = %update Custom se

21、t Crmoney=+str+ +where Cname=+Cname+; ado.ExecuteSQL(_bstr_t)sql); sql = select count(*) num from Operate; ResultSet = ado.GetRecordSet(_bstr_t)sql) int num = atoi(LPCTSTR)(_bstr_t)ResultSet-GetCollect( um); str = ?600; CString Oid,Cid,Sid,Bid,Otime,Omoney,OAmoney,OBmoney; Oid.Format(%d,+num); Oid=s

22、tr+Oid; Sid=_x0007_985001Bid = bid; double temp2; sql = select * from Bank where Bid=+Bid+; ResultSet = ado.GetRecordSet(_bstr_t)sql); CString bmoney = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Bmoney); temp2 = atof(bmoney); temp2-=temp1; bmoney.Format(%f,temp2); sql = %update Bank set Bmoney=+bmoney+

23、where Bid=+Bid+; ado.ExecuteSQL(_bstr_t)sql); 千牴湩?瑏灹?取款;CString m_time; CTime time; time = CTime:GetCurrentTime(); m_time = time.Format(%Y年%m月%d日 %X); Otime = m_time; int flag = 0; Omoney.Format(%f,temp1);OAmoney = str2; OBmoney = str3; sql.Format(insert into Operate values(%s,%s,%s,%s,%s,%s,%d,%s,%

24、s,%s),Oid,str4,Bid,Sid,Otype,m_time,flag,Omoney,OAmoney,OBmoney); ado.ExecuteSQL(_bstr_t)sql); 摡?硅瑩潃湮捥?敽獬筥晁?獥慳敧潂?账户余额不足!); c. void CClientDlg:OnButtonTurn() / TODO: Add your control notification handler code here CTurnDlg TurnDlg; if (TurnDlg.DoModal()=IDOK) ADOConn ado; CString sql = select * from

25、Custom; _RecordsetPtr ResultSet = ado.GetRecordSet(_bstr_t)sql); int flag = 0; while (!ResultSet-adoEOF) CString TCusId = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Cid); CString TCusMon = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Crmoney); /收钱客户在操作前的余额 CString str5 = TCusMon; TCusId.Remove( ); if (TCusId=

26、TurnDlg.m_TurnId) double temp,temp1; sql = select * from Custom where Cname=+Cname+; ResultSet = ado.GetRecordSet(_bstr_t)sql); CString str = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Crmoney); CString bid = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Bid); CString str4 = (LPCTSTR)(_bstr_t)ResultSet-GetColl

27、ect(Cid); CString str2= str; /将交易前钱数暂存temp=atof(str); temp1=(double)TurnDlg.m_TurnNum; if (temptemp1) flag = 1; temp-=temp1; /住客户钱数减少str.Format(%f,temp); 暂存交易后金额/ CString str3 = str; sql = %update Custom set Crmoney=+str+ +where Cname=+Cname+; do.ExecuteSQL(_bstr_t)sql); temp=atof(TCusMon); /收钱客户钱数增

28、加temp+=temp1; TCusMon.Format(%f,temp); sql = %update Custom set Crmoney=+TCusMon+ +where Cid=+TCusId+; ado.ExecuteSQL(_bstr_t)sql); sql = select count(*) num from Operate; ResultSet = ado.GetRecordSet(_bstr_t)sql); int num = atoi(LPCTSTR)(_bstr_t)ResultSet-GetCollect( um); str = ?600;CString Oid,Cid

29、,Sid,Bid,Otime,Omoney,OAmoney,OBmoney; int Otype; Oid.Format(%d,+num); Oid=str+Oid; Sid=_x0007_985001;Bid = bid; Otype = 2; CString m_time; CTime time; time = CTime:GetCurrentTime();m_time = time.Format(%Y年%m月%d日 %X);Otime = m_time; Omoney.Format(%f,temp1); OAmoney = str2; OBmoney = str3; sql.Format

30、(insert into Operate values(%s,%s,%s,%s,%d,%s,%s,%s,%s),Oid,str4,Bid,Sid,Otype,m_time,Omoney,OAmoney,OBmoney); ado.ExecuteSQL(_bstr_t)sql); sql.Format(insert into Operate values(%s,%s,%s,%s,%d,%s,%s,%s,%s),TCusId,str4,Bid,Sid,Otype,m_time,Omoney,str5,TCusMon); ado.ExecuteSQL(_bstr_t)sql); ado.ExitCo

31、nnect();break; 敽獬筥晁?獥慳敧潂?账户余额不足!); break; ResultSet-MoveNext();if(flag=0) ?硦敍獳条?硯尨该账户不存在!); d. void CClientDlg:OnButtonHistoy() /历史记录查询 / TODO: Add your control notification handler code here CShowHistory showDlg; showDlg.Cname = Cname;showDlg.DoModal(); e. void CClientDlg:OnButtonAlter() / TODO: Ad

32、d your control notification handler code here CAlterDlg alteDlg; if(alteDlg.DoModal()=IDOK) if (alteDlg.m_AItem=0) if (alteDlg.m_Alter1=alteDlg.m_Alter2) ADOConn ado; CString sql = %update Custom set Cname=+alteDlg.m_Alter1+ where Cname=+Cname+;ado.ExecuteSQL(_bstr_t)sql); 晁?獥慳敧潂?姓名修改成功!); 汥敳笠晁?獥慳敧潂

33、?两次姓名输入不一致,请重新输入!); else if(alteDlg.m_AItem=1) if (alteDlg.m_Alter1=alteDlg.m_Alter2) ADOConn ado; where Cpass=+alteDlg.m_Alter1+ CString sql = %update Custom set 密码晁?獥慳敧潂?Cname=+Cname+;ado.ExecuteSQL(_bstr_t)sql); !); 修改成功 汥敳?硦敍獳条?硯尨两次密码输入不一致,请重新输入!); elseif (alteDlg.m_Alter1=alteDlg.m_Alter2) if (

34、alteDlg.m_Alter1.GetAt(0)=1 Cphone=+alteDlg.m_Alter1+ where Cname=+Cname+; !); 摡?硅捥瑵卥?扟瑳彲?煳?晁?獥慳敧潂?电话号码修改成功 !); else 电话号码格式不对 汥敳笠晁?獥慳敧潂? !); 请重新输入?硦敍獳条?硯尨两次电话输入不一致, 管理员部分： 添加新成员 /a. void CStaffDlg:OnButtonInsert() / TODO: Add your control notification handler code here CInsertDlg insertDlg; if (inse

35、rtDlg.DoModal()=IDOK) if (insertDlg.m_Phone.GetAt(0)=1 where from Custom CString sql = select count(*) Num Ccode=+insertDlg.m_Code+; _RecordsetPtr ResultSet = ado.GetRecordSet(_bstr_t)sql); int Num = atoi(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Num); if (Num=0) CString str = 50500; sql = select count(

36、*) num from Custom; ResultSet = ado.GetRecordSet(_bstr_t)sql); Num = atoi(LPCTSTR)(_bstr_t)ResultSet-GetCollect( um); CString Cid,Cpass,Bid,Pmoney; double dou; Cid.Format(%d,+Num); Cid = str+Cid; Cpass = CTime time; time = CTime:GetCurrentTime(); %X); 日 年 m_time = time.Format(%Y%m月%ddou = (double)in

37、sertDlg.m_Pmoney; Pmoney.Format(%f,dou); Custom Otime = m_time; sql.Format(insert into / values(%s,%s,%s,%s,%s,%s,%s,%s),Cid,insertDlg.m_Name,Cpass,m_time,in sertDlg.m_Code,Bid,Pmoney,insertDlg.m_Phone); ado.ExecuteSQL(_bstr_t)sql); sql = select count(*) num from Operate; ResultSet = ado.GetRecordSe

38、t(_bstr_t)sql); Num = atoi(LPCTSTR)(_bstr_t)ResultSet-GetCollect( um); ;Oid.Format(%d,+Num); 千牴湩?楏?潭敮?瑏灹?传祴数?尠 新注册str = ?600;Oid = str+Oid; Bid= sql.Format(insert into Operate values(%s,%s,%s,%s,%s,%s,%d,%s,%s,%s),Oid,Cid,Bid,Sid,Otype,m_time,flag,Pmoney,OAmoney,Pmoney); ado.ExecuteSQL(_bstr_t)sql);

39、 晁?獥慳敧潂? 新增客户成功!); else ?硦敍獳条?硯尨 该证件号已经注册 !); b. void CStaffDlg:OnButtonDelete() /注销客户 / TODO: Add your control notification handler code here CDelDlg delDlg; if (delDlg.DoModal()=IDOK) if (delDlg.m_DelId1=delDlg.m_DelId2) ADOConn ado; count(*) Num from Custom where Cid=+delDlg.m_DelId1+; _Recordset

40、Ptr ResultSet = ado.GetRecordSet(_bstr_t)sql); int Num = atoi(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Num); if (Num!=0) CString str = 50500;sql = delete from Custom where Cid=+delDlg.m_DelId1+;ado.ExecuteSQL(_bstr_t)sql); CString Bid,Cid; Cid = 505000;CString m_time; CTime time; time = CTime:GetCurren

41、tTime(); 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( um); CString Oid,OAmoney,Otype; Otype = 注销用户;Oid.Format(%d,+Num); tr = ?600;Oid = str+Oid; Bid= sql.Format(insert into Opera

42、te values(%s,%s,%s,%s,%s,%s,%d,%s,%s,%s),Oid,Cid,Bid,Sid,Otype,m_time,flag,OAmoney,OAmoney,OAmoney); ado.ExecuteSQL(_bstr_t)sql); 晁?獥慳敧潂?注销客户成功!); else ?硦敍獳条?硯尨不存在该用户!); 汥敳笠晁?獥慳敧潂?两次输入用户ID不一致，请重新输入!); c. void CStaffDlg:OnButtonResher() 查询客户 / TODO: Add your control notification handler code here CAl

43、terDlg altDlg; if (altDlg.DoModal()=IDOK) if (altDlg.m_AItem=0) if (altDlg.m_Alter1=altDlg.m_Alter2) ADOConn ado; CString sql = %update Staff set Sname=+altDlg.m_Alter1+ where Sname=+Sname+;ado.ExecuteSQL(_bstr_t)sql); 晁?獥慳敧潂?姓名修改成功!); 敽獬筥晁?獥慳敧潂?两次姓名输入不一致,请重新输入!); else if(altDlg.m_AItem=1) if (altDl

44、g.m_Alter1=altDlg.m_Alter2) ADOConn ado; CString sql = %update Custom set Spass=+altDlg.m_Alter1+ where Sname=+Sname+;ado.ExecuteSQL(_bstr_t)sql); 晁?獥慳敧潂?密码修改成功!); else ?硦敍獳条?硯尨两次密码输入不一致 ,请重新输入!); else if (altDlg.m_Alter1=altDlg.m_Alter2) if (altDlg.m_Alter1.GetAt(0)=1 CString sql = %update Custom s

45、et Sphone=+altDlg.m_Alter1+ where Sname=+Sname+;ado.ExecuteSQL(_bstr_t)sql); 晁?獥慳敧潂?电话号码修改成功!); 汥敳?硦敍獳条?硯尨电话号码格式不对!); else ?硦敍獳条?硯尨 两次电话输入不一致, 请重新输入!); d. void CStaffDlg:OnButtonMoneyctrl() /资金管理 / TODO: Add your control notification handler code here CSerchDlg serDlg; if (serDlg.DoModal()=IDOK) ADO

46、Conn ado; CString sql = select count(*) Num from Custom where Cid=+serDlg.m_Cid+; _RecordsetPtr ResultSet = ado.GetRecordSet(_bstr_t)sql); int Num = atoi(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Num); if (Num!=0) CCusInfoDlg cusDlg; cusDlg.Cid = serDlg.m_Cid ADOConn ado; CString sql = select * from Cus

47、tom where Cid=+cusDlg.Cid+; _RecordsetPtr ResultSet = ado.GetRecordSet(_bstr_t)sql); cusDlg.m_Cname = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Cname); cusDlg.m_Cid = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Cid); cusDlg.m_Code = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Ccode); cusDlg.m_Phone = (LPCTSTR)(_

48、bstr_t)ResultSet-GetCollect(Cphone); cusDlg.m_Rmoney = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Crmoney); cusDlg.DoModal();ado.ExitConnect();else ?硦敍獳条?硯尨不存在该客户!); e. void CStaffDlg:OnButtonAlter() /信息修改 CMoneyDlg monDlg; ADOConn ado; CString sql = select count(*) Num from BMoney ; _RecordsetPtr Resul

49、tSet = ado.GetRecordSet(_bstr_t)sql); monDlg.m_GetNum = atoi(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Num); sql = select count(*) Num from BInMoney; ResultSet = ado.GetRecordSet(_bstr_t)sql); monDlg.m_InNum = atoi(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Num); sql = select count(*) Num from VIP; ResultSet

50、 = ado.GetRecordSet(_bstr_t)sql); monDlg.m_VIPNum= atoi(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Num); sql = select * from Bank where Bid=00001; ResultSet = ado.GetRecordSet(_bstr_t)sql); monDlg.m_Cash = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Bmoney); ado.ExitConnect(); monDlg.DoModal(); f. void CStaffDlg:OnButtonAnyse() /员工业绩分析 / TODO: Add your control n