课程设计超市数据库管理系统

1、.课 程 设 计 课程名称_数据库_题目名称_超市数据库管理系统_学生学院_计算机学院_专业班级_学 号_ _学生姓名_ _指导教师_左亚尧老师_ 2013年 1月 16日目录一、相关技术介绍1二、需求分析1三、概念结构设计4四、逻辑结构设计5五、数据库实施5六、使用说明25七、收获与体会35:一、 相关技术介绍RDBMS：SQL Server 2008SQL Server 2008提供了公司可依靠的技术和能力来接受不断发展的对于管理数据和给用户发送全面的洞察的挑战。具有在关键领域方面的显著的优势，SQL Server 2008是一个可信任的、高效的、智能的数据平台。SQL Server 20

2、08是微软数据平台愿景中的一个主要部分，旨在满足目前和将来管理和使用数据的需求。应用开发环境和工具：Visual Basic 6.0Visual Basic是一种由微软公司开发的包含协助开发环境的事件驱动编程语言。从任何标准来说，VB都是世界上使用人数最多的语言不仅是盛赞VB的开发者还是抱怨VB的开发者的数量。它源自于BASIC编程语言。VB拥有图形用户界面（GUI）和快速应用程序开发（RAD）系统，可以轻易的使用DAO、RDO、ADO连接数据库，或者轻松的创建ActiveX控件。程序员可以轻松的使用VB提供的组件快速建立一个应用程序二、 需求分析本次选题的题目是超市数据库管理系统，针对的是日

3、常生活中离不开大家的超级市场。要完成超市数据库管理系统，就要对超市的架构体系以及运营流程做一个较为充分的了解。作为一个商业运营系统，超市系统始终离不开人员管理、物资管理以及财务管理，由于财务管理比较简单，所以这次超市系统主要以人员和物资为中心，围绕着这两点展开设计。本次对人员和物资的设计方面部分地方做了简化处理，基本不影响数据库的运行。以下是本次数据库系统的数据字典：数据结构名数据结构含义说明数据项名数据项含义说明数据类型长度取值范围与其他数据项的逻辑关系数据项之间的关系员工超市员工基本信息，仓库管理员和店面员工的超类员工号区分每个员工的编号char4员工的主码姓名员工姓名char8性别员工性

4、别char2男，女年龄员工年龄smallint部门名员工所在部门char10人事部， 销售部， 后勤部职务员工的职务char10员工，主管工龄员工在超市工作的时间（年）smallint主管工号员工所在部门的主管的编号char4员工号员工号的子集员工号的子集仓库超市仓库基本信息仓库号每个仓库的编号char4仓库的主码仓库面积仓库面积smallint仓库管理员后勤部的员工，员工的子类，负责管理商品入货和存储情况工号后勤部员工的编号char4员工.员工号的子集主码，员工的外码仓库号每个仓库的编号char4仓库.仓库号的子集仓库的外码店面超市店面基本信息商品种类超市所卖的商品种类char20店面的主码

5、所在区域商品所在的超市的某区域char10店面员工销售部的员工，负责管理超市里的商品以及商品出售情况工号销售部员工的编号char4员工.员工号的子集主码，员工的外码商品种类销售部员工所负责超市里面的某类商品种类char20店面.商品种类的子集店面的外码商品超市系统中所涉及的物品，超市运行的关键物品条形码商品的条形码，是每件商品的唯一标示char20商品的主码名称商品的名称char20品牌商品的品牌char20规格同种商品的各种尺寸char10单价商品出售的单价float卖出商品卖出的数量smallint仓库号商品存放的仓库号char4仓库.仓库号的子集仓库的外码库存商品剩余的数量smallin

6、t商品种类商品所属的一种种类char20店面.商品种类的子集店面的外码供应商为超市供应商品的商家商家名供应商的名称char20供应商的主码经营范围供应商销售的商品范围char20联系电话供应商的电话char15货单供应商供应给超市的商品货单货单号每张货单的编号char20货单的主码之一条形码每张货单的商品条形码char20货单的主码之一商家名货单对应的商家char20供应商的外码数量商品的数量smallint进货单价商品的进货价float出货单顾客在超市消费的单据货单号出货单的编号char20主码数量商品的数量smallint条形码商品的条形码，是每件商品的唯一标示char20商品的外码以下是

7、本次数据库系统的功能需求：三、 概念结构设计以下为本次超市数据库系统的E-R图：四、 逻辑结构设计以下为本数据库的关系模式：员工（员工号，姓名，性别，年龄，部门名，职务，工龄，主管工号）仓库（仓库号，仓库面积）店面（商品种类，所在区域）仓库管理员（工号，仓库号）店面员工（工号，商品种类）商品（条形码，名称，品牌，规格，单价，卖出，仓库号，库存，商品种类）供应商（商家名，经营范围，联系电话）货单（货单号，条形码，商家名，数量，进货单价）出货单（货单号，条形码，数量）达到3NF五、 数据库实施以下为创建数据库的所有代码：USE master;gocreate database smarketon(

8、name='SDBS',filename='e:学习大三数据库课程设计DBSDBS.mdf',size=50,filegrowth=15%)log on(name='SDBS_log',filename='e:学习大三数据库课程设计DBSDBS_log.ldf',size=6,filegrowth=2)use smarket;gocreate table 员工(员工号 char(4),姓名 char(8) not null,性别 char(2),年龄 smallint,部门名 char(10) not null,职务 char(1

9、0),工龄 smallint,主管工号 char(4),primary key(员工号),foreign key(主管工号)references 员工)use smarket;gocreate table 仓库(仓库号 char(4) primary key,仓库面积 smallint)create table 店面(商品种类 char(20) primary key,所在区域 char(10)use smarket;gocreate table 仓库管理员(工号 char(4) primary key,仓库号 char(4),foreign key(仓库号)references 仓库 on

10、update cascade,foreign key(工号)references 员工 on delete cascade on update cascade)create table 店面员工(工号 char(4) primary key,商品种类 char(20),foreign key(商品种类)references 店面 on update cascade,foreign key(工号)references 员工 on delete cascade on update cascade)use smarket;gocreate table 商品(条形码 char(20) primary

11、key,名称 char(20)not null,品牌 char(20)not null,规格 char(10)not null,单价 float not null,卖出 smallint not null,仓库号 char(4),库存 smallint not null,商品种类 char(20),foreign key(仓库号)references 仓库,foreign key(商品种类)references 店面)create table 供应商(商家名 char(20)primary key,经营范围 char(20),联系电话 char(15)use smarket;gocreate

12、table 货单(货单号 char(20),条形码 char(20),商家名 char(20) not null,数量 smallint not null,进货单价 float not null,primary key(货单号,条形码),foreign key(条形码) references 商品on delete cascade on update cascade,foreign key(商家名) references 供应商on delete cascade on update cascade)create table 出货单(货单号 char(20)primary key,数量 smal

13、lint条形码 char(20),foreign key(条形码) references 商品on delete cascade on update cascade)以下为图形界面上的所有代码：Form1:Private Sub Label2_Click()If Text1.Text = "333333" ThenForm1.HideForm2.ShowUnload MeElseLabel3.Visible = TrueEnd IfEnd SubPrivate Sub Label4_Click()EndEnd SubForm2:Private Sub Command1_Cl

14、ick(Index As Integer)Form3.ShowUnload MeEnd SubPrivate Sub Command19_Click()Form110.ShowUnload MeEnd SubPrivate Sub Command20_Click()Form320.ShowUnload MeEnd SubPrivate Sub Command2_Click()Form4.ShowUnload MeEnd SubPrivate Sub Command21_Click()Form321.ShowUnload MeEnd SubPrivate Sub Command22_Click(

15、)Form322.ShowUnload MeEnd SubPrivate Sub Command23_Click()Form323.ShowUnload MeEnd SubPrivate Sub Command24_Click()Form401.ShowUnload MeEnd SubPrivate Sub Command27_Click()Form11.ShowUnload MeEnd SubPrivate Sub Command3_Click()Form5.ShowUnload MeEnd SubPrivate Sub Command32_Click()Form402.ShowUnload

16、 MeEnd SubPrivate Sub Command34_Click()Form403.ShowUnload MeEnd SubPrivate Sub Command4_Click()Form6.ShowUnload MeEnd SubPrivate Sub Command6_Click()Form8.ShowUnload MeEnd SubPrivate Sub command5_click()Form7.ShowUnload MeEnd SubPrivate Sub Command7_Click()Form12.ShowUnload MeEnd SubPrivate Sub Comm

17、and8_Click()Form404.ShowUnload MeEnd SubPrivate Sub Label4_Click()Form1.ShowUnload MeEnd SubPrivate Sub command25_click()Form9.ShowUnload MeEnd SubForm3:Private Sub Command1_Click()Text1.SetFocusAdodc1.Recordset.MoveLastAdodc1.Recordset.AddNewEnd SubPrivate Sub Form_Load()If Adodc1.Recordset.EOF = F

18、alse ThenAdodc1.Recordset.MoveLastEnd IfAdodc1.Recordset.AddNewEnd SubPrivate Sub Label1_Click()Form3.HideForm2.ShowUnload MeEnd SubPrivate Sub Text1_keypress(keyascii As Integer)Text1.SetFocusEnd SubPrivate Sub text2_keypress(keyascii As Integer)Text2.SetFocusEnd SubPrivate Sub text3_keypress(keyas

19、cii As Integer)Text3.SetFocusEnd SubPrivate Sub text4_keypress(keyascii As Integer)Text4.SetFocusEnd SubPrivate Sub text5_keypress(keyascii As Integer)Text5.SetFocusEnd SubPrivate Sub text6_keypress(keyascii As Integer)Text6.SetFocusEnd SubPrivate Sub text7_keypress(keyascii As Integer)Text7.SetFocu

20、sEnd SubPrivate Sub text8_keypress(keyascii As Integer)Text8.SetFocusEnd SubForm4:Private Sub Command1_Click()Text1.SetFocusAdodc1.Recordset.MoveLastAdodc1.Recordset.AddNewEnd SubPrivate Sub Form_Load()If Adodc1.Recordset.EOF = False ThenAdodc1.Recordset.MoveLastEnd IfAdodc1.Recordset.AddNewEnd SubP

21、rivate Sub Label1_Click()Form4.HideForm2.ShowUnload MeEnd SubPrivate Sub Text1_keypress(keyascii As Integer)Text1.SetFocusEnd SubPrivate Sub text2_keypress(keyascii As Integer)Text2.SetFocusEnd SubForm5:Private Sub Command1_Click()Text1.SetFocusAdodc1.Recordset.MoveLastAdodc1.Recordset.AddNewEnd Sub

22、Private Sub Form_Load()If Adodc1.Recordset.EOF = False ThenAdodc1.Recordset.MoveLastEnd IfAdodc1.Recordset.AddNewEnd SubPrivate Sub Label1_Click()Form5.HideForm2.ShowUnload MeEnd SubPrivate Sub Text1_keypress(keyascii As Integer)Text1.SetFocusEnd SubPrivate Sub text2_keypress(keyascii As Integer)Tex

23、t2.SetFocusEnd SubForm6:Private Sub Label1_Click()Form6.HideForm2.ShowUnload MeEnd SubPrivate Sub Command1_Click()Text1.SetFocusAdodc1.Recordset.MoveLastAdodc1.Recordset.AddNewEnd SubPrivate Sub Form_Load()If Adodc1.Recordset.EOF = False ThenAdodc1.Recordset.MoveLastEnd IfAdodc1.Recordset.AddNewEnd

24、SubPrivate Sub Text1_keypress(keyascii As Integer)Text1.SetFocusEnd SubPrivate Sub text2_keypress(keyascii As Integer)Text2.SetFocusEnd SubForm7:Private Sub Label1_Click()Form7.HideForm2.ShowUnload MeEnd SubPrivate Sub Command1_Click()Text1.SetFocusAdodc1.Recordset.MoveLastAdodc1.Recordset.AddNewEnd

25、 SubPrivate Sub Form_Load()If Adodc1.Recordset.EOF = False ThenAdodc1.Recordset.MoveLastEnd IfAdodc1.Recordset.AddNewEnd SubPrivate Sub Text1_keypress(keyascii As Integer)Text1.SetFocusEnd SubPrivate Sub text2_keypress(keyascii As Integer)Text2.SetFocusEnd SubForm8:Private Sub Command1_Click()Text1.

26、SetFocusAdodc1.Recordset.MoveLastAdodc1.Recordset.AddNewEnd SubPrivate Sub Form_Load()If Adodc1.Recordset.EOF = False ThenAdodc1.Recordset.MoveLastEnd IfAdodc1.Recordset.AddNewEnd SubPrivate Sub Label1_Click()Form8.HideForm2.ShowUnload MeEnd SubPrivate Sub Text1_keypress(keyascii As Integer)Text1.Se

27、tFocusEnd SubPrivate Sub text2_keypress(keyascii As Integer)Text2.SetFocusEnd SubPrivate Sub text3_keypress(keyascii As Integer)Text3.SetFocusEnd SubPrivate Sub text4_keypress(keyascii As Integer)Text4.SetFocusEnd SubPrivate Sub text5_keypress(keyascii As Integer)Text5.SetFocusEnd SubPrivate Sub tex

28、t6_keypress(keyascii As Integer)Text6.SetFocusEnd SubPrivate Sub text7_keypress(keyascii As Integer)Text7.SetFocusEnd SubPrivate Sub text8_keypress(keyascii As Integer)Text8.SetFocusEnd SubForm9:Private Sub Command1_Click()Text1.SetFocusAdodc1.Recordset.MoveLastAdodc1.Recordset.AddNewEnd SubPrivate

29、Sub Form_Load()If Adodc1.Recordset.EOF = False ThenAdodc1.Recordset.MoveLastEnd IfAdodc1.Recordset.AddNewEnd SubPrivate Sub Label1_Click()Form9.HideForm2.ShowUnload MeEnd SubPrivate Sub Text1_keypress(keyascii As Integer)Text1.SetFocusEnd SubPrivate Sub text2_keypress(keyascii As Integer)Text2.SetFo

30、cusEnd SubPrivate Sub text3_keypress(keyascii As Integer)Text3.SetFocusEnd SubForm11:Private Sub Command1_Click()On Error GoTo a1Text1.SetFocusAdodc2.RecordSource = "update 商品 set 库存=库存+" & Val(Text4.Text) & " from 商品 join 货单 on(商品.条形码=货单.条形码)where 商品.条形码='" & Tri

31、m(Text2.Text) & "'"Adodc2.Refresha1: DataGrid1.RefreshAdodc1.Recordset.MoveLastAdodc1.Recordset.AddNewEnd SubPrivate Sub Command3_Click()Adodc2.RecordSource = "select 条形码,名称,卖出,库存 from 商品"Adodc2.RefreshDataGrid1.RefreshEnd SubPrivate Sub Form_Load()If Adodc1.Recordset.EOF

32、 = False ThenAdodc1.Recordset.MoveLastEnd IfAdodc1.Recordset.AddNewEnd SubPrivate Sub Label1_Click()Form11.HideForm2.ShowEnd SubPrivate Sub Text1_keypress(keyascii As Integer)Text1.SetFocusEnd SubPrivate Sub text2_keypress(keyascii As Integer)Text2.SetFocusEnd SubPrivate Sub text3_keypress(keyascii

33、As Integer)Text3.SetFocusEnd SubPrivate Sub text4_keypress(keyascii As Integer)Text4.SetFocusEnd SubPrivate Sub text5_keypress(keyascii As Integer)Text5.SetFocusEnd SubForm12:Private Sub Command1_Click()On Error GoTo a1Text1.SetFocusAdodc2.RecordSource = "update 商品 set 卖出=卖出+" & Val(Te

34、xt3.Text) & " ,库存=库存-" & Val(Text3.Text) & " from 商品 where 商品.条形码='" & Trim(Text2.Text) & "'"Adodc2.Refresha1: DataGrid1.RefreshAdodc1.Recordset.MoveLastAdodc1.Recordset.AddNewEnd SubPrivate Sub Command3_Click()Adodc2.RecordSource = "sel

35、ect 条形码,名称,品牌,规格,库存,卖出 from 商品"Adodc2.RefreshDataGrid1.RefreshEnd SubPrivate Sub Form_Load()If Adodc1.Recordset.EOF = False ThenAdodc1.Recordset.MoveLastEnd IfAdodc1.Recordset.AddNewEnd SubPrivate Sub Label1_Click()Form12.HideForm2.ShowEnd SubPrivate Sub Text1_keypress(keyascii As Integer)Text1

36、.SetFocusEnd SubPrivate Sub text2_keypress(keyascii As Integer)Text2.SetFocusEnd SubPrivate Sub text3_keypress(keyascii As Integer)Text3.SetFocusEnd SubForm110:Dim S As String, Tx As StringDim result As IntegerPrivate Sub Command1_Click()If Text1.Text <> "" ThenIf Text2.Text <>

37、 "" ThenAdodc1.RecordSource = "select * from 员工 where 员工号='" & Trim(Text1.Text) & "'and 姓名='" & Trim(Text2.Text) & "'"Adodc1.RefreshDataGrid1.RefreshElseAdodc1.RecordSource = "select * from 员工 where 员工号='" & Tr

38、im(Text1.Text) & "'"Adodc1.RefreshDataGrid1.RefreshEnd IfElseIf Text2.Text <> "" ThenAdodc1.RecordSource = "select * from 员工 where 姓名='" & Trim(Text2.Text) & "'"Adodc1.RefreshDataGrid1.RefreshEnd IfEnd IfEnd SubPrivate Sub Command

39、4_Click() Adodc1.Recordset.Update Adodc1.RefreshEnd SubPrivate Sub Label5_Click(Index As Integer)Form110.HideForm2.ShowUnload MeEnd SubForm320:Dim S As String, Tx As StringDim result As IntegerPrivate Sub Command1_Click()If Text1.Text <> "" ThenIf Text2.Text <> "" The

40、nAdodc1.RecordSource = "select * from 员工,仓库管理员,仓库 where 仓库管理员.工号='" & Trim(Text1.Text) & "'and 仓库管理员.仓库号='" & Trim(Text2.Text) & "' and 仓库管理员.仓库号=仓库.仓库号 and 工号=员工号"Adodc1.RefreshDataGrid1.RefreshElseAdodc1.RecordSource = "select * fr

41、om 员工,仓库管理员,仓库 where 仓库管理员.工号='" & Trim(Text1.Text) & "'and 仓库管理员.仓库号=仓库.仓库号 and 工号=员工号"Adodc1.RefreshDataGrid1.RefreshEnd IfElseIf Text2.Text <> "" ThenAdodc1.RecordSource = "select * from 员工,仓库管理员,仓库 where 仓库管理员.仓库号='" & Trim(Text2.T

42、ext) & "' and 仓库管理员.仓库号=仓库.仓库号 and 工号=员工号"Adodc1.RefreshDataGrid1.RefreshEnd IfEnd IfEnd SubPrivate Sub Command4_Click() Adodc1.Recordset.Update Adodc1.RefreshEnd SubPrivate Sub Label5_Click(Index As Integer)Form320.HideForm2.ShowUnload MeEnd SubForm321:Dim S As String, Tx As Strin

43、gDim result As IntegerPrivate Sub Command1_Click()If Text1.Text <> "" ThenIf Text2.Text <> "" ThenAdodc1.RecordSource = "select * from 员工,店面员工,店面 where 店面员工.工号='" & Trim(Text1.Text) & "'and 店面.商品种类='" & Trim(Text2.Text) &

44、; "' and 店面.商品种类=店面员工.商品种类 and 工号=员工号"Adodc1.RefreshDataGrid1.RefreshElseAdodc1.RecordSource = "select * from 员工,店面员工,店面 where 店面员工.工号='" & Trim(Text1.Text) & "'and 店面.商品种类=店面员工.商品种类 and 工号=员工号"Adodc1.RefreshDataGrid1.RefreshEnd IfElseIf Text2.Text &

45、lt;> "" ThenAdodc1.RecordSource = "select * from 员工,店面员工,店面 where 店面.商品种类='" & Trim(Text2.Text) & "' and 店面.商品种类=店面员工.商品种类 and 工号=员工号"Adodc1.RefreshDataGrid1.RefreshEnd IfEnd IfEnd SubPrivate Sub Command4_Click() Adodc1.Recordset.Update Adodc1.Refresh

46、End SubPrivate Sub Label5_Click(Index As Integer)Form321.HideForm2.ShowUnload MeEnd SubForm322:Dim S As String, Tx As StringDim result As IntegerPrivate Sub Command1_Click()If Text1.Text <> "" ThenIf Text2.Text <> "" ThenAdodc1.RecordSource = "select 商品.仓库号,条形码,名

47、称,品牌,规格,库存,仓库面积 from 商品,仓库 where 商品.条形码='" & Trim(Text1.Text) & "'and 商品.仓库号='" & Trim(Text2.Text) & "' and 商品.仓库号=仓库.仓库号"Adodc1.RefreshDataGrid1.RefreshElseAdodc1.RecordSource = "select 商品.仓库号,条形码,名称,品牌,规格,库存,仓库面积 from 商品,仓库 where 商品.条形码

48、='" & Trim(Text1.Text) & "'and 商品.仓库号=仓库.仓库号"Adodc1.RefreshDataGrid1.RefreshEnd IfElseIf Text2.Text <> "" ThenAdodc1.RecordSource = "select 商品.仓库号,条形码,名称,品牌,规格,库存,仓库面积 from 商品,仓库 where 商品.仓库号='" & Trim(Text2.Text) & "' and

49、 商品.仓库号=仓库.仓库号"Adodc1.RefreshDataGrid1.RefreshEnd IfEnd IfEnd SubPrivate Sub Command4_Click() Adodc1.Recordset.Update Adodc1.RefreshEnd SubPrivate Sub Label5_Click(Index As Integer)Form322.HideForm2.ShowUnload MeEnd SubForm323:Dim S As String, Tx As StringDim result As IntegerPrivate Sub Comman

50、d1_Click()If Text1.Text <> "" ThenIf Text2.Text <> "" ThenAdodc1.RecordSource = "select 商品.商品种类,条形码,名称,品牌,规格,单价,卖出,所在区域 from 商品,店面 where 条形码='" & Trim(Text1.Text) & "'and 商品.商品种类='" & Trim(Text2.Text) & "' and 店

51、面.商品种类=商品.商品种类"Adodc1.RefreshDataGrid1.RefreshElseAdodc1.RecordSource = "select 商品.商品种类,条形码,名称,品牌,规格,单价,卖出,所在区域 from 商品,店面 where 条形码='" & Trim(Text1.Text) & "'and 店面.商品种类=商品.商品种类"Adodc1.RefreshDataGrid1.RefreshEnd IfElseIf Text2.Text <> "" The

52、nAdodc1.RecordSource = "select 商品.商品种类,条形码,名称,品牌,规格,单价,卖出,所在区域 from 商品,店面 where 商品.商品种类='" & Trim(Text2.Text) & "' and 店面.商品种类=商品.商品种类"Adodc1.RefreshDataGrid1.RefreshEnd IfEnd IfEnd SubPrivate Sub Command4_Click() Adodc1.Recordset.Update Adodc1.RefreshEnd SubPriva

53、te Sub Label5_Click(Index As Integer)Form323.HideForm2.ShowUnload MeEnd SubForm401:Dim S As String, Tx As StringDim result As IntegerPrivate Sub Command1_Click()If Text1.Text <> "" ThenIf Text2.Text <> "" ThenAdodc1.RecordSource = "select * from 商品 where 条形码='

54、;" & Trim(Text1.Text) & "'and 名称='" & Trim(Text2.Text) & "'"Adodc1.RefreshDataGrid1.RefreshElseAdodc1.RecordSource = "select * from 商品 where 条形码='" & Trim(Text1.Text) & "'"Adodc1.RefreshDataGrid1.RefreshEnd IfEl

55、seIf Text2.Text <> "" ThenAdodc1.RecordSource = "select * from 商品 where 名称='" & Trim(Text2.Text) & "'"Adodc1.RefreshDataGrid1.RefreshEnd IfEnd IfEnd SubPrivate Sub Command4_Click() Adodc1.Recordset.Update Adodc1.RefreshEnd SubPrivate Sub Label5_Cli

56、ck(Index As Integer)Form401.HideForm2.ShowUnload MeEnd SubForm402:Dim S As String, Tx As StringDim result As IntegerPrivate Sub Command1_Click()If Text1.Text <> "" ThenAdodc1.RecordSource = "select * from 供应商 where 商家名='" & Trim(Text1.Text) & "'"Adodc1.RefreshDataGrid1.RefreshEnd IfEnd SubPrivate Sub Command4_Click() Adodc1.Recordset.Update Adodc1.RefreshEnd SubPrivate Sub Label5_Click(Index As Integer)Form4