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

数据库系统概论课 程 设 计 报 告设计题目： 银行储蓄管理系统 姓名： 学号： 班级： 设计起止时间： 需求分析： 在对软件工程相关知识学习之后，我们对设计软件有了基本的认识和一些应用技能。在数据库的课程设计中，我们计划做一个小型的银行储蓄管理系统，包括了基本的存取转，和管理员对日常工作的管理功能。功能设计： (1).客户：包括存款，取款，转账，查询余额，查看流水，密码修改功能 (2).管理员：新增用户，删除用户，查看用户，员工绩效，VIP用户判别，储备金预警分析。3.功能流程图：详细设计：1.E-R图模型2.根据E-R图设计关系表 (1).银行信息表（bank）字段名字段类型及长度允许空主键 说明Bid nchar(9) no PK银行号Bname nchar(20) no银行名Bmoney numeric(20,3) no银行余额 （2）.客户信息表（custom）字段名字段类型及长度允许空主键 说明Cid nchar(9) no PK 客户IDCname nchar(10) no 客户姓名Cpass nchar(10) no 密码Ctime nchar(20) no 注册时间Bid nchar(9) no 所在银行行号 外码（Bank（Bid）Crmoney numeric(10,3) no 账户余额Cphone nchar(11) no 客户电话 (3).员工（管理员）表（staff）字段名字段类型及长度允许空主键 说明Sid nchar(9) no PK 员工IDSname nchar(10) no 员工姓名Spass nchar(10) no 登陆密码SItime nchar(20) no 入行时间Sphone nchar(11) no 联系电话 (4).流水信息表字段名字段类型及长度允许空主键 说明Oid nchar(9) no PK 流水号Cid nchar(9) no 客户ID 外码（Custom（Cid）Bid nchar(9) no 银行ID 外码（Bank（Bid）Sid nchar(9) no 员工ID 外码（Staff（Sid）Otype smallint no 操作类型Otime nchar(20) no 操作时间Omoney numeric(10,3) yes 交易金额OBmoney numeric(10,3) yes 上次余额OAmoney numeric(10,3) 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 smallint 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 nchar(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 smallint 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 (Bid) references Bank(Bid) on update cascade)insert into Bank values(00001,中国银行小寨分行,10000)update Bank set Bname=中国银行经开分行 where Bid=00002select * from Bank;delete from Bank where Bid=1 or Bid=2;insert into Custom values(6505001,花花,111,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,6505007,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 Oid=6505001drop view BMoney;create view BMoneyasselect Omoneyfrom Operatewhere Oflag = 0 and Omoney2000 and Otype = 取款;create view BInMoneyasfrom Operatewhere Oflag = 0 and Omoney2000 and Otype=存款;create view VIPas select Ctypefrom Customwhere Ctype=1;select count(*) from BMoney;select count(*) from BInMoney;select count(*) from VIP;update Bank set Bmoney=10000 where 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 hereCInDlg InDlg;if (InDlg.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(Crmoney);CString bid = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Bid);CString str4 = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Cid);CString str2= 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 num = atoi(LPCTSTR)(_bstr_t)ResultSet-GetCollect(num);str = 240600;CString Oid,Cid,Sid,Bid,Otime,Omoney,OAmoney,OBmoney;Oid.Format(%d,+num); Oid=str+Oid; Sid=7985001;Bid = 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+ where Bid=+Bid+;ado.ExecuteSQL(_bstr_t)sql);CString Otype = 存款;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,%s,%s),Oid,str4,Bid,Sid,Otype,m_time,flag,Omoney,OAmoney,OBmoney);ado.ExecuteSQL(_bstr_t)sql);ado.ExitConnect();b. void CClientDlg:OnButtonGet() /取款函数/ TODO: Add your control notification handler code hereCGetDlg 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(Crmoney);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 set 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(num);str = 240600;CString Oid,Cid,Sid,Bid,Otime,Omoney,OAmoney,OBmoney;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);CString bmoney = (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 Otype = 取款;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,%s,%s),Oid,str4,Bid,Sid,Otype,m_time,flag,Omoney,OAmoney,OBmoney); ado.ExecuteSQL(_bstr_t)sql);ado.ExitConnect();elseAfxMessageBox(账户余额不足!); c. void CClientDlg:OnButtonTurn() / TODO: Add your control notification handler code hereCTurnDlg TurnDlg;if (TurnDlg.DoModal()=IDOK)ADOConn ado;CString sql = select * from 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=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-GetCollect(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);/收钱客户钱数增加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(num);str = 240600;CString Oid,Cid,Sid,Bid,Otime,Omoney,OAmoney,OBmoney;int Otype; Oid.Format(%d,+num); Oid=str+Oid; Sid=7985001;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(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.ExitConnect();break; elseAfxMessageBox(账户余额不足!); break; ResultSet-MoveNext();if(flag=0)AfxMessageBox(该账户不存在!); d. void CClientDlg:OnButtonHistoy() /历史记录查询/ TODO: Add your control notification handler code hereCShowHistory showDlg;showDlg.Cname = Cname;showDlg.DoModal();e. void CClientDlg:OnButtonAlter() / TODO: Add your control notification handler code hereCAlterDlg 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);AfxMessageBox(姓名修改成功!); else AfxMessageBox(两次姓名输入不一致,请重新输入!); else if(alteDlg.m_AItem=1) if (alteDlg.m_Alter1=alteDlg.m_Alter2) ADOConn ado;CString sql = update Custom set Cpass=+alteDlg.m_Alter1+ where Cname=+Cname+;ado.ExecuteSQL(_bstr_t)sql); AfxMessageBox(密码修改成功!); elseAfxMessageBox(两次密码输入不一致,请重新输入!); elseif (alteDlg.m_Alter1=alteDlg.m_Alter2) if (alteDlg.m_Alter1.GetAt(0)=1&alteDlg.m_Alter1.GetLength()=11)ADOConn ado; CString sql = update Custom set Cphone=+alteDlg.m_Alter1+ where Cname=+Cname+; ado.ExecuteSQL(_bstr_t)sql); AfxMessageBox(电话号码修改成功!); else AfxMessageBox(电话号码格式不对!); else AfxMessageBox(两次电话输入不一致,请重新输入!); 管理员部分：a. void CStaffDlg:OnButtonInsert() /添加新成员/ TODO: Add your control notification handler code hereCInsertDlg insertDlg;if (insertDlg.DoModal()=IDOK)if (insertDlg.m_Phone.GetAt(0)=1&insertDlg.m_Phone.GetLength()=11)ADOConn ado;CString sql = select count(*) Num from Custom where 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 = 650500;sql = select count(*) num from Custom;ResultSet = ado.GetRecordSet(_bstr_t)sql);Num = atoi(LPCTSTR)(_bstr_t)ResultSet-GetCollect(num);CString Cid,Cpass,Bid,Pmoney; double dou;Cid.Format(%d,+Num); Cid = str+Cid;Cpass = 000000;CString m_time; Bid= 00001;CTime time; time = CTime:GetCurrentTime();m_time = time.Format(%Y年%m月%d日 %X);dou = (double)insertDlg.m_Pmoney;Pmoney.Format(%f,dou);/Otime = m_time; sql.Format(insert into Custom values(%s,%s,%s,%s,%s,%s,%s,%s),Cid,insertDlg.m_Name,Cpass,m_time,insertDlg.m_Code,Bid,Pmoney,insertDlg.m_Phone);ado.ExecuteSQL(_bstr_t)sql);sql = select count(*) num from Operate;ResultSet = ado.GetRecordSet(_bstr_t)sql);Num = atoi(LPCTSTR)(_bstr_t)ResultSet-GetCollect(num);CString Oid,OAmoney,Otype; Otype = 新注册;Oid.Format(%d,+Num);str = 240600;Oid = str+Oid; Bid= 00001;int flag = 0; OAmoney = 0;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);AfxMessageBox(新增客户成功!); elseAfxMessageBox(该证件号已经注册!); b. void CStaffDlg:OnButtonDelete() /注销客户/ TODO: Add your control notification handler code hereCDelDlg delDlg;if (delDlg.DoModal()=IDOK)if (delDlg.m_DelId1=delDlg.m_DelId2)ADOConn ado; count(*) 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)CString str = 650500;sql = delete from Custom where Cid=+delDlg.m_DelId1+;ado.ExecuteSQL(_bstr_t)sql); CString Bid,Cid;Cid = 6505000;CString m_time;CTime time; time = CTime:GetCurrentTime();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(num);CString Oid,OAmoney,Otype; Otype = 注销用户;Oid.Format(%d,+Num);tr = 240600;Oid = str+Oid; Bid= 00001;int flag = 2; OAmoney = 0;sql.Format(insert into Operate 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);AfxMessageBox(注销客户成功!); elseAfxMessageBox(不存在该用户!); else AfxMessageBox(两次输入用户ID不一致，请重新输入!); c. void CStaffDlg:OnButtonResher() 查询客户/ TODO: Add your control notification handler code hereCAlterDlg 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);AfxMessageBox(姓名修改成功!);elseAfxMessageBox(两次姓名输入不一致,请重新输入!); else if(altDlg.m_AItem=1)if (altDlg.m_Alter1=altDlg.m_Alter2)ADOConn ado;CString sql = update Custom set Spass=+altDlg.m_Alter1+ where Sname=+Sname+;ado.ExecuteSQL(_bstr_t)sql); AfxMessageBox(密码修改成功!); elseAfxMessageBox(两次密码输入不一致,请重新输入!); elseif (altDlg.m_Alter1=altDlg.m_Alter2)if (altDlg.m_Alter1.GetAt(0)=1&altDlg.m_Alter1.GetLength()=11)ADOConn ado;CString sql = update Custom set Sphone=+altDlg.m_Alter1+ where Sname=+Sname+;ado.ExecuteSQL(_bstr_t)sql);AfxMessageBox(电话号码修改成功!); elseAfxMessageBox(电话号码格式不对!); elseAfxMessageBox(两次电话输入不一致,请重新输入!); d. void CStaffDlg:OnButtonMoneyctrl() /资金管理 / TODO: Add your control notification handler code hereCSerchDlg serDlg;if (serDlg.DoModal()=IDOK)ADOConn 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 Custom 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)(_bstr_t)ResultSet-GetCollect(Cphone);cusDlg.m_Rmoney = (LPCTSTR)(_bstr_t)ResultSet-GetCollect(Crmoney);cusDlg.DoModal();ado.ExitConnect();elseAfxMessageBox(不存在该客户!); e. void CStaffDlg:OnButtonAlter() /信息修改 CMoneyDlg monDlg; ADOConn ado;CString sql = select count(*) Num from BMoney ;_RecordsetPtr ResultSet = 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 = 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