饮料厂销售管理系统.docx

数据库原理课程设计

题 目 饮料厂销售管理系统
学 院 商学院
专 业 信息管理和信息系统
班 级 信息082
学 号 200853275237
学生姓名 许叶飞
指导教师 张细香
编写日期 2011月1月5日
目 录
1 系统需求分析......................................................1
1.1 系统调查......................................................1
1.2用户需求...................................................1
2 系统总体结构设计..................................................2
2.1软件系统总体结构设计...........................................2
2.2数据存储的总体设计............................................ 2
2.3系统功能模块设计.............................................. 2
3 数据库详细设计........................................3
3.1数据库设计....................................................3
3.11 E-R图设计....................................................3
3.12 逻辑结构设计.................................................5
3.2数据库的完整性和安全性........................................8
4数据库操作总代码（SQL Server2000语句完成）.9
5 部分功能模块运行结果.............................................29
5.1建立存储过程实现插入、删除、修改数据..........................29
5.2 建立存储过程实现单表查询，连接查询，嵌套查询和集合查询........30
6 课程设计心得.....................................................31
参考文献............................................32

1 系统需求分析
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图如下：

部分E－R图如下：
(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 drinksales
go
use drinksales
go
--创建表Users
create table Users(userID varchar(8) not null primary key,username varchar(20) null,passward varchar(8) null)
go
--创建表Salers
create table Salers(saleID char(3) not null primary key,salename char(8) not null,sex char(2) default('女') null,
birthday datetime null,hiredate datetime null,address char(60) null,telephone char(13) null)
go
--创建表Customers
create table Customers(customerID char(3) not null primary key,companyName char(60) not nullconnectName
char(8) null, address char(40) null,zipcode char(14) null,telephone char(13) null)
--创建表drink
create 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
--创建表orders
create table orders(orderID char(4) not null primary key,
customerID char(3) not null,saleID char(3) not null,
orderdate datetime null)
--创建表orderDetails
create table orderDetails(orderID char(4) not null,drinkID char(6) not null,quantity int null,totalprice decimal(10,2),
constraint pk_orderdetails primary key (orderID,drinkID))
--创建表storehouse
create table storehouse(storehouseID char(2) not null primary key,address char(40) null)
--创建表storetable
create table storetable(storehouseID char(2) not null,drinkID char(6) not null,quantity int null
constraint pk_storetable primary key (storehouseID,drinkID))
--创建表TotalSales
create table TotalSales(saleID char(3) not null primary key,totalprice decimal(10,2))
go
insert into Users values('0001','许叶飞','s01')
insert into Users values('0002','吴纯杰','s02')
insert into Users values('0003','张敬杰','s03')
insert into Users values('0004','罗明孔''s04')
go
insert into Salers values('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')
insert into Salers values('006','孙力','男',1967-07-02,1991-02-09,'建国路102号','67756466')
go
insert into Customers values('c01','三川实业有限公司','刘小姐','中山路11号','343325','83642978')
insert into Customers values('c02','东南实业','王先生','承德东路87号','342783','88683829')
insert into Customers values('c03','长江公司','李先生','黄山西路89号','352786','86372973')
insert into Customers values('c04','国鼎有限公司','徐小姐','天台南路102号','897497','98372948')
insert into Customers values('c05','盛大游戏公司','黄先生','西厢北路293号','830239','88302934')
insert into Customers values('c06','申通科技','何小姐','广发西路11号','293740','88392034')
insert into Customers values('c07','钱江晚报','陈先生','临江路293号','200392','88302994')
insert into Customers values('c08','天鸿贸易','谢小姐','常山路290号','768594','88392000')
go
insert 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)
go
insert 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-0913)
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)
go
insert 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)
go
insert into storehouse values('s1','中山路46号')
go
insert 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)
go
insert 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_Salers
foreign key (saleID) references Salers (saleID),
constraint fk_orders_Customers foreign key (customerID) references Customers (customerID) on
update cascade
go
alter table orderDetails add constraint fk_orderDetails_drink foreign key (drinkID) references
drink (drinkID) on update cascade
go
（3）输出表中所有的信息
--查询用户信息
select * from Users
go
--查询销售员信息
select * from Salers
go
--查询客户信息
select * from Customers
go
--查询饮料信息
select * from drink
go
--查询订单信息
select * from orders
go
--查询订购表信息
update orderDetails set totalprice=scount from orderDetails,(select top 100 percent orderID,price*quantity scount from drink,orderDetails
where drink.drinkID=orderDetails.drinkID order by orderID)C where orderDetails.orderID=C.orderID
select * from orderDetails
go
--查询仓库信息
select * from storehouse
go
--查询库存表信息
select * from storetable
go
--查询销售员总销售额信息
update TotalSales set totalprice=ccount from TotalSales,(select saleID,sum(price*quantity) ccount
from orderDetails,drink,orders where orderDetails.drinkID=drink.drinkID and orderDetails.orderID=orders.orderID group by saleID)C
where TotalSales.saleID=C.saleID
select * from TotalSales order by totalprice desc
go
（4）建立查询统计视图
--统计所有订单的总金额
create view sum1
as
select sum(totalprice) 所有订单总金额 from orderDetails
go
--按照订单编号的查询统计总金额
create view sum2
as
select orderID,sum(totalprice) 按照订单编号统计的总金额 from orderDetails group by orderID
go
--按照销售员的查询统计总金额
create view sum3
as
select saleID,totalprice 按照销售员的查询统计总金额 from TotalSales
go
--按照客户的查询统计总金额
create view sum4
as
select customerID,sum(totalprice) 按照客户的查询统计总金额 from orderDetails,orders where orderDetails.orderID=orders.orderID group by customerID
go
--按照产品的查询统计总金额
create view sum5
as
select drinkID,sum(totalprice) 按照产品的查询统计总金额 from orderDetails group by drinkID
go
--按照产品分类的查询统计总金额
create view sum6
as
select categoryID,sum(totalprice) 按照产品分类的查询统计总金额 from orderDetails,drink
where orderDetails.drinkID=drink.drinkID group by categoryID
go
（5）建立存储过程插入、删除、修改数据
--向Salers表插入数据
create proc insertSalers
@saleID char(3),
@salename char(8),
@sex char(2),
@birthday datetime,
@hiredate datetime,
@address char(60),
@telephone char(13)
as
delete from Salers where saleID=@saleID
insert into Salers values(@saleID,@salename,
@sex,@birthday,@hiredate,@address,
@telephone)
go
--向Customers表插入数据
create proc insertCustomers
@customerID char(3),
@companyName char(60),
@connectName char(8),
@address char(40),
@zipcode char(14),
@telephone char(13)
as
delete from Customers where customerID=
@customerID
insert into Customer values(@customerID,@companyName,@connectName,
@address,@zipcode,@telephone)
go
--向drink表插入数据
create proc insertdrink
@drinkID char(6),
@drinkname varchar(40),
@categoryID int,
@categoryname varchar(15),
@price decimal(10,2)
as
delete from drink where drinkID=@drinkID
insert into drink values(@drinkID,@drinkname,
@categoryID,@categoryname,@price)
go
--向orders表插入数据
create proc insertorders
@orderID ch