某送水公司的送水系统-课程设计报告.docx

精品文档学号某送水公司的送水系统课程设计报告题目：某送水公司的送水系统专业：网络工程班级：姓名：指导教师：成绩：计算机学院2017年12月8日1 设计内容及要求1.1 实现工作人员、客户信息的管理；实现矿泉水类别和供应商的管理；实现矿泉水入库管理和出库管理；1.2 创建触发器，实现入库、出库时相应类型矿泉水的数量的增加或减少;创建存储过程统计每个送水员工指定月份送水的数量；创建存储过程查询指定月份用水量最大的前10个用户，并按用水量递减排列；建立数据库相关表之间的参照完整性约束。2 概要设计 2.1 需求分析随着经济不断发展，城市的不断扩张，城市居民的用水需求量也在不断的攀高。为了适应人们的消费需求，近年来我国的饮用水市场发展极快。各地类似“送水公司”这样的服务业也随之不断发展。各企业竞争激烈，提高公司员工工作效率和服务质量是制胜的重要因素，另一方面在于与客户的沟通和公司自我形象的不断完善。在用水旺季电话较多时由于接话员效率低，不可避免的会发生漏接电话等情况，失去一些定水客户。在这种情况下，拥有一个专业的针对送水行业上述问题的软件系统就成为同行业中竞争发展的有利优势。另外，目前市场上这方面的软件系统使用范围比较有限，就算有使用的也很少涉及送水配送业务。目前大多数公司现行的管理系统虽然解决了公司业务上的处理问题，但在员工工作效率、客户信息管理、信息查询分析等方面不完善，需要进一步的优化系统处理业务流程和解决公司以往单人操作系统处理烦琐事务等问题。本系统需完成的功能：工作人员 、客户的管理、矿泉水的类别（信息）、供应商的信息、矿泉水入库、出库、收费、进货、销售管理。2.2系统功能结构员工管理：新建、查询、编辑、删除员工信息、奖惩、权限管理客户管理：新建、查询、编辑、删除客户信息矿泉水管理：新建、查询、编辑、删除矿泉水信息，实现时时更新供应商管理：新建、查询、编辑、删除供应商信息，实现时时更新以保证公司利益仓库管理：进货、出库登记、商品库存统计管理，生成商品积压或缺货报告单订单管理：新建、查询、订单编辑、未发货订单显示、发货中订单显示、已完成订单显示、已取消订单显示财务管理：新建、查询、编辑、删除财务报表，统计、比较各级阶段的财务情况进货管理：新建、查询、编辑、删除进货订单信息，与供应商的联系，交易，员工人员分配销售管理：新建、查询、编辑、删除销售信息，与客户的联系，交易，员工人员分配，做好售后服务等2.3 E-R图矿泉水与仓库的E-R图进货E-R图销售E-R图订单处理E-R图 全局E-R图 将E-R图转换成关系模式，并注明主码和外码员工：员工编号，姓名，职称，电话，工资，业绩，出勤客户：客户编号，姓名，地址，电话公司：公司编号，公司名称，地址，联系电话矿泉水：产品编号，产品名称，生产商，生产日期，产品种类，进货价格，销售价格订单：订单编号，客户编号，商品编号，订单数量，订单日期订单处理情况，总金额供应商：供应商编号，单位名称，地址，电话费用：费用编号，付费方式，已付，金额仓库：仓库编号，仓库名称，仓库容量进货：公司编号，员工号，订单号，进货日期，进货数量，订单处理状态存取：商品编号，仓库编号，存取数量，存取日期销售：公司编号，客户编号，数量，产品编号，金额2.4数据结构数据结构：员工含义说明：是送水系统进行完成事务的数据结构，定义了一个员工的有关信息组成：员工编号、姓名、职称、电话、工资、业绩、出勤数据结构：客户含义说明：是送水系统产生事务的数据结构，定义了一个客户的有关信息组成：客户编号、姓名、地址、联系电话数据结构：公司含义说明：是送水系统产生事务的另一方的数据结构，定义了一个公司的有关信息组成：公司编号、公司名称、地址、联系电话数据结构：矿泉水含义说明：是送水系统主体数据的数据结构，定义了一个矿泉水的有关信息组成：产品编号、产品名称、生产商、生产日期、产品种类、进货价格、销售价格 数据结构：订单含义说明：是送水系统矿泉水交易依据的数据结构，定义了一个订单的有关信息组成：订单编号、客户编号、商品编号、订单数量、订单日期、订单处理情况、总金额 数据结构：供应商含义说明：是送水系统提供货源的数据结构，定义了一个供应商的有关信息组成：供应商编号、单位名称、地址、电话数据结构：费用含义说明：是送水系统订单里包含的一部分数据结构，定义了一个费用的有关信息组成：费用编号、付费方式、已付、金额 数据结构：仓库含义说明：是送水系统矿泉水存储地的数据结构，定义了一个仓库的有关信息组成：仓库编号、仓库名称、仓库容量3 设计过程或程序代码create table buy( w_id char(8) not null, c_id char(8) not null, order_id char(8) not null, order_num numeric(36) null, oder_date date null, order_state varchar(100) null, constraint PK_BUY primary key (w_id, c_id); create index beingbuy_FK on buy (w_id ASC); create index buy_FK on buy (c_id ASC);create table buyin( com_id char(8) not null, o_id char(8) not null, in_date date null, in_money numeric(8,2) null, in_num real null, in_goods varchar(100) null, in_price numeric(36) null, constraint PK_BUYIN primary key (com_id, o_id); create index buyin_FK on buyin (com_id ASC);create index sale_FK on buyin (o_id ASC);create table company( com_id char(8) not null, c_id char(8) not null, com_name varchar(50) null, com_tel char(11) not null, com_address varchar(50) not null, out_date date null, out_money numeric(30) null, out_num real null, out_goods varchar(100) null, constraint PK_COMPANY primary key (com_id), constraint AK_COM_ADDRESS_COMPANY unique (com_address); create index out_store_FK on company (c_id ASC);create unique index company_AK on company (com_address ASC); create table custerm( c_id char(8) not null, c_name varchar(50) null, c_address varchar(100) null, c_tel numeric(11) not null, constraint PK_CUSTERM primary key (c_id); create table deal( w_id char(8) not null, c_id char(8) not null, e_id char(8) not null, deal_date date null, deal_num real null, constraint PK_DEAL primary key (w_id, c_id, e_id); create index deal_FK on deal (e_id ASC);create index deal2_FK on deal (w_id ASC,c_id ASC); create table employee( e_id char(8) not null, com_id char(8) null, e_name varchar(50) null, e_tel char(11) not null, e_leveal varchar(50) null, e_wage numeric(11) null, e_present varchar(100) null, constraint PK_EMPLOYEE primary key (e_id); create index belong_to_FK on employee (com_id ASC); create table money( w_id char(8) null, c_id char(8) null, money_id char(8) not null, money_way varchar(100) null, money_insum numeric(36) null, money_sum numeric(36) null, order_id char(8) null); create index belongto_FK on money (w_id ASC,c_id ASC); create table offer( o_id char(8) not null, o_name varchar(50) null, o_address varchar(50) null, o_tel varchar(11) null, constraint PK_OFFER primary key (o_id), constraint AK_O_TADDRESS_OFFER unique (o_address); create table save( s_id char(8) not null, s_name varchar(50) null, s_num real not null, constraint PK_SAVE primary key (s_id); create table save_produce( w_id char(8) not null, s_id char(8) not null, save_water_date date null, save_water_num real null, constraint PK_SAVE_PRODUCE primary key (w_id, s_id); create index save_produce_FK on save_produce (w_id ASC); create index save_produce2_FK on save_produce (s_id ASC); create table water( w_id char(8) not null, w_name varchar(50) null, w_kind varchar(50) null, w_produce varchar(50) null, w_time date not null, in_price numeric(36) null, out_price numeric(36) null, constraint PK_WATER primary key (w_id);alter table buyin add constraint FK_BUYIN_SALE_OFFER foreign key (o_id) references offer (o_id) alter table company add constraint FK_COMPANY_OUT_STORE_CUSTERM foreign key (c_id) references custerm (c_id) alter table deal add constraint FK_DEAL_DEAL_EMPLOYEE foreign key (e_id) references employee (e_id) alter table deal add constraint FK_DEAL_DEAL2_BUY foreign key (w_id, c_id) references buy (w_id, c_id) alter table employee add constraint FK_EMPLOYEE_BELONG_T_COMPANY foreign key (com_id) references company (com_id) alter table money add constraint FK_MONEY_BELONGTO_BUY foreign key (w_id, c_id) references buy (w_id, c_id) alter table save_produce add constraint FK_SAVE_PRO_SAVE_PROD_WATER foreign key (w_id) references water (w_id) alter table save_produce add constraint FK_SAVE_PRO_SAVE_PROD_SAVE foreign key (s_id) references save (s_id)4设计结果与分析 4.1测试数据insert into water (w_id, w_name, w_kind, w_produce, w_time, in_price, out_price) values (11111110, 龙井水, 茶水, 长春, 17-6月-2011, 4, 10);insert into water (w_id, w_name, w_kind, w_produce, w_time, in_price, out_price) values (11111111, 农夫山泉, 山泉, 哈尔滨, 05-9月-2014, 2, 14);insert into water (w_id, w_name, w_kind, w_produce, w_time, in_price, out_price) values (11111112, 纯净水, 山泉, 沈阳, 13-5月-2011, 12, 3);insert into water (w_id, w_name, w_kind, w_produce, w_time, in_price, out_price) values (11111113, 娃哈哈, 牛奶, 长春, 26-4月-2012, 14, 9);insert into water (w_id, w_name, w_kind, w_produce, w_time, in_price, out_price) values (11111114, 冰红茶, 饮料, 北京, 06-10月-2013, 6, 7);insert into water (w_id, w_name, w_kind, w_produce, w_time, in_price, out_price) values (11111115, 冰糖雪梨, 饮料, 秦皇岛, 20-4月-2014, 8, 16);insert into water (w_id, w_name, w_kind, w_produce, w_time, in_price, out_price) values (11111116, 白水, 山泉, 哈尔滨, 16-10月-2012, 5, 17);insert into water (w_id, w_name, w_kind, w_produce, w_time, in_price, out_price) values (11111117, 可乐, 饮料, 长春, 06-11月-2012, 16, 1);insert into water (w_id, w_name, w_kind, w_produce, w_time, in_price, out_price) values (11111118, 美年达, 饮料, 佳木斯, 24-10月-2014, 3, 2);insert into water (w_id, w_name, w_kind, w_produce, w_time, in_price, out_price) values (11111119, 咖啡, 饮料, 牡丹江, 01-11月-2013, 10, 18);insert into custerm (c_id, c_name, c_address, c_tel) values (21111110, 小王, 上海, 16);insert into custerm (c_id, c_name, c_address, c_tel) values (21111111, 小李, 北京, 12);insert into custerm (c_id, c_name, c_address, c_tel) values (21111112, 小赵, 佳木斯, 1);insert into custerm (c_id, c_name, c_address, c_tel) values (21111113, 小钱, 大庆, 17);insert into custerm (c_id, c_name, c_address, c_tel) values (21111114, 小范, 哈尔滨, 11);insert into custerm (c_id, c_name, c_address, c_tel) values (21111115, 小邓, 沈阳, 4);insert into custerm (c_id, c_name, c_address, c_tel) values (21111116, 小刘, 牡丹江, 2);insert into custerm (c_id, c_name, c_address, c_tel) values (21111117, 小张, 大连, 15);insert into custerm (c_id, c_name, c_address, c_tel) values (21111118, 小吴, 哈尔滨, 0);insert into custerm (c_id, c_name, c_address, c_tel) values (21111119, 小马, 沈阳, 14);insert into buy (w_id, c_id, order_id, order_num, oder_date, order_state) values (11111110, 21111110, 51111110, 19, 19-10月-2013, 签约);insert into buy (w_id, c_id, order_id, order_num, oder_date, order_state) values (11111110, 21111111, 51111111, 3, 13-8月-2013, 签约);insert into buy (w_id, c_id, order_id, order_num, oder_date, order_state) values (11111112, 21111111, 51111112, 11, 25-2月-2012, 处理中);insert into buy (w_id, c_id, order_id, order_num, oder_date, order_state) values (11111113, 21111112, 51111113, 5, 28-3月-2014, 已完成);insert into buy (w_id, c_id, order_id, order_num, oder_date, order_state) values (11111114, 21111113, 51111114, 17, 23-9月-2012, 已完成);insert into buy (w_id, c_id, order_id, order_num, oder_date, order_state) values (11111114, 21111115, 51111115, 15, 18-3月-2013, 欠款中);insert into buy (w_id, c_id, order_id, order_num, oder_date, order_state) values (11111116, 21111116, 51111116, 7, 15-11月-2014, 处理中);insert into buy (w_id, c_id, order_id, order_num, oder_date, order_state) values (11111117, 21111117, 51111117, 18, 20-9月-2013, 处理中);insert into buy (w_id, c_id, order_id, order_num, oder_date, order_state) values (11111114, 21111116, 51111118, 13, 14-9月-2012, 已完成);insert into buy (w_id, c_id, order_id, order_num, oder_date, order_state) values (11111119, 21111118, 51111119, 8, 15-3月-2013, 欠款中);insert into money (money_id, w_id, c_id, money_way, money_insum, money_sum, order_id) values (61111110, 11111110, 21111110, 现金, 14, 18, 51111110); insert into money (money_id, w_id, c_id, money_way, money_insum, money_sum, order_id) values (61111111, 11111110, 21111111, 现金, 9, 11, 51111111);insert into money (money_id, w_id, c_id, money_way, money_insum, money_sum, order_id) values (61111112, 11111112, 21111111, 信用卡, 5, 18, 51111112);insert into money (money_id, w_id, c_id, money_way, money_insum, money_sum, order_id) values (61111113, 11111113, 21111112, 信用卡, 6, 23, 51111113);insert into money (money_id, w_id, c_id, money_way, money_insum, money_sum, order_id) values (61111114, 11111114, 21111113, 信用卡, 11, 29, 51111114);insert into money (money_id, w_id, c_id, money_way, money_insum, money_sum, order_id) values (61111115, 11111114, 21111115, 支票, 15, 30, 51111115);insert into money (money_id, w_id, c_id, money_way, money_insum, money_sum, order_id) values (61111116, 11111116, 21111116, 支票, 7, 19, 51111116);insert into money (money_id, w_id, c_id, money_way, money_insum, money_sum, order_id) values (61111117, 11111117, 21111117, 支票, 16, 32, 51111117);insert into money (money_id, w_id, c_id, money_way, money_insum, money_sum, order_id) values (61111118, 11111114, 21111116, 现金, 19, 25, 51111118);insert into money (money_id, w_id, c_id, money_way, money_insum, money_sum, order_id) values (61111119, 11111119, 21111118, 现金, 8, 22, 51111119);insert into deal (w_id, c_id, e_id, deal_date, deal_num) values (11111110, 21111110, 71111110, 20-10月-2013, 12);insert into deal (w_id, c_id, e_id, deal_date, deal_num) values (11111110, 21111111, 71111110, 20-10月-2013, 3);insert into deal (w_id, c_id, e_id, deal_date, deal_num) values (11111112, 21111111, 71111111, 16-1月-2012, 10);insert into deal (w_id, c_id, e_id, deal_date, deal_num) values (11111113, 21111112, 71111112, 17-1月-2014, 1);insert into deal (w_id, c_id, e_id, deal_date, deal_num) values (11111114, 21111113, 71111113, 06-3月-2012, 15);insert into deal (w_id, c_id, e_id, deal_date, deal_num) values (11111114, 21111115, 71111114, 15-9月-2012, 18);insert into deal (w_id, c_id, e_id, deal_date, deal_num) values (11111116, 21111116, 71111115, 05-3月-2013, 9);insert into deal (w_id, c_id, e_id, deal_date, deal_num) values (11111117, 21111117, 71111116, 25-