茶叶公司进销存管理系统的数据库.docx

课程设计 设计说明书茶叶公司进销存管理系统的数据库设计与数据操作学生姓名 白子健 学 号 1318014057 班 级 计本1302 成 绩 指导教师 肖海蓉 数学与计算机科学学院2015 2016 学年第 2 学期课程设计名称： 课程设计II 课程设计题目： 茶叶公司进销存管理系统的数据库设计与数据操作 完 成 期 限：自 2016 年 6 月 20日至 2016 年 7 月 1日共 2 周设计内容：调查我市茶叶公司，针对该公司的实际情况，实现公司的基本信息管理。1系统功能（1）系统用户权限管理功能：包括客户、分店负责人、营业员及管理员的不同权限设置功能；（2）茶叶信息管理功能：包括茶叶类别、商标等基本信息管理的录入、修改、删除、查询功能；（3）茶叶的进销存管理功能：进货、存货管理主要负责管理供应商在不同时间供应（进货）茶叶的信息情况、库存情况；销售管理主要负责不同分店的销售信息；（4）客户管理功能：包括客户基本信息的管理、客户订单管理功能。（5）相关信息的统计和报表打印功能：系统能够打印进货单、销售单等。营业员可以查看自己本日报表、本月报表功能，分店负责人具有茶叶信息统计及查看销售日报表、月报表、营业员业绩报表的功能。2. 具体要求：（1）根据自己的选题，进行系统调查，搜集资料。（2）确定系统的功能需求，性能需求，完成系统分析。（3）设计数据库概念结构：绘制所选课题详细的E-R图。（4）设计数据库逻辑结构：将E-R图转换数据库的逻辑结构，并进行优化；（5）设计数据库物理结构：选定实施环境，确定系统数据库的存储结构和存取方法等；（6）数据实施和维护：选用SQL SERVER建立数据库结构，加载数据，实现各种数据查询，设计库中视图、触发器、存储过程等对象，并能对数据库做简单的维护操作，完成建立数据库的备份作业。（7）根据题目的需求，写出完成各个功能操作的SQL语句。指导教师： 教研室负责人：课程设计评阅评语： 指导教师签名： 年 月 日摘要设计一个茶叶信息管理数据库，该数据库具有简单的用户权限控制、信息管理等功能，采用MySQL数据库作为软件开发环境。系统着重从概念结构、逻辑结构和物理结构分析并实现用数据库对数据进行管理的过程，从而为科学管理茶叶销售业务提供方便。关键词：茶叶进销存管理；数据库；MySQL目录1 课题描述12需求分析23数据库设计43.1概念结构设计43.2逻辑结构设计63.3物理结构设计64创建数据库84.1初步创建数据库84.2修改升级数据库定义：95数据库操作105.1数据库相关操作105.2权限设置146总结15参考文献151 课题描述本次课程主要设计了一个茶叶信息管理系统数据库，从概念结构设计、逻辑结构设计和物理结构设计层次完成数据库的设计。本次设计具有用户权限管理和基本的数据操作。2需求分析 需求分析是数据库设计最重要的一个阶段，它的基本任务是准确的回答：系统必须做什么。目前，茶叶市场已成为一个很火爆的行业，伴随着行业的快速发展，人工管理已无法满足公司的需求。通过实地调查汉中市茶叶市场，我们发现当地已开始逐渐由人工管理向计算机管理过度。用计算机管理已成为当前发展的必然趋势，我们通过调查得到的数据，对茶叶信息管理系统数据库做了一个详细的概述。茶叶信息管理系统主要提供方便高效的管理功能，用户可以通过该系统查阅相关信息，管理员可以管理所有信息。对于本系统，我们需要实现以下一些基本功能：（1）系统用户权限管理功能：包括客户、分店负责人、营业员及管理员的不同权限设置功能；（2）茶叶信息管理功能：包括茶叶类别、商标等基本信息管理的录入、修改、删除、查询功能；（3）茶叶的进销存管理功能：进货、存货管理主要负责管理供应商在不同时间供应（进货）茶叶的信息情况、库存情况；销售管理主要负责不同分店的销售信息；（4）客户管理功能：包括客户基本信息的管理、客户订单管理功能。（5）相关信息的统计和报表打印功能：系统能够打印进货单、销售单等。营业员可以查看自己本日报表、本月报表功能，分店负责人具有茶叶信息统计及查看销售日报表、月报表、营业员业绩报表的功能。系统顶层数据流图如图2.1所示：图2.1茶叶公司信息管理系统顶层数据流图系统销售数据流图如图2.2：图2.2茶叶公司信息管理系统销售数据流图系统供货数据流图如图2.3：图2.3茶叶公司信息管理系统供货数据流图3数据库设计3.1概念结构设计先设计初步的E-R图，再对E-R图优化得到最终图，按要求建立概念模型如图：图3.1仓库实体图图3.2茶叶实体图图3.3订单实体图图3.4分店实体图图3.5负责人实体图图3.6供应单实体图图3.7供应商实体图图3.8客户实体图图3.9销售员实体图图3.10茶叶公司进销存数据库E-R图3.2逻辑结构设计客户（客户编号、客户名称、客户电话）。供应商（供应商编号、供应商名称、供应商电话）。茶叶（茶叶编号、茶叶名称、茶叶品类）。负责人（负责人编号、负责人姓名、负责人电话）。分店（分店编号、分店名称、分店负责人编号）。销售员（销售员编号、销售员姓名、销售员电话、分店编号）。仓库（茶叶编号、仓库编号、仓库名称、茶叶余量）。订单（订单号、茶叶编号、数量、时间、销售单价、销售员编号、客户编号）。供应单（供应单号、茶叶编号、数量、进货单价、供应商编号、负责人编号）。3.3物理结构设计客户表的数据项：数据项含义数据类型长度客户编号客户的唯一标识varchar10客户名称客户的名称varchar10客户电话客户的联系方式varchar11销售员表的数据项：数据项含义数据类型长度销售员编号销售员的唯一标识varchar10销售员名称销售员的名称varchar10销售员电话销售员的联系方式varchar11分店编号销售员所从属的分店varchar10供应商的数据项：数据项含义数据类型长度供应商编号供应商的唯一标识varchar10供应商名称供应商的名称varchar10供应商电话供应商的联系方式varchar11茶叶的数据项：数据项含义数据类型长度茶叶编号茶叶的唯一标识varchar10茶叶名称茶叶的名称varchar10茶叶类别茶叶具体属性varchar10分店表的数据项：数据项含义数据类型长度分店编号分店的唯一标识varchar10分店名称分店的名称varchar10分店负责人电话分店负责人联系方式varchar11仓库表的数据项：数据项含义数据类型长度库存茶叶编号仓库内茶叶唯一标识varchar10茶叶余量茶叶库存余量float仓库编号仓库的标识varchar10仓库名称仓库的名称varchar10订单表的数据项：数据项含义数据类型长度订单编号订单的唯一标识varchar10茶叶编号订单茶叶的编号varchar10时间订单具体时间datetime销售单价茶叶卖出价格float销售员编号负责订单销售员varchar10客户编号订单发出者varchar10供应单表的数据项：数据项含义数据类型长度供应单编号供应单的唯一标识varchar10供应单价进货价格float茶叶编号供应的茶叶varchar10数量供应的数量float供应商编号供应来源varchar10负责人编号进货负责人varchar104创建数据库4.1初步创建数据库CREATE DATABASE TeaCompany CHARACTER SET GBK;USE TeaCompany;CREATE TABLE kehu(khbh varchar(10) NOT NULL UNIQUE,khmcvarchar(10) NOT NULL UNIQUE,khdhvarchar(11) NOT NULL UNIQUE,constraint kehuPk primary key(khbh);CREATE TABLE gongyingshang(gysbhvarchar(10)NOT NULL UNIQUE,gysmcvarchar(10)NOT NULL UNIQUE,gysdhvarchar(11)NOT NULL UNIQUE,constraint gongyingshangPk primary key(gysbh);CREATE TABLE chaye(cybhvarchar(10)NOT NULL UNIQUE,cymcvarchar(10)NOT NULL UNIQUE,constraint chayePk primary key(cybh);CREATE TABLE fuzeren(fzrbhvarchar(10)NOT NULL UNIQUE,fzrxmvarchar(10)NOT NULL UNIQUE,fzrdhvarchar(11)NOT NULL UNIQUE,constraint fuzerenPk primary key(fzrbh);CREATE TABLE fendian(fdbhvarchar(10)NOT NULL UNIQUE,fdmcvarchar(10)NOT NULL UNIQUE,fdfzrbhvarchar(10)NOT NULL UNIQUE,constraint fendianPk primary key(fdbh),constraint fendian_fuzeren_fk foreign key(fdfzrbh) references fuzeren(fzrbh);CREATE TABLE xiaoshouyuan(xsybhvarchar(10)NOT NULL UNIQUE,xsyxmvarchar(10)NOT NULL UNIQUE,xsydhvarchar(11)NOT NULL UNIQUE,fdbh varchar(10)NOT NULL,constraint xiaoshouyuanPk primary key(xsybh),constraint xiaoshouyuan_fendian_fk foreign key(fdbh) references fendian(fdbh);CREATE TABLE cangku(cybhvarchar(10)NOT NULL UNIQUE,cyylfloat NOT NULL,constraint cangkuPk primary key(cybh),constraint cangku_chaye_fk foreign key(cybh) references chaye(cybh);CREATE TABLE dingdan(ddhvarchar(10)NOT NULL UNIQUE,cybhvarchar(10)NOT NULL,slfloatNOT NULL,sjdatetime NOT NULL,xsdjfloatNOT NULL,xsybhvarchar(10)NOT NULL,khbhvarchar(10)NOT NULL,constraint dingdanPk primary key(ddh),constraint dingdan_chaye_fk foreign key(cybh) references chaye(cybh),constraint dingdan_xiaoshouyuan_fk foreign key(xsybh) references xiaoshouyuan(xsybh),constraint dingdan_kehu_fk foreign key(khbh) references kehu(khbh);CREATE TABLE gongyingdan(gydh varchar(10)NOT NULL UNIQUE,cybhvarchar(10)NOT NULL,sl floatNOT NULL,jhdjfloatNOT NULL,gysbhvarchar(10)NOT NULL,fzrbh varchar(10)NOT NULL,constraint gongyingdanPk primary key(gydh),constraint gongyingdan_chaye_fk foreign key(cybh) references chaye(cybh),constraint gongyingdan_gongyingshang_fk foreign key(gysbh) references gongyingshang(gysbh),constraint gongyingdan_fuzeren_fk foreign key(fzrbh) references fuzeren(fzrbh);4.2修改升级数据库定义：ALTER TABLE chaye ADD pl varchar(10);ALTER TABLE cangku ADD ckmc varchar(10);ALTER TABLE cangku ADD ckbh varchar(10);ALTER TABLE cangku DROP PRIMARY KEY;ALTER TABLE cangku ADD CONSTRAINT cangkuPk PRIMARY KEY (cybh, ckbh); 5数据库操作5.1数据库相关操作插入数据：INSERT INTO kehu VALUES(001, 张一,;INSERT INTO kehu VALUES(002, 张二,;INSERT INTO kehu VALUES(003, 张三,;INSERT INTO kehu VALUES(004, 张四,;INSERT INTO gongyingshang VALUES(001, 李一,;INSERT INTO gongyingshang VALUES(002, 李二,;INSERT INTO gongyingshang VALUES(003, 李三,;INSERT INTO gongyingshang VALUES(004, 李四,;INSERT INTO chaye VALUES(001, 龙井茶);INSERT INTO chaye VALUES(002, 碧螺春);INSERT INTO chaye VALUES(003, 普洱茶);INSERT INTO fuzeren VALUES(001, 何宁,;INSERT INTO fendian VALUES(001, 汉中茶叶店, 001);INSERT INTO xiaoshouyuan VALUES(001, 销售1, 001);INSERT INTO xiaoshouyuan VALUES(002, 销售2, 001);INSERT INTO cangku VALUES(001, 100);INSERT INTO cangku VALUES(002, 90);INSERT INTO cangku VALUES(003, 80);INSERT INTO dingdan VALUES(001, 001, 10, 2016-05-25 08:08:02, 50, 001, 001);INSERT INTO dingdan VALUES(002, 002, 10, 2016-05-25 08:08:08, 50, 002, 002);INSERT INTO gongyingdan VALUES(001, 001, 10, 20,001, 001);INSERT INTO gongyingdan VALUES(002, 002, 10, 20,002, 001);查看数据库：SELECT kehu.khbh,ddh,sl,xsdj,sj,xsybhFROM dingdan, kehuwhere dingdan.khbh = 002 and kehu.khbh = dingdan.khbh;SELECT *FROM fuzeren, gongyingshang, gongyingdanWHERE gongyingshang.gysbh=gongyingdan.gysbhAND gongyingdan.fzrbh = fuzeren.fzrbh;操作结果如图：图5.1普通查询结果图SELECT *FROM kehu,dingdan,xiaoshouyuanWHERE kehu.khbh=dingdan.khbh AND dingdan.xsybh=xiaoshouyuan.xsybhGROUP BY xiaoshouyuan.xsybh;操作结果如图：图5.2分组查询结果图SELECT dingdan.xsybh, xiaoshouyuan.xsyxmFROM dingdan, xiaoshouyuanWHERE xiaoshouyuan.xsybh=dingdan.xsybh AND sl ANY(SELECT slFROM dingdan WHERE xsybh=(SELECT xsybhFROM xiaoshouyuan WHERE xsyxm = 销售1);操作结果如图：图5.3嵌套查询结果图创建视图：CREATE VIEW kehu_chaye_viewAS SELECT kehu.*, chaye.*FROM kehu,chaye,dingdanWHERE dingdan.khbh=kehu.khbh AND dingdan.cybh=chaye.cybh;修改数据库数据：UPDATE cangkuSET ckbh = 001, ckmc = 天下第一茶;UPDATE chayeSET pl = 绿茶WHERE cymc=龙井茶;UPDATE chayeSET pl = 绿茶WHERE cymc=普洱茶;UPDATE chayeSET pl = 绿茶WHERE cymc=碧螺春;删除数据：DELETEFROM kehuWHERE khmc = 张四;备份：echo offC:MySQLbinmysqladmin -u root password=toor shutdownC:MySQLbinmysqldump opt -u root password=toor teacompany C:teacompanybak.sqlC:MySQLbinmysqld-nt触发器：/*在增加订单和供应单时不用考虑仓库的变化。*/DELIMITER $CREATE TRIGGER dingdan_add_triggerafter INSERT ON dingdanfor each rowBEGINUPDATE cangkuSET cyyl = cyyl - new.slWHERE cybh = new.cybh;END$CREATE TRIGGER gongyingdan_add_triggerafter INSERT ON gongyingdanfor each rowBEGINUPDATE cangkuSET cyyl = cyyl + new.slWHERE cybh = new.cybh;END$DELIMITER ;操作结果如图所示：图5.4创建触发器结果图存储过程：DELIMITER $CREATE PROCEDURE count_kehu(OUT s int)BEGINSE