同济大学
《数据库技术及应用》
项目报告
项目名称
iTeach
小组成员
年级: 2012 专业: 土木
指导教师:
日期: 2015 年 1 月 2 日
一. 系统概述
1.1系统简介
来到大学,我们似乎只需过三点一线的生活,很多人的才能都因此被埋没。本iTeach项目是为了让大学生更方便的展示自我,将自己会的一些技能教给他人,并从别人那里学到自己想学的技能,互惠互助从而广交朋友。
1.2功能特色
1、 系统操作
为了使得本软件能够提供给多用户使用,并能够让用户本身对自身的信息等做出修改与注 销,开发出注册用户,信息更改等功能。
2、 用户功能
用户可以在本系统中填入自己会的技能与想学的技能,然后通过检索匹配找到其他对应用户。
3、 综合功能
(1)实现个人才能录入
(2)实现大规模技能互助匹配
在界面上注册并登录后,在窗体上输入用户会的技能和想学的技能,就能检索到跟用户配对的人的信息,然后通过用户自己的筛选,比如性别、校区等,还可以通过手机联系最终实现技能配对。可以把iTeach看做一个以能力为依托的交友平台,既可以让用户展示自己,又能因此交到朋友,一举两得。
二. 需求分析
2.1 系统功能需求分析
为了实现系统目标,需将整个项目分为以下几个模块:
(1)注册模块,在VB端实现对资源数据库及原始信息的录入
(2)主界面(匹配)模块,通过存储过程实现资源数据库的查询与呈现以及人数统计
(3)修改模块,在VB端实现数据更新
2.2 数据库需求分析
在数据库方面,需要用
的形式实现对资源信息和用户信息的存储:
(1) 系统目标:实现一个才能匹配系统,完成用户个人信息管理和用户之间的信息交互配对
(2) 功能划分:用户先注册信息,然后通过身份验证进入主界面,主界面提供技能选择、检索配对能人信息和修改个人信息等功能
(3) 基本需求:根据对才能配对系统的要求,客观实体只有一个——用户
三. 数据库设计
3.1概念模式设计
E-R图:
3.2逻辑模式设计
关系模式(用户名,密码,姓名,性别,联系方式,校区,会的技能,想学的技能)
规范化处理:
表一(用户名,密码,姓名,性别,联系方式,校区)
表二(用户名,会的技能,想学的技能)
3.3物理数据库设计
利用SQL语句实现数据库、表的创建。
(1)数据库创建
use master
go
create database xm
on primary(name='xm',filename='E:\学习\大三上\数据库\大项目\iTeach\xm.mdf')
log on (name='xm_log',filename='E:\学习\大三上\数据库\大项目\iTeach\xm.ldf')
(2)表的创建
CREATE TABLE x1(
用户名 char(50) NOT NULL primary key,
密码 char(20) NOT NULL,
姓名 char(20) NOT NULL,
性别 char(2) NOT NULL, CHECK (性别='女' OR 性别='男'),
手机 char(12) NOT NULL, CHECK (isnumeric(left(手机,11))=1),
校区 char(50) NOT NULL, CHECK (校区='嘉定校区' OR 校区='四平路校区')
)
CREATE TABLE x2(
用户名 char(50) NOT NULL FOREIGN KEY REFERENCES x1,
can char(100) NOT NULL,
learn char(100) NOT NULL
)
3.4触发器设计
利用SQL语句实现触发器的创建。
(1)密码触发器TA
drop trigger TA
create trigger TA on x1
for insert,update
as
begin declare @_pw as char(40)
select @_pw=i.密码 from inserted i
if not (ISNUMERIC(left(@_pw,1))=1 )
begin
print '密码第一位必须为数字'
rollback
end
end
(2)手机号触发器TB
drop trigger TB
create trigger TB on x1
for insert,update
as
begin declare @_phone as char(40)
select @_phone =i.手机 from inserted i
if not (left(@_phone,1)=1)
begin
print '手机号第一位必须为1'
rollback
end
end
3.5存储过程设计
利用SQL语句实现存储过程的创建。
(1)匹配查询存储过程
create proc match
@_skl1 char(100),
@_skl2 char(100)
As
select x1.用户名,姓名,性别,手机,校区,can,learn
from x1,x2
where
x2.learn =@_skl1 and
x2.can=@_skl2 and x1.用户名=x2.用户名
(2)人数统计存储过程
drop proc search
create proc search
@_skl1 char(100),
@_skl2 char(100),
@_sum int output
As
select @_sum=count(用户名)
from x2
where
x2.learn =@_skl1 and
x2.can=@_skl2
完整SQL语句见xm.sql附件。
四. 系统设计和实现
4.1窗体类和功能类统计表
功能类别
窗体类/模块名
主要功能
说明
启动界面
Form1.vb(iTeach)
登录界面
注册
Form2.vb(register)
信息录入
触发器约束
Form3.vb(register)
主界面
Form4.vb(main)
查询,匹配,统计
存储过程实现
修改
Form5.vb(update)
信息修改
触发器约束
Form6.vb(update)
连接公共模块
Module1.vb
创建与数据库连接
4.2界面设计及其代码实现
(1)登录界面
代码:
Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim myconn As New SqlConnection("Data Source=DP-201404030043\LCW;Initial Catalog=xm;Integrated Security=True")
Dim mysql As String
mysql = "select * from x1 where 用户名='" & Me.TextBox1.Text & "'and 密码='" & Me.TextBox2.Text & "'"
Dim myadapter As New SqlDataAdapter(mysql, myconn)
Dim data As New DataSet
myadapter.Fill(data, "user")
If data.Tables("user").Rows.Count = 0 Then
MsgBox("用户名或密码错误!")
Me.TextBox1.Text = ""
Me.TextBox2.Text = ""
Else : Me.Hide()
Form4.Show()
End If
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Me.Hide()
Form2.Show()
End Sub
End Class
(2)注册界面
Imports System.Data.SqlClient
Public Class Form2
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
If TextBox1.Text = "" Then
MsgBox(" 请输入用户名 ")
ElseIf TextBox2.Text = "" Then
MsgBox("请输入密码 ")
ElseIf TextBox3.Text = "" Then
MsgBox(" 请输入姓名 ")
ElseIf TextBox4.Text = "" Then
MsgBox("请输入性别")
ElseIf TextBox5.Text = "" Or Len(TextBox5.Text) <> 11 Then
TextBox5.Text = ""
MsgBox("请输入正确的手机号码")
ElseIf TextBox6.Text = "嘉定校区" Or TextBox6.Text = "四平路校区" Then
Dim ins As String = "insert into x1 values('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & TextBox5.Text & "','" & TextBox6.Text & "')"
'MessageBox.Show(ins)
Dim inscmd As New SqlCommand(ins, conn)
Try
conn.Open()
inscmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
conn.Close()
End Try
Me.Hide()
Form3.Show()
Else
TextBox6.Text = ""
MsgBox("请输入正确的校区")
End If
End Sub
Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
Me.Close()
Form1.Show()
End Sub
End Class
Imports System.Data.SqlClient
Public Class Form3
Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
If RadioButton1.Checked Then TextBox1.Text = RadioButton1.Text
If RadioButton2.Checked Then TextBox1.Text = RadioButton2.Text
If RadioButton3.Checked Then TextBox1.Text = RadioButton3.Text
If RadioButton4.Checked Then TextBox1.Text = RadioButton4.Text
If RadioButton5.Checked Then TextBox1.Text = RadioButton5.Text
If RadioButton6.Checked Then TextBox2.Text = RadioButton6.Text
If RadioButton7.Checked Then TextBox2.Text = RadioButton7.Text
If RadioButton8.Checked Then TextBox2.Text = RadioButton8.Text
If RadioButton9.Checked Then TextBox2.Text = RadioButton9.Text
If RadioButton10.Checked Then TextBox2.Text = RadioButton10.Text
If TextBox1.Text = "" Or TextBox2.Text = "" Then
MsgBox("请选择技能")
End If
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
If TextBox1.Text = "" Or TextBox2.Text = "" Then
MsgBox("请输入技能")
Else
Dim ins As String = "insert into x2 values('" & Form2.TextBox1.Text & "','" & TextBox1.Text & "','" & TextBox2.Text & "')"
'MessageBox.Show(ud)
Dim inscmd As New SqlCommand(ins, conn)
conn.Open()
inscmd.ExecuteNonQuery()
conn.Close()
Me.Close()
Form1.Show()
End If
End Sub
Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
Me.Close()
Form2.Show()
End Sub
End Class
(3)主界面
Imports System.Data.SqlClient
Public Class Form4
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
If ComboBox1.Text = "" Or ComboBox2.Text = "" Then
MsgBox("请确认技能")
End If
Dim cmd As SqlCommand = New SqlCommand("match", conn)
cmd.CommandType = CommandType.StoredProcedure
Dim in1 As SqlParameter = cmd.Parameters.Add("@_skl1", SqlDbType.Char, 100)
Dim in2 As SqlParameter = cmd.Parameters.Add("@_skl2", SqlDbType.Char, 100)
in1.Direction = ParameterDirection.Input
in2.Direction = ParameterDirection.Input
in1.Value = ComboBox1.Text
in2.Value = ComboBox2.Text
Dim da As New SqlDataAdapter(cmd)
Dim ds As New DataSet()
da.Fill(ds, "users")
DataGrid1.DataSource = ds.Tables("users")
DataGrid1.CaptionText = " " + " 配对能人信息 " + " "
Dim mycomm As SqlCommand = New SqlCommand("search", conn)
mycomm.CommandType = CommandType.StoredProcedure
Dim inp1 As SqlParameter = mycomm.Parameters.Add("@_skl1", SqlDbType.Char, 100)
Dim inp2 As SqlParameter = mycomm.Parameters.Add("@_skl2", SqlDbType.Char, 100)
inp1.Direction = ParameterDirection.Input
inp2.Direction = ParameterDirection.Input
Dim outp As SqlParameter = mycomm.Parameters.Add("@_sum", SqlDbType.Int)
outp.Direction = ParameterDirection.Output
inp1.Value = ComboBox1.Text
inp2.Value = ComboBox2.Text
conn.Open()
mycomm.ExecuteNonQuery()
TextBox1.Text = mycomm.Parameters("@_sum").Value
conn.Close()
End Sub
Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
Me.Close()
End Sub
Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
Me.Hide()
Form5.Show()
End Sub
Private Sub Form4_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
Dim qry As String = "select can,learn FROM x2 where 用户名='" & Form1.TextBox1.Text & "'"
Dim da As New SqlDataAdapter(qry, conn)
Dim dt As New DataTable()
Try
da.Fill(dt)
Dim bs As New BindingSource
bs.DataSource = dt
ComboBox1.DataSource = bs
ComboBox1.DisplayMember = "can"
ComboBox2.DataSource = bs
ComboBox2.DisplayMember = "learn"
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Class
(4)信息修改界面
Imports System.Data.SqlClient
Public Class Form5
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
If TextBox1.Text = "" Then
MsgBox(" 请输入用户名 ")
ElseIf TextBox2.Text = "" Then
MsgBox("请输入密码 ")
ElseIf TextBox3.Text = "" Then
MsgBox(" 请输入姓名 ")
ElseIf TextBox4.Text = "" Then
MsgBox("请输入性别")
ElseIf TextBox5.Text = "" Or Len(TextBox5.Text) <> 11 Then
MsgBox("请输入正确的手机号码")
ElseIf TextBox6.Text = "嘉定校区" Or TextBox6.Text = "四平路校区" Then
Dim ud As String = "update x1 set 密码=" & TextBox2.Text & ",姓名=" & TextBox3.Text & ",性别=" & TextBox4.Text & ",手机=" & TextBox5.Text & ",校区=" & TextBox6.Text & " where 用户名='" & TextBox1.Text & "'"
'MessageBox.Show(ud)
Dim udcmd As New SqlCommand(ud, conn)
Try
conn.Open()
udcmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
conn.Close()
End Try
Me.Hide()
Form6.Show()
Else
TextBox6.Text = ""
MsgBox("请输入正确的校区")
End If
End Sub
Private Sub Form5_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
TextBox1.Text = Form1.TextBox1.Text
End Sub
Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
Me.Close()
Form4.Show()
End Sub
End Class
Imports System.Data.SqlClient
Public Class Form6
Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
If RadioButton1.Checked Then TextBox1.Text = RadioButton1.Text
If RadioButton2.Checked Then TextBox1.Text = RadioButton2.Text
If RadioButton3.Checked Then TextBox1.Text = RadioButton3.Text
If RadioButton4.Checked Then TextBox1.Text = RadioButton4.Text
If RadioButton5.Checked Then TextBox1.Text = RadioButton5.Text
If RadioButton6.Checked Then TextBox2.Text = RadioButton6.Text
If RadioButton7.Checked Then TextBox2.Text = RadioButton7.Text
If RadioButton8.Checked Then TextBox2.Text = RadioButton8.Text
If RadioButton9.Checked Then TextBox2.Text = RadioButton9.Text
If RadioButton10.Checked Then TextBox2.Text = RadioButton10.Text
If TextBox1.Text = "" Or TextBox2.Text = "" Then
MsgBox("请选择技能")
End If
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
If TextBox1.Text = "" Or TextBox2.Text = "" Then
MsgBox("请输入技能")
Else
Dim ud As String = "update x2 set can='" & TextBox1.Text & "',learn='" & TextBox2.Text & "' where 用户名='" & Form5.TextBox1.Text & "'"
'MessageBox.Show(ud)
Dim udcmd As New SqlCommand(ud, conn)
conn.Open()
udcmd.ExecuteNonQuery()
conn.Close()
Me.Hide()
Form1.Show()
End If
End Sub
Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
Me.Close()
Form5.Show()
End Sub
End Class
(5)模块
Imports System.Data.SqlClient
Module Module1
Public conn As New SqlConnection("data source=DP-201404030043\LCW;database = xm;integrated security=true")
End Module
五. 系统测试
5.1触发器测试用例
(1)密码触发器TA
用例及结果如图:
(2)手机号触发器TA
用例及结果如图:
5.2存储过程测试用例
匹配查询及人数统计存储过程测试如图:
5.3功能测试用例
同上图。
约束测试:
六.
第一,通过这次项目设计,我明白了项目设计的难点不是在于创意,而是在于功能的实现。在最初,我想做一个算命的软件,后来发现不仅原始数据难找,实现起来也有很多问题。对于这个最终实现的iTeach项目在开始做之前设计的功能也很多,不过在之后项目的实施当中遇到了很多基础问题,导致我不得不简化,也最终使项目在前台显示上的表现出不足;
第二,我在整个过程中也学到了很多知识,例如,对密码或者手机号进行约束在vb端难以实现,我就运用触发器的知识在.net实现;用SQLstring实现很复杂时就可以改用存储过程简化;使用模块可以降低程序耦合性,便于更改等等。当能运用数据库课上所学知识解决我所碰到的实际问题时,我就真正的感受到了理论应用于实际的快乐。
第三,目前此iTeach项目还有很多不完善的地方,比如用户的技能不能多选,技能选项过少等等,对于这些问题,我会在之后利用空闲时间努力解决。争取能做得更加完美。