图书借阅管理系统java数据库设计

转载

mob6454cc6caa80 2024-07-07 07:49:42

文章标签 图书借阅管理系统java数据库设计 VB.net 图书管理系统 Click Text 文章分类 Java 后端开发

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

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

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

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

功能分析：

图书借阅管理系统java数据库设计_Click

概念模型设计：

图书借阅管理系统java数据库设计_Text_02

关系模型

根据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

图书借阅管理系统java数据库设计_Text_03

二、具体操作

1、数据库建立

（1）利用SQL软件，建立新的数据库Libdb。

（2）利用设计器建立表格Book、Reader、Borrow、ReaderType

（3）在需要建立外键FK的表格中添加关系。

具体操作：在设计界面，右键点击需要建立外键的属性名，在弹出菜单上点击关系，打开“外键关系”窗口，如图所示：

图书借阅管理系统java数据库设计_图书管理系统_04

点击添加按钮，将会添加一个新的关系，在相应的右侧内窗体内，点击表和列规范。打开表和列窗体，如图所示：

图书借阅管理系统java数据库设计_图书借阅管理系统java数据库设计_05

将逐渐表改为要建立外部联系的表格名称及数据项，外键表为当前表格名称及数据项。如下图所示：关系名会改变为FK_本表格名称_建立联系表格名称

图书借阅管理系统java数据库设计_Text_06

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

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

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

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

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

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

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

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

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

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

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

End Class

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。