数据库课程设计报告-个人记账系统(共31页).doc
精选优质文档-倾情为你奉上目 录一、课程设计目的1二、系统设计1三、数据库逻辑结构设计2四、系统功能模块图4五、源程序代码11六、课程设计总结30七、存在的问题及不足30一、 课程设计目的通过数据库课程设计，培养学生数据库设计的综合能力。从需求分析和数据库逻辑、物理设计，到功能分析和应用程序的最终实现，让学生亲自动手参与一个具有一定规模的数据库应用系统的设计和开发，真正理解和掌握数据库设计和开发的思路和方法，并更好的理解和消化课本所学的知识，为今后的实际应用打下良好的基础。二、 系统设计本课程设计（个人记账系统）是用VB+SQL设计完成的。在本系统里可以对家庭人员基本信息以及家庭账目的收入支出进行简单维护。主要功能包括对家庭人员信息以及家庭收支的添加、修改、删除、查找、统计等功能。三、数据库逻辑结构设计1.SQL数据库设计表格设计：userinfo表，记录用户信息familyinfo表，记录家庭人员信息 income表，记录收入方面信息 pay表，记录支出方面信息 account表，汇总收支信息 allaccount表，计算总收支及结余存储过程定义countall存储过程，统计account表中的收支信息，并对收支金额进行求和，算出结余插入到allaccount表中。触发器:familyinfo表上定义update触发器完成对income和pay上收支人员的一致性更新。income表上定义insert，delete及update触发器，完成对于account表上的各个数据的一致性更新。pay表上定义insert，delete及update触发器，完成对于account表上的各个数据的一致性更新。account表上定义insert,delete及update触发器，调用countall存储过程完成对于allacount表上数据的统计更新。四、系统功能模块图1、数据源连接2、界面设计登陆界面：注册界面：收入管理界面：界面中分为三个部分，第一部分收入信息，第二部分显示当前总收支及与结余，并随着用户添加修改删除的操作及时更新，第三部分是操作信息，包括查询、添加、修改、删除。住处管理界面：界面中分为三个部分，第一部分收入信息，第二部分显示当前总收支及与结余，并随着用户添加修改删除的操作及时更新，第三部分是操作信息，包括查询、添加、修改、删除。收支界面显示：家庭人员信息显示：五、附：源程序代码 SQL代码触发器 pay表上de_pay create trigger de_payon payfor deleteasdeclare new_time char(20)declare new_item char(20)declare new_mon intdeclare new_rela char(20)declare new_expl char(200)select new_time=支出时间 from deletedselect new_item=支出项目 from deletedselect new_mon=支出金额 from deletedselect new_rela=支出人员 from deletedselect new_expl=支出说明 from deleteddelete from account where 录入时间=new_time and 收支金额=new_mon and 收支项目=new_item and 收支人员=new_rela and 收支说明=new_explout_trcreate trigger out_tron payfor insertasdeclare new_time char(20)declare new_item char(20)declare new_mon intdeclare new_rela char(20)declare new_expl char(200)select new_time=支出时间 from insertedselect new_item=支出项目 from insertedselect new_mon=支出金额 from insertedselect new_rela=支出人员 from insertedselect new_expl=支出说明 from insertedinsert into account(录入时间,收支类型,收支金额,收支项目,收支人员,收支说明)values(new_time,'支出',new_mon,new_item,new_rela,new_expl) up_paycreate trigger up_payon payfor updateasdeclare new_time char(20)declare new_item char(20)declare new_mon intdeclare new_rela char(20)declare new_expl char(200)declare old_time char(20)declare old_item char(20)declare old_mon intdeclare old_rela char(20)declare old_expl char(200)select old_time=支出时间 from deletedselect old_item=支出项目 from deletedselect old_mon=支出金额 from deletedselect old_rela=支出人员 from deletedselect old_expl=支出说明 from deletedselect new_time=支出时间 from insertedselect new_item=支出项目 from insertedselect new_mon=支出金额 from insertedselect new_rela=支出人员 from insertedselect new_expl=支出说明 from insertedupdate account set 录入时间=new_time where 录入时间=old_timeupdate account set 收支金额=new_mon where 收支金额=old_monupdate account set 收支项目=new_item where 收支项目=old_itemupdate account set 收支人员=new_rela where 收支人员=old_relaupdate account set 收支说明=new_expl where 收支说明=old_explincome 表上create trigger de_incomeon incomefor deleteasdeclare new_time char(20)declare new_item char(20)declare new_mon intdeclare new_rela char(20)declare new_expl char(200)select new_time=收入日期 from deletedselect new_item=收入项目 from deletedselect new_mon=收入金额 from deletedselect new_rela=收入人员 from deletedselect new_expl=收入说明 from deleteddelete from account where 录入时间=new_time and 收支金额=new_mon and 收支项目=new_item and 收支人员=new_rela and 收支说明=new_explcreate trigger in_tron incomefor insertasdeclare new_time char(20)declare new_item char(20)declare new_mon intdeclare new_rela char(20)declare new_expl char(200)select new_time=收入日期 from insertedselect new_item=收入项目 from insertedselect new_mon=收入金额 from insertedselect new_rela=收入人员 from insertedselect new_expl=收入说明 from insertedinsert into account(录入时间,收支类型,收支金额,收支项目,收支人员,收支说明)values(new_time,'收入',new_mon,new_item,new_rela,new_expl) create trigger up_incomeon incomefor updateasdeclare new_time char(20)declare new_item char(20)declare new_mon intdeclare new_rela char(20)declare new_expl char(200)declare old_time char(20)declare old_item char(20)declare old_mon intdeclare old_rela char(20)declare old_expl char(200)select old_time=收入日期 from deletedselect old_item=收入项目 from deletedselect old_mon=收入金额 from deletedselect old_rela=收入人员 from deletedselect old_expl=收入说明 from deletedselect new_time=收入日期 from insertedselect new_item=收入项目 from insertedselect new_mon=收入金额 from insertedselect new_rela=收入人员 from insertedselect new_expl=收入说明 from insertedupdate account set 录入时间=new_time where 录入时间=old_timeupdate account set 收支金额=new_mon where 收支金额=old_monupdate account set 收支项目=new_item where 收支项目=old_itemupdate account set 收支人员=new_rela where 收支人员=old_relaupdate account set 收支说明=new_expl where 收支说明=old_explfamilyinfo表上create trigger up_familyon dbo.familyinfofor updateasdeclare new_rela char(20)declare old_rela char(20)select new_rela=家庭人员 from insertedselect old_rela=家庭人员 from deletedupdate income set 收入人员=new_rela where 收入人员=old_relaupdate pay set 支出人员=new_rela where 支出人员=old_relaaccount表上create trigger de_accounton accountfor deleteasexec countallcreate trigger in_accounton accountfor insert asexec countallcreate trigger up_accounton accountfor updateasexec countall存储过程CREATE proc countallasdeclare sum_income intdeclare sum_pay intdeclare sum_count intdeclare old_income intdeclare old_pay intdeclare old_sum intselect old_income=总收入,old_pay=总支出,old_sum=结余 from allaccountselect sum_income=sum(收支金额) from account where 收支类型='收入'select sum_pay=sum(收支金额) from account where 收支类型='支出'set sum_count=sum_income-sum_payupdate allaccount set 总收入=sum_income where 总收入=old_incomeupdate allaccount set 总支出=sum_pay where 总支出=old_payupdate allaccount set 结余=sum_count where 结余=old_sumVB代码'模块Public cn As ADODB.ConnectionPublic rs As ADODB.Recordset'菜单Private Sub Command1_Click()菜单.Hide收入管理.ShowEnd SubPrivate Sub Command2_Click()菜单.Hide支出管理.ShowEnd SubPrivate Sub Command3_Click()菜单.Hide收支显示.ShowEnd SubPrivate Sub Command4_Click()菜单.Hide登录.ShowEnd SubPrivate Sub Command5_Click()If MsgBox("你确定要退出吗？", vbOKCancel, "提示") = vbOK Then EndEnd IfEnd SubPrivate Sub Command6_Click()菜单.Hide家庭人员.ShowEnd Sub'登录Private Sub Command1_Click()Set cn = New ADODB.ConnectionSet rs = New ADODB.Recordsetcn.Open "dsn=keep_account"rs.Open "select * from userinfo where username='" & Text1 & "'" & "and password='" & Text2 & "'", cn, adOpenStatic, adLockOptimisticIf rs.RecordCount > 0 Then 菜单.Show 登录.HideElse MsgBox "密码错误或用户名不存在！请重新输入。" a = a + 1 '密码没输入一次a的数值就增加1 Text1.Text = "" Text2.Text = "" If a > 2 Then '三次输入错误,程序强制退出 MsgBox "三次输入错误，强制退出！" End End IfEnd IfEnd SubPrivate Sub Command2_Click()登录.Hide注册.ShowEnd SubPrivate Sub Command3_Click()If MsgBox("你确定要退出吗？", vbOKCancel, "提示") = vbOK Then EndEnd IfEnd Sub'家庭人员Public Sub export()Text1.Text = rs.Fields("家庭人员")Text2.Text = rs.Fields("年龄")Text3.Text = rs.Fields("政治面貌")Text4.Text = rs.Fields("职业")Text5.Text = rs.Fields("说明")End SubPublic Sub import()rs.Fields("家庭人员") = Text1.Textrs.Fields("年龄") = Text2.Textrs.Fields("政治面貌") = Text3.Textrs.Fields("职业") = Text4.Textrs.Fields("说明") = Text5.TextEnd SubPublic Sub clear()Text1.Text = ""Text2.Text = ""Text3.Text = ""Text4.Text = ""Text5.Text = ""End SubPrivate Sub Command1_Click()On Error GoTo flagrs.MoveFirstCall exportExit Subflag: MsgBox "数据库空或当前已是第一条！"End SubPrivate Sub Command10_Click()If MsgBox("你确定要退出吗？", vbOKCancel, "提示") = vbOK Then EndEnd IfEnd SubPrivate Sub Command2_Click()On Error GoTo flagrs.MovePreviousIf rs.BOF Then MsgBox "当前已是第一条记录！", , "提示" rs.MoveFirstElse Call exportEnd IfExit Subflag: MsgBox "数据库无记录或当前已是第一条！"End SubPrivate Sub Command3_Click()On Error GoTo flagrs.MoveNextIf rs.EOF Then MsgBox "当前已是最后一条记录！", , "提示" rs.MoveLastElse Call exportEnd IfExit Subflag: MsgBox "数据库无记录或当前已是最后一条记录！"End SubPrivate Sub Command4_Click()On Error GoTo flagrs.MoveLastCall exportExit Subflag: MsgBox "数据库无记录或当前已是最后一条记录！"End SubPrivate Sub Command5_Click()On Error GoTo flagIf rs.RecordCount > 0 Then rs.MoveLast rs.AddNew Call import rs.UpdateElse rs.AddNew Call import rs.UpdateEnd If收入管理.Combo3.AddItem (Text1.Text)收入管理.Hide支出管理.Combo3.AddItem (Text1.Text)支出管理.Hide'Call clearrs.Closers.Open "select * from familyinfo", cn, adOpenStatic, adLockOptimisticrs.MoveLastExit Subflag: MsgBox "请检查你输入的格式是否正确."End SubPrivate Sub Command6_Click()If MsgBox("你确定要修改该条记录？", vbOKCancel, "提示") = vbOK Then rs.Delete rs.Update rs.AddNew Call import rs.UpdateEnd Ifrs.Closers.Open "select * from familyinfo", cn, adOpenStatic, adLockOptimisticEnd SubPrivate Sub Command7_Click()On Error GoTo flagDim a As IntegerDim b As Integera = 收入管理.Combo3.ListCount - 1b = 支出管理.Combo3.ListCount - 1If MsgBox("你确定要删除这一条记录？", vbOKCancel, "提示") = vbOK Then rs.Delete rs.Update rs.MovePrevious Call export 收入管理.Combo3.RemoveItem a 收入管理.Hide 支出管理.Combo3.RemoveItem bEnd Ifrs.Closers.Open "select * from familyinfo", cn, adOpenStatic, adLockOptimisticExit Subflag: MsgBox "数据库无记录或已删除记录！"End SubPrivate Sub Command8_Click()On Error GoTo flagIf Combo2.Text = "全部显示" Then Set 家庭人员显示.DataGrid1.DataSource = rs 家庭人员显示.ShowElse If Text6.Text = "" Then MsgBox "请输入查找信息！" Else rs.Close rs.Open "select * from familyinfo where " & Combo2.Text & "='" & Text6 & "'", cn, adOpenStatic, adLockOptimistic '按用户指定的方式,与用户提供的信息查询 If rs.RecordCount > 0 Then Call export Else MsgBox "对不起，记录中没有你要查找的项！" End If End IfEnd If'rs.Close'rs.Open "select * from familyinfo", cn, adOpenStatic, adLockOptimisticExit Subflag: MsgBox "请检查你输入的格式是否正确:"End SubPrivate Sub Command9_Click()家庭人员.Hide菜单.ShowEnd SubPrivate Sub Form_Load()Set cn = New ADODB.ConnectionSet rs = New ADODB.Recordsetcn.Open "dsn=keep_account"rs.Open "select * from familyinfo", cn, adOpenStatic, adLockOptimisticCall exportEnd Sub'收入管理Private Sub Command1_Click()On Error GoTo flagrs.MoveFirstCall exportExit Subflag: MsgBox "数据库空或当前已是第一条！"End SubPrivate Sub Command10_Click()If MsgBox("你确定要退出吗？", vbOKCancel, "提示") = vbOK Then EndEnd IfEnd SubPrivate Sub Command2_Click()On Error GoTo flagrs.MovePreviousIf rs.BOF Then MsgBox "当前已是第一条记录！", , "提示" rs.MoveFirstElse Call exportEnd IfExit Subflag: MsgBox "数据库无记录或当前已是第一条！"End SubPrivate Sub Command3_Click()On Error GoTo flagrs.MoveNextIf rs.EOF Then MsgBox "当前已是最后一条记录！", , "提示" rs.MoveLastElse Call exportEnd IfExit Subflag: MsgBox "数据库无记录或当前已是最后一条记录！"End SubPrivate Sub Command4_Click()On Error GoTo flagrs.MoveLastCall exportExit Subflag: MsgBox "数据库无记录或当前已是最后一条记录！"End SubPrivate Sub Command5_Click()On Error GoTo flagIf rs.RecordCount > 0 Then rs.MoveLast rs.AddNew Call import rs.UpdateElse rs.AddNew Call import rs.UpdateEnd IfCall clearrs.Closers.Open "select * from allaccount", cn, adOpenStatic, adLockOptimisticLabel10.Caption = rs.Fields("总收入")Label11.Caption = rs.Fields("总支出")Label12.Caption = rs.Fields("结余")rs.Closers.Open "select * from income", cn, adOpenStatic, adLockOptimisticrs.MoveLastExit Subflag: MsgBox "请检查你输入的格式是否正确." & vbCrLf & "收入金额为数字格式" & vbCrLf & "时期请按:(年)-(月)-(日)输入" & vbCrLf & "其他为文本格式"End SubPrivate Sub Command6_Click()If MsgBox("你确定要修改该条记录？", vbOKCancel, "提示") = vbOK Then rs.Delete rs.Update rs.AddNew Call import rs.UpdateEnd Ifrs.Closers.Open "select * from allaccount", cn, adOpenStatic, adLockOptimisticLabel10.Caption = rs.Fields("总收入")Label11.Caption = rs.Fields("总支出")Label12.Caption = rs.Fields("结余")'rs.Close'rs.Open "select * from income", cn, adOpenStatic, adLockOptimisticEnd SubPrivate Sub Command7_Click()On Error GoTo flagIf MsgBox("你确定要删除这一条记录？", vbOKCancel, "提示") = vbOK Then rs.Delete rs.Update rs.MovePrevious Call exportEnd Ifrs.Closers.Open "select * from allaccount", cn, adOpenStatic, adLockOptimisticLabel10.Caption = rs.Fields("总收入")Label11.Caption = rs.Fields("总支出")Label12.Caption = rs.Fields("结余")'rs.Close'rs.Open "select * from income", cn, adOpenStatic, adLockOptimisticExit Subflag: MsgBox "数据库无记录或已删除记录！"End SubPrivate Sub Command8_Click()On Error GoTo flagIf Combo2.Text = "全部显示" Then Set 收入显示.DataGrid1.DataSource = rs 收入显示.ShowElse If Text6.Text = "" Then MsgBox "请输入查找信息！" Else rs.Close rs.Open "select * from income where " & Combo2.Text & "='" & Text6 & "'", cn, adOpenStatic, adLockOptimistic '按用户指定的方式,与用户提供的信息查询 If rs.RecordCount > 0 Then Call export Else MsgBox "对不起，记录中没有你要查找的项！" End If End IfEnd If'rs.Close'rs.Open "select * from income", cn, adOpenStatic, adLockOptimisticExit Subflag: MsgBox "请检查你输入的格式是否正确:" & vbCrLf & "日期请按(年)-(月)-(日)格式输入."End SubPrivate Sub Command9_Click()收入管理.Hide菜单.ShowEnd SubPrivate Sub Form_Load()Set cn = New ADODB.ConnectionSet rs = New ADODB.Recordsetcn.Open "dsn=keep_account"rs.Open "select * from allaccount", cn, adOpenStatic, adLockOptimisticLabel10.Caption = rs.Fields("总收入")Label11.Caption = rs.Fields("总支出")Label12.Caption = rs.Fields("结余")rs.Closers.Open "select * from income", cn, adOpenStatic, adLockOptimisticCall exportEnd Sub'收支显示Private Sub Command1_Click()收支显示.Hide菜单.ShowEnd SubPrivate Sub Command2_Click()If MsgBox("你确定要退出吗？", vbOKCancel, "提示") = vbOK Then EndEnd IfEnd SubPrivate Sub Form_Load()Set cn = New ADODB.ConnectionSet rs = New ADODB.Recordsetcn.Open "dsn=keep_account"rs.Open "select * from account"
