超市销售数据库

1、数据库系统课程设计题目超市销售管理数据库设计院系信息技术与工程学院专业计算机科学与技术姓名学号班级名称指导教师成绩2016年12月 15日目录前言1一、系统需求分析11、处理对象12、系统功能及信息处理13、安全性和完整性要求24、系统结构图2二、概念模型31、分 E-R 图建立32、全局 / 整体 E-R 图4三、关系数据模型41、关系模式建立42、用户子模式建立5四、数据库物理设计5五、数据库实施与测试61、 数据库实施62、 数据库测试6六、总结6附录7前言超市销售管理系统是为了解决超市销售管理业务方面面临的复杂业务流程和繁琐数据处理等问题，提高超市运营效率，提高经济效益，提高市场竞争力

2、而研究开发的一款数据库软件。在开发平台 windows 上，采用了 SQL 数据库程序设计语言予以实现；可访问 SQL SEREVER 2005 数据库，具有商品录入、商品信息查询、交易额计算、会员打折、打印销售发票、退货、打印退货发票、记录交易细节、货架管理、商品过期警告、缺货警告等方面的功能，为超市管理提供了有效的技术保障，并且可直接作为开发整个超市管理系统时已完成的一部分模块。一、系统需求分析1、处理对象系统要处理的基本对象包括营业员基本信息、会员基本信息、管理员基本信息、商品基本信息、货架基本信息、销售单基本信息、退货单基本信息等多个对象。各个基本对象包含信息如下所示：营业员 (营业员

3、号，收银台位置，上班时间，下班时间，月薪，联系 ); 会员 (会员卡号，会员，卡密码，注册时间，累计消费 );商品（商品编号，货架编号，条形码，商品名称，商品价格，现有存量，存量底线，生产日期，保质期，供货商） ；销售单（销售单编号，营业员编号，会员卡号，打印时间，有无折扣） ；销售商品（销售单号，商品编号，销售数量） ；货架（货架编号，管理员号，货架名称）；管理员（管理员号，联系，上班时间，下班时间，月薪） ；退货单（退货单号，商品编号，退货数量） ；打印（退货单号，销售单号，营业员号，打印时间）。2、系统功能及信息处理本中小型超市销售管理系统大体上包含三个模块，为前台收银业务处理，前台退货

4、业务处理以及后台销售处理功能模块，具体如下所述。2.1 收银业务通过扫描条形码或者直接输入商品名称（对于同类多件商品采用一次录入加数量的方式），计算本次交易的总金额，同时打印销售发票给顾客（包括交易的流水账号、每类商品的商品名、数量、该类商品的总金额、交易的时间、负责本次收银的员工号等信息）。如果顾客是本店会员并持有本人会员卡， 则在交易前先扫描会员卡， 对此会员一定优惠，并将所购物品的总金额累计到该会员的总消费金额中， 记录好会员信息。2.2 退货处理顾客持有销售发票到收银台找收银员退货，若没有相应销售发票不予以退货。通过扫描销售发票计算本次退货处理的退货额，并打印出退货发票给顾客，系统记录

5、好退货信息。2.3 销售处理作为超市后台管理部分，此系统应该具有功能包括当打印销售发票或退货发票时自动增减货架上商品数量，当货架商品不足时自动生成缺货警告信息给管理员，系统能够生成销售排行榜供管理员查看。3、安全性和完整性要求安全性先通过视图机制，不同的用户只能访问系统授权的视图；再通过用户授权机制，通过用户登陆来识别用户级别，根据这个级别来分配用户权限，达到数据更高层次的安全功能。完整性要求用于确保各处理对象的主属性取值唯一并且一般不能为空；各处理对象的参照属性取值必须是来自于被参照属性。可以通过用户自定义完整性（符合实际要求）来确保数据符合更高规要求。详细完整性要求见于系统的逻辑设计阶段。

6、4、系统结构图通过对中小型超市销售管理业务及其功能方面的分析，构造出超市管理系统的总体结构图如下：中小型超市销售管理系统前台销售后台管理收退销进库人银货售货存员业处处管管管务理理理理理图 1 超市管理总体结构图由于本系统为销售管理系统，只是超市管理系统的一部分，因此只实现了收营业务、退货处理和销售处理部分的功能。对这三个处理模块进一步细化得到如下分结构图：图 1-1收银业务结构图图 1-2退货处理结构图销售处理货架缺货销存货架售量警补排管告货行理图 1-3销售处理结构图二、概念模型1、分 E-R 图建立根据分解第二层数据流程图可分别得到三个分E-R 图。姓名累计注册时间消 费 额卡 号会 员密

7、 码编 号编号1条 形 码姓名获得时 间数 量名 称收 银 台m位 置1m销售n价 格n营业员销售单商品上班时间打印商品生产日期下班时间保 质 期月 薪联系编号消费额商 品发 货 商电话折扣编 号图 4-1收银业务模块 E-R图发票编号消费额折扣打印时间退货单号购物发票1编号商品编号m打印退货单姓名1退货数量营业员收 银 台位 置退费额上班时间下班时间月薪联系电话图 4-2退货处理模块 E-R图管理员号条 形 码现有存量货架编号姓名商品编号联系电话商品m1m1存放货架管理管理员商品名称上班时间价 格下班时间最低存量月薪图 4-3销售处理模块 E-R图2、全局 / 整体 E-R 图由于在做局部

8、ER 图时，只考虑了局部功能模块，这样会导致各个 E R 图之间有很多不一致的地方，造成在合并各分 E R图生成全局 E R 图时有很多冲突。通过仔细分析各个分 E-R 图之间的联系， 消除冗余，消除冲突，最终成功生成全局 E-R 图，如下图所示。会 员1现有存量存量底线打印时间销售数量获得m营业员1m销售单m销售商品nm存放1打印商品货架11m打印管理m打印时间1退货单管理员图 5 系统总 E-R图三、关系数据模型1、关系模式建立关系模型由 E R 图转换而来，实际上就是要将实体、 实体的属性和实体之间的联系明确表示出来，这种转换一般遵循如下规则：一个实体型转换为一个关系模式。实体的属性就是

9、关系的属性，实体的码就是关系的码。此数据库系统包括营业员、会员、管理员、商品、货架、销售单、销售商品、退货单、打印多个关系模式：营业员 (营业员号，收银台位置，上班时间，下班时间，月薪，联系 ); 会员 (会员卡号，会员，卡密码，注册时间，累计消费 );商品（商品编号，货架编号，条形码，商品名称，商品价格，现有存量，存量底线，生产日期，保质期，供货商） ；销售单（销售单编号，营业员编号，会员卡号，打印时间，有无折扣） ；销售商品（销售单号，商品编号，销售数量） ；货架（货架编号，管理员号，货架名称）；管理员（管理员号，联系，上班时间，下班时间，月薪） ；退货单（退货单号，商品编号，退货数量）

10、；打印（退货单号，销售单号，营业员号，打印时间）；2、用户子模式建立用户子模式可通过建立视图来表示。视图是虚表，是从一个或几个基本表（或视图）中导出的表， 在系统的数据字典中仅存放了视图的定义， 不存放视图对应的数据。定义视图可以简化应用程序、可以实现一定的权限控制。为了满足用户需求及方便后期数据库实施阶段的设计，此系统设计了如下视图。各视图定义：商品保质期（商品名称，生产日期，保质期，过期日期）下架商品（商品名称，货架名称，过期日期）营业员基本信息（营业员号，上班时间，下班时间，月薪，联系）会员总消费情况（卡号，累计消费）商品价格（商品名称，价格）商品存放（商品名称，货架名称）缺货商品（商品

11、名称，现有存量，底线存量）货架信息（货架名称，存放商品名）上班情况（上班时间，营业员，管理员）工作人员联系方式（，编号，联系）工作人员工资信息（，编号，工资）四、数据库物理设计此数据库系统建立的索引如下所述。（1）对于基本表 ShopAssistant （营业员） , 由于要经常对属性列 Snum查询从而获得某个营业员的完整信息， 并且很少对其更新， 因此可以给属性列 Snum建一个聚簇索引。（2）同理对于基本表 Member（会员） ,由于要经常对属性列 Mnum 查询从而获得某个会员的完整信息， 并且很少对其更新， 因此可以给属性列 Mnum 建一个聚簇索引。（3）对于 Adminastr

12、ator（管理员），可在其属性列 Anum 上建立唯一性索引，索引值按降序排列。（4）同样对于基本表 GoodsShelf（货架）、 Goods（商品），可在其主属性上建立唯一性索引。（5）对于基本表 BillSell （销售单）、SellGoods（销售商品）、ReturnGoods（退货单）、PrintBill（打印），由于其属性值经常发生变化， 权衡系统为维护索引付出的代价，可考虑不建立索引。五、数据库实施与测试数据库实施与测试阶段主要容包括数据库实施和测试两个部分。1、 数据库实施1.1数据库及数据库对象建立T-SQL 和主要包括：数据库、基本表、视图、索引、触发器以及存储过程。相应P

13、L/SQL 语句详见附录 .1.2数据入库50 条左右的测试记录，测试结果满足设计要求。对各个数据表成功要录入了2、 数据库测试数据库测试阶段主要容是对建立的数据库及数据库对象进行测试，对测试结果进行分析，是否满足设计要求。六、总结通过数据库课程设计实习，对数据库知识有了更广泛的了解，在数据库的应用方面有了很大的收获。（2）加深了对数据库系统相关知识和 SQL SERVER 2008数据库相关功能的理解。以前只停留于记忆书本上关于数据库系统的理论知识，没有切身实地的实践过，而通过这次实习我再次加深了对数据库相关功能的理解与应用。（3）进一步掌握相关的SQL语句。开发设计超市销售管理系统的过程中

14、牵涉到相当多基本表的建立，视图、索引以及存储过程的设计，这让我对这些 SQL语句操作更熟练了。（4）熟悉了对项目开发的大致过程。这次系统开发， 我更加明白体验到了数据库系统开发的过程， 包括系统需求分析、概念设计、逻辑设计、物理设计，再到数据库实施、系统的测试和调试，对项目（系统）开发的大致流程有了一定的了解，为以后的系统的开发打下了良好的基础。同样在在这次实习中，我看到了自己的基础知识的薄弱性，更体验到了基础知识的重要性。比如说对具体的 SQL语句还不是很熟悉，在画 E-R 图、设计带输出变量的存储过程以及建立相关索引（聚簇索引）时感到有些棘手。遇到问题不可怕，其实能遇到问题是好事，它能让我

15、在解决问题时学到更多的新知识，更能增加我的自信。和传统管理模式相比较，使用本系统，毫无疑问会大大提高超市的运作效率，辅助提高超市的决策水平，管理水平，为降低经营成本，提高效益，减少差错，节省人力，减少顾客购物时间，增加客流量，提高顾客满意度，增强超市扩能力， 都能提供有效的技术保障。但超市管理系统涉及围宽，要解决的问题多，功能复杂，实现困难，本系统只能做出其中的销售管理部分功能，只适合小型超市使用。由于自己初次独立设计开发数据库系统，能力非常有限，加上时间仓促，本系统毫无疑问有许多的不足之处。对于出现的以上问题，我们深表歉意，恳请老师批评指正。附录1、创建数据库create database

16、Supermarket_SM;2、创建基本表create table ShopAssistant(Snum char(10) primary key,Sname char(10) not null,Splace char(10),Swtime char(10),Sctime char(10),Ssalaary numeric(10,1),Sphone char(20)create table Administrator(Anum char(5) primary key,Aname char(15) not null,Awtime char(10),Actime char(10),Asalary

17、 numeric(10,1),Aphone char(25)create table Member(Mnum char(15) primary key,Mname char(15),Mpassword char(6) not null,MregisterTime date,Mexpense numeric(10,2)create table GoodsShelf(GSnum char(10) primary key,Anum char(5) foreign key references Administrator(Anum), GSname char(15),)create table Goo

18、ds(Gnum char(10) primary key,GSnum char(10) foreign key references GoodsShelf(GSnum),Gbarcode char(20),Gname char(20),Gprice numeric(5,2),GSstock smallint,GSlimit smallint,Gproducetime date,Gtime int,Gsupplier char(50)create table BillSell(Bnum char(15) primary key,Snum char(10) foreign key referenc

19、es ShopAssistant(Snum),Mnum char(15) foreign key references Member(Mnum),Bdate smalldatetime,Bdiscount char(2)create table SellGoods(Bnum char(15),Gnum char(10),Bquantity smallint,primary key(Gnum,Bnum),foreign key (Gnum) references Goods(Gnum),foreign key (Bnum) references BillSell(Bnum)create tabl

20、e ReturnGoods(Rnum char(10) primary key,Gnum char(10) foreign key references Goods(Gnum),Rquantity smallint)create table PrintBill(Bnum char(15),Rnum char(10),Snum char(10),Rdata smalldatetime,primary key (Bnum,Rnum,Snum),foreign key (Bnum) references BillSell(Bnum), foreign key (Rnum) references Re

21、turnGoods(Rnum), foreign key (Snum) references ShopAssistant(Snum)3、创建索引（1）对于基本表 ShopAssistant （营业员） , 由于要经常对属性列 Snum查询从而获得某个营业员的完整信息， 并且很少对其更新， 因此可以给属性列 Snum建一个聚簇索引。其相应 SQL语句为：create clustered index dex_Snum on ShopAssistant(Snum);（2）同理对于基本表Member（会员） , 由于要经常对属性列Mnum查询从而获得某个会员的完整信息，并且很少对其更新，因此可以给属性

22、列Mnum建一个聚簇索引。其相应 SQL语句为：create clustered index dex_Munm on Member(Mnum);（3）对于 Adminastrator （管理员），可在其属性列 Anum上建立唯一性索引，索引值按降序排列。其相应 SQL语句为：create unique index dex_Anum on Administrator(Anum desc);（4）同样对于基本表 GoodsShelf （货架）、Goods（商品），可在其主属性上建立唯一性索引。其相应 SQL语句为：create unique index dex_GSnum on GoodsShel

23、f(GSnum); create unique index dex_Gnum on Goods(Gnum);4、创建视图相应 SQL语句为：create view V_Salesmanasselect Snum,Swtime,Sctime,Ssalaary,Sphonefrom ShopAssistantcreate view V_memberasselect Mnum,Mname,Mexpensefrom Membercreate view V_GoodsPriceasselect Gname,Gpricefrom Goodscreate view V_GoodsStoreasselect

24、Gname,GSnamefrom Goods,GoodsShelfwhere Goods.GSnum=GoodsShelf.GSnumcreate view V_GoodsLackasselect Gname,GSstock,GSlimitfrom Goodswhere GSstock<=GSlimitcreate view V_GoodsShelfasselect GSname,Gnamefrom GoodsShelf,Goodswhere GoodsShelf.GSnum=Goods.GSnumcreate view V_Workasselect ShopAssistant.Swti

25、me,ShopAssistant.Sname,Administrator.Aname from ShopAssistant,Administratorwhere ShopAssistant.Swtime=Administrator.Awtimecreate view V_Phone(name,num,phone)as(select Sname,Snum,Sphonefrom ShopAssistant)union(select Aname,Anum,Aphonefrom Administrator)create view V_Salary(name,num,salary)as(select S

26、name,Snum,Ssalaaryfrom ShopAssistant)union(select Aname,Anum,Asalaryfrom Administrator)create view V_GoodsEndasselect Gname,Gproducetime,Gtime,DateAdd(DAY,Gtime,Gproducetime)EndTime from Goodscreate view V_GoodsUnloadasselect Gname,GoodsShelf.GSname,DateAdd(DAY,Gtime,Gproducetime)EndTime from Goods,

27、GoodsShelfwhere DateAdd(DAY,Gtime,Gproducetime)<=GETDATE()and Goods.GSnum=GoodsShelf.GSnumcreate view v_BillExpenseasselect Bnum,Gname,Gprice,Bquantity,(Bquantity*Gprice)TotalExpense from SellGoods,Goodswhere SellGoods.Gnum=Goods.Gnum5、创建触发器（1）当打印一销售发票时， 即删除数据库中刚建立的销售发票时，相应商品在货架上的现有存量减少，相应会员总消费额增

28、加。其相应 SQL语句为：create trigger t1 on v_BillExpenseINSTEAD OF deleteasdeclare Quantity int,Gname char(10),TotalExpense float-Bnum char(10)select Quantity=Bquantity from deletedselect Gname=Gname from deleted-select TotalExpense=TotalExpense from deleted-select Bnum=Bnum from deletedupdate Goodsset GSsto

29、ck=GSstock-Quantitywhere Goods.Gname=Gname-update Member-set Mexpense=Mexpense+TotalExpense-where Bnum=SellBill.Bnum-and SellBill.Mnum=Mnum（2）当从过期商品中删除某记录时，相应商品现有存量减少.其相应 SQL语句为：create trigger t2 on V_GoodsUnloadINSTEAD OF deleteasdeclare Gname char(10)select Gname=Gname from deleteddelete from Good

30、swhere Gname=Gname（3）打印一退货发票， 即删除退货单中的记录， 表示 R商品重新返回原货架存储（假设脱货商品不影响第二次销售） ，货架商品现有存量增加 .其相应 SQL语句为：create trigger t3 on ReturnGoodsafter deleteasdeclare Gnum char(10),Rquantity intselect Gnum=Gnum from deletedselect Rquantity from deletedupdate Goodsset GSstock=GSstock+Rquantitywhere Gnum=Gnum6、 建立储存

31、过程（1）计算某个销售单上，每种商品的累计销售额（即输出每种商品名称，件数，单价，费用小计）。其相应 SQL语句为：create procedure p_TotalExpense(BS_Bnum varchar,Bnum varchar output,Gname varchar output,Bquantity int output,Gprice numeric output,TotalExpense numeric output)asselectBnum=Bnum,Gname=Gname,Bquantity=Bquantity,Gprice=Gprice,TotalExpense=(Bqua

32、ntity*Gprice)from v_BillExpensewhere Bnum=BS_Bnum其验证语句为：DeclareT_Bnum varchar,T_Gname varchar,T_Bquantity int,T_Gprice varchar,T_TotalExpense varcharExecute p_TotalExpense 'GS-0000004',T_Bnum output,T_Gname output,T_Bquantity output,T_Gprice output,T_TotalExpense outputPrint T_Bnum+' 

33、9;+T_Gname+' '+T_Bquantity+' '+T_Gprice+' '+T_TotalExpense（2）计算某个销售单上，顾客消费商品总数量和总消费额。其相应 SQL语句为：create procedure p_BillExpenseBS_Bnum varcharasselect Bnum,sum(Bquantity)TotalQuantity,sum(TotalExpense)TotalExpense from v_BillExpensegroup by Bnumhaving Bnum=BS_Bnum（3）查询某件商品价格。其相应 SQL语句为：create pro