vb.net图书管理系统_读者与图书的关系模型

作者：盐析白兔 | 2024-06-05 21:49:59

踩

读者与图书的关系模型

Part8 VB.net编程程序设计
——图书借阅管理系统

一、问题引入
学校原本使用人工管理模式进行借阅。现需要将原有借阅模式改革，引入计算机借阅模式。可以管理借书、还书等流程。

二、需求分析
由于时间原因，系统已实现主要功能为目的。采用最简化开发模式。
功能描述：
1、当学校新进了教师或学生时，可以添加读者。
2、当管理员不清楚读者借阅权限和已借数量时，可以进行查询。
3、图书馆买了新书时，可以进行添加。
4、淘汰旧书时，可以删除。
5、管理员可以对读者类别进行管理。
6、可以进行借书和还书操作。
7、根据实际情况添加相关需求。

需求分析：
根据功能要求，主要需要实现对三种功能，读者管理、图书管理、借阅管理。并据此建立相关关系。

功能分析：

概念模型设计：

关系模型
根据E-R模型进行关系模型设计：
1、将中文实体转化为英文标识的命名符号：
读者：Reader；读者编号：RID；姓名：Rname；读者类型：Type；已借数量：Lendnum
图书：Book；图书编号：BID；书名：Bname；作者：Author；出版社：PudComp；
出版日期：PubDate；定价：Price
借阅：Borrow；借期：LendDate；还期：ReturnDate；限期：SReturnDate；超期天数：ExtDays
2、将概念模型转换为关系模型：
读者与图书的联系是多对多的，借阅联系转换为一个Borrow关系模型，读者实体的主码RID和图书实体的主码BID和联系本身的属性构成关系Borrow的属性。
得到如下的关系模型
实体（读者）：Reader（RID，Rname，Type，Lendunm）
PK：RID
联系（借阅）：Borrow（RID，BID，LendDate，ReturnDate，SReturnDate，ExtDays）
PK：RID，BID，LendDate
FK：RID，BID
实体（图书）：Book（BID，Bname，Author，PubComp，PubDate，Price）
PK：BID
在借阅系统中，不同类型的读者可以借阅的图书有区别，因此，在读者实体的Type属性中，还应该包括读者类型TypeID、读者类型名称TypeName、限借数量LimitNum、限借天数LimitDays属性。
读者实体进行转化后为：
Reader（RID，Rname，Lendnum，TypeID，Typename，LimitNum，LimitDays）
转化后的关系中存在函数依赖传递，不满足3NF原则。需要对Reader进行拆分。
拆分后的关系如下：
ReaderType（TypeID，TypeName，LimitNum，LimitDays）
PK：TypeID
Reader（RID，Rname，TypeID，Lendunm）
PK：RID
FK：TypeID
Borrow（RID，BID，LendDate，ReturnDate，SReturnDate，ExtDays）
PK：RID，BID，LendDate
FK：RID，BID
Book（BID，Bname，Author，PubComp，PubDate，Price）
PK：BID
据此建立数据库：Libdb

二、具体操作
1、数据库建立
（1）利用SQL软件，建立新的数据库Libdb。
（2）利用设计器建立表格Book、Reader、Borrow、ReaderType
（3）在需要建立外键FK的表格中添加关系。
具体操作：在设计界面，右键点击需要建立外键的属性名，在弹出菜单上点击关系，打开“外键关系”窗口，如图所示：
在这里插入图片描述
点击添加按钮，将会添加一个新的关系，在相应的右侧内窗体内，点击表和列规范。打开表和列窗体，如图所示：

将逐渐表改为要建立外部联系的表格名称及数据项，外键表为当前表格名称及数据项。如下图所示：关系名会改变为FK_本表格名称_建立联系表格名称
在这里插入图片描述
2、界面设计：
（1）建立MDI主界面
（2）建立数据源
（3）利用数据源建立各个窗体界面

3、代码设计：
完整代码：
Imports System.Windows.Forms

Public Class MDIParent1
Private Sub CascadeToolStripMenuItem_Click(ByVal sender As Object, ByVal e As EventArgs)
Me.LayoutMdi(MdiLayout.Cascade)
End Sub

Private Sub TileVerticalToolStripMenuItem_Click(ByVal sender As Object, ByVal e As EventArgs)
    Me.LayoutMdi(MdiLayout.TileVertical)
End Sub

Private Sub TileHorizontalToolStripMenuItem_Click(ByVal sender As Object, ByVal e As EventArgs)
    Me.LayoutMdi(MdiLayout.TileHorizontal)
End Sub

Private Sub ArrangeIconsToolStripMenuItem_Click(ByVal sender As Object, ByVal e As EventArgs)
    Me.LayoutMdi(MdiLayout.ArrangeIcons)
End Sub

Private m_ChildFormNumber As Integer

Private Sub 退出ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles 退出ToolStripMenuItem.Click
    Global.System.Windows.Forms.Application.Exit()
End Sub

Private Sub 添加读者信息ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles 添加读者信息ToolStripMenuItem.Click
    Dim form As New Form1
    form.MdiParent = Me
    form.Show()
End Sub

Private Sub 查询读者信息ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles 查询读者信息ToolStripMenuItem.Click
    Dim form As New Form2
    form.MdiParent = Me
    form.Show()
End Sub

Private Sub 新书上架ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles 新书上架ToolStripMenuItem.Click
    Dim form As New Form3
    form.MdiParent = Me
    form.Show()
End Sub

Private Sub 添加读者类型ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles 添加读者类型ToolStripMenuItem.Click
    Dim form As New Form4
    form.MdiParent = Me
    form.Show()
End Sub


Private Sub 查询读者类型ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles 查询读者类型ToolStripMenuItem.Click
    Dim form As New Form5
    form.MdiParent = Me
    form.Show()
End Sub

Private Sub 旧书淘汰ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles 旧书淘汰ToolStripMenuItem.Click
    Dim form As New Form6
    form.MdiParent = Me
    form.Show()
End Sub

Private Sub 图书信息修改ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles 图书信息修改ToolStripMenuItem.Click
    Dim form As New Form7
    form.MdiParent = Me
    form.Show()
End Sub

Private Sub 图书信息查询ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles 图书信息查询ToolStripMenuItem.Click
    Dim form As New Form8
    form.MdiParent = Me
    form.Show()
End Sub

Private Sub 借书ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles 借书ToolStripMenuItem.Click
    Dim form As New Form9
    form.MdiParent = Me
    form.Show()
End Sub

Private Sub 还书ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles 还书ToolStripMenuItem.Click
    Dim form As New Form10
    form.MdiParent = Me
    form.Show()
End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

End Class

添加读者信息代码
Imports System.Data.SqlClient
Public Class Form1

Private Sub ReaderBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs)
    Me.Validate()
    Me.ReaderBindingSource.EndEdit()
    Me.TableAdapterManager.UpdateAll(Me.LibdbDataSet)

End Sub

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    'TODO: 这行代码将数据加载到表“LibdbDataSet.Reader”中。您可以根据需要移动或删除它。
    Me.ReaderTableAdapter.Fill(Me.LibdbDataSet.Reader)

End Sub
Dim conn As SqlConnection
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Try
        conn = New SqlConnection
        conn.ConnectionString = "Server=(local);database=libdb;UID=sa;PWD=Sa123"
        conn.Open()
        Dim str1 As String
        str1 = "insert into Reader(RID,Rname,TypeID,LendNum,Email) values('" & RIDTextBox.Text & "','" & RnameTextBox.Text & "','" & TypeIDTextBox.Text & "','" & LendNumTextBox.Text & "','" & EmailTextBox.Text & "')"
        Dim comm As New SqlCommand(str1, conn)
        comm.ExecuteNonQuery()
        conn.Close()
        MsgBox("添加读者信息成功！")
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

End Class

查询读者信息代码
Public Class Form2

Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    'TODO: 这行代码将数据加载到表“LibdbDataSet.Reader”中。您可以根据需要移动或删除它。
    Me.ReaderTableAdapter.Fill(Me.LibdbDataSet.Reader)

End Sub

Private Sub FillByToolStripButton_Click(sender As Object, e As EventArgs) Handles FillByToolStripButton.Click
    Try
        Me.ReaderTableAdapter.FillBy(Me.LibdbDataSet.Reader, RidToolStripTextBox.Text)
    Catch ex As System.Exception
        System.Windows.Forms.MessageBox.Show(ex.Message)
    End Try

End Sub

Private Sub FillBy1ToolStripButton_Click(sender As Object, e As EventArgs) Handles FillBy1ToolStripButton.Click
    Try
        Me.ReaderTableAdapter.FillBy1(Me.LibdbDataSet.Reader, RnameToolStripTextBox.Text)
    Catch ex As System.Exception
        System.Windows.Forms.MessageBox.Show(ex.Message)
    End Try

End Sub

Private Sub FillBy2ToolStripButton_Click(sender As Object, e As EventArgs) Handles FillBy2ToolStripButton.Click
    Try
        Me.ReaderTableAdapter.FillBy2(Me.LibdbDataSet.Reader, New System.Nullable(Of Integer)(CType(TidToolStripTextBox.Text, Integer)))
    Catch ex As System.Exception
        System.Windows.Forms.MessageBox.Show(ex.Message)
    End Try

End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

End Class
添加读者类型代码
Imports System.Data.SqlClient
Public Class Form4

Private Sub ReaderTypeBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs)
    Me.Validate()
    Me.ReaderTypeBindingSource.EndEdit()
    Me.TableAdapterManager.UpdateAll(Me.LibdbDataSet)

End Sub

Private Sub Form4_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    'TODO: 这行代码将数据加载到表“LibdbDataSet.ReaderType”中。您可以根据需要移动或删除它。
    Me.ReaderTypeTableAdapter.Fill(Me.LibdbDataSet.ReaderType)

End Sub
Dim conn As SqlConnection
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    conn = New SqlConnection()
    conn.ConnectionString = "Server=(local);database=libdb;UID=sa;PWD=Sa123"
    conn.Open()
    Dim sqlstr As String
    sqlstr = "insert into ReaderType(TypeID,Typename,LimitNum,LimtDays) values('" & TypeIDTextBox.Text & "','" & TypenameTextBox.Text & "','" & LimitNumTextBox.Text & "','" & LimtDaysTextBox.Text & "')"
    Dim comm As New SqlCommand(sqlstr, conn)
    comm.ExecuteNonQuery()
    conn.Close()
    MsgBox("添加读者类型成功！")
End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

End Class

查询读者类型代码
Public Class Form5

Private Sub Form5_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    'TODO: 这行代码将数据加载到表“LibdbDataSet.ReaderType”中。您可以根据需要移动或删除它。
    Me.ReaderTypeTableAdapter.Fill(Me.LibdbDataSet.ReaderType)

End Sub

Private Sub FillByidToolStripButton_Click(sender As Object, e As EventArgs) Handles FillByidToolStripButton.Click
    Try
        Me.ReaderTypeTableAdapter.FillByid(Me.LibdbDataSet.ReaderType, CType(IdToolStripTextBox.Text, Integer))
    Catch ex As System.Exception
        System.Windows.Forms.MessageBox.Show(ex.Message)
    End Try

End Sub

Private Sub FillBytnameToolStripButton_Click(sender As Object, e As EventArgs) Handles FillBytnameToolStripButton.Click
    Try
        Me.ReaderTypeTableAdapter.FillBytname(Me.LibdbDataSet.ReaderType, TnameToolStripTextBox.Text)
    Catch ex As System.Exception
        System.Windows.Forms.MessageBox.Show(ex.Message)
    End Try

End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

End Class

新书上架
Imports System.Data.SqlClient
Public Class Form3

Private Sub BookBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs)
    Me.Validate()
    Me.BookBindingSource.EndEdit()
    Me.TableAdapterManager.UpdateAll(Me.LibdbDataSet)

End Sub

Private Sub Form3_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    'TODO: 这行代码将数据加载到表“LibdbDataSet.Book”中。您可以根据需要移动或删除它。
    Me.BookTableAdapter.Fill(Me.LibdbDataSet.Book)

End Sub
Dim conn As SqlConnection
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Try
        conn = New SqlConnection
        conn.ConnectionString = "Server=(local);database=libdb;UID=sa;PWD=Sa123"
        conn.Open()
        Dim str1 As String
        str1 = "insert into Book(BID,Bname,Author,Pubcomp,PubDate,Price,ISBN) values('" & BIDTextBox.Text & "','" & BnameTextBox.Text & "','" & AuthorTextBox.Text & "','" & PubCompTextBox.Text & "','" & PubDateDateTimePicker.Value & "','" & PriceTextBox.Text & "','" & ISBNTextBox.Text & "')"
        Dim comm As New SqlCommand(str1, conn)
        comm.ExecuteNonQuery()
        conn.Close()
        MsgBox("新书上架成功！")
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

End Class

旧书淘汰
Imports System.Data.SqlClient
Public Class Form6
Private Sub Form6_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'TODO: 这行代码将数据加载到表“LibdbDataSet.Book”中。您可以根据需要移动或删除它。
Me.BookTableAdapter.Fill(Me.LibdbDataSet.Book)

End Sub
Private Sub FillBybid1ToolStripButton_Click(sender As Object, e As EventArgs) Handles FillBybid1ToolStripButton.Click
    Try
        Me.BookTableAdapter.FillBybid1(Me.LibdbDataSet.Book, BidToolStripTextBox.Text)
    Catch ex As System.Exception
        System.Windows.Forms.MessageBox.Show(ex.Message)
    End Try

End Sub
Dim conn As SqlConnection
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    If (MessageBox.Show("确认删除记录吗？", "确认", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) = Windows.Forms.DialogResult.Yes) Then
        Try
            BindingSource1.RemoveCurrent()
            conn = New SqlConnection
            conn.ConnectionString = "Server=(local);database=libdb;UID=sa;PWD=Sa123"
            conn.Open()
            Dim sqlstr As String
            sqlstr = "delete from Book where BID='" & BidToolStripTextBox.Text & "'"
            Dim comm As New SqlCommand(sqlstr, conn)
            comm.ExecuteNonQuery()
            conn.Close()
            MsgBox("删除记录成功！")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End If
End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

End Class
图书信息修改
Imports System.Data.SqlClient
Public Class Form7

Private Sub Form7_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    'TODO: 这行代码将数据加载到表“LibdbDataSet.Book”中。您可以根据需要移动或删除它。
    Me.BookTableAdapter.Fill(Me.LibdbDataSet.Book)
    'TODO: 这行代码将数据加载到表“LibdbDataSet.Book”中。您可以根据需要移动或删除它。
    Me.BookTableAdapter.Fill(Me.LibdbDataSet.Book)

End Sub

Private Sub BookBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs)
    Me.Validate()
    Me.BookBindingSource.EndEdit()
    Me.TableAdapterManager.UpdateAll(Me.LibdbDataSet)

End Sub
Dim conn As SqlConnection
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Try
        Me.Validate()
        Me.BookBindingSource.EndEdit()
        Me.BookTableAdapter.Update(Me.LibdbDataSet.Book)
        conn = New SqlConnection()
        conn.ConnectionString = "server=(local);database=libdb;UID=sa;PWD=Sa123"
        conn.Open()
        Dim sqlstr As String
        sqlstr = "update Book set Bname='BnameTextBox.text',Author='AuthorTextBox.text',PubComp='PubCompText.text',PubDate='PubDateDateTimePicker.value',Price='PriceTextBox.text',ISBN='ISBNTextBox.text' where BID='BIDTextBox.text'"
        Dim comm As New SqlCommand(sqlstr, conn)
        comm.ExecuteNonQuery()
        conn.Close()
        MsgBox("修改图书信息成功！")
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

End Class

图书信息查询
Public Class Form8

Private Sub Form8_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    'TODO: 这行代码将数据加载到表“LibdbDataSet.Book”中。您可以根据需要移动或删除它。
    Me.BookTableAdapter.Fill(Me.LibdbDataSet.Book)

End Sub

Private Sub FillBybidToolStripButton_Click(sender As Object, e As EventArgs) Handles FillBybidToolStripButton.Click
    Try
        Me.BookTableAdapter.FillBybid(Me.LibdbDataSet.Book, BidToolStripTextBox.Text)
    Catch ex As System.Exception
        System.Windows.Forms.MessageBox.Show(ex.Message)
    End Try

End Sub

Private Sub FillBybnameToolStripButton_Click(sender As Object, e As EventArgs)
    Try
        Me.BookTableAdapter.FillBybname(Me.LibdbDataSet.Book)
    Catch ex As System.Exception
        System.Windows.Forms.MessageBox.Show(ex.Message)
    End Try

End Sub

Private Sub FillBybname1ToolStripButton_Click(sender As Object, e As EventArgs) Handles FillBybname1ToolStripButton.Click
    Try
        Me.BookTableAdapter.FillBybname1(Me.LibdbDataSet.Book, BnameToolStripTextBox.Text)
    Catch ex As System.Exception
        System.Windows.Forms.MessageBox.Show(ex.Message)
    End Try

End Sub

Private Sub FillByauthorToolStripButton_Click(sender As Object, e As EventArgs) Handles FillByauthorToolStripButton.Click
    Try
        Me.BookTableAdapter.FillByauthor(Me.LibdbDataSet.Book, AuthorToolStripTextBox.Text)
    Catch ex As System.Exception
        System.Windows.Forms.MessageBox.Show(ex.Message)
    End Try

End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

End Class
借书
Imports System.Data.SqlClient
Public Class Form9

Private Sub BorrowBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs)
    Me.Validate()
    Me.BorrowBindingSource.EndEdit()
    Me.TableAdapterManager.UpdateAll(Me.LibdbDataSet)

End Sub
Dim conn As SqlConnection
Private Sub Form9_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    'TODO: 这行代码将数据加载到表“LibdbDataSet.Borrow”中。您可以根据需要移动或删除它。
    Me.BorrowTableAdapter.Fill(Me.LibdbDataSet.Borrow)

End Sub


Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Try
        conn = New SqlConnection
        conn.ConnectionString = "Server=(local);database=libdb;UID=sa;PWD=Sa123"
        conn.Open()
        Dim str1 As String
        str1 = "insert into Borrow(RID,BID,LendDate) values('" & RIDTextBox.Text & "','" & BIDTextBox.Text & "','" & LendDateDateTimePicker.Value & "')"
        Dim comm As New SqlCommand(str1, conn)
        comm.ExecuteNonQuery()
        conn.Close()
        MsgBox("借书成功！")
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

End Class

还书
Imports System.Data.SqlClient
Public Class Form10
Private Sub BorrowBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs)
Me.Validate()
Me.BorrowBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.LibdbDataSet)

End Sub

Private Sub Form10_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    'TODO: 这行代码将数据加载到表“LibdbDataSet.Borrow”中。您可以根据需要移动或删除它。
    Me.BorrowTableAdapter.Fill(Me.LibdbDataSet.Borrow)

End Sub
Dim conn As SqlConnection

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Try
        Me.Validate()
        Me.BorrowBindingSource.EndEdit()
        Me.BorrowTableAdapter.Update(Me.LibdbDataSet.Borrow)
        conn = New SqlConnection()
        conn.ConnectionString = "server=(local);database=libdb;UID=sa;PWD=Sa123"
        conn.Open()
        Dim sqlstr As String
        sqlstr = "update Borrow set Returndate='ReturnDateDateTimePicker.value',ExtDays='ExtDaysTextbox.text',LendDate='LendDateDateTimePicker.value',SReturnDate='SReturnDateDateTimePicker.value' where RID='RIDTextBox.text' and BID='BIDTextBox.text'"
        Dim comm As New SqlCommand(sqlstr, conn)
        comm.ExecuteNonQuery()
        conn.Close()
        MsgBox("还书成功")
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

End Class
如需文件压缩包留下邮箱

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/盐析白兔/article/detail/678231