数据库实验报告范例.docx
《数据库实验报告范例.docx》由会员分享,可在线阅读,更多相关《数据库实验报告范例.docx(17页珍藏版)》请在冰豆网上搜索。
数据库实验报告范例
1.实验目的:
掌握数据库模式设计,依据实际要求设计表结构,建立表的关系;结合一定的开发工具实现数据库应用程序的开发。
2.实验环境
2.1硬件环境:
处理器:
InterCore2。
显卡:
NVIDIAGeForce9700GT。
硬盘:
500G。
2.2软件环境(包括操作系统、应用服务器配置、开发环境配置等)
WindowsXP,SQLserver2005,VB6.0
3.需求分析:
针对库存管理系统,描述其主要功能,给出E-R图;
买主发出要求,说明所买货物情况,货主接到订单后联系货船向买家发送货物,货物由货船运往目的地。
到达目的地后由委托的代理人帮助货主联系车队和场地。
4.概念结构设计:
如以下E-R图
5.逻辑结构设计
主要关系有
货物(货名,箱号,提单号,类别,数量,重量,航次);
货船(航次,船名,出发港,目的港,预计离港时间,预计到达时间);
货主(联系人,联系电话,公司名称,地址,开户银行,银行账号,发票抬头);
买主(联系人,联系电话,公司名称,开户银行,银行账号);
代理人(联系电话,委托公司,委托日期);
费用信息(费用名称,凭证号,收/付款单位,应收/付金额,实收/付金额,实收/付日期)。
6.数据库实现
创建系统用户表格user_Info
[user_ID][varchar](10)COLLATEChinese_PRC_CI_ASNOTNULL,
[user_PWD][varchar](10)COLLATEChinese_PRC_CI_ASNULL,
创建货船信息表格 GoodsInfo
[orderNo][varchar](10)COLLATEChinese_PRC_CI_ASNOTNULL,
[shipname][varchar](10)COLLATEChinese_PRC_CI_ASNOTNULL,
[vessel][varcher](10)NULL,
[departureprot][varcher](10)NULL,
[destination][varchar](10)COLLATEChinese_PRC_CI_ASNULL,
[Arrivetime][date]COLLATEChinese_PRC_CI_ASNULL,
[Leavetime][date]COLLATEChinese_PRC_CI_ASNULL,
创建货物信息表格 Goods
[goodsname][varchar](10)COLLATEChinese_PRC_CI_ASNOTNULL,
[goodsclass][varchar](10)COLLATEChinese_PRC_CI_ASNOTNULL,
[weight][varchar](10)COLLATEChinese_PRC_CI_ASNULL,
[count][varcher](10)NULL,
[boxNo][varchar](10)COLLATEChinese_PRC_CI_ASNOTNULL
创建货主及代理信息表Owner
[owner][varcher](10)COLLATEChinese_PRC_CI_ASNULL
[agent][varcher](10)COLLATEChinese_PRC_CI_ASNULL
[agentTel][varcher](10)COLLATEChinese_PRC_CI_ASNULL
[comissionedDate][date]COLLATEChinese_PRC_CI_ASNULL
[companyName][varcher](10)COLLATEChinese_PRC_CI_ASNULL
[address][varcher](10)COLLATEChinese_PRC_CI_ASNULL
[bank][varcher](10)COLLATEChinese_PRC_CI_ASNULL
[bankCount][varcher](10)COLLATEChinese_PRC_CI_ASNULL
[contacher][varcher](10)COLLATEChinese_PRC_CI_ASNULL
[head][varcher](10)COLLATEChinese_PRC_CI_ASNULL
创建客户信息表feeInfo
[feename][varchar](10)COLLATEChinese_PRC_CI_ASNOTNULL,
[fee][varcher](10)COLLATEChinese_PRC_CI_ASNULL)
[realfee][varcher](10)COLLATEChinese_PRC_CI_ASNULL
[realtime][date]COLLATEChinese_PRC_CI_ASNULL
[company][varcher](10)COLLATEChinese_PRC_CI_ASNULL
[flag][varcher](10)COLLATEChinese_PRC_CI_ASNULL
[certificateNo][varcher](10)COLLATEChinese_PRC_CI_ASNULL
7.应用系统设计实现
登陆界面Login
PrivateSubCmdLogin_Click()
IfTxtLoginUid.Text=""ThenMsgBox"帐户不能为空!
",vbOKOnly:
ExitSub
IfTxtLoginPwd.Text=""ThenMsgBox"密码不能为空!
",vbOKOnly:
ExitSub
'DimcnStrAsString
DimcnAsADODB.Connection
Setcn=NewADODB.Connection
DimrsAsADODB.Recordset
DimrsStrAsString
Setrs=NewADODB.Recordset
cn.OpenMStr
rsStr="Select*fromUserInfoWhereUserId='"+TxtLoginUid.Text+"'andpassword='"+TxtLoginPwd+"'"
rs.OpenrsStr,cn,adOpenStatic,adLockOptimistic
Ifrs.RecordCount<0Then
MsgBox("用户名或密码错误!
")
Else
Module1.userFlag=rs.Fields
(2)
LoadfrmMain
frmMain.Show
Ifcn.State=1Then
cn.Close
EndIf
Setcn=Nothing
UnloadFrmLogin
EndIf
EndSub
次级主界面Main
PrivateSubForm_Load()
IfModule1.userFlag=2Then
feeManage.Enabled=False
feeManage.Visible=False
ElseIfModule1.userFlag=3Then
OwnerManage.Enabled=False
OwnerManage.Visible=False
EndIf
EndSub
PrivateSubGoodsAdd_Click()
LoadfrmGoodsAdd
frmGoodsAdd.Show
EndSub
PrivateSubGoodsQuery_Click()
LoadfrmGoodsQuery
frmGoodsQuery.Show
EndSub
PrivateSubfeeAdd_Click()
LoadfrmFeeAdd
frmFeeAdd.Show
EndSub
PrivateSubfeeQuery_Click()
LoadfrmFeeQuery
frmFeeQuery.Show
EndSub
PrivateSubOwnerManage_Click()
LoadfrmOwnerManage
frmOwnerManage.Show
EndSub
货主管理界面OwnerManage
PrivateSubCmdAdd_Click()
DimcnAsADODB.Connection
Setcn=NewADODB.Connection
DimrsAsADODB.Recordset
DimrsStrAsString
Setrs=NewADODB.Recordset
cn.OpenMStr
rsStr="Select*fromOwnerWhereCompanyName='"+DCCompanyName.Text+"'"
rs.OpenrsStr,cn,adOpenStatic,adLockOptimistic
Ifrs.RecordCount=1Then
MsgBox"已存在此货主信息!
"
Else
DimrsStr1AsString
rsStr="InsertIntoOwner(companyName,contacter,telephone,address,bank,bankacount,head)Values('"+DCCompanyName.Text+"','"+txtContacter.Text+"','"+txtTelephone.Text+"','"+txtAddress.Text+"','"+txtBank.Text+"','"+txtBankAccount.Text+"','"+txtHead.Text+"')"
rs.Close
rs.OpenrsStr,cn,adOpenStatic,adLockOptimistic
MsgBox"恭喜,添加成功!
"
Calldatabind
EndIf
EndSub
PrivateSubCmdAll_Click()
DimcnAsADODB.Connection
Setcn=NewADODB.Connection
DimrsAsADODB.Recordset
DimrsStrAsString
Setrs=NewADODB.Recordset
cn.OpenMStr
rsStr="Select*fromOwner"
rs.OpenrsStr,cn,adOpenStatic,adLockOptimistic
SetDGOwner.DataSource=rs
EndSub
PrivateSubCmdDelete_Click()
DimcnAsADODB.Connection
Setcn=NewADODB.Connection
DimrsAsADODB.Recordset
DimrsStrAsString
Setrs=NewADODB.Recordset
cn.OpenMStr
rsStr="DeleteFromOwnerWherecompanyName='"+DCCompanyName.Text+"'"
rs.OpenrsStr,cn,adOpenStatic,adLockOptimistic
MsgBox"恭喜,删除成功成功!
"
Calldatabind
EndSub
PrivateSubCmdUpdate_Click()
DimcnAsADODB.Connection
Setcn=NewADODB.Connection
DimrsAsADODB.Recordset
DimrsStrAsString
Setrs=NewADODB.Recordset
cn.OpenMStr
rsStr="UpdateOwnerSetcontacter='"+txtContacter.Text+"',telephone='"+txtTelephone.Text&_
"',address='"+txtAddress.Text+"',bank='"+txtBank.Text+"',bankacount='"+txtBankAccount.Text+"',head='"+txtHead.Text+"'WherecompanyName='"&_
DCCompanyName.Text+"'"
rs.OpenrsStr,cn,adOpenStatic,adLockOptimistic
MsgBox"恭喜,修改成功成功!
"
Calldatabind
EndSub
PrivateSubCmdQuery_Click()
DimcnAsADODB.Connection
Setcn=NewADODB.Connection
DimrsAsADODB.Recordset
DimrsStrAsString
Setrs=NewADODB.Recordset
cn.OpenMStr
rsStr="Select*fromOwnerWhereCompanyName='"+DCCompanyName.Text+"'"
rs.OpenrsStr,cn,adOpenStatic,adLockOptimistic
Ifrs.RecordCount=1Then
txtContacter.Text=rs.Fields
(1).Value
txtTelephone.Text=rs.Fields
(2).Value
txtAddress.Text=rs.Fields(3).Value
txtBank.Text=rs.Fields(4).Value
txtBankAccount.Text=rs.Fields(5).Value
txtHead.Text=rs.Fields(6).Value
Else
txtContacter.Text=""
txtTelephone.Text=""
txtAddress.Text=""
txtBank.Text=""
txtBankAccount.Text=""
txtHead.Text=""
EndIf
EndSub
PrivateSubForm_Load()
Calldatabind
EndSub
PublicFunctiondatabind()
DimcnAsADODB.Connection
Setcn=NewADODB.Connection
DimrsAsADODB.Recordset
DimrsStrAsString
Setrs=NewADODB.Recordset
cn.OpenMStr
rsStr="Select*fromOwner"
rs.OpenrsStr,cn,adOpenStatic,adLockOptimistic
SetDGOwner.DataSource=rs
SetDCCompanyName.RowSource=rs
DCCompanyName.ListField="companyName"
Ifrs.RecordCount>0Then
DCCompanyName.Text=rs.Fields(0).Value
txtContacter.Text=rs.Fields
(1).Value
txtTelephone.Text=rs.Fields
(2).Value
txtAddress.Text=rs.Fields(3).Value
txtBank.Text=rs.Fields(4).Value
txtBankAccount.Text=rs.Fields(5).Value
txtHead.Text=rs.Fields(6).Value
EndIf
EndFunction
货物增加信息GoodsAdd
PrivateSubCmdGoodsAdd_Click()
IftxtShipName.Text=""ThenMsgBox("船名不能为空!
"):
ExitSub
IftxtVessel.Text=""ThenMsgBox("船次不能为空!
"):
ExitSub
IftxtStart.Text=""ThenMsgBox("出发港不能为空!
"):
ExitSub
IftxtDestination.Text=""ThenMsgBox("目的港不能为空!
"):
ExitSub
'IftxtArriveTime.Text=""ThenMsgBox("预计到港时间不能为空!
"):
ExitSub
'IftxtLeaveTime.Text=""ThenMsgBox("预计离港时间不能为空!
"):
ExitSub
'IftxtRealArriveTime.Text=""ThenMsgBox("预计到港时间不能为空!
"):
ExitSub
'IftxtRealLeaveTime.Text=""ThenMsgBox("预计离港时间不能为空!
"):
ExitSub
IftxtGoodsName.Text=""ThenMsgBox("货名不能为空!
"):
ExitSub
IftxtClass.Text=""ThenMsgBox("货物种类不能为空!
"):
ExitSub
IftxtWeight.Text=""ThenMsgBox("货物重量不能为空!
"):
ExitSub
IftxtCount.Text=""ThenMsgBox("货物数量不能为空!
"):
ExitSub
IftxtBoxNo.Text=""ThenMsgBox("箱号不能为空!
"):
ExitSub
IftxtCompany.Text=""ThenMsgBox("委托公司不能为空!
"):
ExitSub
IftxtDate.Text=""ThenMsgBox("代理日期不能为空!
"):
ExitSub
IftxtAgent.Text=""ThenMsgBox("代理人不能为空!
"):
ExitSub
IftxtAgentTel.Text=""ThenMsgBox("代理人电话不能为空!
"):
ExitSub
IfMsgBox("确认添加信息?
",vbOKCancel)=vbOKThen
DimcnAsADODB.Connection
DimrsStrAsString
Setcn=NewADODB.Connection
DimrsAsADODB.Recordset
Setrs=NewADODB.Recordset
cn.OpenMStr
rsStr="InsertintoGoodsInfoValues('"+txtShipName.Text+"','"+txtVessel.Text+"','"+txtStart.Text+"','"+txtDestination.Text+"','"+txtArriveTime.Text+"','"+txtLeaveTime.Text+"','"+txtRealArriveTime.Text+"','"+txtRealLeaveTime.Text+"','"&_
txtGoodsName.Text+"','"+txtClass.Text+"',"+txtWeight.Text+","+txtCount.Text+",'"+txtBoxNo.Text+"','"&_
txtCompany.Text+"','"+txtDate.Text+"','"+txtAgent.Text+"','"+txtAgentTel.Text+"')"
rs.OpenrsStr,cn,adOpenStatic,adLockOptimistic
MsgBox("恭喜,添加成功")
UnloadfrmGoodsAdd
EndIf
EndSub
费用增加信息FeeAdd
PrivateSubCmdSubmit_Click()
IftxtFeeName.Text=""ThenMsgBox"费用名称不能为空!
":
ExitSub
IftxtFee.Text=""ThenMsgBox"费用额不能为空!
":
ExitSub
IftxtCompany.Text=""ThenMsgBox"收/付款单位不能为空!
":
ExitSub
IfrealFee.Text=""ThenMsgBox"实收/实付金额不能为空!
":
ExitSub
IfrealTime.Text=""ThenMsgBox"实收/实付时间不能为空!
":
ExitSub
IfMsgBox("确认添加信息?
",vbOKCancel)=vbOKThen
DimcnAsADODB.Connection
DimrsStrAsString
Setcn=NewADODB.Connection
DimrsAsADODB.Recordset
Setrs=NewADODB.Recordset
'cnStr="Provider=SQLOLEDB.1;InitialCatalog=MyDB;UID=feng;PWD=feng123;persitSecurityInfo=True;DataSource=JUNIE\JUNIE"
cn.OpenMStr
rsStr="InsertintoF