饮料厂销售管理系统课程设计(doc35页)

数据库原理课程设计题 目饮料厂销售管理系统学 院商学院专 业信息管理和信息系统班 级信息 082学 号200853275237学生姓名许叶飞指导教师张细香编写日期2011 月 1 月 5 日目录1系统需求分析 .11.1系统调查 .11.2用户需求 .12系统总体结构设计 .22.1软件系统总体结构设计 .22.2数据存储的总体设计 .22.3系统功能模块设计 .23数据库详细设计 .33.1数据库设计 .33.11E-R 图设计 .33.12 逻辑结构设计 .53.2数据库的完整性和安全性 .84 数据库操作总代码 （ SQL Server2000语句完成）.95部分功能模块运行结果 .295.1建立存储过程实现插入、删除、修改数据.295.2建立存储过程实现单表查询， 连接查询，嵌套查询和集合查询 .306课程设计心得 .31参考文献 .321 系统需求分析1.1系统调查对现行饮料厂销售管理业务进行详细调查是为了了解不同客户对不同产品的需求， 并统计不同销售员的销售业绩。 因此，我们必须对饮料厂的销售管理进行全面、 细致而充分的调查研究。 首先可以了解一下该厂已经生产的产品有哪些种类以及不同种类包含哪些产品， 该厂目前有哪些销售员，哪些客户，以及目前的各产品的库存量。在此基础上，我们开始对该厂进行更为细致、全面、深入的调查。通过调查，我们了解到随着时间的发展，该厂所生产的产品会增加，销售员也会增加，客户也会增加。 为了适应该厂的变化， 所以我们所设计出的数据库必须能够随着该厂的实际变化而变化。1.2 用户需求要设计一个性能良好的管理系统，明确用户的应用环境对系统的要求是首要的和基本的。本系统从以下四个方面对用户需求进行了分析：用户的信息需求：用户能够通过单表查询、嵌套查询、集合查询等方法来查询用户信息表的信息，销售员信息表的信息，客户信息表的信息，饮料信息表的信息，订单信息表的信息，订购表的信息，仓库信息表的信息，库存表的信息和销售员总销售额表的信息。此外，用户还可以通过多表连接查询等方法来查询来自不同表的数据列值的信息。用户的处理要求： 用户能够对销售员信息表、 客户信息表、 饮料信息表、 订单信息表以及订单详细信息表等进行增加、删除和修改等操作。在客户订购饮料之后，用户可以根据目前仓库中的饮料的库存量来确定能否发货。如果可以发货，则将在库存表里扣除对应订单详细表里订购的饮料数量。如果不可以发货，则将原来的订单删除，并删除对应的订单详细信息表。当用户退订饮料之后，则将在库存表里加上对应订单详细表里订购的饮料数量。系统能够自动生成做了插入、删除或修改订单及订单详细表之后的订单的总价格和某一销售员的销售业绩。对系统的适应性、通用性要求：该系统不仅仅局限于饮料行业的销售系统，也可以移植到其他行业，进行其他行业的产品的销售管理。对系统的安全性要求，要求进入系统必须进行用户口令校验。2 系统总体结构设计2.1 软件系统总体结构设计软件系统总体结构设计的主要任务就是将整个系统合理的划分成各个功能模块， 正确处理模块之间与模块内部的联系及它们之间的调用关系和数据联系 ,定义各模块的内部结构等，本系统的软件总体结构按分解协调的原则， 信息隐蔽抽象的原则， 自顶向下的原则， 一致性原则，面向用户的原则进行设计。2.2数据存储的总体设计按用途分类可把本系统文件分成以下几种：（ 1） 主文件：系统中的主文件是最重要的共享文件，主要存放具有固定值属性的数据。它需要长期保存，并不断更新。如销售员信息表、客户信息表、饮料信息表。（ 2） 处理文件：处理文件包含下一次更新文件所需要的全部记录，本系统中的处理文件如订单信息表、订购表、库存表。2.3 系统功能模块设计通过对系统的数据流图进行分析，进一步进行功能分解，直到分解成含义明确，功能单一的功能模块，从而得到系统的功能模块结构图。如图下图所示。饮料厂销售管理系销用售户员登插修登删查插录删入改录除看入除3 数据库详细设计订订信信信订单单单息息息3.1 数据库设计数据库的设计是指对一个给定的应用环境，构造数据库模式，建立数据库及其应用系统，满足各种用户需求。作为信息资源开发、管理和服务的一种有效的手段，数据库技术的应用已越来越广泛， 从小型的单项事务处理系统到大型的信息系统大都用先进的数据库技术来保持系统数据的安全性、完整性和共享性。对一个实际的系统来说，数据库表的设计在遵循数据库理论的同时，必须能用开发工具来实现用户在各方面提出的功能要求。3.1.1 E-R图设计在系统的数据库设计中， 先要对系统分析得到的数据字典中的数据存储进行分析， 分析各数据存储之间的关系，然后才能得出系统的关系模式。可以采用 E-R 图的方法来进行数据结构分析， E-R 设计方法是一种通过 E-R 图来描述现实世界信息结构的DB 设计方法。 E-R 图由实体、属性、联系三部分组成。总 E-R 图如下：部分 ER 图如下：(1) 销售员表 E-R 图见下图：(2) 客户表 E-R 图见下图：（3）饮料表 E-R 图见下表：（4）订单表 E R 图见下表：饮料厂销售管理系统中所涉及到的主要实体集及其属性如下：用户用户编号，用户名，登录密码销售员销售员编号，销售员姓名，性别，出生年月，雇佣日期，住址,销售员电话客户 客户编号 ,客户公司名称 ,联系人 ,公司地址 ,邮政编码，客户电话饮料饮料编号，饮料名称，种类编号，种类名称，饮料单价订单订单编号，客户编号，销售员编号，订单日期订购表订单编号，饮料编号，订购数量，总价格仓库仓库编号，仓库地址库存表仓库编号，饮料编号，库存量销售员总销售额表销售员编号，总销售额3.1.2.逻辑结构设计（E-R 图向关系模型的转换）E R 图向关系模型的转化要解决的问题是如何将实体和实体间的联系转换为关系模式，如何确定这些关系横式的属性和码。对于实体间的联系我们分不同的情况讨论：(1) 若实体间的联系是 1:1，可以在两个实体转换成的两个关系中任意一个关系的属性中加入另一个关系的码。(2) 若实体间联系为1:n，则在 n 端实体转换成的关系中加入1 端实体转换成的关系码。(3) 若实体间联系为 n:m，则将联系转换为关系， 关系的属性为诸个实体的码加上联系具有的属性，而关系的码则为诸实体的码的组合。由以上三点，可转换为关系模型。每个关系的主码用下划线表示。用户用户编号，用户名，登录密码销售员销售员编号，销售员姓名，性别，出生年月，雇佣日期，住址, 销售员电话客户 客户编号 ,客户公司名称 ,联系人 ,公司地址 ,邮政编码，客户电话饮料饮料编号，饮料名称，种类编号，种类名称，饮料单价订单订单编号，客户编号，销售员编号，订单日期订购表订单编号，饮料编号，订购数量，总价格仓库仓库编号，仓库地址库存表仓库编号，饮料编号，库存量销售员总销售额表销售员编号，总销售额基于以上关系模型，本系统中关于数据库表结构设计如下：在服务器上用SQL Sever2000 建立名为exam 的数据库。其中包括以下表：用户信息表（ Users) ，销售员信息表（ Salers) ，客户信息表（ Customers) ，饮料信息表（ drink) ，订单 信 息 表 （ orders)， 订 购 表（ orderDetails)， 仓 库 信 息 表 （ storehouse)， 库 存 表(storetable)，销售员总销售额表（TotalSales）。下面将具体介绍数据库中各个表的结构：3.2 数据库的完整性和安全性3.2.1. 数据库的完整性数据库的完整性是指数据的正确性和相容性。数据库管理系统（DBMS ）用一定的机制来检查数据库中的数据是否满足规定的条件完整性约束条件，数据的约束条件是语义的体现，将作为模式的一部分存入数据库中。本系统中定义了表与表之间的联系有助于实现完整性规则，一般在程序中实现具体的完整性控制。3.2.2.数据库的安全性数据库的安全性是指保护数据库以防止不合法的使用所造成的数据泄漏、 更改或破坏。 在数据库系统中， 大量的数据集中存放，而且为许多用户直接共享， 是宝贵的信息资源， 系统的安全保护措施就显得更为重要，它保护数据库防止恶意的破坏和非法的存取。本系统包括数据库的安全和服务器的安全。采用管理员表示和鉴定的方法实现数据库的安全，此安全管理措施并不在前台开发工具中实现， 而是在后台数据库中实现。 在登录数据库时，系统让管理员户表示自己的身份，不同的管理员权限不同，系统进行核实，鉴别此管理员是否为合法用户， 若是，系统进一步核实用户， 通常要求用户输入口令， 系统和对用户口令以鉴别用户身份。 服务器的安全也是通过用户在登录服务器时输入合法的用户名和密码来实现的。 这是一种简单可行的方法，实现起来比较方便。没有采用更加复杂的系统安全管理措施是因为本管理系统一般是应用在校园网中， 采用安全管理措施主要是为了防止没有修改权限的用户无意间修改了数据， 因此采用用户标识和鉴定的安全管理措施就能够保障数据库的安全性。4 数据库操作（ SQL Server2000 语句完成）（ 1）建立数据库以及相应数据表create database drinksalesgouse drinksalesgo- 创建表 UserscreatetableUsers(userIDvarchar(8)not nullprimarykey,usernamevarchar(20)null,passward varchar(8) null)go- 创建表 Salerscreate table Salers(saleID char(3) not null primary key,salename char(8) notnull,sex char(2) default( birthday datetime女 ) null, null,hiredatedatetimenull,addresschar(60)null,telephonechar(13) null)go- 创建表 Customerscreate table Customers(customerID char(3) not null primary key,companyName char(60) not null,connectNamechar(8) null, address char(40) null,zipcode char(14) null,telephone char(13)null)- 创建表 drinkcreate table drink(drinkID char(6) not null primary key,drinkname varchar(40) not null,categoryID int null,categoryname varchar(15) null,price decimal(10,2) null)go- 创建表 orderscreate table orders(orderID char(4) not null primary key,customerID char(3) not null,saleID char(3) not null,orderdate datetime null)- 创建表 orderDetailscreatetableorderDetails(orderIDchar(4)notnull,drinkIDchar(6)notnull,quantity int null,totalprice decimal(10,2),constraint pk_orderdetails primary key (orderID,drinkID)- 创建表 storehousecreatetablestorehouse(storehouseIDchar(2)notnull primary key,addresschar(40) null)- 创建表 storetablecreatetablestoretable(storehouseIDchar(2)notnull,drinkIDchar(6)notnull,quantity int nullconstraint pk_storetable primary key (storehouseID,drinkID)- 创建表 TotalSalescreatetableTotalSales(saleIDchar(3)notnullprimarykey,totalpricedecimal(10,2)goinsert into Users values(0001,许叶飞 ,s01)insert into Users values(0002,吴纯杰 ,s02)insert into Users values(0003,张敬杰 ,s03)insert into Users values(0004,罗明孔 ,s04)goinsertintoSalersvalues(001,张颖 ,女 ,1968-12-10,1990-05-01,复兴路 200号 ,65559857)insert into Salers values(002,王伟 ,男,1969-02-10,1990-06-02,罗马花园880 号 ,65559890)insert into Salers values(003,李芳 ,女,1973-08-30,1990-03-19,茶山小区11 号 ,65557078)insert into Salers values(004,郑建杰 ,男 ,1968-09-19,1992-05-04,前门大街 15 号 ,65559091)insert into Salers values(005,赵军 ,男 ,1965-03-04,1993-10-18,学院路 78号 ,65557773)insertintoSalersvalues(006,孙力 ,男 ,1967-07-02,1991-02-09,建国路 102号 ,67756466) goinsert into Customers values(c01,三川实业有限公司 , 刘小姐 , 中山路 11号,343325,83642978)insertinto Customersvalues(c02,东 南 实 业 ,王 先 生 ,承 德 东 路 87号,342783,88683829)insertinto Customersvalues(c03,长 江 公 司 ,李 先 生 ,黄 山 西 路 89号,352786,86372973)insert into Customers values(c04,国鼎有限公司 ,徐小姐 ,天台南路 102号,897497,98372948)insert into Customers values(c05,盛大游戏公司 ,黄先生 ,西厢北路 293号,830239,88302934)insertinto Customersvalues(c06,申 通 科 技 ,何 小 姐 ,广 发 西 路 11号,293740,88392034)insertinto Customersvalues(c07,钱 江 晚 报 ,陈 先 生 ,临 江 路 293号,200392,88302994)insertinto Customersvalues(c08,天 鸿 贸 易 ,谢 小 姐 ,常 山 路 290号,768594,88392000)goinsert into drink values(f01001,可口可乐 ,1,碳酸饮料 ,3.0)insert into drink values(f01002,雪碧 ,1,碳酸饮料 ,3.0)insert into drink values(f01003,芬达 ,1,碳酸饮料 ,3.0)insert into drink values(f01004,醒目 ,1,碳酸饮料 ,3.0)insert into drink values(f01005,健怡可乐 ,1,碳酸饮料 ,3.0)insert into drink values(f01006,酷儿 ,1,碳酸饮料 ,3.5)insert into drink values(f02001,水活森 ,2,纯净水类 ,1.5)insert into drink values(f02002,冰露 ,2,纯净水类 ,1.0)insert into drink values(f03001,雀巢冰爽茶 ,3, 茶类 ,3.0)insert into drink values(f03002,茶研工坊 ,3,茶类 ,3.0)insert into drink values(f03003,原叶 ,3,茶类 ,3.0)insert into drink values(f04001,雀巢咖啡 ,4,咖啡类 ,18.0)insert into drink values(f05001,果粒橙 ,5,果汁类 ,5.8)insert into drink values(f05002,热带果粒 ,5,果汁类 ,4.5)insert into drink values(f05003,C粒柠檬 ,5,果汁类 ,4.5)insert into drink values(f05004,果粒奶优 ,5,果汁类 ,4.5)goinsert into orders values(o01,c01,001,2010-10-20)insert into orders values(o02,c01,002,2010-09-27)insert into orders values(o03,c02,004,2010-08-12)insert into orders values(o04,c02,005,2010-09-10)insert into orders values(o05,c03,006,2010-09-11)insert into orders values(o06,c03,005,2010-09-13)insert into orders values(o07,c04,002,2010-09-17)insert into orders values(o08,c04,003,2010-11-16)insert into orders values(o09,c05,003,2010-09-18)insert into orders values(o10,c05,002,2010-09-20)insert into orders values(o11,c06,006,2010-11-11)insert into orders values(o12,c06,003,2010-11-23)insert into orders values(o13,c07,002,2010-12-11)insert into orders values(o14,c07,005,2010-11-19)insert into orders values(o15,c08,004,2010-09-29)insert into orders values(o16,c08,006,2010-08-19)goinsert into orderDetails values(o01,f03001,18,0.00)insert into orderDetails values(o02,f02002,51,0.00)insert into orderDetails values(o03,f01004,25,0.00)insert into orderDetails values(o04,f02001,20,0.00)insert into orderDetails values(o05,f01005,56,0.00)insert into orderDetails values(o06,f04001,23,0.00)insert into orderDetails values(o07,f01006,11,0.00)insert into orderDetails values(o08,f05004,90,0.00)insert into orderDetails values(o09,f01001,34,0.00)insert into orderDetails values(o10,f05002,45,0.00)insert into orderDetails values(o11,f03003,41,0.00)insert into orderDetails values(o12,f05003,53,0.00)insert into orderDetails values(o13,f01003,21,0.00)insert into orderDetails values(o14,f05001,54,0.00)insert into orderDetails values(o15,f01002,61,0.00)insert into orderDetails values(o16,f03002,74,0.00)goinsert into storehouse values(s1,中山路 46 号 )goinsert into storetable values(s1,f01001,400)insert into storetable values(s1,f01002,400)insert into storetable values(s1,f01003,400)insert into storetable values(s1,f01004,400)insert into storetable values(s1,f01005,400)insert into storetable values(s1,f01006,400)insert into storetable values(s1,f02001,400)insert into storetable values(s1,f02002,400)insert into storetable values(s1,f03001,400)insert into storetable values(s1,f03002,400)insert into storetable values(s1,f03003,400)insert into storetable values(s1,f04001,400)insert into storetable values(s1,f05001,400)insert into storetable values(s1,f05002,400)insert into storetable values(s1,f05003,400)insert into storetable values(s1,f05004,400)goinsert into TotalSales values(001,0.00)insert into TotalSales values(002,0.00)insert into TotalSales values(003,0.00)insert into TotalSales values(004,0.00)insert into TotalSales values(005,0.00)insert into TotalSales values(006,0.00)go（2）创建外键约束alter table orders add constraint fk_orders_Salersforeign key (saleID) references Salers (saleID),constraint fk_orders_Customers foreign key (customerID) references Customers (customerID) onupdate cascadegoaltertableorderDetailsaddconstraintfk_orderDetails_drinkforeignkey(drinkID) referencesdrink (drinkID) on update cascadego（3）输出表中所有的信息- 查询用户信息select * from Usersgo- 查询销售员信息select * from Salersgo- 查询客户信息select * from Customersgo- 查询饮料信息select * from drinkgo- 查询订单信息select * from ordersgo- 查询订购表信息update orderDetailsset totalprice=scountfrom orderDetails,(selecttop 100 percentorderID,price*quantity scount from drink,orderDetailswheredrink.drinkID=orderDetails.drinkIDorderbyorderID)CwhereorderDetails.orderID=C.orderIDselect * from orderDetailsgo- 查询仓库信息select * from storehousego- 查询库存表信息select * from storetablego- 查询销售员总销售额信息updateTotalSalessettotalprice=ccountfromTotalSales,(selectsaleID,sum(price*quantity) ccountfromorderDetails,drink,orderswhereorderDetails.drinkID=drink.drinkIDandorderDetails.orderID=orders.orderID group by saleID)Cwhere TotalSales.saleID=C.saleIDselect * from TotalSales order by totalprice descgo（4）建立查询统计视图- 统计所有订单的总金额create view sum1asselect sum(totalprice)所有订单总金额from orderDetailsgo- 按照订单编号的查询统计总金额create view sum2asselect orderID,sum(totalprice)按照订单编号统计的总金额from orderDetails groupby orderIDgo- 按照销售员的查询统计总金额create view sum3asselect saleID,totalprice按照销售员的查询统计总金额from TotalSalesgo- 按照客户的查询统计总金额create view sum4asselect customerID,sum(totalprice)按照客户的查询统计总金额fromorderDetails,orderswhere orderDetails.orderID=orders.orderIDgroup by customerIDgo- 按照产品的查询统计总金额create view sum5asselectdrinkID,sum(totalprice)按照产品的查询统计总金额fromorderDetails groupby drinkIDgo- 按照产品分类的查询统计总金额create view sum6asselect categoryID,sum(totalprice)按照产品分类的查询统计总金额fromorderDetails,drinkwhere orderDetails.drinkID=drink.drinkID group by categoryIDgo（5）建立存储过程插入、删除、修改数据- 向 Salers 表插入数据create proc insertSalerssaleID char(3),salename char(8),sex char(2),birthday datetime,hiredate datetime,address char(60),telephone char(13)asdelete from Salers where saleID=saleIDinsert into Salers values(saleID,salename,sex,birthday,hiredate,address,telephone)go- 向 Customers 表插入数据create proc insertCustomerscustomerID char(3),companyName char(60),connectName char(8),address char(40),zipcode char(14),telephone char(13)asdelete from Customers where customerID=customerIDinsert into Customer values(customerID,companyName,connectName,address,zipcode,telephone)go- 向 drink 表插入数据create proc insertdrinkdrinkID char(6),drinkname varchar(40),categoryID int,categoryname varchar(15),price decimal(10,2)asdelete from drink where drinkID=drinkIDinsert into drink values(drinkID,drinkname,categoryID,categoryname,price)go- 向 orders 表插入数据create proc insertordersorderID char(4),customerID char(3),saleID char(3),orderdate datetimeasdelete from orders where orderID=orderIDinsert into orders values(orderID,customerID,saleID,orderdate)go- 向 orderDetails表插入数据create proc insertorderDetailsorderID char(4),drinkID char(6),quantity int,totalprice decimal(10,2)asdelete from orderDetails where orderID=orderIDinsert into orderDetails values(orderID,drinkID,quantity,totalprice)go- 从 Salers 表删除数据create proc deleteSalerssaleID char(3)asif exists(select * from Salers where saleID=saleID)beginprint 表中存在这条记录, 可以删除 delete from Salers where saleID=saleIDendelseprint 表中不存在这条记录，无法进行删除go- 从 Customers 表删除数据create proc deleteCustomerscustomerID char(3)asif exists(select * from Customers where customerID=customerID)beginprint 表中存在这条记录, 可以删除 delete from Customers where customerID=customerIDendelseprint 表中不存在这条记录，无法进行删除go- 从 drink 表删除数据create proc deletedrinksdrinkID char(6)asif exists(select * from drink where drinkID=drinkID)beginprint 表中存在这条记录, 可以删除 delete from drink where drinkID=drinkIDendelseprint 表中不存在这条记录，无法进行删除go- 从 orders 表删除数据create proc deleteordersorderID char(4)asif exists(select * from orders where orderID=orderID)beginprint 表中存在这条记录, 可以删除 delete from orders where orderID=orderIDendelseprint 表中不存在这条记录，无法进行删除go- 从 orderDetails表删除数据create proc deleteorderDetailsorderID char(4)asif exists(select * from orderDetails where orderID=orderID)beginprint 表中存在这条记录, 可以删除 delete from orderDetails where orderID=orderIDendelseprint 表中不存在这条记录，无法进行删除go- 从 Salers 表更新数据create proc updateSalerssaleID char(3),salename char(8),sex char(2),birthday datetime,hiredate datetime,address char(60),telephone char(13)asupdate Salers setsaleID=saleID,salename=salename,sex=sex,birthday=birthday,hiredate=hiredate,address=address,telephone=telephonego- 从 Customers 表更新数据create proc updateCustomers