数据库设计报告模板
附录
出租屋管理系统设计说明
(本设计以ASP 为前台对数据库进行操作)
1. 需求
本系统主要用于旅馆或宾馆出租的房间管理。 1. 用户身份的登记 2. 房屋出租管理 3. 年收、支情况
2. 总体设计
本系统包括:标准模块、系统登录模块、主界面模块、系统管理模块、学生基本信息管理模块、选课模块、成绩管理模块。 1. 标准模块
定义公共变量和过程。 2. 系统登录模块
进行用户身份的验证。 3. 主界面模块
作为系统总界面,供用户进行各项选择。 4. 系统管理模块
用户管理及系统退出。 5. 学生基本信息管理模块
学生基本信息的录入和查询。 6. 选课模块
为班级选课。 7. 成绩管理模块
进行学生成绩的录入及查询。
3. 数据库设计及配置
3.1. 数据库设计
该数据库由以下几个表组成:
用户表、学生基本信息表、班级课程表、学生成绩表。
3.1.1 用户表
3.1.2 学生基本信息表
3.1.3 班级课程表
3.1.4 学生成绩表
3.2. 数据库中各表关系
学生基本信息表与班级课程表通过班级名称字段相关联,学生基本信息表与学生成绩表通过学号字段相关联,班级课程表与学生成绩表通过课程名称字段相关联。
4.
系统实现
4.1. 标准模块(公有模块) 4.1.1
标准模块中的数据定义
4.1.2 标准模块代码设计
Option Explicit
Public UserName As String Public UserType As Boolean
4.2. 系统登录模块 4.2.1
说明
进行用户身份的验证。特定的用户才能登录到系统。
4.2.2
窗体设计
系统登录窗体的窗体类型为Form ,界面如下图所示:
4.2.3 代码设计
● 公共定义
Option Explicit
Dim Count1 As Integer
● 窗体装载处理程序(Load )
Private Sub Form_Load() Count1 = 0 End Sub
‘错误次数清零
● 取消按钮(cmdCancel )处理程序(Click )
Private Sub cmdCancel_Click() Unload Me
‘系统退出
End Sub
确定按钮(cmdOk )处理程序(Click )
Private Sub cmdOk_Click() Dim s1 As String
Dim Password As String
On Error Resume Next
If Text1.Text = "" Then ‘用户名为空,则退出 MsgBox ("用户名不能为空, 请重新输入") Text1.SetFocus Exit Sub
End If
‘按输入的用户名进行查询 s1 = "SELECT * from 用户表 WHERE 用户名='" & Text1.Text & "'" Adodc1.RecordSource = s1 Adodc1.Refresh
If Adodc1.Recordset.EOF = False Then ‘判断查询结果 UserName = Adodc1.Recordset.Fields(0) ‘有该用户名,即用户名正确 Password = Adodc1.Recordset.Fields(1) UserType = Adodc1.Recordset.Fields(2)
If Password = Text2.Text Then ‘判断密码是否正确
Unload Me ‘密码正确,关闭当前窗体 FrmMain.Show ‘显示主界面窗体 Exit Sub Else
MsgBox ("密码错误, 请重新输入") ‘密码错误,提示 Text2.SetFocus Text2.Text = "" End If
Else ‘没有该用户名,即用户名错误 MsgBox "用户名错误, 请重新输入" Text1.SetFocus Text1.Text = "" Text2.Text = "" End If
Count1 = Count1 + 1 ‘错误次数累计 If Count1 >= 3 Then
MsgBox "错误超过3次, 系统退出" Unload Me End If End Sub
4.3. 主界面模块 4.3.1 说明
作为系统总界面,供用户进行各项选择。
4.3.2
窗体设计
主界面模块窗体类型为MDIForm ,界面如下图所示。
4.3.3 代码设计
● 窗体变为活动窗体处理程序(MDIForm -Activate )
Private Sub MDIForm_Activate()
WindowState = 2 ‘窗体最大化状态
StatusBar1.Panels(1).Text = "用户名: " & UserName ‘在状态栏显示用户名、用户类型 If UserType = True Then
StatusBar1.Panels(2).Text = "用户类型: 管理员" Else
StatusBar1.Panels(2).Text = "用户类型: 普通用户"
End If
If UserType = False Then ‘对普通用户设某些菜单项不可用 AA1.Enabled = False BB1.Enabled = False CC.Enabled = False DD1.Enabled = False
Toolbar1.Buttons(1).Enabled = False ‘对普通用户设工具栏的某些按钮不可用 Toolbar1.Buttons(3).Enabled = False Toolbar1.Buttons(6).Enabled = False Toolbar1.Buttons(7).Enabled = False Toolbar1.Buttons(9).Enabled = False End If End Sub
● 定时器(Timer1)处理程序(Timer )
Private Sub Timer1_Timer()
StatusBar1.Panels(3).Text = Now End Sub
‘在状态栏中显示当前时间
● 工具栏(Toolbar1)处理程序(ButtonClick )
Private Sub Toolbar1_ButtonClick(ByVal Button As MSComctlLib.Button) Select Case Button.Index Case 1
Form11.Show (1) ‘用户管理 Case 3
Form21.Show (1) ‘学生基本信息维护 Case 4
Form22.Show (1) ‘学生基本信息查询 Case 6
Form31.Show (1) ‘课程添加 Case 7
Form32.Show (1) ‘课程删除 Case 9
Form41.Show (1) ‘学生成绩维护
Case 10
Form42.Show (1) ‘学生成绩查询 Case 12
Unload Me
‘系统退出
End Select End Sub
● “用户管理”菜单项处理程序(AA1-Click )
Private Sub AA1_Click() Form11.Show (1) End Sub
● “退出”菜单项处理程序(AA3-click )
Private Sub AA3_Click() Unload Me End Sub
● “学生基本信息维护”菜单项处理程序(BB1-click )
Private Sub BB1_Click() Form21.Show (1) End Sub
● “学生基本信息查询”菜单项处理程序(BB2-click )
Private Sub BB2_Click() Form22.Show (1) End Sub
● “课程添加”菜单项处理程序(CC1-click )
Private Sub CC1_Click() Form31.Show (1) End Sub
● “课程删除”菜单项处理程序(CC2-click )
Private Sub CC2_Click() Form32.Show (1) End Sub
● “学生成绩维护”菜单项处理程序(DD1-click )
Private Sub DD1_Click() Form41.Show (1) End Sub
● “学生成绩查询”菜单项处理程序(DD2-click )
Private Sub DD2_Click() Form42.Show (1) End Sub
4.4. 用户管理模块 4.4.1 说明
创建、删除用户。设定用户权限。
4.4.2
窗体设计
4.4.3 代码设计
● 上一条(Command1)处理(Click )
Private Sub Command1_Click() Command2.Enabled = True
Adodc1.Recordset.MovePrevious If Adodc1.Recordset.BOF Then Command1.Enabled = False Adodc1.Recordset.MoveFirst End If End Sub
● 下一条(Command2)处理(Click )
Private Sub Command2_Click() Command1.Enabled = True Adodc1.Recordset.MoveNext If Adodc1.Recordset.EOF Then Command2.Enabled = False Adodc1.Recordset.MoveLast End If End Sub
● 添加(Command3)处理(Click )
Private Sub Command3_Click()
If Command3.Caption = "添加" Then Command3.Caption = "确定"
Command1.Enabled = False
Command2.Enabled = False Command4.Enabled = False Adodc1.Recordset.AddNew Text1.SetFocus Else
If Text1.Text = "" Then
MsgBox ("用户名不能为空") Exit Sub End If
Command3.Caption = "添加" Adodc1.Recordset.Update Command1.Enabled = True Command2.Enabled = True Command4.Enabled = True End If End Sub
● 删除(Command4)处理(Click )
Private Sub Command4_Click() Adodc1.Recordset.Delete Adodc1.Recordset.MoveNext If Adodc1.Recordset.EOF Then Adodc1.Recordset.MoveLast End If End Sub
● 退出(Command5)处理(Click )
Private Sub Command5_Click() Unload Me End Sub
4.5. 学生基本信息管理模块 4.5.1
说明
学生基本信息的添加、删除、查询。
该模块有两个窗体:学生基本信息维护窗体和学生基本信息查询窗体
4.5.2
4.5.2.1.
学生基本信息维护窗体
窗体设计
4.5.2.2. 代码设计
● 窗体装载处理程序(Load )
Private Sub Form_Load()
DataGrid1.AllowAddNew = True DataGrid1.AllowDelete = True DataGrid1.AllowUpdate = True End Sub
● 退出(Command1)处理(Click )
Private Sub Command1_Click() Unload Me End Sub
4.5.3
4.5.3.1.
学生基本信息查询窗体
窗体设计
4.5.3.2. 代码设计
● 查询(Command1)处理(Click )
Private Sub Command1_Click() Dim s1 As String
s1 = ""
If Check1.Value = 1 Then If Text1.Text "" Then
s1 = s1 & "姓名='" & Text1.Text & "'" End If
End If
If Check2.Value = 1 Then If Text2.Text "" Then If s1 "" Then
s1 = s1 & " and " End If
s1 = s1 & "学号='" & Text2.Text & "'" End If
End If
If Check3.Value = 1 Then
If DataList1.Text "" Then If s1 "" Then
s1 = s1 & " and " End If
s1 = s1 & "班级名称='" & DataList1.Text & "'" End If End If
If s1 "" Then
s1 = "where " & s1
End If
Adodc1.RecordSource = "select * from 学生基本信息表 " & s1 Adodc1.Refresh
Set DataGrid1.DataSource = Adodc1 End Sub
● 退出(Command2)处理(Click )
Private Sub Command2_Click() Unload Me End Sub
● 窗体装载处理程序(Load )
Private Sub Form_Load()
DataGrid1.AllowAddNew = False
DataGrid1.AllowDelete = False DataGrid1.AllowUpdate = False End Sub
4.6. 选课模块 4.6.1 4.6.2
4.6.2.1.
说明
该模块为一个班级选课。
课程添加窗体
用于课程的添加和查询
窗体设计
4.6.2.2. 代码设计
● 添加(Command1)处理(Click )
Private Sub Command1_Click()
Dim s1 As String, s2 As String On Error Resume Next
If Text1.Text = "" Or Text2.Text = "" Then MsgBox ("输入不能为空") Exit Sub End If
s1 = "select * from 班级课程表 where 班级名称='"
s2 = Text1.Text & "' and 课程名称='" & Text2.Text & "'" Adodc1.RecordSource = s1 & s2 Adodc1.Refresh
If Adodc1.Recordset.EOF = False Then Text1.SetFocus
MsgBox "该记录已存在, 请重新输入", , "警告" Exit Sub End If
s1 = "select * from 班级课程表" Adodc1.RecordSource = s1 Adodc1.Refresh
Adodc1.Recordset.MoveLast Adodc1.Recordset.AddNew
Adodc1.Recordset.Fields("班级名称") = Text1.Text Adodc1.Recordset.Fields("课程名称") = Text2.Text Adodc1.Recordset.Update Adodc1.Refresh
s1 = "select 课程名称 from 班级课程表 where 班级名称='" & Text1.Text & "'" Adodc1.RecordSource = s1 Adodc1.Refresh
Set DataGrid1.DataSource = Adodc1.Recordset Text2.SetFocus Text2.Text = "" Exit Sub End Sub
● 查询(Command2)处理(Click )
Private Sub Command2_Click() Dim s1 As String
s1 = "select 课程名称 from 班级课程表 where 班级名称='" & Text1.Text & "'"
Adodc1.RecordSource = s1 Adodc1.Refresh
Set DataGrid1.DataSource = Adodc1.Recordset End Sub
● 退出(Command3)处理(Click )
Private Sub Command3_Click() Unload Me End Sub
● 窗体装载处理程序(Load )
Private Sub Form_Load()
DataGrid1.AllowAddNew = False DataGrid1.AllowDelete = False DataGrid1.AllowUpdate = False End Sub
4.6.3
4.6.3.1.
课程删除窗体
窗体设计
4.6.3.2. 代码设计
● 退出(Command1)处理(Click )
Private Sub Command1_Click() Unload Me End Sub
● 数据列表框(DataList1)处理(Click )
Private Sub DataList1_Click() Dim s1 As String
s1 = "select * from 班级课程表 where 班级名称='" & DataList1.Text & "'" Adodc2.RecordSource = s1 Adodc2.Refresh
Set DataGrid1.DataSource = Adodc2 End Sub
● 窗体装载处理程序(Load )
Private Sub Form_Load()
DataGrid1.AllowDelete = True DataGrid1.AllowUpdate = True DataGrid1.AllowAddNew = False End Sub
4.7. 成绩管理模块 4.7.1 4.7.2
4.7.2.1.
说明
进行学生成绩的录入及查询。
成绩录入窗体
进行学生成绩的添加、修改及删除。
窗体设计
4.7.2.2. 代码设计
录入(Command1)处理(Click )
Private Sub Command1_Click()
Dim s1 As String, s2 As String, s3 As String On Error Resume Next
s1 = "select * from 学生成绩表 "
s2 = "where 学号='" & List2.Text & "' and " s3 = "课程名称='" & List1.Text & "'"
Adodc3.RecordSource = s1 & s2 & s3 Adodc3.Refresh
If Adodc3.Recordset.EOF = False Then
Adodc3.Recordset.Fields(2) = Val(Text1.Text) Else
Adodc3.Recordset.MoveLast Adodc3.Recordset.AddNew
Adodc3.Recordset.Fields(0) = List2.Text Adodc3.Recordset.Fields(1) = List1.Text
Adodc3.Recordset.Fields(2) = Val(Text1.Text) End If
Adodc3.Recordset.Update Adodc3.Refresh
s1 = "select 学号, 成绩 from 学生成绩表 where 课程名称='" & List1.Text & "'" s2 = " and 学号 in (select 学号 from 学生基本信息表 "
s3 = "where 班级名称 ='" & DataList1.Text & "')" Adodc3.RecordSource = s1 & s2 & s3 Adodc3.Refresh
Set DataGrid1.DataSource = Adodc3 End Sub
● 退出(Command1)处理(Click )
Private Sub Command2_Click() Unload Me End Sub
● 数据列表框(DataList1)处理(Click )
Private Sub DataList1_Click()
Dim s1 As String, s2 As String
Set DataGrid1.DataSource = Nothing
s1 = "select 课程名称 from 班级课程表 "
s2 = "where 班级名称='" & DataList1.Text & "'" Adodc2.RecordSource = s1 & s2 Adodc2.Refresh
If Adodc2.Recordset.EOF = True Then
MsgBox "请在班级课程表里添加该班级的课程名称" Exit Sub End If
List1.Clear
Adodc2.Recordset.MoveFirst
Do While Adodc2.Recordset.EOF = False
List1.AddItem Adodc2.Recordset.Fields(0) Adodc2.Recordset.MoveNext Loop
s1 = "select 学号 from 学生基本信息表 "
s2 = "where 班级名称='" & DataList1.Text & "'" Adodc2.RecordSource = s1 & s2 Adodc2.Refresh
If Adodc2.Recordset.EOF = True Then MsgBox "请在学生信息表里添加该班级" Exit Sub End If
List2.Clear
Adodc2.Recordset.MoveFirst
Do While Adodc2.Recordset.EOF = False
List2.AddItem Adodc2.Recordset.Fields(0)
Adodc2.Recordset.MoveNext Loop End Sub
● 窗体装载处理程序(Load )
Private Sub Form_Load()
DataGrid1.AllowDelete = True DataGrid1.AllowAddNew = False DataGrid1.AllowUpdate = False End Sub
● 列表框(List1)处理(Click )
Private Sub List1_Click()
Dim s1 As String, s2 As String, s3 As String
s1 = "select 学号, 成绩 from 学生成绩表 where 课程名称='" & List1.Text & "'" s2 = " and 学号 in (select 学号 from 学生基本信息表 " s3 = "where 班级名称 ='" & DataList1.Text & "')" Adodc3.RecordSource = s1 & s2 & s3 Adodc3.Refresh
Set DataGrid1.DataSource = Adodc3 End Sub
● 列表框(List2)处理(Click )
Private Sub List2_Click() Text1.Text = "" End Sub
4.7.3 成绩查询窗体
进行学生成绩的查询
4.7.3.1. 窗体设计
4.7.3.2.
● 公共定义
代码设计
Option Explicit Dim opt As Integer
● 组合框(Combo1)处理(Click )
Private Sub Combo1_Click()
Dim s1 As String, s2 As String
Set DataGrid1.DataSource = Nothing
Combo2.Clear
If opt = 0 Then
s1 = "select 课程名称 from 班级课程表 where 班级名称='"
s2 = Combo1.Text & "' group by 课程名称"
Adodc2.RecordSource = s1 & s2
Else
s1 = "select 学号 from 学生基本信息表 where 班级名称='"
s2 = Combo1.Text & "'"
Adodc2.RecordSource = s1 & s2
End If
Adodc2.Refresh
Do While Adodc2.Recordset.EOF = False
Combo2.AddItem Adodc2.Recordset.Fields(0)
Adodc2.Recordset.MoveNext
Loop
End Sub
● 组合框(Combo2)处理(Click )
Private Sub Combo2_Click()
Dim s1 As String, s2 As String, s3 As String
Set DataGrid1.DataSource = Nothing
If opt = 0 Then
s1 = "select * from 学生成绩表 where 课程名称='"
s2 = Combo2.Text & " ' and 学号 in (select 学号 "
s3 = "from 学生基本信息表 where 班级名称 ='" & Combo1.Text & "')" Adodc1.RecordSource = s1 & s2 & s3
Adodc1.Refresh
Set DataGrid1.DataSource = Adodc1
Else
s1 = "select * from 学生成绩表 where 学号='" & Combo2.Text & "'" Adodc1.RecordSource = s1
Adodc1.Refresh
Set DataGrid1.DataSource = Adodc1
End If
End Sub
● 退出(Command1)处理(Click )
Private Sub Command1_Click()
Unload Me
End Sub
● 窗体装载处理程序(Load )
Private Sub Form_Load()
DataGrid1.AllowAddNew = False
DataGrid1.AllowDelete = False
DataGrid1.AllowUpdate = False
End Sub
单选按钮(Option1)处理(Click )
Private Sub Option1_Click(Index As Integer)
If Index > 1 Then
Exit Sub
End If
Combo1.Clear
Combo2.Clear
Set DataGrid1.DataSource = Nothing
opt = Index
If Index = 0 Then '按班级查询
Label1.Caption = "请选择班级"
Label2.Caption = "请选择课程"
ElseIf Index = 1 Then '按学号查询
Label1.Caption = "请选择班级"
Label2.Caption = "请选择学号"
End If
Adodc2.RecordSource = "select distinct 班级名称 from 班级课程表" Adodc2.Refresh
Do While Adodc2.Recordset.EOF = False
Combo1.AddItem Adodc2.Recordset.Fields(0)
Adodc2.Recordset.MoveNext
Loop
Combo1.Text = Combo1.List(0)
End Sub