2012实验报告库存管理.doc

南京理工大学数据库系统实验报告课 程：数据库系统 院 系：计算机学院班 级：11068401 成 绩： 学号： 姓名： 学号： 姓名： 指导教师： 李波 日期： 2013 年 1 月 8 日 目 录 1 数据库设计31.1 需求分析31.2 概念模式设计31.3 关系模式设计42 数据库编程52.1 表的创建62.2 数据的修改102.3 查询122.4 统计142.5 视图及索引173 小结181 数据库设计1.1 需求分析本节中对于库存管理系统的具体需求进行一个简要的说明。包括：问题的提出，系统的主要功能需求，系统的信息需求。 系统的功能至少包括： 基础信息管理设置并管理系统基础信息，包括仓库，商品类型，职工信息，供应商信息，客户信息等。 库存商品管理管理商品的编码、条码、名称、规格、类型、计量单位、货位、库存数量、单价、最低库存量、最高库存量。 进货管理输入并管理进货单：包括进货单编号、进货日期、供应商编码、经手人、商品编码、进货单价、进货数量。每张进货单可以进货多种商品。进货时修改对应商品的库存数量。 销售（出库）管理 输入并管理销售单：包括销售单编号、销售日期、客户编码、经手人、商品编码、销售单价、销售数量。每张销售单可以销售多种商品。销售时修改对应商品的库存数量。 查询统计 入库单查询 商品库存量查询 待采购商品查询（库存数量低于最低库存量） 销售日报 商品进销存一览表1.2 概念模式设计1.3 关系模式设计Product表的创建 属性名数据类型是否主键是否允许为空缺省值说明ProdidVarchar（20）YesNOT NULL产品编号P_TypeVarchar（10）Nonull类型NameVarchar(20)NoNOT NULL产品名称BarcodeVarchar(13)NoNOT NULL条码SpecVarchar（30）Nonull规格QuantityIntegerNoNot null0库存数量MaxquantIntegernonull0最高库存量MinquantIntegerNoNull0最低库存量ModuleVarchar(5)noNot null个计量单位G_PosVarchar（10）yesNull货位StoIdintegeryesNot null001仓库号PriceNumeric(6,1)noNot null0单价Customer表的创建属性名数据类型是否主键是否允许为空缺省值说明CnameVarchar（20）NoNull客户名CidVarchar（20）YesNot null客户编号Supplier表的创建属性名数据类型是否主键是否允许为空缺省值说明SnameVarchar（20）NoNull供应商名SidVarchar（20）YesNot null供应商编号Inorders表的创建属性名数据类型是否主键是否允许为空缺省值说明inOrderidVarchar（20）Yesnot Null进货订单编号ProdidVarchar（20）YesNot null商品编号SidVarchar（20）NoNot null供应商编号StoidVarchar（10）yesNot null仓库号G_posVarchar（5）yesNot null货位InDateDateNoNot null进货日期InQualityIntegerNoNot null0进货数量HanderVarchar（10）NoNot null经手人InPriceNumeric（6,1）noNot null进货单价Outorders表的创建属性名数据类型是否主键是否允许为空缺省值说明outOrderidintegerYesnot Null销售订单编号ProdidVarchar（20）YesNot null商品编号CidVarchar（20）NoNot null客户编号OutDateDateNoNot null销售日期StoidIntegeryesNot null001仓库号G_posVarchar（5）yesNot null货位outQuantyIntegerNoNot null0销售数量HanderVarchar（10）NoNot null经手人outPriceNumeric（6,1）noNot null销售单价Employees表的创建属性名数据类型是否主键是否允许为空缺省值说明EmidVarchar（20）Yes not Null员工编号NameVarchar（10）No Not null姓名AgeIntegerNoNot null年龄GenderVarchar（2）NoNot null性别Storage表的创建属性名数据类型是否主键是否允许为空缺省值说明StoIdVarchar（10）Yesnot Null仓库编号G_PosVarchar（10）nonull货位ProdidIntegerYesNot null商品编号2 数据库编程2.1 表的创建Product的创建CREATE TABLE PRODUCT(PRODID INT PRIMARY KEY,P_TYPE VARCHAR(10) NOT NULL,NAME VARCHAR(20),BARCODE VARCHAR(13) NOT NULL,SPEC VARCHAR(30),QUANTITY INT NOT NULL DEFAULT 0,MAXQUANT INT ,MINQUANT INT ,MODULE VARCHAR(5) DEFAULT 个,G_POS VARCHAR(10),STOID INT DEFAULT 001,PRICE NUMERIC(6,1)Employees的创建CREATE TABLE EMPLOYEES(EMID INT PRIMARY KEY,NAME VARCHAR(10),AGE INT,GENDER VARCHAR(2),PHONE INT,POSITION VARCHAR(10)Customers的创建CREATE TABLE CUSTOMER(CNAME VARCHAR(20),CID INT PRIMARY KEY)Supplier的创建CREATE TABLE SUPPLIER(SNAME VARCHAR(20),SID INT PRIMARY KEY)Inorders的创建CREATE TABLE INORDERS(INORDERID INT PRIMARY KEY,PRODID INT,SID INT ,G_POS VARCHAR(10) NOT NULL,STOID INT NOT NULL DEFAULT 001,INDATE DATE,INQUANTY INT DEFAULT 0,HANDER VARCHAR(10),INPRICE NUMERIC(6,1),FOREIGN KEY(PRODID)REFERENCES PRODUCT(PRODID) ON DELETE SET NULL ON UPDATE CASCADE,FOREIGN KEY (SID)REFERENCES SUPPLIER(SID)ON DELETE SET NULL ON UPDATE CASCADE)Outorders的创建CREATE TABLE OUTORDERS(OUTORDERID INT PRIMARY KEY,PRODID INT,CID INT,G_POS VARCHAR(10) NOT NULL ,STOID INT NOT NULL DEFAULT 001,OUTDATE DATE,OUTQUANTY INT DEFAULT 0,HANDER VARCHAR(10),OUTPRICE NUMERIC(6,1),FOREIGN KEY(PRODID)REFERENCES PRODUCT(PRODID)ON DELETE SET NULL ON UPDATE CASCADE,FOREIGN KEY (CID)REFERENCES CUSTOMER(CID)ON DELETE SET NULL ON UPDATE CASCADE)Storage的创建CREATE TABLE STORAGE(STOID INT,G_POS VARCHAR(10)DEFAULT 001;PRIMARY KEY(STOID,G_POS)添加一些测试数据：仓库INSERT INTO STOrAGE VALUES(1,001);INSERT INTO STOrAGE VALUES(1,002);INSERT INTO STOrAGE VALUES(2,003);INSERT INTO STOrAGE VALUES(2,004);INSERT INTO STOrAGE VALUES(3,005);INSERT INTO STOrAGE VALUES(3,001);INSERT INTO STOrAGE VALUES(3,002);INSERT INTO STOrAGE VALUES(3,112);INSERT INTO STOrAGE VALUES(3,120);INSERT INTO STOrAGE VALUES(2,121);INSERT INTO STOrAGE VALUES(2,110);INSERT INTO STOrAGE VALUES(1,111);INSERT INTO STOrAGE VALUES(1,211);商品INSERT INTO PRODUCT VALUES(KT10011110,家用电器,电脑,11068401,台式机,100,1000,20,台,001,1,15000); INSERT INTO PRODUCT VALUES(KT10011111,家用电器,电冰箱,11068402,箱式,500,2000,100,台,002,1,2000);INSERT INTO PRODUCT VALUES(KT10011112,家用电器,电冰箱,11068403,对开门,200,1000,50,台,003,2,11000);INSERT INTO PRODUCT VALUES(KT10011113,家用电器,空调,11068404,挂壁式,300,3000,100,台,004,2,2000); INSERT INTO PRODUCT VALUES(KT10011114,家用电器,空调,11068405,立式,400,1000,60,台,005,3,4000); INSERT INTO PRODUCT VALUES(KT10011115,食品,方便面,11068406,酸菜牛肉,1000,5000,500,台,001,3,100);INSERT INTO PRODUCT VALUES(KT10011116,食品,可乐,11068407,500ml,1000,2000,100,瓶,002,3,3);INSERT INTO PRODUCT VALUES(KT10011117,食品,饼干,11068408,500g,1000,2000,100,袋,112,3,5); INSERT INTO PRODUCT VALUES(KT10011118,日用品,洗发露,11068409,600ml,500,1000,100,瓶,120,3,15);INSERT INTO PRODUCT VALUES(KT10011119,日用品,牙膏,11068410,30g,10000,50000,1000,盒,121,2,5);INSERT INTO PRODUCT VALUES(KT10011120,日用品,牙刷,11068411,电动,4000,10000,2000,根,110,2,2);INSERT INTO PRODUCT VALUES(KT10011121,服装,毛衣,11068412,大号,2000,3000,20,件,111,1,150);INSERT INTO PRODUCT VALUES(KT10011122,鞋类,篮球鞋,11068413,Nike,100,500,10,双,211,1,500);职工INSERT INTO employees VALUES(1106840129,刘菲,24,女,12551225883);INSERT INTO employees VALUES(1106840136,成龙,53,男,12552211111);INSERT INTO employees VALUES(1106840137,张平,21,男,12342235323);INSERT INTO employees VALUES(1106840135,刘华,52,男;INSERT INTO employees VALUES(1106840138,张学,53,男;INSERT INTO employees VALUES(1106840139,张柏,34,女,12321312312);INSERT INTO employees VALUES(1106840134,黎明,53,男;供应商：INSERT INTO supplier VALUES(11068402,大宝);INSERT INTO supplier VALUES(11068401,星辰);INSERT INTO supplier VALUES(11068403,潘神);INSERT INTO supplier VALUES(11068404,苹果);INSERT INTO supplier VALUES(11068405,微软);客户：INSERT INTO customer VALUES(10086,移动);INSERT INTO customer VALUES(10087,联通);INSERT INTO customer VALUES(10088,电信);INSERT INTO customer VALUES(10089,联想);销售单INSERT INTO outorders VALUES (CK20121011029,KT10011117,10086,3,112,2012-10-21,900,1106840134,7);INSERT INTO outorders VALUES(CK20121011030,KT10011118,10087,3,120,2012-10-22,300,1106840135,18);INSERT INTO outorders VALUES(CK20121011031,KT10011119,10089,2,121,2012-10-23,5000,1106840136,8);INSERT INTO outorders VALUES(CK20121011031,KT10011120,10086,2,110,2012-10-25,3000,1106840137,4);INSERT INTO outorders VALUES(CK20121011032,KT10011113,10088,2,004,2012-10-08,100,1106840137,2500);INSERT INTO outorders VALUES(CK20121011022,KT10011116,10089,3,002,2012-10-08,500,1106840136,5);INSERT INTO outorders VALUES(CK20121011021,KT10011117,10086,2,002,2012-10-08,700,1106840135,8);INSERT INTO outorders VALUES(CK20121011020,KT10011110,10088,2,001,2012-10-20,78,1106840135,20000);INSERT INTO outorders VALUES(CK20121011023,KT10011111,10087,2,002,2012-10-20,300,1106840137,2500);INSERT INTO outorders VALUES(CK20121011024,KT10011112,10086,1,002,2012-10-20,100,1106840137,12000);INSERT INTO outorders VALUES(CK20121011025,KT10011114,10089,2,003,2012-10-21,300,1106840137,4500);INSERT INTO outorders VALUES(CK20121011026,KT10011115,10088,2,004,2012-10-21,600,1106840139,5);INSERT INTO outorders VALUES(CK20121011027,KT10011116,10087,2,123,2012-10-21,799,1106840135,5)进货单INSERT INTO inorders VALUES(RK20121011020,KT10011117,11068403,3,112,2012-10-08,100,1106840135,5);INSERT INTO inorders VALUES (RK20121011021,KT10011111,11068402,1,002,2012-10-08,200,1106840137,2000);INSERT INTO inorders VALUES(RK20121011022,KT10011110,11068401,1,001,2012-10-08,100,1106840137,15000);INSERT INTO inorders VALUES (RK20121011023,KT10011116,11068405,3,002,2012-10-08,100,1106840137,3);2.2 数据的修改 1） 增加新的商品类别INSERT INTO PRODUCT VALUES (KT10011130,家用电器,iPad,11068404,平板,300,3000,100,台,004,2,4000 )2） 修改已有的供应商信息UPDATE SUPPLIER SET SNAME=林叔WHERE SID=110684013） 输入一份进货单（至少包含两种不同的商品，注意有什么其它的相关操作！）CREATE TRIGGER protrigger ON inordersAFTER INSERT AsBEGINUPDATE productSET product.quantity=product.quantity(SELECT quantity FROM product WHERE did=did)+inserted.inquantyEND为在增加进货单时同时更改库存量所创建的触发器INSERT INTO inorders VALUES(RK20121011020,KT10011117,11068403,3,112,2012-10-08,100,1106840135,5);INSERT INTO inorders VALUES (RK20121011021,KT10011111,11068402,1,002,2012-10-08,200,1106840137,2000);4） 删除某一份销售单（有什么其它的相关操作吗？）CREATE TRIGGER deltrigger ON outordersAFTER DELETE AS BEGINUPDATE productSET product.quantity=product.quantity(SELECT quantity FORM productWHERE did=did)- deleted.inquantyEND创建触发器实现库存量的同步更改DELETE FORM outordersWHERE outorderid=id2.3 查询1) 商品编号为KT10011113的商品的名称、规格、类型、货位、库存数量、单价SELECT NAME,SPEC,P_TYPE,G_POS,QUANTITY,PRICEFROM PRODUCTWHERE PRODID=KT100111132) 当前库存量低于最低库存量的商品信息SELECT *FROM PRODUCTWHERE QUANTITY5 3) 2012年10月销售量低于10件的商品SELECT NAME AS 商品名,SUM(OUTQUANTY) AS 销量FROM OUTORDERS INNER JOIN PRODUCT ON OUTORDERS.PRODID=PRODUCT.PRODIDWHERE CONVERT(varchar(10),OUTDATE,121) LIKE 2012-10% GROUP BY nameHAVING SUM(OUTQUANTY)=104) 2012年10月进货金额最高的进货单（可以利用视图）select INORDERID as 订单号,sum(INPRICE*INQUANTY)as 总额from inorders where inorderid in(Select distinct INORDERIDFrom inorders inner join product on did=didWhere inorders.INORDERID in(select top(1) INORDERIDfrom inorderswhere convert(varchar(10),indate,121) like 2012-10%group by INORDERIDorder by sum(inprice*INQUANTY)desc)group by INORDERIDorder by sum(INQUANTY*INPRICE)5) 2012年10月所有商品的进销存一览表（使用外部连接） SELECT PRODUCT.PRODID,NAME,INORDERID,INDATE,INQUANTY,OUTORDERID,OUTDATE,OUTQUANTYFROM PRODUCT FULL JOIN INORDERS ON(PRODUCT.PRODID=INORDERS.PRODID)FULL JOIN OUTORDERS ON(OUTORDERS.PRODID=PRODUCT.PRODID)WHERE PRODUCT.PRODID=INORDERS.PRODID OR PRODUCT.PRODID=OUTORDERS.PRODID2.5 视图及索引销量视图Create view p_sale as Select did , inner join product o