《信息系统与数据库技术》课程设计网上书店信息系统

32信息系统与数据库技术技术实践信息系统与数据库技术课程设计设计题目： 网上书店信息系统 设计者;黄闪浪1系统目标：本系统支持等图书商品管理、图书销售、客户分析3个方面的功能，并具有信息管理、事务处理和决策支持的系统特点。2需求分析（1）业务需求酒店客房预订管理系统主要有三类用户角色：客户、店员、店长。他们各自的主要业务处理需求如下：1客户在系统中完成对酒店房间的浏览和预订。主要业务和流程包括：查询、浏览图书商品选择放入购物车选择支付方式、发货方式和填写个人信息生成和提交订单。2店员在系统中完成商品管理、订单处理等：商品上架、下架；商品折扣管理；商品汇总。3店长在系统中获得商品和营销分析、客户分析以制订营销策略：客房汇总（包括分类汇总）：营业状况分析（包括按时间、图书类别、客户类别等汇总）；客户分析（按客户重要性、忠诚度分析等）。(2)数据处理需求系统涉及的数据源有图书数据、客户数据、客户购买数据，产生图书信息表、客户信息表、订单、客户分析表、销售分析等各项表单。结合业务处理需求，系统数据流图如图一所示。 图一: 系统数据流图3系统总体设计（1）系统结构 网络书店的客户散落在不同地域，凡是可以接入互联网的用户都可以成为书店的客户。我们从系统安全、快速访问等方面考虑，可以采用客户机/服务器结构（C/S）。（2）系统技术方案系统前台开发工具：面向客户、店员、店长的C/S应用程序采用VB.NET语言开发。后台数据库采用Microsoft SQL Server。（3）系统功能结构 根据需求分析所获得的业务需求，可以定义系统的详细功能说明如表所示。用户主要功能客户店员店长客户信息管理注册、维护个人信息；查看个人入住记录等查询、浏览、汇总客户信息；查询、浏览、汇总客户信息；图书信息管理图书查询、预订查询图书信息；查看图书优惠政策使用情况图书汇总；查询图书信息；查看图书优惠政策使用情况购书管理预订图书查看订单，修改订单查看、汇总订单营销分析按时间、图书类别、地域等汇总销售情况，查询和生成相关信息报表；客户新兴趣分析、忠诚度分析图二：网上书店系统功能结构4数据库设计（1） 数据库概念设计（2） 系统主要实体有图书和客户两个，她们通过订购活动建立联系，分析定义各实体的属性，建立实体间的联系模型的E-R图如下图三：网上书店E-R图（3） 数据库逻辑设计1） 将数据库的概念模式转化为如下四个基本关系模式客户（客户号，姓名，性别，职业，生日，所在省区，联系电话，E-mail，客户等级，忠诚度）图书（书号，书名，作者，出版社，出版时间，类别，内容简介，价格，折扣）订单（订单号，客户号，支付方式，发货方式，发货地址，订货时间）2）数据库Bookstore的关系图如下3）数据库物理设计1）下面给出SQL Server 下的网上书店数据库物理设计的关系模型设计、完整性约束设计和索引选择。2）Customer 客户表，用于存储客户基本信息，如下图所示Customer 客户表3）Book图书表，用于存储图书基本情况Book图书表4）BookOrder 订单表，用于存储订单的基本情况BookOrder 订单表5）OrderDetail 订单详细情况表，用于存储订单的详细情况5数据库应用对象设计可以充分利用SQL Server所提供的数据库应用对象来支持应用程序开发，这样，可以提高程序开发效率和系统运行效率并且可以加强系统的安全性。如下面示例1） 示图设计示图设计是一个虚拟表，可以简化客户端程序和提高访问安全性*视图V_Customer and Suppliers by CityV_Customer and Suppliers by City支持客户和供应商的城市情况的查询。视图的定义语句如下create view Customer and Suppliers by City ASSELECT City, CompanyName, ContactName, Customers AS Relationship FROM CustomersUNION SELECT City, CompanyName, ContactName, SuppliersFROM Suppliers-ORDER BY City, CompanyName视图如下 *视图V_Orders Qry 视图V_Orders Qry的定义语句如下create view Orders Qry ASSELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.CountryFROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID视图V_Orders Qry如下2） 储存过程设计.储存过程建立在SQL_Server服务器端，在数据库应用程序中使用存储过程可以简化客户端程序，提供系统运行效率，加强安全性并且减少网络信息传输量。*储存过程proc_SearchBookproc_SearchBook按书名(BookName)实现模糊查询CREATE TRIGGER tri_BookOrderDe1bookname VarChar(60)ASSELECT*FROM Book WHERE BookName LIKE “%” + bookname + ”%”6系统开发设计系统开发设计设计很多详细的设计内容，限于篇幅，这里只给出一般性说明和要求。（1）子系统设计按照系统结构设计，本系统是C/S系统。（2）界面设计要求1）以菜单集成各功能窗口。2)各窗口界面整齐美观，恰当地使用各种窗体控件，便于用户使用。3)有用户登录界面，即进行身份验证，根据用户信息使用不同的功能。4)报表要简洁清晰，布局合理。功能;查询功能：用户登录界面，提供客户信息查询，添加图书查询，删除图书查询。功能：客户信息查询。功能：添加图书功能：删除图书。功能：查询订房情况表的详细内容。(3)程序设计注意事项1）注意输入有效性的检查，即编写程序检验用户输入的数据是否符合数据类型或格式的需求，如果不合适，提醒其重新输入，从而保证与数据库的正确交互及保证数据库信息的有效性。2）过程和函数的使用。将一些具有独立功能的程序段编写为过程或函数，这样不仅使程序结构模块化，而且可以通过过程或函数多次调用共享代码。3）全局变量或过程的使用。因为系统中含有多个窗体和模块文件，为了数据处理需要，可以使用一些全局变量或全局过程，这样可以简化程序，减少代码量，提高系统效率，因此可以将它们放在模块文件中。4）存储过程建立在SQL SERVER 服务器端，在数据库应用程序中使用存储过程可以简化客户端程序，提供系统运行效率，并且减少网络信息传输量。5）视图建立在SQL SERVER 服务器端，可以简化客户端程序和提高访问安全性。6）触发器建立在数据表上。当有对数据库更新操作时，自动执行，可以维护数据的一致性，如果一般性的关系完整性约束规则无法实现的数据约束，则可使用触发器实现。 （4）程序代码* Form1Imports System.DataImports System.Data.SqlClientPublic Class Form1 Inherits System.Windows.Forms.Form Public mybind As BindingManagerBase#Region Windows 窗体设计器生成的代码 Public Sub New() MyBase.New() 该调用是 Windows 窗体设计器所必需的。 InitializeComponent() 在 InitializeComponent() 调用之后添加任何初始化 End SubButton1 Me.Button1.BackColor = System.Drawing.SystemColors.ActiveCaption Me.Button1.Location = New System.Drawing.Point(648, 144) Me.Button1.Name = Button1 Me.Button1.TabIndex = 0 Me.Button1.Text = 确认更新 Button2 Me.Button2.BackColor = System.Drawing.SystemColors.ActiveCaption Me.Button2.Location = New System.Drawing.Point(32, 344) Me.Button2.Name = Button2 Me.Button2.TabIndex = 1 Me.Button2.Text = 第一条 Button3 Me.Button3.BackColor = System.Drawing.SystemColors.ActiveCaption Me.Button3.Location = New System.Drawing.Point(472, 344) Me.Button3.Name = Button3 Me.Button3.TabIndex = 2 Me.Button3.Text = 最后一条 Button4 Me.Button4.BackColor = System.Drawing.SystemColors.ActiveCaption Me.Button4.Location = New System.Drawing.Point(648, 96) Me.Button4.Name = Button4 Me.Button4.TabIndex = 3 Me.Button4.Text = 删除 Button5 Me.Button5.BackColor = System.Drawing.SystemColors.ActiveCaption Me.Button5.Location = New System.Drawing.Point(176, 344) Me.Button5.Name = Button5 Me.Button5.TabIndex = 4 Me.Button5.Text = 上一条 Button6 Me.Button6.BackColor = System.Drawing.SystemColors.ActiveCaption Me.Button6.Location = New System.Drawing.Point(648, 184) Me.Button6.Name = Button6 Me.Button6.TabIndex = 5 Me.Button6.Text = 取消更新 Button7 Me.Button7.BackColor = System.Drawing.SystemColors.ActiveCaption Me.Button7.Location = New System.Drawing.Point(648, 56) Me.Button7.Name = Button7 Me.Button7.TabIndex = 6 Me.Button7.Text = 添加 Button8 Me.Button8.BackColor = System.Drawing.SystemColors.ActiveCaption Me.Button8.Location = New System.Drawing.Point(312, 344) Me.Button8.Name = Button8 Me.Button8.TabIndex = 7 Me.Button8.Text = 下一条Button9 Me.Button9.BackColor = System.Drawing.SystemColors.ActiveCaption Me.Button9.Location = New System.Drawing.Point(648, 344) Me.Button9.Name = Button9 Me.Button9.TabIndex = 19 Me.Button9.Text = 退出 Label1 Me.Label1.Location = New System.Drawing.Point(16, 264) Me.Label1.Name = Label1 Me.Label1.TabIndex = 20 Me.Label1.Text = Hometown Label2 Me.Label2.Location = New System.Drawing.Point(272, 264) Me.Label2.Name = Label2 Me.Label2.TabIndex = 21 Me.Label2.Text = Telephone Label3 Me.Label3.Location = New System.Drawing.Point(400, 264) Me.Label3.Name = Label3 Me.Label3.TabIndex = 22 Me.Label3.Text = VIPClass Label4 Me.Label4.Location = New System.Drawing.Point(528, 264) Me.Label4.Name = Label4 Me.Label4.TabIndex = 23 Me.Label4.Text = LoyaltyClass Label5 Me.Label5.Location = New System.Drawing.Point(144, 264) Me.Label5.Name = Label5 Me.Label5.TabIndex = 24 Me.Label5.Text = Email Label6 Me.Label6.Location = New System.Drawing.Point(528, 200) Me.Label6.Name = Label6 Me.Label6.TabIndex = 25 Me.Label6.Text = Birthday Label7 Me.Label7.Location = New System.Drawing.Point(400, 200) Me.Label7.Name = Label7 Me.Label7.TabIndex = 26 Me.Label7.Text = Job Label8 Me.Label8.Location = New System.Drawing.Point(272, 200) Me.Label8.Name = Label8 Me.Label8.TabIndex = 27 Me.Label8.Text = Sex Label9 Me.Label9.Location = New System.Drawing.Point(144, 200) Me.Label9.Name = Label9 Me.Label9.TabIndex = 28 Me.Label9.Text = Name Label10 Me.Label10.Location = New System.Drawing.Point(16, 200) Me.Label10.Name = Label10 Me.Label10.TabIndex = 29 Me.Label10.Text = CustomerCodeForm1 Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14) Me.BackgroundImage = CType(resources.GetObject($this.BackgroundImage), System.Drawing.Image) Me.ClientSize = New System.Drawing.Size(744, 406) Me.Controls.Add(Me.Label10) Me.Controls.Add(Me.Label9) Me.Controls.Add(Me.Label8) Me.Controls.Add(Me.Label7) Me.Controls.Add(Me.Label6) Me.Controls.Add(Me.Label5) Me.Controls.Add(Me.Label4) Me.Controls.Add(Me.Label3) Me.Controls.Add(Me.Label2) Me.Controls.Add(Me.Label1) Me.Controls.Add(Me.Button9) Me.Controls.Add(Me.DataGrid1) Me.Controls.Add(Me.TextBox10) Me.Controls.Add(Me.TextBox9) Me.Controls.Add(Me.TextBox8) Me.Controls.Add(Me.TextBox7) Me.Controls.Add(Me.TextBox6) Me.Controls.Add(Me.TextBox5) Me.Controls.Add(Me.TextBox4) Me.Controls.Add(Me.TextBox3) Me.Controls.Add(Me.TextBox2) Me.Controls.Add(Me.TextBox1) Me.Controls.Add(Me.Button8) Me.Controls.Add(Me.Button7) Me.Controls.Add(Me.Button6) Me.Controls.Add(Me.Button5) Me.Controls.Add(Me.Button4) Me.Controls.Add(Me.Button3) Me.Controls.Add(Me.Button2) Me.Controls.Add(Me.Button1) Me.Name = Form1 Me.Text = Form1 CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).EndInit() CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit() Me.ResumeLayout(False) End Sub Private Sub SqlDataAdapter1_RowUpdated(ByVal sender As System.Object, ByVal e As System.Data.SqlClient.SqlRowUpdatedEventArgs) Handles SqlDataAdapter1.RowUpdated End Sub Private Sub DataGrid1_Navigate(ByVal sender As System.Object, ByVal ne As System.Windows.Forms.NavigateEventArgs) Handles DataGrid1.Navigate End Sub Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click mybind.AddNew() End Sub Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged End Sub Private Sub TextBox5_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox5.TextChanged End Sub Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load SqlDataAdapter1.Fill(DataSet11.Customer) mybind = BindingContext(DataSet11, Customer) DataGrid1.DataSource = DataSet11.Customer End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click mybind.Position = 0 DataGrid1.CurrentRowIndex = mybind.Position End Sub Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click mybind.RemoveAt(mybind.Position) End Sub Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click mybind.EndCurrentEdit() SqlDataAdapter1.Update(DataSet11.Customer) End Sub Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click mybind.CancelCurrentEdit() End Sub Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click mybind.Position = mybind.Position - 1 DataGrid1.CurrentRowIndex = mybind.Position End Sub Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click mybind.Position = mybind.Position + 1 DataGrid1.CurrentRowIndex = mybind.Position End Sub Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click mybind.Position = mybind.Count - 1 DataGrid1.CurrentRowIndex = mybind.Position End Sub Private Sub Label6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label6.Click End SubEnd Class*Form2Imports System.DataImports System.Data.SqlClientPublic Class Form2 Inherits System.Windows.Forms.Form#Region Windows 窗体设计器生成的代码 Public Sub New() MyBase.New() 该调用是 Windows 窗体设计器所必需的。 InitializeComponent() 在 InitializeComponent() 调用之后添加任何初始化 End Sub 窗体重写 dispose 以清理组件列表。 Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean) If disposing Then If Not (components Is Nothing) Then components.Dispose() End If End If MyBase.Dispose(disposing) End SubButton1 Me.Button1.BackColor = System.Drawing.SystemColors.ActiveBorder Me.Button1.Location = New System.Drawing.Point(16, 128) Me.Button1.Name = Button1 Me.Button1.TabIndex = 0 Me.Button1.Text = Publisher Button2 Me.Button2.BackColor = System.Drawing.SystemColors.ActiveBorder Me.Button2.Location = New System.Drawing.Point(16, 168) Me.Button2.Name = Button2 Me.Button2.TabIndex = 1 Me.Button2.Text = PublishTime Button3 Me.Button3.BackColor = System.Drawing.SystemColors.ActiveBorder Me.Button3.Location = New System.Drawing.Point(248, 88) Me.Button3.Name = Button3 Me.Button3.TabIndex = 2 Me.Button3.Text = Price Button4 Me.Button4.BackColor = System.Drawing.SystemColors.ActiveBorder Me.Button4.Location = New System.Drawing.Point(16, 88) Me.Button4.Name = Button4 Me.Button4.TabIndex = 3 Me.Button4.Text = Author Button5 Me.Button5.BackColor = System.Drawing.SystemColors.ActiveBorder Me.Button5.Location = New System.Drawing.Point(16, 48) Me.Button5.Name = Button5 Me.Button5.TabIndex = 4 Me.Button5.Text = BookName Button6 Me.Button6.BackColor = System.Drawing.SystemColors.ControlLight Me.Button6.Location = New System.Drawing.Point(16, 16) Me.Button6.Name = Button6 Me.Button6.TabIndex = 5 Me.Button6.Text = BookCode Button7 Me.Button7.BackColor = System.Drawing.SystemColors.GrayText Me.Button7.Location = New System.Drawing.Point(136, 224) Me.Button7.Name = Button7 Me.Button7.TabIndex = 6 Me.Button7.Text = 添加 Button8 Me.Button8.BackColor = System.Drawing.SystemColors.ActiveBorder Me.Button8.Location = New System.Drawing.Point(248, 128) Me.Button8.Name = Button8 Me.Button8.TabIndex = 7 Me.Button8.Text = Discount Button9 Me.Button9.BackColor = System.Drawing.SystemColors.ActiveBorder Me.Button9.Location = New System.Drawing.Point(248, 48) Me.Button9.Name = Button9 Me.Button9.TabIndex = 8 Me.Button9.Text = Content Button10 Me.Button10.BackColor = System.Drawing.SystemColors.ActiveBorder Me.Button10.Location = New System.Drawing.Point(248, 16) Me.Button10.Name = Button10 Me.Button10.TabIndex = 9 Me.Button10.Text = BookSortButton11 Me.Button11.BackColor = System.Drawing.SystemColors.GrayText Me.Button11.Location = New System.Drawing.Point(264, 224) Me.Button11.Name = Button11 Me.Button11.TabIndex = 20 Me.Button11.Text = 退出Form2 Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14) Me.BackColor = System.Drawing.SystemColors.ActiveCaption Me.ClientSize = New System.Drawing.Size(472, 310) Me.Controls.Add(Me.TextBox9) Me.Controls.Add(Me.Button11) Me.Controls.Add(Me.TextBox8) Me.Controls.Add(Me.TextBox7) Me.Controls.Add(Me.TextBox6) Me.Controls.Add(Me.TextBox5) Me.Controls.Add(Me.TextBox4) Me.Controls.Add(Me.TextBox3) Me.Controls.Add(Me.TextBox2) Me.Controls.Add(Me.TextBox1) Me.Controls.Add(Me.Button10) Me.Controls.Add(Me.Button9) Me.Controls.Add(Me.Button8) Me.Controls.Add(Me.Button7) Me.Controls.Add(Me.Button6) Me.Controls.Add(Me.Button5) Me.Controls.Add(Me.Button4) Me.Controls.Add(Me.Button3) Me.Controls.Add(Me.Button2) Me.Controls.Add(Me.Button1) Me.Name = Form2 Me.Text = 添加图书 Me.ResumeLayout(False) End Sub#End Region Private Sub TextBox7_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox7.TextChanged End Sub Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load End Sub Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click Dim hsl As SqlConnection hsl = New SqlConnection hsl.ConnectionString = Initial Catalog=Bookstore; Data Source=.; User ID=sa; Password=; Dim wbl As SqlCommand wbl = New SqlCommand wbl.CommandText = insert into Book values ( & TextBox1.Text & , & TextBox2.Text & , & TextBox3.Text & , & TextBox4.Text & , & TextBox5.Text & , & TextBox6.Text & , & TextBox7.Text & , & TextBox8.Text & , & TextBox9.Text & ) wbl.Connection = hsl hsl.Open() wbl.ExecuteNonQuery() hsl.Close() End Sub Private Sub Button11_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button11.Click End End SubEnd Class*Form3Imports System.dataImports System.data.SqlClientPublic Class Form3 Inherits System.Windows.Forms.Form#Region Windows 窗体设计器生成的代码 Public Sub New() MyBase.New() 该调用是 Windows 窗体设计器所必需的。 InitializeComponent() 在 InitializeComponent() 调用之后添加任何初始化 End Sub Button1 Me.Button1.Location = New System.Drawing.Point(184, 104) Me.Button1.Name = Button1 Me.Button1.TabIndex = 3 Me.Button1.Text = 退出 Button2 Me.Button2.Location = New System.Drawing.Point(184, 40) Me.Button2.Name = Button2 Me.Button2.TabIndex = 4 Me.Button2.Text = 删除 Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim hsl As SqlConnection hsl = New SqlConnection hsl.ConnectionString = Initial Catalog=Bookstore; Data Source=.; User ID=sa; Password=; Dim sql As String sql = select Bookcode from Book Dim mycommand As SqlCommand mycommand = New SqlCommand