同济数据库项目报告.docx

装订线 嘉食通餐饮信息查询系统TONGJI UNIVERSITY数据库技术及其应用项目设计报告项目名称“嘉食通”校园餐饮信息查询系统组 长Xx xxxxxx小组成员 Xxx xxxxxx学院（系）xxxxxxxxxxx专 业xxxx指导教师朱静完成日期2011年12月20 日 “嘉食通”校园餐饮信息查询系统项目实际意义 如今，越来越多的学生来到了嘉定校区，来到这个陌生的地方。当又一批学生来到嘉定以后，都会发现这里并不像想象中的那样荒凉，在同济新天地有许许多多的商家，有着各种各样的小吃和菜目，但是初来乍到的他们对于这些东西并没有太大的了解，不管是品种，还是胃口、价格等，都不具备太多的了解，为了让大家对嘉定校区的“吃”有所了解，同时也增加在这过程中的趣味性，我们决定制作一个可以为嘉定的新面孔提供一个集信息查询、评价系统、条件搜索、推荐功能于一身的小系统，既能为新生提供帮助，这对于老生来说这也有很大的引导作用，能提供很大的便捷，能利用自己的经验为他人提供一个参考，该系统的评价功能是一个很好的直观参考，也极具互动娱乐价值。项目主要完成工作1. 用户注册及密码修改：进入登陆页面后，可点击注册按钮进行注册，登录成功后可以修改密码。2. 登陆： 用户进入登陆页面，输入用户名及密码，正确则进入系统，不正确则提示。3. 信息查询：用户查看食品的相关内容，包括相应餐馆信息，食品价格，商家图片，他人的评价、评分等。4. 评价：用户对食品的评价，包括文字评价，评分，“顶”相应的菜目等。5. 系统推荐：根据评分、价格和类别排序同时，还可以给出一份人气榜单。6. 图片浏览：用户可以在窗体中查看所有商家的图片。7. 管理员界面：对菜目、商家信息进行插入、删除、更新等操作，对评价进行删除操作。数据库结构设计1. 关系表：菜目表（菜名，商家名称，所属类别，价格，卫生评分总和，口味评分总和，价格评分总和，人气，评价人数）商家表（商家名称，简介，电话）商家评价表（商家名称，评价时间，学号，姓名，评价内容）菜目评价表（菜名，商家名称，评价时间，学号，姓名，评价内容）用户表（学号，姓名，密码）管理员表（账号，密码）2.E - R模型：密码商家名称称管理员编辑评价内容商家密码姓名学号用户简介菜名价格评分口味评分评价时间编辑账号电话评价内容评价时间评分人数图片所属类别价格卫生评分评价评分评价评分出售菜目人气3.安全性设计进入登录界面（界面见用户指南），以用户名以及密码判断是否为用户。学校学生和管理员为用户，用户名、密码错误的不能登录用户手册1） 登录窗体如果没有帐号，可以点击“注册”。输入相关信息点击“注册”，如果存在了该用户，显示“已存在该用户”，注册成功后点击“返回”，回到登录页面。输入用户名、密码，如果是用户权限的，成功后出现欢迎界面点击“SKIP”或自动跳过进入主窗体点击菜单栏左方的“”按钮，控制音乐的播放与停止此时可以查看菜品详细信息，点击“顶”，可以提升菜品的人气，且每个用户只能“顶”一次，不能够重复操作，也可以按商家查询此商家的菜品，同时可在“详细信息”的下方输入对菜品的评分。点击主菜单上的按钮，可查看以下窗体。点击“商家信息”本窗体可查看商家信息。点击“用户评价”。在单选框中可选择查看商家或菜品，点击“我要评价”可以对菜品和商家进行评价点击单选框“商家名”查看商家的评价。点击“系统推荐”在本窗体中可以选择类别和条件查询相关排名情况。点击“全局视图”点击“修改密码”可以修改当前用户的密码。程序（窗体设计略）1. 存储过程用于查询人气排在前20的菜品create procedure dbo.caimu_renqi asselect top 20 * from 菜目order by 人气descgo2. 触发器（7个）用于实现数据库内部表间的外键约束：1）防止插入菜目时出现不存在的商家trigger dbo.插入菜目on dbo.菜目for insertasdeclare s_name as char(20)select s_name = 商家名称from insertedif s_name not in (select 商家名称from 商家)beginprint 商家不存在rollbackend2）菜目表中删除时菜目评价表中的联动删除trigger dbo.删除菜目ON dbo.菜目FOR DELETEASDECLARE _sname as char(20)DECLARE _cname as char(20)select _sname = d.商家名称, _cname=d.菜名FROM deleted dbegin delete from 菜目评价where 商家名称= _sname and 菜名=_cnamedelete from 菜目where 商家名称= _sname and 菜名=_cnameprint已经删除相关信息end3）菜目表中修改时菜目评价表中的联动修改trigger dbo.修改菜目ON dbo.菜目FOR updateASDECLARE _sname as char(20)DECLARE _cname as char(20)DECLARE _sname_o as char(20)DECLARE _cname_o as char(20)select _sname = 商家名称, _cname=菜名FROM inserted select _sname_o = 商家名称, _cname_o=菜名FROM deletedbegin update 菜目评价set 商家名称=_sname,菜名=_cnamewhere 商家名称= _sname_o and 菜名=_cname_oprint已经修改相关信息end4）菜目表中修改时商家评价表中的联动修改trigger dbo.修改菜目ON dbo.菜目FOR updateASDECLARE _sname as char(20)DECLARE _cname as char(20)DECLARE _sname_o as char(20)DECLARE _cname_o as char(20)select _sname = 商家名称, _cname=菜名FROM inserted select _sname_o = 商家名称, _cname_o=菜名FROM deletedbegin update 菜目评价set 商家名称=_sname,菜名=_cnamewhere 商家名称= _sname_o and 菜名=_cname_oprint已经修改相关信息end5）商家表中修改时菜目表、商家评价表中的联动修改ALTER trigger dbo.修改商家ON dbo.商家FOR updateASDECLARE _sname as char(20)DECLARE _sname_o as char(20)select _sname = 商家名称FROM inserted select _sname_o = 商家名称FROM deleted beginupdate 菜目set 商家名称=_snamewhere 商家名称= _sname_oupdate 商家评价set 商家名称=_snamewhere 商家名称= _sname_oprint已经修改相关信息end6）删除学号时的联动删除ALTER trigger dbo.删除学号ON dbo.用户FOR DELETEASDECLARE _sname as char(20)select _sname = 学号FROM deleted begin delete from 用户where 学号= _snamedelete from 商家评价where 学号= _snamedelete from 菜目评价where 学号=_snameprint已经删除相关信息end7）修改学号时菜目评价表、商家评价表中的联动修改ALTER trigger dbo.修改学号ON dbo.用户FOR updateASDECLARE _sname as char(20)dECLARE _sname_o as char(20)select _sname = 学号FROM inserted select _sname_o = 学号FROM deletedbegin update 菜目评价set 学号=_snamewhere 学号= _sname_oupdate 商家评价set 学号=_snamewhere 学号= _sname_oprint已经修改相关信息end3. 界面程序1)登录窗体Public Class 登录窗体 Private Sub 登录窗体_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load AxWindowsMediaPlayer1.settings.setMode(loop, True) AxWindowsMediaPlayer1.URL = Application.StartupPath & summer 久石让 .wma AxWindowsMediaPlayer1.Ctlcontrols.play() playornot = 0 AxWindowsMediaPlayer1.Visible = False Dim ob As Control Dim obtype As String For Each ob In Me.Controls obtype = ob.GetType().ToString() If obtype = System.Windows.Forms.Button Or obtype = System.Windows.Forms.Label Then ob.Font = New System.Drawing.Font(宋体, 14.0F, FontStyle.Bold) End If Next Me.BackgroundImage = Image.FromFile(Application.StartupPath & 201011020935229213.png) Label1.BackColor = Color.Transparent Label2.BackColor = Color.Transparent Me.管理员TableAdapter.Fill(Me.嘉食通DataSet.管理员) Me.用户TableAdapter.Fill(Me.嘉食通DataSet.用户) End Sub Private Sub button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click If TextBox1.Text = Nothing Or TextBox2.Text = Nothing Then MessageBox.Show(用户名或密码不能为空, 提示) Exit Sub End If 管理员TableAdapter.FillBy管理员(嘉食通DataSet.管理员, TextBox1.Text.Trim, TextBox2.Text.Trim) If 嘉食通DataSet.管理员.Count = 0 Then 用户TableAdapter.Fillby用户(嘉食通DataSet.用户, TextBox1.Text.Trim, TextBox2.Text.Trim) If 嘉食通DataSet.用户.Count = 0 Then MessageBox.Show(用户名或密码错误, 提示) TextBox2.Text = Nothing Else : Dim f2 As New welcome 当前用户名 = Me.TextBox1.Text.Trim MessageBox.Show(登陆成功, 欢迎) gly = False AxWindowsMediaPlayer1.Ctlcontrols.pause() f2.Show() Me.Hide() End If Else : Dim f1 As New guanliyuan gly = True f1.Show() Me.Hide() End If End Sub Private Sub button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click Me.Close() End SubPrivate Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim f2 As New 注册页面 f2.Show() Me.Hide() End SubEnd Class 2)welcomePublic Class welcome Dim time As Integer Private Sub welcome_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Me.StartPosition = FormStartPosition.CenterScreen Me.AutoSize = False Me.BackColor = Color.Black Label1.ForeColor = Color.Wheat AxShockwaveFlash1.Height = 300 AxShockwaveFlash1.Width = 400 AxShockwaveFlash1.Left = -5 AxShockwaveFlash1.Top = 30 Me.Height = AxShockwaveFlash1.Height + 50 Me.Width = AxShockwaveFlash1.Width AxWindowsMediaPlayer1.Visible = False Call flash() Label1.Text = （点击此处跳过动画）欢迎用户 & 当前用户名 & 登陆嘉食通美食信息管理系统 Label1.Top = 10 Me.Height - Label1.Height - 40 Label1.Left = Me.Width Timer1.Enabled = True Timer2.Enabled = True Timer1.Interval = 50 Timer2.Interval = 1000 time = 0 Label1.BackColor = Color.Transparent Button1.Text = Skip Button1.Left = Me.Width - Button1.Width Button1.Top = 3 Button1.Height = 25 End Sub Private Sub flash() AxShockwaveFlash1.Movie = Application.StartupPath & 中国包子大战日本寿司.swf AxShockwaveFlash1.GotoFrame(0) AxShockwaveFlash1.Play() If Not AxShockwaveFlash1.IsPlaying Then MsgBox(播放结束) End If End Sub Private Sub media() AxWindowsMediaPlayer1.URL = Application.StartupPath & 中国包子大战日本寿司.swf AxWindowsMediaPlayer1.Ctlcontrols.play() AxWindowsMediaPlayer1.Visible = True End Sub Private Sub AxShockwaveFlash1_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AxShockwaveFlash1.EnterEnd Sub Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick Label1.Left -= 2 If Label1.Left = -10 - Label1.Width Then Label1.Left = Me.Width + 10 End If End Sub Private Sub Label1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Label1.Click Call f End SubPrivate Sub Timer2_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer2.Tick time += 1 If time = 85 Then Call f End If End SubPrivate Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Call f() End Sub Private Sub f() AxShockwaveFlash1.StopPlay() mainform.Show() 合并时要把此行代码改为用户看到的第一个窗体 Me.Hide() End Sub End Class3)mainformPublic Class mainform Dim time As Integer Private Sub 信息浏览ToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 信息浏览ToolStripMenuItem.Click Dim F1 As New 菜目浏览 F1.MdiParent = Me F1.Width = Me.Width F1.Show() End Sub Private Sub 系统推荐ToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 系统推荐ToolStripMenuItem.Click Dim F2 As New 排序筛选 F2.MdiParent = Me F2.Width = Me.Width F2.Show() End Sub Private Sub wait() time = 0 Timer1.Enabled = True End Sub Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick time += 1 If time = 3 Then 登录窗体.AxWindowsMediaPlayer1.Ctlcontrols.play() End If End Sub Private Sub mainform_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Timer1.Enabled = False Timer1.Interval = 1000 Call wait() 播放ToolStripMenuItem.Image = Image.FromFile(Application.StartupPath & 129100m03620-45294.png) Dim F3 As New 菜目浏览 F3.MdiParent = Me F3.Width = Me.Width F3.Show() End Sub Private Sub 退出ToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 退出ToolStripMenuItem.Click Application.Exit() End SubPrivate Sub 用户评价ToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 用户评价ToolStripMenuItem.Click Dim F4 As New 用户评价 F4.MdiParent = Me F4.Width = Me.Width F4.Show() End Sub Private Sub 修改密码ToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 修改密码ToolStripMenuItem.Click Dim F5 As New 修改密码 F5.MdiParent = Me F5.Width = Me.Width F5.Show() End SubPrivate Sub 商家简介ToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 商家简介ToolStripMenuItem.Click Dim F6 As New guanliyuan F6.MdiParent = Me F6.ControlBox = False F6.AutoScroll = True F6.Width = Me.Width F6.Show() End SubPrivate Sub 全局视图ToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 全局视图ToolStripMenuItem.Click butview.MdiParent = Me butview.Show() butview.Label1.Visible = False End Sub Private Sub 播放ToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 播放ToolStripMenuItem.Click If playornot = 0 Then 登录窗体.AxWindowsMediaPlayer1.Ctlcontrols.pause() 播放ToolStripMenuItem.Image = Image.FromFile(Application.StartupPath & 20071279182659.png) playornot = 1 Else 登录窗体.AxWindowsMediaPlayer1.Ctlcontrols.play() 播放ToolStripMenuItem.Image = Image.FromFile(Application.StartupPath & 129100m03620-45294.png) playornot = 0 End If End SubEnd Class4)菜目浏览Public Class 菜目浏览Private Sub 信息查询_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim ob As Control Dim obtype As String For Each ob In Me.Controls obtype = ob.GetType().ToString() If obtype = System.Windows.Forms.Label Then ob.BackColor = Color.Transparent ob.Font = New System.Drawing.Font(宋体, 9.0F, FontStyle.Bold) End If Next Me.GroupBox1.BackColor = Color.Transparent Me.BackgroundImage = Image.FromFile(Application.StartupPath & 200810521585848477801.jpg) Me.菜目TableAdapter.Fill(Me.嘉食通DataSet.菜目) Me.顶TableAdapter.Fill(Me.嘉食通DataSet.顶) Me.菜目1TableAdapter.Fill(Me.嘉食通DataSet.菜目1) Me.商家TableAdapter.Fill(Me.嘉食通DataSet.商家) End SubPrivate Sub 系统推荐ToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Dim f2 As New 排序筛选 f2.Show() End SubPrivate Sub 用户评价ToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Dim f3 As New 用户评价 f3.Show() End SubPrivate Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.ClickIf TextBox1.Text.Trim = Nothing Then MessageBox.Show(查询内容不能为空) Else 菜目TableAdapter.FillBy菜名(Me.嘉食通DataSet.菜目, TextBox1.Text.Trim) Dim i As Integer For i = 0 To Me.菜目1DataGridView.Rows.Count - 1 If Me.菜目1DataGridView.Rows(i).Cells(0).Value Like Me.菜目DataGridView.Rows(菜目BindingSource.Position).Cells(0).Value Then 菜目1BindingSource.Position = i Exit For End If Next If i = 菜目1DataGridView.Rows.Count Then MsgBox(没找到) TextBox1.Text = End IfEnd If End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click cname = Me.菜名TextBox.Text.Trim sname = Me.商家名称TextBox.Text.Trim Dim f4 As New 用户评价 f4.MdiParent = Me.MdiParent f4.Show() Me.Hide()End SubPrivate Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click 菜目TableAdapter.FillBy商家名称(Me.嘉食通DataSet.菜目, ComboBox1.Text.Trim) Dim i As Integer For i = 0 To Me.菜目1DataGridView.Rows.Count - 1 If Me.菜目1DataGridView.Rows(i).Cells(0).Value Like Me.菜目DataGridView.Rows(菜目BindingSource.Position).Cells(0).Value Then 菜目1BindingSource.Position = i Exit For End If NextEnd Sub Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click菜目1BindingSource.MoveFirst() End SubPrivate Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click 菜目1BindingSource.MovePrevious() End SubPrivate Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click 菜目1BindingSource.MoveNext() End SubPrivate Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click 菜目1BindingSource.MoveLast() End SubPrivate Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click If TextBox2.Text = Nothing Or TextBox3.Text = Nothing Or TextBox4.Text = Nothing Then MessageBox.Show(请完整输入评分, ) End If If Not IsNumeric(TextBox2.Text) Or Not IsNumeric(TextBox3.Text) Or Not IsNumeric(TextBox4.Text) ThenMessageBox.Show(评分应为数字, ) ElseIf TextBox2.Text.Trim 100 Or TextBox3.Text.Trim 100 Or TextBox4.Text.Trim 100 Then MessageBox.Show(评分应为0-100的数字, ) Else 菜目1TableAdapter.UpdateQuery评分(TextBox3.Text.Trim, TextBox4.Text.Trim, TextBox2.Text.Trim, 菜名TextBox.Text.Trim, 商家名称TextBox.Text.Trim) MessageBox.Show(评分成功, ) TextBox3.Text = Nothing TextBox4.Text = Nothing TextBox2.Text = Nothing End If End SubPrivate Sub Button9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button9.Click Me.顶TableAdapter.FillBy是否存在(Me.嘉食通DataSet.顶, Me.菜名TextBox.Text.Trim, Me.商家名称TextBox.Text.Trim, 当前用户名) If Me.嘉食通DataSet.顶.Count = 0 Then 顶TableAdapter.InsertQuery是否顶过(菜名TextBox.Text.Trim, 商家名称TextBox.Text.Trim, 当前用户名) 菜目1TableAdapter.UpdateQuery顶(菜名TextBox.Text.Trim, 商家名称TextBox.Text.Trim) 人气TextBox.Text += 1 Else MessageBox.Show(您已经顶过) End If End Sub Private Sub Button10_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button10.Click Dim k As Integer k = 菜目BindingSource.Position 菜目Binding