超市销售数据库

1、超市销售数据库数据库系统课程设计题目超市销售管理数据库设计院系信息技术与工程学院专业计算机科学与技术姓名学号班级名称指导教师成绩2016年12月 15日1销售处理货架缺货销存货架售量警补排管告货行理图 1-3销售处理结构图二、概念模型1、分 E-R 图建立根据分解第二层数据流程图可分别得到三个分E-R 图。姓名累计注册时间消 费 额卡 号会 员密 码编 号编号1条 形 码姓名获得时 间数 量名 称收 银 台m位 置1nm销售n价 格营业员销售单商品上班时间打印商品生产日期下班时间保 质 期联系编号消费额月 薪发 货 商电话商 品折扣编 号图4-1收银业务模块 E-R图1发票编号消费额折扣打印时

2、间退货单号购物发票1编号商品编号m打印退货单姓名1退货数量营业员收 银 台位 置退费额上班时间下班时间月薪联系电话图 4-2 退货处理模块E-R图管理员号条 形 码现有存量货架编号姓名商品编号联系电话商品m1货架m1存放管理管理员商品名称上班时间价 格下班时间最低存量月薪图 4-3 销售处理模块E-R图2、全局 / 整体 E-R 图由于在做局部 ER 图时，只考虑了局部功能模块，这样会导致各个 ER图之间有很多不一致的地方，造成在合并各分 E R 图生成全局 E R图时有很多冲突。通过仔细分析各个分 E-R 图之间的联系， 消除冗余，消除冲突，最终成功生成全局 E-R 图，如下图所示。2会 员

3、1打印时间销售数量现有存量存量底线获得m营业员1mmnm存放1打印销售单销售商品商品货架11m打印管理m打印时间1退货单管理员图 5 系统总 E-R图三、关系数据模型1、关系模式建立关系模型由 E R图转换而来，实际上就是要将实体、 实体的属性和实体之间的联系明确表示出来，这种转换一般遵循如下规则：一个实体型转换为一个关系模式。实体的属性就是关系的属性，实体的码就是关系的码。此数据库系统包括营业员、会员、管理员、商品、货架、销售单、销售商品、退货单、打印多个关系模式：营业员 (营业员号，姓名，收银台位置，上班时间，下班时间，月薪，联系电话 ); 会员 (会员卡号，会员姓名，卡密码，注册时间，累

4、计消费 );商品（商品编号，货架编号，条形码，商品名称，商品价格，现有存量，存量底线，生产日期，保质期，供货商） ；销售单（销售单编号，营业员编号，会员卡号，打印时间，有无折扣） ；销售商品（销售单号，商品编号，销售数量） ；货架（货架编号，管理员号，货架名称）；管理员（管理员号，姓名，联系电话，上班时间，下班时间，月薪） ；退货单（退货单号，商品编号，退货数量） ；打印（退货单号，销售单号，营业员号，打印时间）；2、用户子模式建立用户子模式可通过建立视图来表示。视图是虚表，是从一个或几个基本表（或视图）中导出的表， 在系统的数据字典中仅存放了视图的定义， 不存放视图对应的数据。定义视图可以简

5、化应用程序、可以实现一定的权限控制。为了满足用户需求及方便后期数据库实施阶段的设计，此系统设计了如下视图。各视图定义：商品保质期（商品名称，生产日期，保质期，过期日期）下架商品（商品名称，货架名称，过期日期）3营业员基本信息（营业员号，上班时间，下班时间，月薪，联系电话）会员总消费情况（卡号，姓名，累计消费）商品价格（商品名称，价格）商品存放（商品名称，货架名称）缺货商品（商品名称，现有存量，底线存量）货架信息（货架名称，存放商品名）上班情况（上班时间，营业员姓名，管理员姓名）工作人员联系方式（姓名，编号，联系电话）工作人员工资信息（姓名，编号，工资）四、数据库物理设计此数据库系统建立的索引如

6、下所述。（1）对于基本表 ShopAssistant （营业员） , 由于要经常对属性列Snum查询从而获得某个营业员的完整信息，并且很少对其更新，因此可以给属性列 Snum建一个聚簇索引。（2）同理对于基本表 Member （会员） ,由于要经常对属性列 Mnum 查询从而获得某个会员的完整信息， 并且很少对其更新， 因此可以给属性列 Mnum 建一个聚簇索引。（3）对于 Adminastrator （管理员），可在其属性列 Anum 上建立唯一性索引，索引值按降序排列。（4）同样对于基本表 GoodsShelf（货架）、Goods（商品），可在其主属性上建立唯一性索引。（5）对于基本表 B

7、illSell （销售单）、SellGoods（销售商品）、 ReturnGoods （退货单）、 PrintBill （打印），由于其属性值经常发生变化，权衡系统为维护索引付出的代价，可考虑不建立索引。五、数据库实施与测试数据库实施与测试阶段主要内容包括数据库实施和测试两个部分。1、 数据库实施1.1 数据库及数据库对象建立主要包括：数据库、基本表、视图、索引、触发器以及存储过程。相应T-SQL 和PL/SQL 语句详见附录 .1.2 数据入库对各个数据表成功要录入了50 条左右的测试记录，测试结果满足设计要求。2、 数据库测试数据库测试阶段主要内容是对建立的数据库及数据库对象进行测试，对测

8、试结果进行分析，是否满足设计要求。六、总结通过数据库课程设计实习，对数据库知识有了更广泛的了解，在数据库的应用方面有了很大的收获。4（2）加深了对数据库系统相关知识和 SQL SERVER 2008数据库相关功能的理解。以前只停留于记忆书本上关于数据库系统的理论知识，没有切身实地的实践过，而通过这次实习我再次加深了对数据库相关功能的理解与应用。（3）进一步掌握相关的SQL语句。开发设计超市销售管理系统的过程中牵涉到相当多基本表的建立，视图、索引以及存储过程的设计，这让我对这些 SQL语句操作更熟练了。（4）熟悉了对项目开发的大致过程。这次系统开发， 我更加明白体验到了数据库系统开发的过程，包括

9、系统需求分析、概念设计、逻辑设计、物理设计，再到数据库实施、系统的测试和调试，对项目（系统）开发的大致流程有了一定的了解，为以后的系统的开发打下了良好的基础。同样在在这次实习中，我看到了自己的基础知识的薄弱性，更体验到了基础知识的重要性。比如说对具体的SQL语句还不是很熟悉，在画E-R 图、设计带输出变量的存储过程以及建立相关索引（聚簇索引）时感到有些棘手。遇到问题不可怕，其实能遇到问题是好事，它能让我在解决问题时学到更多的新知识，更能增加我的自信。和传统管理模式相比较，使用本系统，毫无疑问会大大提高超市的运作效率，辅助提高超市的决策水平，管理水平，为降低经营成本，提高效益，减少差错，节省人力

10、，减少顾客购物时间，增加客流量，提高顾客满意度，增强超市扩张能力，都能提供有效的技术保障。但超市管理系统涉及范围宽，要解决的问题多，功能复杂，实现困难，本系统只能做出其中的销售管理部分功能，只适合小型超市使用。由于自己初次独立设计开发数据库系统，能力非常有限，加上时间仓促，本系统毫无疑问有许多的不足之处。对于出现的以上问题，我们深表歉意，恳请老师批评指正。5附录1、创建数据库create database Supermarket_SM;2、创建基本表create table ShopAssistant(Snum char(10) primary key,Sname char(10) not n

11、ull,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),Mpas

12、sword 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 c

13、har(20),Gname char(20),Gprice numeric(5,2),6GSstock 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,B

14、discount 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),Rquan

15、tity 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

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

17、），可在其属性列 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_Salesmanasse

18、lect Snum,Swtime,Sctime,Ssalaary,Sphonefrom ShopAssistantcreate view V_memberasselect Mnum,Mname,Mexpensefrom Membercreate view V_GoodsPriceasselect Gname,Gpricefrom Goodscreate view V_GoodsStoreasselect Gname,GSnamefrom Goods,GoodsShelfwhere Goods.GSnum=GoodsShelf.GSnumcreate view V_GoodsLackassele

19、ct Gname,GSstock,GSlimitfrom Goodswhere GSstock=GSlimitcreate view V_GoodsShelf8asselect GSname,Gnamefrom GoodsShelf,Goodswhere GoodsShelf.GSnum=Goods.GSnumcreate view V_Workasselect ShopAssistant.Swtime,ShopAssistant.Sname,Administrator.Aname from ShopAssistant,Administratorwhere ShopAssistant.Swti

20、me=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 Sname,Snum,Ssalaaryfrom ShopAssistant)union(select Aname,Anum,Asalaryfrom Administrator)create view

21、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,GoodsShelfwhere DateAdd(DAY,Gtime,Gproducetime)=GETDATE()and Goods.GSnum=GoodsShelf.GSnumcreate vie

22、w v_BillExpenseasselect Bnum,Gname,Gprice,Bquantity,(Bquantity*Gprice)TotalExpense from SellGoods,Goodswhere SellGoods.Gnum=Goods.Gnum5、创建触发器91）当打印一张销售发票时，即删除数据库中刚建立的销售发票时，相应商品在货架上的现有存量减少，相应会员总消费额增加。其相应 SQL语句为：create trigger t1 on v_BillExpenseINSTEAD OF deleteasdeclare Quantity int,Gname char(10),T

23、otalExpense float-Bnum char(10)select Quantity=Bquantity from deletedselect Gname=Gname from deleted-select TotalExpense=TotalExpense from deleted -select Bnum=Bnum from deletedupdate Goodsset GSstock=GSstock-Quantitywhere Goods.Gname=Gname-update Member-set Mexpense=Mexpense+TotalExpense-where Bnum

24、=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 Goodswhere Gname=Gname（3）打印一张退货发票，即删除退货单中的记录，表示 R 商品重新返回原货架存储（假设脱货商品不影响第二次销售） ，货架商品现有存量增加 .其相应 SQL语句为：cr

25、eate trigger t3 on ReturnGoodsafter deleteasdeclare Gnum char(10),Rquantity intselect Gnum=Gnum from deletedselect Rquantity from deletedupdate Goodsset GSstock=GSstock+Rquantitywhere Gnum=Gnum6、 建立储存过程（1）计算某个销售单上，每种商品的累计销售额（即输出每种商品名称，件数，10单价，费用小计）。其相应 SQL语句为：create procedure p_TotalExpense(BS_Bnum

26、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=(Bquantity*Gprice)from v_BillExpensewhere Bnum=BS_Bnum其验证语句为：DeclareT_Bnum varchar,T_Gname varchar,T_Bq

27、uantity 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 output Print T_Bnum+ +T_Gname+ +T_Bquantity+ +T_Gprice+ +T_TotalExpense（2）计算某个销售单上，顾客消费商品总数量和总消费额。其相应 SQL语句为：create procedure p_BillExpe

28、nseBS_Bnum varcharasselect Bnum,sum(Bquantity)TotalQuantity,sum(TotalExpense)TotalExpense from v_BillExpensegroup by Bnumhaving Bnum=BS_Bnum（3）查询某件商品价格。其相应 SQL语句为：create procedure p_priceGname char(10)asselect Gpricefrom V_GoodsPricewhere Gname=Gname（4）查询某件商品现有数量。11其相应 SQL语句为：create procedure p_GquantityGname char(10)asselect GSstockfro