银行数据库表的设计

系统需求分析：1.在学习了数据库之后，为了更进一步巩固所学知识，将其用于实践，我们选择了银行管理系统，来完成最基本的一些操作。

2.功能(1).客户：包括存款，取款，转账，查询余额，查看流水，密码修改这些功能(2).管理员：1.新增用户2 .删除3.查看4.员工绩效（就是根据流水表中的员工号，select出员工一天内做的所有单，用来考评绩效）5.VIP判别（就是账户存储资金大于某个值的时候在账户表的一个字段上赋值为VIP）6.资金储备金判别，当库存资金总数小于一个值的时候，系统对管理者要提出风险警告。

3.功能流程图：银行管理系统登陆管理员客户销户查看员工绩效VIP判别资金储备金判别开户存款取款转账余额显示查看流水修改密码详细设计： 1.E-R 图模型operatecustombankstaffCphoneCtimeCpassCRmoneyCnameCidBidSphoneSnameSidSpassSItimeBname BmoneyBid1112.根据E-R 图设计关系表 (1).银行信息表（bank ）字段名 字段类型及长度 允许空 主键 说明Bid nchar (9) no PK 银行号 Bnamenchar (20)no 银行名 Bmoneynumeric (20,3)no 银行余额（2）.客户信息表（custom ）字段名 字段类型及长度 允许空主键 说明 Cid nchar (9) no PK 客户ID Cname 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) noPK 员工ID Sname 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关系图：程序代码：客户部分：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.E xitConnect(); }}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 (temp>temp1){temp-=temp1; str.Format("%f",temp); CString str3 = str;//暂存交易后金额sql = "update Custom set Crmoney='"+str+"' "+"whereCname='"+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+"' whereBid='"+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();}else{AfxMessageBox("账户余额不足!"); }}}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 (temp>temp1){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 Operatevalues('%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; }else{AfxMessageBox("账户余额不足!");break; }}ResultSet->MoveNext();}if(flag==0){AfxMessageBox("该账户不存在!"); }}}d.void CClientDlg::OnButtonHistoy() //历史记录查询{// TODO: Add your control notification handler code hereCShowHistory showDlg;ame = 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+"' whereCname='"+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+"' whereCname='"+Cname+"'";ado.ExecuteSQL((_bstr_t)sql); AfxMessageBox("密码修改成功!"); }else{AfxMessageBox("两次密码输入不一致,请重新输入!"); }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 setCphone='"+alteDlg.m_Alter1+"' where Cname='"+Cname+"'";ado.ExecuteSQL((_bstr_t)sql); AfxMessageBox("电话号码修改成功!"); }else {AfxMessageBox("电话号码格式不对!");} } else{AfxMessageBox("两次电话输入不一致,请重新输入!"); } }}}高磊磊：心得体会：这次课程设计体会非常深刻，首先在团队合作方面，我觉得自己作为队长没有两道好我的小队，这是我的失职。