银行数据库表的设计

系统需求分析：1.在学习了数据库之后，为了更进一步巩固所学知识，将其用于实践，我们选择了银行管理系统，来完成最基本的一些操作。2.功能(1).客户：包括存款，取款，转账，查询余额，查看流水，密码修改这些功能(2).管理员：1.新增用户2.删除3.查看4.员工绩效（就是根据流水表中的员工号，select出员工一天内做的所有单，用来考评绩效）5.VIP判别（就是账户存储资金大于某个值的时候在账户表的一个字段上赋值为VIP）6.资金储备金判别，当库存资金总数小于一个值的时候，系统对管理者要提出风险警告。3.功能流程图：银行管理系统登陆管理员客户销户查看员工绩效VIP判别资金储备金判别开户存款取款转账余额显示查看流水修改密码详细设计：1.E-R图模型operatecustombankstaffCphoneCtimeCpassCRmoneyCnameCidBidSphoneSnameSidSpassSItimeBnameBmoneyBid1112.根据E-R图设计关系表(1).银行信息表（bank）字段名字段类型及长度允许空主键说明Bidnchar(9)noPK银行号Bnamenchar(20)no银行名Bmoneynumeric(20,3)no银行余额（2）.客户信息表（custom）字段名字段类型及长度允许空主键说明Cidnchar(9)noPK客户IDCnamenchar(10)no客户姓名Cpassnchar(10)no密码Ctimenchar(20)no注册时间Bidnchar(9)no所在银行行号外码（Bank（Bid））Crmoneynumeric(10,3)no账户余额Cphonenchar(11)no客户电话(3).员工（管理员）表（staff）字段名字段类型及长度允许空主键说明Sidnchar(9)noPK员工IDSnamenchar(10)no员工姓名Spassnchar(10)no登陆密码SItimenchar(20)no入行时间Sphonenchar(11)no联系电话(4).流水信息表字段名字段类型及长度允许空主键说明Oidnchar(9)noPK流水号Cidnchar(9)no客户ID外码（Custom（Cid））Bidnchar(9)no银行ID外码（Bank（Bid））Sidnchar(9)no员工ID外码（Staff（Sid））Otypesmallintno操作类型Otimenchar(20)no操作时间Omoneynumeric(10,3)yes交易金额OBmoneynumeric(10,3)yes上次余额OAmoneynumeric(10,3)yes账户余额三个实体：bank,staff,custom一个联系：operate关系图：程序代码：客户部分：a.voidCClientDlg::OnButtonIn()//存款函数{//TODO:AddyourcontrolnotificationhandlercodehereCInDlgInDlg;if(InDlg.DoModal()==IDOK){doubletemp,temp1;ADOConnado;CStringsql=select*fromCustomwhereCname='+Cname+';_RecordsetPtrResultSet=ado.GetRecordSet((_bstr_t)sql);CStringstr=(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Crmoney);CStringbid=(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Bid);CStringstr4=(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Cid);CStringstr2=str;//将交易前钱数暂存temp=atof(str);temp1=(double)InDlg.m_InNum;temp+=temp1;str.Format(%f,temp);CStringstr3=str;//暂存交易后金额sql=updateCustomsetCrmoney='+str+'+whereCname='+Cname+';ado.ExecuteSQL((_bstr_t)sql);sql=selectcount(*)numfromOperate;ResultSet=ado.GetRecordSet((_bstr_t)sql);intnum=atoi((LPCTSTR)(_bstr_t)ResultSet-GetCollect(num));str=240600;CStringOid,Cid,Sid,Bid,Otime,Omoney,OAmoney,OBmoney;Oid.Format(%d,++num);Oid=str+Oid;Sid=7985001;Bid=bid;doubletemp2;sql=select*fromBankwhereBid='+Bid+';ResultSet=ado.GetRecordSet((_bstr_t)sql);CStringbmoney=(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Bmoney);temp2=atof(bmoney);temp2+=temp1;bmoney.Format(%f,temp2);sql=updateBanksetBmoney='+bmoney+'whereBid='+Bid+';ado.ExecuteSQL((_bstr_t)sql);CStringOtype=存款;CStringm_time;CTimetime;time=CTime::GetCurrentTime();m_time=time.Format(%Y年%m月%d日%X);Otime=m_time;intflag=0;Omoney.Format(%f,temp1);OAmoney=str2;OBmoney=str3;sql.Format(insertintoOperatevalues('%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.voidCClientDlg::OnButtonGet()//取款函数{//TODO:AddyourcontrolnotificationhandlercodehereCGetDlgGetDlg;;if(GetDlg.DoModal()==IDOK){doubletemp,temp1;ADOConnado;CStringsql=select*fromCustomwhereCname='+Cname+';_RecordsetPtrResultSet=ado.GetRecordSet((_bstr_t)sql);CStringstr=(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Crmoney);CStringbid=(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Bid);CStringstr4=(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Cid);CStringstr2=str;//将交易前钱数暂存temp=atof(str);temp1=(double)GetDlg.m_GetNum;if(temptemp1){temp-=temp1;str.Format(%f,temp);CStringstr3=str;//暂存交易后金额sql=updateCustomsetCrmoney='+str+'+whereCname='+Cname+';ado.ExecuteSQL((_bstr_t)sql);sql=selectcount(*)numfromOperate;ResultSet=ado.GetRecordSet((_bstr_t)sql)intnum=atoi((LPCTSTR)(_bstr_t)ResultSet-GetCollect(num));str=240600;CStringOid,Cid,Sid,Bid,Otime,Omoney,OAmoney,OBmoney;Oid.Format(%d,++num);Oid=str+Oid;Sid=7985001Bid=bid;doubletemp2;sql=select*fromBankwhereBid='+Bid+';ResultSet=ado.GetRecordSet((_bstr_t)sql);CStringbmoney=(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Bmoney);temp2=atof(bmoney);temp2-=temp1;bmoney.Format(%f,temp2);sql=updateBanksetBmoney='+bmoney+'whereBid='+Bid+';ado.ExecuteSQL((_bstr_t)sql);CStringOtype=取款;CStringm_time;CTimetime;time=CTime::GetCurrentTime();m_time=time.Format(%Y年%m月%d日%X);Otime=m_time;intflag=0;Omoney.Format(%f,temp1);OAmoney=str2;OBmoney=str3;sql.Format(insertintoOperatevalues('%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.voidCClientDlg::OnButtonTurn(){/TODO:AddyourcontrolnotificationhandlercodehereCTurnDlgTurnDlg;if(TurnDlg.DoModal()==IDOK){ADOConnado;CStringsql=select*fromCustom;_RecordsetPtrResultSet=ado.GetRecordSet((_bstr_t)sql);intflag=0;while(!ResultSet-adoEOF)CStringTCusId=(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Cid);CStringTCusMon=(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Crmoney);//收钱客户在操作前的余额CStringstr5=TCusMon;TCusId.Remove('');if(TCusId==TurnDlg.m_TurnId){doubletemp,temp1;sql=select*fromCustomwhereCname='+Cname+';ResultSet=ado.GetRecordSet((_bstr_t)sql);CStringstr=(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Crmoney);CStringbid=(LPCTSTR)(_bstr_t)ResultSet-GetCollect(Bid);CStringstr4=(LPCTSTR)(_bstr_t)ResultSet-GetCollect