超市营销系统数据库实训.doc

一、系统需求规格说明1项目概述 超市商品销售系统为展示，明确所要开发的软件应具有的功能、性能，使系统分析人员及软件开发人员能清楚地了解用户的需求，并在此基础上进一步提出概要设计说明书和完成后续设计与开发工作。2主要任务与性能要求 主要任务：系统的E-R图和相关的SQl server 建立性能要求：保证数据的正确与完整二系统设计1引言名称：超市商品销售系统 目的：让用户结合系统给方便应用2参考资料 1. 王珊 萨师煊，数据库系统概论（第四版），高等教育出版社，2006年5月 2.赵杰等SQL Server数据库管理设计与实现教程 清华大学出版社 2004年3月3系统任务描述 顾客购买商品后应该打印详细购物清单，清单中内容有购买日期、商品编号、商品名称、单价、数量、合计金额，清单最后还要有现金和找零。顾客分为普通和会员2种，会员购物时需提供会员卡号，结帐后将根据购物金额按照1元1分进行积分的累积。一定时期可进行积分换购。此外，本系统应该实现库存查询、销售金额统计及报表打印。3.研究的网站（1）凡客： 主要属性：颜色、尺码、数量 （2）京东商城： 主要属性：颜色、版本、数量 （3）淘宝商城： 主要属性：配送地点、付费方式、数量、颜色总结：由上面三个网站分析可知购买商品时候的主要属性各不相同，因商品本身而变，但是有些属性是必须的了，例如数量、颜色、付费方式等是在网上购买东西时候大部分都需要的属性4.数据库概念结构设计（1）E-R图： （2）关系图： 5数据库逻辑结构设计商品（商品编号、类别、名称、型号、进价、库存数量、销售价、售出数量）订单（订单号，购买日期、合计金额、会员卡号）销售明细（订单号，商品编号、单价、数量）会员（会员卡号，姓名，积分，联系方式）工作人员（员工编号，姓名，年龄，性别，电话） goods(gno,gclass, gname gtype gin_price gout_price gin_number gout_number) DD(DDno DDdate DDHYno DDcount HYno) XSMX(DDno gno XSMXdj XSMXsl) HY(HYno HYname HYlxfs HYjf) People(pno pname page psex ptele) 商品表字段名称数据类型可否为空约束条件说明gnoChar(6)NOT NULL主键商品编号gclassChar(7)NOT NULL无商品种类gnamechar(10) NOT NULL无商品名称gtypeChar(7)NOT NULL无型号Gin_priceint不小于零进价Gout_priceint不小于零卖出价格Gin_numberint不小于零库存数量Gout_numberint不小于零销售数量pnoChar(7)NOT NULL外键管理人员编号订单表字段名称数据类型可否为空约束条件说明DDnoChar(20)NOT NULL主键订单编号DDdatedatetime无订单日期DDHYnochar(20)NOT NULL外键会员编DDcountChar(20)不小于零总金额销售明细表字段名称数据类型可否为空约束条件说明DDnoChar(20)NOT NULL外键订单编号gnoChar(6)NOT NULL外键商品编号XSMXdjchar(20) NOT NULL单价XSMXslint不小于零数量会员表字段名称数据类型可否为空约束条件说明HYnochar(20NOT NULL主键会员卡号HYnamechar(20)NOT NULL无姓名HYlxfschar(20) NOT NULL无联系方式HYjfchar(20)不小于零积分工作人员表：字段名称数据类型可否为空约束条件说明pnochar(7)NOT NULL主键员工编号pnameCHAR(10)NOT NULL无姓名pagechar(3) NOT NULL无年龄psexchar(2)NOT NULL男或女性别pteleCHAR(10)NOT NULL无电话6数据库物理结构设计 商品表（商品编号、类别、名称、型号、进价、库存数量、销售价、售出数量）CREATE TABLE goods(gno CHAR(6) PRIMARY KEY,gclass CHAR(7) NOT NULL,gname CHAR(10) NOT NULL,gtype CHAR(7) NOT NULL,gin_price int CHECK (gin_price= 0 ),gout_price int CHECK (gout_price= 0 ),gin_number int CHECK (gin_number= 0 ),gout_number int CHECK (gout_number= 0 ),pno char(7) NOT NULL,FOREIGN KEY (pno) REFERENCES people(pno),)插入商品数据 订单表（订单号，购买日期）create table DD(DDno char(20) primary key, DDdate datetime , DDHYno char(20), DDcount char(20) check(DDcount=0), foreign key(DDHYno) references HY(HYno), );插入订单数据 销售明细表（订单号，商品编号、单价、数量）create table XSMX(DDno char(20) not null, gno char(6) not null, XSMXdj char(20) not null, XSMXsl int check(XSMXsl=0), primary key(DDno,gno), foreign key(DDno) references DD(DDno), foreign key(gno) references goods(gno), );插入销售明细数据 会员表（会员卡号，姓名，积分，联系方式）create table HY(HYno char(20) primary key, HYname char(20) not null, HYlxfs char(20) not null, HYjf char(20), check (HYjf=0),);插入会员数据 工作人员表（员工编号，姓名，年龄，性别，电话）CREATE TABLE people(pno char(7) PRIMARY KEY,pname CHAR(10) NOT NULL,page char(3) NOT NULL,psex char(2) NOT NULL,ptele CHAR(10) NOT NULL)插入人员数据7主要功能实现 1查询处理 （1） 查询goods表，将商品按商品号降序排列 select *from goodsorder by gno desc （2） 查询pelple表中年龄大约22的员工号select pno , page from people where page22 （3）连接查询查询管理001号商品的工作人员编号、性别、年龄、姓名select people.pno , page,psex,pnamefrom people join goods on goods.pno=people.pnowhere gno=001（4）嵌套查询 查询管理编号为002的工作人员姓名，工作编号，性别select pno,pname,psexfrom peoplewhere pno in(select pno from goodswhere gno=002（5）模糊查询 查询名字中有张字的员工姓名、性别、工作编号、年龄SELECT pname, pno,psex,pagefrom peoplewhere pname like 张%（6）查询订单号为3303的客户订购的商品编号，商品名称，商品数量，商品单价select DDno,XSMX.gno ,XSMXsl,XSMXdj,gnamefrom XSMX join goods on XSMX.gno=goods.gnowhere DDno=33032.创建视图（1） 商品号，商品型号，卖出价视图 create view A_1asselect gno,gtype,gout_pricefrom goods（2）工作人员编号，工作人员性别create view A_2AS select pno,psexfrom people3创建存储过程(1)创建金额统计的存储过程 create procedure 金额统计(st_gno char(6),money int output)asDECLARE out_price intDECLARE out_number intSELECTout_price=gout_price,out_number=gout_number,money=out_price*out_numberFROM goodswhere gno=st_gno运行销售金额declare st_gno char(6),money intselect st_gno=005,money=12000exec 金额统计st_gno,money outputselect *,gno=st_gno,销售金额=moneyfrom goodswhere gno=st_gno (2)创建打印订单的存储过程 create procedure 打印订单(st_DDno char(6)asSELECTDD.DDno,DDdate,XSMXdj,XSMXsl,XSMXxj,XSMXzl,XSMXhjje,XSMX.gno,gclass,gname,gtypeFROM goods join XSMX on goods.gno=XSMX.gno join DD on XSMX.DDno=DD.DDnowhere DD.DDno=st_DDno 运行打印订单 declare st_DDno char(6)select st_DDno=3301exec 打印订单st_DDno(3)创建视图创建查询库存的存储过程create procedure 查询库存(st_gno char(6),number int output)asDECLARE in_number intDECLARE out_number intSELECTin_number=gin_number,out_number=gout_number,number=in_number-out_numberFROM goodswhere gno=st_gno运行查询库存DECLARE number intDECLARE st_gno char(6)select st_gno=0001exec 查询库存st_gno,number outputselect gno=st_gno,当前库存=number4创建函数(1)金额统计函数CREATE FUNCTION fn_f1(st_gno char(6) RETURNS intAS BEGIN DECLARE out_price intDECLARE out_number intDECLARE money intSELECTout_price=gout_price,out_number=gout_number,money=out_price*out_numberFROM goodswhere gno=st_gno RETURN money END(2)运行统计函数declare st_gno char(6)set st_gno=0003SELECT dbo.fn_f1(st_gno)三收获和体会通过这次数据库的上机操作，我学到了很多东西，通过真实的上机操作，我明白了许多课上学习所学