数据库课程设计 (17)

课程设计报告课程设计题目：档案管理系统专业：网络工程班级：10211303姓名：朱秀刚学号: 1021130326指导教师: 游胜玉2011年12月3日一、目的：通过本次课程设计让学生能够综合运用所学的关系数据库原理知识解决并能设计一个实际问题，进一步掌握数据库原理的相关理论和数据库的设计实现过程，进一步提高学生的分析问题和解决问题的能力以及学生的动手能力。

二、课程设计要求：1．对各个系统进行系统功能需求分析2. 数据库设计分析阶段，进行详细的数据库需求分析，进行概念数据库的设计，画出数据库的E-R图（局部和整体E-R图）3. 设计出详细的逻辑数据库结构，将各个实体和联系转化为相应的二维表即关系模式，指定各个关系的主关键字和外部关键字，并对各个关系的约束加以限定4. 通过企业管理器或是查询分析器实现各个二维关系（建议最好用SQL代码实现），要求建立相关的索引5. 根据系统功能需求设计相应的查询视图6. 要求根据系统功能需求建立存储过程7. 根据功能需求建立相应的触发器以保证数据的一致性8. 通过建立用户和权限分配实现数据库一定的安全性，考虑数据库的备份与恢复（此内容选作）三、实现思路1.实验要求建立一个档案管理系统，实现档案信息的录入、修改、删除查询等功能。

通过分析本实验的E-R图如下：2.利用SQL建立一个名为：DangAn的数据库，数据库中在建立三张表分别为：个人档案基本信息、个人档案详细信息、迁动信息。

3.利用VB做为前台，对数据库中的数据进行管理操作。

四、实现过程1.建立数据库和变的代码如下：create database DangAnOn(name=DangAn_data,filename='D:\DangAnData.mdf',size=10,maxsize=50,fileGrowth=5)loG on(name=DangAn_Log,filename='D:\DanganData.ldf',size=5,maxsize=25,fileGrowth=5)create table 个人档案基本信息(省份证号int constraint Number_prim primary key,姓名char(10),性别char(2)default '男',年龄int,籍贯char(12),家庭住址varchar(40),)create table 个人档案详细信息(身份证号int constraint xiangxi_prim primary key,政治面貌char(10),婚姻状况char(4) default '未婚',现居住地varchar(40),工作单外varchar(40))create table 迁动信息(身份证号int constraint qingdong_prim primary key, 迁出时间datetime,迁入时间datetime,)2.建立VB前台界面图如下：2.功能实现的代码如下：(1).登录界面代码：Private Sub Command1_Click() ‘登录按钮的功能If Text1.Text = "zhuxiugang" And Text2.Text = "123" Then Form1.Visible = FalseForm2.Visible = TrueEnd IfEnd SubPrivate Sub Denglu_Click() ‘编辑菜单中登录项的功能Label1.Visible = TrueLabel2.Visible = TrueLabel3.Visible = TrueText1.Visible = TrueText2.Visible = TrueCommand1.Visible = TrueEnd SubPrivate Sub Form_Load() ‘窗体1的初始化Label1.Visible = FalseLabel2.Visible = FalseLabel3.Visible = TrueText1.Visible = FalseText2.Visible = FalseCommand1.Visible = FalseEnd SubPrivate Sub Tuichu_Click() ‘单击退出按钮退出程序EndEnd Sub(2)操作界面的代码实验总结如下：Private Sub Bianji_Click()'编辑信息初始化Text1.DataField = "身份证号"Text2.DataField = "姓名"Text3.DataField = "性别"Text4.DataField = "年龄"Text5.DataField = "籍贯"Text6.DataField = "家庭住址"Text7.DataField = "身份证号"Text8.DataField = "政治面貌"Text9.DataField = "婚姻状况"Text10.DataField = "现居住地"Text11.DataField = "学历"Text12.DataField = "工作单位"Text13.DataField = "迁动号"Text14.DataField = "身份证号"Text15.DataField = "迁出时间"Text16.DataField = "迁入时间"Text17.DataField = "迁出地址"Text18.DataField = "迁往地址"SSTab1.Tab = 0DataGrid1.Visible = FalseDataGrid2.Visible = FalseDataGrid3.Visible = FalseCommand1.Visible = FalseForm1.Visible = FalseForm2.Visible = TrueCmdadd.Visible = TrueCmdchange.Visible = TrueCmddelete.Visible = TrueCmdprev.Visible = TrueCmdnext.Visible = TrueCmdfirst.Visible = TrueCmdlast.Visible = TrueEnd SubPrivate Sub chaxun_Click() '查询信息初始化Form1.Visible = False'Frame1.Visible = FalseCmdadd.Visible = FalseCmdchange.Visible = FalseCmddelete.Visible = FalseCmdprev.Visible = FalseCmdnext.Visible = FalseCmdfirst.Visible = FalseCmdlast.Visible = FalseCommand1.Visible = TrueDataGrid1.Visible = TrueDataGrid2.Visible = TrueDataGrid3.Visible = True'清楚信息Text1.Text = ""Text2.Text = ""Text3.Text = ""Text4.Text = ""Text5.Text = ""Text6.Text = ""Text7.Text = ""Text8.Text = ""Text9.Text = ""Text10.Text = ""Text11.Text = ""Text12.Text = ""Text13.Text = ""Text14.Text = ""Text15.Text = ""Text16.Text = ""Text17.Text = ""Text18.Text = ""End SubPrivate Sub Cmdadd_Click() '增加信息Select Case Cmdadd.CaptionCase "增加"Select Case SSTab1.TabCase 0Ado1.Recordset.AddNewCase 1Ado2.Recordset.AddNewCase 2Ado3.Recordset.AddNewEnd Select'在添加操作完成之前禁止其他操作SSTab1.TabEnabled(0) = FalseSSTab1.TabEnabled(1) = FalseSSTab1.TabEnabled(2) = FalseCmdadd.Caption = "确定"Cmdchange.Enabled = FalseCmddelete.Enabled = FalseCmdprev.Enabled = FalseCmdnext.Enabled = FalseCmdfirst.Enabled = FalseCmdlast.Enabled = FalseCase "确定"SSTab1.TabEnabled(0) = TrueSSTab1.TabEnabled(1) = TrueSSTab1.TabEnabled(2) = TrueCmdadd.Caption = "增加"Cmdchange.Enabled = TrueCmddelete.Enabled = TrueCmdprev.Enabled = TrueCmdnext.Enabled = TrueCmdfirst.Enabled = TrueCmdlast.Enabled = TrueEnd SelectEnd SubPrivate Sub Cmdchange_Click() '修改信息Select Case Cmdchange.CaptionCase "修改"Select Case SSTab1.TabCase 0Ado1.Recordset.EditModeCase 1Ado2.Recordset.EditModeCase 2Ado3.Recordset.EditModeEnd Select'在添加操作完成之前禁止其他操作SSTab1.TabEnabled(0) = FalseSSTab1.TabEnabled(1) = FalseSSTab1.TabEnabled(2) = FalseCmdadd.Caption = FalseCmdchange.Enabled = "确定"Cmddelete.Enabled = FalseCmdprev.Enabled = FalseCmdnext.Enabled = FalseCmdfirst.Enabled = FalseCmdlast.Enabled = FalseCase "确定"SSTab1.TabEnabled(0) = TrueSSTab1.TabEnabled(1) = TrueSSTab1.TabEnabled(2) = TrueCmdadd.Caption = TrueCmdchange.Enabled = "修改"Cmddelete.Enabled = TrueCmdprev.Enabled = TrueCmdnext.Enabled = TrueCmdfirst.Enabled = TrueCmdlast.Enabled = TrueEnd SelectEnd SubPrivate Sub Cmddelete_Click() '删除信息Dim i As Integeri = MsgBox("确定要删除此记录？", vbYesNo + vbExclamation + vbDefaultButton1, "编辑")If i = vbYes Then'删除当前选项卡所对应表中的记录Select Case SSTab1.TabCase 0Ado1.Recordset.DeleteAdo1.RefreshCase 1Ado2.Recordset.DeleteAdo2.RefreshCase 2Ado3.Recordset.DeleteAdo3.RefreshEnd SelectEnd IfEnd SubPrivate Sub Cmdfirst_Click() '移动到第一条记录Select Case SSTab1.TabCase 0Ado1.Recordset.MoveFirstCmdfirst.Enabled = FalseCmdprev.Enabled = FalseCmdnext.Enabled = TrueCmdlast.Enabled = TrueCase 1Ado2.Recordset.MoveFirstCmdfirst.Enabled = FalseCmdprev.Enabled = FalseCmdnext.Enabled = TrueCmdlast.Enabled = TrueCase 2Ado3.Recordset.MoveFirstCmdfirst.Enabled = FalseCmdprev.Enabled = FalseCmdnext.Enabled = TrueCmdlast.Enabled = TrueEnd SelectEnd SubPrivate Sub Cmdlast_Click() '移动到最后一条记录Select Case SSTab1.TabCase 0Ado1.Recordset.MoveLastCmdfirst.Enabled = TrueCmdprev.Enabled = TrueCmdnext.Enabled = FalseCmdlast.Enabled = FalseCase 1Ado2.Recordset.MoveLastCmdfirst.Enabled = TrueCmdprev.Enabled = TrueCmdnext.Enabled = FalseCmdlast.Enabled = FalseCase 2Ado3.Recordset.MoveLastCmdfirst.Enabled = TrueCmdprev.Enabled = TrueCmdnext.Enabled = FalseCmdlast.Enabled = FalseEnd SelectEnd SubPrivate Sub Cmdnext_Click() '显示下一条记录Select Case SSTab1.TabCase 0Ado1.Recordset.MoveNextIf Ado1.Recordset.EOF ThenAdo1.Recordset.MoveLastCmdfirst.Enabled = TrueCmdprev.Enabled = TrueCmdnext.Enabled = FalseCmdlast.Enabled = FalseElseCmdfirst.Enabled = TrueCmdprev.Enabled = TrueCmdnext.Enabled = TrueCmdlast.Enabled = TrueEnd IfCase 1Ado2.Recordset.MoveNextIf Ado2.Recordset.EOF ThenAdo2.Recordset.MoveLastCmdfirst.Enabled = TrueCmdprev.Enabled = TrueCmdnext.Enabled = FalseCmdlast.Enabled = FalseElseCmdfirst.Enabled = TrueCmdprev.Enabled = TrueCmdnext.Enabled = TrueCmdlast.Enabled = TrueEnd IfCase 2Ado3.Recordset.MoveNextIf Ado3.Recordset.EOF ThenAdo3.Recordset.MoveLastCmdfirst.Enabled = TrueCmdprev.Enabled = TrueCmdnext.Enabled = FalseCmdlast.Enabled = FalseElseCmdfirst.Enabled = TrueCmdprev.Enabled = TrueCmdnext.Enabled = TrueCmdlast.Enabled = TrueEnd IfEnd SelectEnd SubPrivate Sub Cmdprev_Click() '显示上一条记录Select Case SSTab1.TabCase 0Ado1.Recordset.MovePreviousIf Ado1.Recordset.BOF ThenAdo1.Recordset.MoveFirstCmdfirst.Enabled = FalseCmdprev.Enabled = FalseCmdnext.Enabled = TrueCmdlast.Enabled = TrueElseCmdfirst.Enabled = TrueCmdprev.Enabled = TrueCmdnext.Enabled = TrueCmdlast.Enabled = TrueEnd IfCase 1Ado2.Recordset.MovePreviousIf Ado2.Recordset.BOF ThenAdo2.Recordset.MoveFirstCmdfirst.Enabled = FalseCmdprev.Enabled = FalseCmdnext.Enabled = TrueCmdlast.Enabled = TrueElseCmdfirst.Enabled = TrueCmdprev.Enabled = TrueCmdnext.Enabled = TrueCmdlast.Enabled = TrueEnd IfCase 2Ado3.Recordset.MovePreviousIf Ado3.Recordset.BOF ThenAdo3.Recordset.MoveFirstCmdfirst.Enabled = FalseCmdprev.Enabled = FalseCmdnext.Enabled = TrueCmdlast.Enabled = TrueElseCmdfirst.Enabled = TrueCmdprev.Enabled = TrueCmdnext.Enabled = TrueCmdlast.Enabled = TrueEnd IfEnd SelectEnd SubPrivate Sub Command1_Click() '按条件查找Dim sql As StringSelect Case SSTab1.TabCase 0sql = "select * from 个人档案基本信息where 身份证号='" + Text1.Text + "'" Ado4.RecordSource = sqlAdo4.RefreshCase 1sql = "select * from 个人档案详细信息where 身份证号='" + Text7.Text + "'" Ado5.RecordSource = sqlAdo5.RefreshCase 2sql = "select * from 迁动信息where 身份证号='" + Text14.Text + "'"Ado6.RecordSource = sqlAdo6.RefreshEnd SelectEnd SubPrivate Sub Form_Load()DataGrid1.Visible = FalseDataGrid2.Visible = FalseDataGrid3.Visible = FalseCommand1.Visible = FalseAdo1.Visible = FalseAdo2.Visible = FalseAdo3.Visible = FalseAdo4.Visible = FalseAdo5.Visible = FalseAdo6.Visible = False'建立信息源Text1.DataField = "身份证号"Text2.DataField = "姓名"Text3.DataField = "性别"Text4.DataField = "年龄"Text5.DataField = "籍贯"Text6.DataField = "家庭住址"Text7.DataField = "身份证号"Text8.DataField = "政治面貌"Text9.DataField = "婚姻状况"Text10.DataField = "现居住地"Text11.DataField = "学历"Text12.DataField = "工作单位"Text13.DataField = "迁动号"Text14.DataField = "身份证号"Text15.DataField = "迁出时间"Text16.DataField = "迁入时间"Text17.DataField = "迁出地址"Text18.DataField = "迁往地址"SSTab1.Tab = 0End SubPrivate Sub Tuichu_Click()EndEnd Sub五、分析得与失在该程序中用到了VB作为操作前台，操作见面和Windows系统相似，这让操作人员使用时更直观，容易掌握，没有SQL语言基础的人也能进行对数据的管理，界面也更美观。