数据库答辩ppt

1、信息131 朱安琪 201013218*功能：功能：*维修服务管理功能、客户管理功能、进销存管理功能*具体具体业务：业务：*采购采购：采购，验货，退货，入库*盘存与报盘存与报损：损：检查库存，帐实对比，报损，编制盘存清单*销售：销售：顾客订货，安排试驾，检查货架，货物出库，退货处理，撰写退货反馈*客户客户管理：管理：消费等级信息储存，会员升级*维修维修管理：管理：检查问题并撰写维修清单，派工*数据项共有166个。*数据结构有26个。*数据流有41个。*处理逻辑有18个。*数据存储有13个。 具体参见论文附录。*商品（商品编号，商品名称，商品类型，销售价格）Car , AutoSparePart

2、s , ServiceItem*顾客（客户编号，客户姓名，客户性别，客户年龄，客户身份证号，客户地址，客户电话，客户车牌号，客户车编码，消费总额，会员等级，折扣信息）*供应商（供应商编号，供应商姓名，供应商地址，供应商邮箱，供应商电话，供应商传真号码）*采购单细则（采购单号，细则号，采购价格，采购数量，商品编号）*采购单（采购单号，采购日期，采购项数，供应商编号，采购人姓名，要求到货日期）*入库细则（入库单号，细则号，入库数量，商品编号）*入库单（采购单号，入库日期，入库人姓名 ）*库存信息（商品编号，库存状态，库存上限，库存量）*库存（商品编号，仓库编号，仓库管理员姓名）*盘点清单细则（盘点

3、清单号，细则号，报损数量，报损日期，实存数量，商品编号）*盘点清单（盘点清单号，盘点日期，盘点人姓名）*出库单细则（出库单号，细则号，商品编号，出库数量）*出库单（出库单号，出库日期，出库登记人姓名）*上货（货架格层编号，商品编号，上货人登记人姓名，上货日期，上货数量）*货架（货架格层编号，商品编号，货架货物数量）*货架缺货记录（货架缺货单号，货架格层编号，商品编号，货架缺货数量，货架缺货日期）*缺货单细则（缺货单号，细则号，商品编号，缺货数量）*缺货单（缺货单号，缺货日期，缺货登记人姓名，缺货项数）*维修服务细则 （细则号，维修服务单号，维修用品单号，商品数量，销售价格，维修消费总额，商品编

4、号）*维修服务单（维修服务单号，实际维修消费总额，维修工姓名，送修日期）*维修属于（客户编号，维修服务单号，维修申请单号，维修服务类型，送修车的车牌号，送修车的型号，送修车的里程数）*销售单细则（销售单号，细则号，销售数量，商品编号，试驾车编号）TryDriveList*销售单（销售单号，销售日期，销售总额，实际销售总额，销售人员姓名，客户编号，试驾日期，试驾车编号）*退货单细则（退货单号，细则号，退货数量，商品编号）*退货单（退货单号，退货日期，退货金额，验货人姓名，客户编号）个数功能数据表30视图18查看各种单据，查看客户详细信息，商品详细信息存储过程12连接查询在不违反参照完整性的条件下

5、插入、更新、删除数据插入的同时修改其他表的相应数据触发器6插入或更新的同时，修改其他表的相应数据函数1计算打折后的总销售额GO-增加销售单,自动计算销售单总额与实际销售总额，并修改客户信息CREATE PROCEDURE P8 (ListNum VARCHAR(10),DetailNum VARCHAR(10),GoodNum VARCHAR(10),SaleQty INT,Saler VARCHAR(10),SoldDATE DATE,CusNum VARCHAR(10)ASBEGINDECLARE SalePrice FLOAT,Consumption FLOAT,TotalConsump

6、tion FLOAT,AcutualConsumption FLOAT-商品单价，单项商品消费额，销售单总额，销售单实际消费总额SELECT SalePrice=SalePrice FROM Goods WHERE GoodNum=GoodNumSET Consumption=SalePrice*SaleQty-本次插入的商品的消费额UPDATE Customer SET TotalConsumption=TotalConsumption+Consumption WHERE CusNum=CusNumIF (SELECT ListNum FROM SaleList WHERE ListNum=

7、ListNum)IS NULLBEGINSET TotalConsumption=Consumption-第一次插入的商品的消费额即为消费总额EXEC AcutualConsumption=dbo.Discount TotalConsumption,CusNum-计算实际销售总额INSERT INTO dbo.SaleListVALUES(ListNum,TotalConsumption,AcutualConsumption,Saler,SoldDATE,CusNum)INSERT INTO dbo.SaleDetailVALUES(DetailNum,ListNum,GoodNum,CusN

8、um,SaleQty)ENDELSEBEGINSELECT TotalConsumption=TotalConsumption FROM SaleList WHERE ListNum=ListNumSET TotalConsumption=TotalConsumption+Consumption-前几次插入商品累计销售额与本次插入金额及总销售额EXEC AcutualConsumption=dbo.Discount TotalConsumption,CusNum-计算实际销售总额INSERT INTO dbo.SaleDetailVALUES(DetailNum,ListNum,GoodNum

9、,CusNum,SaleQty)UPDATE SaleList-修改销售总额，修改实际销售总额SET TotalConsumption=TotalConsumption,AcutualConsumption=AcutualConsumption WHERE ListNum=ListNum ENDEND-增加销售单create or replace procedure INSERT_P4(v_ListNum stockoutdetail.listnum%type,v_DetailNum stockoutdetail.detailnum%type,v_GoodNum stockoutdetail.

10、goodnum%type,v_SOutQty stockoutdetail.soutqty%type,v_RecordDATE stockoutlist.recorddate%type,v_RecordStaff stockoutlist.recordstaff%type) isv_ListNumIsNull stockoutdetail.listnum%type;begin begin-异常处理，判断出库单是否已存在,不存在则先创建出库单，再创建细则 SELECT ListNum INTO v_ListNumIsNull FROM StockOutList WHERE ListNum=v_L

11、istNum; exception when no_data_found then insert into stockoutlist (listnum, recorddate, recordstaff) values (v_listnum, v_recorddate, v_recordstaff); commit; end; insert into stockoutdetail (detailnum, listnum, goodnum, soutqty) values (v_detailnum, v_listnum, v_goodnum, v_soutqty); commit;end INSERT_P4;- 自动修改会员信息create or replace trigger VIP before update on customer for each rowbegin IF (:new.totalconsumption=0 AND :new.totalconsumption=1000 AND :new.totalconsumption=20000 AND :new.totalconsumption=60000 AND :new.totalconsumption=200000) THEN :new.VIPLevel: