超市销售数据库精编38049.pdf

超市销售数据库精编 Jenny was compiled in January 2021 数据库系统课程设计 题 目 超市销售管理数据库设计 院 系 信息技术与工程学院 专 业 计算机科学与技术 姓 名 学 号 班级名称 指导教师 成绩 2016 年 12 月 15 日 目 录 前言 超市销售管理系统是为了解决超市销售管理业务方面面临的复杂业务流程和繁琐数据处理等问题，提高超市运营效率，提高经济效益，提高市场竞争力而研究开发的一款数据库软件。在开发平台 windows 上，采用了 SQL 数据库程序设计语言予以实现；可访问 SQL SEREVER 2005 数据库，具有商品录入、商品信息查询、交易额计算、会员打折、打印销售发票、退货、打印退货发票、记录交易细节、货架管理、商品过期警告、缺货警告等方面的功能，为超市管理提供了有效的技术保障，并且可直接作为开发整个超市管理系统时已完成的一部分模块。一、系统需求分析 1、处理对象 系统要处理的基本对象包括营业员基本信息、会员基本信息、管理员基本信息、商品基本信息、货架基本信息、销售单基本信息、退货单基本信息等多个对象。各个基本对象包含信息如下所示：营业员(营业员号，姓名，收银台位置，上班时间，下班时间，月薪，联系电话);会员(会员卡号，会员姓名，卡密码，注册时间，累计消费);商品（商品编号，货架编号，条形码，商品名称，商品价格，现有存量，存量底线，生产日期，保质期，供货商）；销售单（销售单编号，营业员编号，会员卡号，打印时间，有无折扣）；销售商品（销售单号，商品编号，销售数量）；货架（货架编号，管理员号，货架名称）；管理员（管理员号，姓名，联系电话，上班时间，下班时间，月薪）；退货单（退货单号，商品编号，退货数量）；打印（退货单号，销售单号，营业员号，打印时间）。2、系统功能及信息处理 本中小型超市销售管理系统大体上包含三个模块，为前台收银业务处理，前台退货业务处理以及后台销售处理功能模块，具体如下所述。收银业务 通过扫描条形码或者直接输入商品名称（对于同类多件商品采用一次录入加数量的方式），计算本次交易的总金额，同时打印销售发票给顾客（包括交易的流水账号、每类商品的商品名、数量、该类商品的总金额、交易的时间、负责本次收银的员工号等信息）。如果顾客是本店会员并持有本人会员卡，则在交易前先扫描会员卡，对此会员一定优惠，并将所购物品的总金额累计到该会员的总消费金额中，记录好会员信息。退货处理 顾客持有销售发票到收银台找收银员退货，若没有相应销售发票不予以退货。通过扫描销售发票计算本次退货处理的退货额，并打印出退货发票给顾客，系统记录好退货信息。销售处理 作为超市后台管理部分，此系统应该具有功能包括当打印销售发票或退货发票时自动增减货架上商品数量，当货架商品不足时自动生成缺货警告信息给管理员，系统能够生成销售排行榜供管理员查看。3、安全性和完整性要求 安全性先通过视图机制，不同的用户只能访问系统授权的视图；再通过用户授权机制，通过用户登陆来识别用户级别，根据这个级别来分配用户权限，达到数据更高层次的安全保密功能。完整性要求用于确保各处理对象的主属性取值唯一并且一般不能为空；各处理对象的参照属性取值必须是来自于被参照属性。可以通过用户自定义完整性（符合实际要求）来确保数据符合更高规范要求。详细完整性要求见于系统的逻辑设计阶段。4、系统结构图 通过对中小型超市销售管理业务及其功能方面的分析，构造出超市管理系统的总体结构图如下：中小型超市销售管理系统人员管理库存管理进货管理销售处理收银业务后台管理前台销售退货处理 由于本系统为销售管理系统，只是超市管理系统的一部分，因此只实现了收营业务、退货处理和销售处理部分的功能。对这三个处理模块进一步细化得到如下分结构图：收银业务打印交易清单给会员优惠交易额计算 退货业务输出退费单退费额计算 货架存量管理销售排行缺货警告销售处理货架补货 二、概念模型 图 1 超市管理总体结构图 图 1-1 收银业务结构图 图 1-2 退货处理结构图 图 1-3 销售处理结构图 1、分 E-R 图建立 根据分解第二层数据流程图可分别得到三个分 E-R 图。编号折扣消费额商 品编 号联系电话月 薪姓名编号获得营业员销售单打印商品会 员销售商品n1mmn1时 间注 册 时 间累 计消 费 额姓 名卡 号价 格名 称编 号下 班 时 间上 班 时 间收 银 台位 置密 码发 货 商保 质 期生 产 日 期条 形 码数 量 退费额退货单号打印时间退货单打印购物发票营业员发票编号折扣消费额联系电话月 薪姓名编号下 班 时 间上 班 时 间收 银 台位 置11m退 货 数 量商 品 编 号 图4-2 退货处理模块E-R图 图4-1 收银业务模块E-R图 现有存量货架编号货架存放管理员商品管理1m1m联系电话月薪下班时间上班时间姓名管理员号商品名称商品编号最低存量条 形 码价 格 2、全局/整体 E-R 图 由于在做局部 ER 图时，只考虑了局部功能模块，这样会导致各个 ER 图之间有很多不一致的地方，造成在合并各分 ER 图生成全局 ER 图时有很多冲突。通过仔细分析各个分 E-R 图之间的联系，消除冗余，消除冲突，最终成功生成全局 E-R图，如下图所示。营业员销售单打印打印时间会 员退货单获得打印销售商品货架存放管理员商品管理1mmn111m1mm1m打 印 时 间销 售 数 量现有存量存量底线 三、关系数据模型 1、关系模式建立 图5 系统总E-R图 图4-3 销售处理模块E-R图 关系模型由 ER 图转换而来，实际上就是要将实体、实体的属性和实体之间的联系明确表示出来，这种转换一般遵循如下规则：一个实体型转换为一个关系模式。实体的属性就是关系的属性，实体的码就是关系的码。此数据库系统包括营业员、会员、管理员、商品、货架、销售单、销售商品、退货单、打印多个关系模式：营业员(营业员号，姓名，收银台位置，上班时间，下班时间，月薪，联系电话);会员(会员卡号，会员姓名，卡密码，注册时间，累计消费);商品（商品编号，货架编号，条形码，商品名称，商品价格，现有存量，存量底线，生产日期，保质期，供货商）；销售单（销售单编号，营业员编号，会员卡号，打印时间，有无折扣）；销售商品（销售单号，商品编号，销售数量）；货架（货架编号，管理员号，货架名称）；管理员（管理员号，姓名，联系电话，上班时间，下班时间，月薪）；退货单（退货单号，商品编号，退货数量）；打印（退货单号，销售单号，营业员号，打印时间）；2、用户子模式建立 用户子模式可通过建立视图来表示。视图是虚表，是从一个或几个基本表（或视图）中导出的表，在系统的数据字典中仅存放了视图的定义，不存放视图对应的数据。定义视图可以简化应用程序、可以实现一定的权限控制。为了满足用户需求及方便后期数据库实施阶段的设计，此系统设计了如下视图。各视图定义：商品保质期（商品名称，生产日期，保质期，过期日期）下架商品（商品名称，货架名称，过期日期）营业员基本信息（营业员号，上班时间，下班时间，月薪，联系电话）会员总消费情况（卡号，姓名，累计消费）商品价格（商品名称，价格）商品存放（商品名称，货架名称）缺货商品（商品名称，现有存量，底线存量）货架信息（货架名称，存放商品名）上班情况（上班时间，营业员姓名，管理员姓名）工作人员联系方式（姓名，编号，联系电话）工作人员工资信息（姓名，编号，工资）四、数据库物理设计 此数据库系统建立的索引如下所述。（1）对于基本表 ShopAssistant（营业员）,由于要经常对属性列 Snum 查询从而获得某个营业员的完整信息，并且很少对其更新，因此可以给属性列 Snum 建一个聚簇索引。（2）同理对于基本表 Member（会员）,由于要经常对属性列Mnum 查询从而获得某个会员的完整信息，并且很少对其更新，因此可以给属性列 Mnum 建一个聚簇索引。（3）对于 Adminastrator（管理员），可在其属性列 Anum上建立唯一性索引，索引值按降序排列。（4）同样对于基本表 GoodsShelf（货架）、Goods（商品），可在其主属性上建立唯一性索引。（5）对于基本表 BillSell（销售单）、SellGoods（销售商品）、ReturnGoods（退货单）、PrintBill（打印），由于其属性值经常发生变化，权衡系统为维护索引付出的代价，可考虑不建立索引。五、数据库实施与测试 数据库实施与测试阶段主要内容包括数据库实施和测试两个部分。1、数据库实施 数据库及数据库对象建立 主要包括：数据库、基本表、视图、索引、触发器以及存储过程。相应 T-SQL 和 PL/SQL 语句详见附录.数据入库 对各个数据表成功要录入了 50 条左右的测试记录，测试结果满足设计要求。2、数据库测试 数据库测试阶段主要内容是对建立的数据库及数据库对象进行测试，对测试结果进行分析，是否满足设计要求。六、总结 通过数据库课程设计实习，对数据库知识有了更广泛的了解，在数据库的应用方面有了很大的收获。（2）加深了对数据库系统相关知识和 SQL SERVER 2008 数据库相关功能的理解。以前只停留于记忆书本上关于数据库系统的理论知识，没有切身实地的实践过，而通过这次实习我再次加深了对数据库相关功能的理解与应用。（3）进一步掌握相关的 SQL 语句。开发设计超市销售管理系统的过程中牵涉到相当多基本表的建立，视图、索引以及存储过程的设计，这让我对这些 SQL 语句操作更熟练了。（4）熟悉了对项目开发的大致过程。这次系统开发，我更加明白体验到了数据库系统开发的过程，包括系统需求分析、概念设计、逻辑设计、物理设计，再到数据库实施、系统的测试和调试，对项目（系统）开发的大致流程有了一定的了解，为以后的系统的开发打下了良好的基础。同样在在这次实习中，我看到了自己的基础知识的薄弱性，更体验到了基础知识的重要性。比如说对具体的 SQL 语句还不是很熟悉，在画 E-R 图、设计带输出变量的存储过程以及建立相关索引（聚簇索引）时感到有些棘手。遇到问题不可怕，其实能遇到问题是好事，它能让我在解决问题时学到更多的新知识，更能增加我的自信。和传统管理模式相比较，使用本系统，毫无疑问会大大提高超市的运作效率，辅助提高超市的决策水平，管理水平，为降低经营成本，提高效益，减少差错，节省人力，减少顾客购物时间，增加客流量，提高顾客满意度，增强超市扩张能力，都能提供有效的技术保障。但超市管理系统涉及范围宽，要解决的问题多，功能复杂，实现困难，本系统只能做出其中的销售管理部分功能，只适合小型超市使用。由于自己初次独立设计开发数据库系统，能力非常有限，加上时间仓促，本系统毫无疑问有许多的不足之处。对于出现的以上问题，我们深表歉意，恳请老师批评指正。附录 1、创建数据库 create database 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 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 Goods(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 references 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 table 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 ReturnGoods(Rnum),foreign key(Snum)references ShopAssistant(Snum)3、创建索引（1）对于基本表 ShopAssistant（营业员）,由于要经常对属性列 Snum 查询从而获得某个营业员的完整信息，并且很少对其更新，因此可以给属性列 Snum 建一个聚簇索引。其相应 SQL 语句为：create clustered index dex_Snum on ShopAssistant(Snum);（2）同理对于基本表 Member（会员）,由于要经常对属性列 Mnum 查询从而获得某个会员的完整信息，并且很少对其更新，因此可以给属性列 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 GoodsShelf(GSnum);create unique index dex_Gnum on Goods(Gnum);4、创建视图 相应 SQL 语句为：create view V_Salesman as select Snum,Swtime,Sctime,Ssalaary,Sphone from ShopAssistant create view V_member as select Mnum,Mname,Mexpense from Member create view V_GoodsPrice as select Gname,Gprice from Goods create view V_GoodsStore as select Gname,GSname from Goods,GoodsShelf where=create view V_GoodsLack as select Gname,GSstock,GSlimit from Goods where GSstock=GSlimit create view V_GoodsShelf as select GSname,Gname from GoodsShelf,Goods where=create view V_Work as select,from ShopAssistant,Administrator where=create view V_Phone(name,num,phone)as(select Sname,Snum,Sphone from ShopAssistant)union(select Aname,Anum,Aphone from Administrator)create view V_Salary(name,num,salary)as(select Sname,Snum,Ssalaary from ShopAssistant)union(select Aname,Anum,Asalary from Administrator)create view V_GoodsEnd as select Gname,Gproducetime,Gtime,DateAdd(DAY,Gtime,Gproducetime)EndTime from Goods create view V_GoodsUnload as select Gname,DateAdd(DAY,Gtime,Gproducetime)EndTime from Goods,GoodsShelf where DateAdd(DAY,Gtime,Gproducetime)=GETDATE()and=create view v_BillExpense as select Bnum,Gname,Gprice,Bquantity,(Bquantity*Gprice)TotalExpense from SellGoods,Goods where=5、创建触发器（1）当打印一张销售发票时，即删除数据库中刚建立的销售发票时，相应商品在货架上的现有存量减少，相应会员总消费额增加。其相应 SQL 语句为：create trigger t1 on v_BillExpense INSTEAD OF delete as declare Quantity int,Gname char(10),TotalExpense float-Bnum char(10)select Quantity=Bquantity from deleted select Gname=Gname from deleted-select TotalExpense=TotalExpense from deleted-select Bnum=Bnum from deleted update Goods set GSstock=GSstock-Quantity where=Gname-update Member-set Mexpense=Mexpense+TotalExpense-where Bnum=-and=Mnum（2）当从过期商品名单中删除某记录时，相应商品现有存量减少.其相应 SQL 语句为：create trigger t2 on V_GoodsUnload INSTEAD OF delete as declare Gname char(10)select Gname=Gname from deleted delete from Goods where Gname=Gname（3）打印一张退货发票，即删除退货单中的记录，表示 R商品重新返回原货架存储（假设脱货商品不影响第二次销售），货架商品现有存量增加.其相应 SQL 语句为：create trigger t3 on ReturnGoods after delete as declare Gnum char(10),Rquantity int select Gnum=Gnum from deleted select Rquantity from deleted update Goods set GSstock=GSstock+Rquantity where Gnum=Gnum 6、建立储存过程（1）计算某个销售单上，每种商品的累计销售额（即输出每种商品名称，件数，单价，费用小计）。其相应 SQL 语句为：create procedure p_TotalExpense(BS_Bnum varchar,Bnum varchar output,Gname varchar output,Bquantity int output,Gprice numeric output,TotalExpense numeric output)as select Bnum=Bnum,Gname=Gname,Bquantity=Bquantity,Gprice=Gprice,TotalExpense=(Bquantity*Gprice)from v_BillExpense where Bnum=BS_Bnum 其验证语句为：Declare T_Bnum varchar,T_Gname varchar,T_Bquantity int,T_Gprice varchar,T_TotalExpense varchar Execute p_TotalExpense GS-0000004,T_Bnum output,T_Gname output,T_Bquantity output,T_Gprice output,T_TotalExpense output Print T_Bnum+T_Gname+T_Bquantity+T_Gprice+T_TotalExpense（2）计算某个销售单上，顾客消费商品总数量和总消费额。其相应 SQL 语句为：create procedure p_BillExpense BS_Bnum varchar as select Bnum,sum(Bquantity)TotalQuantity,sum(TotalExpense)TotalExpense from v_BillExpense group by Bnum having Bnum=BS_Bnum（3）查询某件商品价格。其相应 SQL 语句为：create procedure p_price Gname char(10)as select Gprice from V_GoodsPrice where Gname=Gname（4）查询某件商品现有数量。其相应 SQL 语句为：create procedure p_Gquantity Gname char(10)as select GSstock from Goods where Gname=Gname（5）查询强制下架商品名单中商品名称和过期日期。其相应 SQL 语句为：create procedure p_GoodsUnload as select Gname 商品名,EndTime 过期日期 from V_GoodsUnload（6）生成缺货单。其相应 SQL 语句为：create procedure p_GoodsLack as select 商品名,现有存量,GSname 货架名 from V_GoodsLack,Goods,GoodsShelf where=and=（7）查询指定某个月内的销售情况。其相应 SQL 语句为：create procedure p_RankingList A_month date=getdate as select Gname,Bquantity,TotalExpense from v_BillExpense,BillSell where=and DATEPART(YEAR,A_month)=DATEPART(YEAR,and DATEPART(MONTH,A_month)=DATEPART(MONTH,其验证语句为：Execute p_RankingList A_month=2010-5-10 18:18;（8）查询指定工作人员的工资。其相应 SQL 语句为：create procedure p_SalesmanSalary s_Snum char(10)as select Ssalaary from V_Salesman where Snum=s_Snum（9）在某段时间内的上班人员名单。其相应 SQL 语句为：create procedure p_TimeWork T_time char(10)as select*from V_Work where Swtime=T_time