(完整版)2012重庆理工大学数据库毕业课程设计—某送水公司送水系统

课程设计评分细则《数据库原理》课程设计报告题目：某送水公司送水系统的数据库设计组长学号：姓名组员学号：姓名分工说明：2015年5月《数据库原理》课程设计报告目录1需求分析 .......................................................................................................................................1..1系统背景 ...........................................................................................................................1..2业务流图 ...........................................................................................................................1..3数据流图 ...........................................................................................................................1..4数据字典 ...........................................................................................................................1..4.1外部实体 .................................................................................................................1..4.2数据存储 .................................................................................................................1..4.3处理过程 .................................................................................................................1..4.4数据流 ..................................................................................................................... 2概念结构设计 ...............................................................................................................................2..1E-R图 ...............................................................................................................................2..2CDM图............................................................................................................................. 3逻辑结构设计 ...............................................................................................................................3..1PDM图 .............................................................................................................................3..2数据项 ............................................................................................................................... 4数据库的物理设计与实施 ...........................................................................................................4..1SQL脚本 .......................................................................................................................... 5学习总结 .......................................................................................................................................1需求分析1..1系统背景对于航院各宿舍人员集中，送水。

现今的社会，计算机已经成为人们处理日常事务不可缺少的一种工具，他能够把事情的复杂化转为简单化，使流程更加顺畅方便。

对于大多数的中小型送水服务公司来说，产品、价格及质量都逐渐趋同，企业若要持续发展，服务和与客户间的沟通是关键。

现在很多送水服务公司接话时都要询问客户编号、地址和姓名等诸多客户资料，从而导致接电话需要很长时间，而且容易造成客户不满意，接话员也累，效率又低。

在用水旺季电话较多时由于接话员效率低，不可避免的会发生漏接电话等情况，失去一些定水客户。

在这种情况下，拥有一个专业的针对送水行业上述问题的软件系统就成为同行业中竞争发展的有利优势。

另外，目前市场上这方面的软件系统使用范围比较有限，就算有使用的也很少涉及送水配送业务。

目前大多数公司现行的管理系统虽然解决了公司业务上的处理问题，但在员工工作效率、客户信息管理、信息查询分析等方面不完善，需要进一步的优化系统处理业务流程和解决公司以往单人操作系统处理烦琐事务等问题。

因此，该系统有着广阔的市场前景的同时，还可以提高企业运营效率，创造更多的社会价值。

本系统需完成的功能：工作人员、客户的管理、矿泉水的类别（信息）、供应商的信息矿泉水入库、出库、收费以及空桶管理图1.1 系统功能图1..2业务流图图1.1 送水工送水业务流图图1.2 送水公司进够桶装水图1.3 送水工登陆可操作业务流图1..3数据流图图1.4 送水公司在供应商购水数据流图图1.5 送水公司存储矿泉水在仓库数据流图图1.6 客户购水、送水工送水、回收空桶、收费数据流图1图1.7 客户购水、送水工送水、回收空桶、收费数据流图21..4数据字典1..4.1 外部实体表1.1 外部实体1..4.2 数据存储表1.3 数据存储1..4.3 处理过程表1.4 处理过程1..4.4 数据流表1.5 数据流2概念结构设计2..1E-R图图 2.1 E-R 图2..2CDM图图2.2 CDM图3逻辑结构设计3..1PDM图图3.1 PDM图3..2数据项表3.1 送水公司员工表表3.2 矿泉水分类表表3.3 客户表表3.4 仓库分类表表3.5 供应商表表3.6 出售详情表表3.7 矿泉水空桶入库表表3.8 购进表4数据库的物理设计与实施4..1SQL脚本*==============================================================* * DBMS name: Microsoft SQL Server 2000 ** Created on: 15:26:33 **==============================================================* alter table buy_fromproviderdrop constraint FK_BUY_FROM_BUY_FROMP_PROVIDERgoalter table buy_fromproviderdrop constraint FK_ASSOCIAT_ASSOCIATI_矿泉水3goalter table buy_fromproviderdrop constraint FK_BUY_FROM_BUY_FROMP_WORKERgoalter table buy_senddrop constraint FK_BUY_SEND_BUY_SEND_CUSTOMgoalter table buy_senddrop constraint FK_ASSOCIAT_ASSOCIATI_矿泉水2goalter table buy_senddrop constraint FK_ASSOCIAT_ASSOCIATI_送水公司员工2 goalter table store_waterdrop constraint FK_STORE_W A_STORE_W AT_STOREgoalter table store_waterdrop constraint FK_STORE_W A_STORE_W AT_WATERgoif exists (select 1from sysindexeswhere id = object_id('buy_fromprovider')and name = 'Association_2_FK'and indid > 0and indid < 255)drop index buy_fromprovider.Association_2_FKgoif exists (select 1from sysindexeswhere id = object_id('buy_fromprovider')and name = 'Association_2_FK2'and indid > 0and indid < 255)drop index buy_fromprovider.Association_2_FK2goif exists (select 1from sysindexeswhere id = object_id('buy_fromprovider')and name = 'Association_2_FK3'and indid > 0and indid < 255)drop index buy_fromprovider.Association_2_FK3goif exists (select 1from sysindexeswhere id = object_id('buy_send')and name = 'Association_1_FK'and indid > 0and indid < 255)drop index buy_send.Association_1_FKgoif exists (select 1from sysindexeswhere id = object_id('buy_send')and name = 'Association_1_FK2'and indid > 0and indid < 255)drop index buy_send.Association_1_FK2goif exists (select 1from sysindexeswhere id = object_id('buy_send')and name = 'Association_1_FK3'and indid > 0and indid < 255)drop index buy_send.Association_1_FK3goif exists (select 1from sysindexeswhere id = object_id('store_water')and name = 'Association_3_FK'and indid > 0and indid < 255)drop index store_water.Association_3_FKgoif exists (select 1from sysindexeswhere id = object_id('store_water')and name = 'Association_3_FK2'and indid > 0and indid < 255)drop index store_water.Association_3_FK2goif exists (select 1from sysobjectswhere id = object_id('buy_fromprovider')and type = 'U')drop table buy_fromprovidergoif exists (select 1from sysobjectswhere id = object_id('buy_send')and type = 'U')drop table buy_sendgoif exists (select 1from sysobjectswhere id = object_id('custom')and type = 'U')drop table customgoif exists (select 1from sysobjectswhere id = object_id('provider')and type = 'U')drop table providergoif exists (select 1from sysobjectswhere id = object_id('store')and type = 'U')drop table storegoif exists (select 1from sysobjectswhere id = object_id('store_water')and type = 'U')drop table store_watergoif exists (select 1from sysobjectswhere id = object_id('water')and type = 'U')drop table watergoif exists (select 1from sysobjectswhere id = object_id('worker')and type = 'U')drop table workergo*==============================================================* * Table: buy_fromprovider **==============================================================* create table buy_fromprovider (workerid int not null,providerid int not null,id int not null,num int null,in_price decimal null,in_time datetime null,receivenum int null,constraint PK_BUY_FROMPROVIDER primary key (workerid, providerid, id) )go*==============================================================* * Index: Association_2_FK **==============================================================* create index Association_2_FK on buy_fromprovider (workerid ASC)go*==============================================================* * Index: Association_2_FK2 **==============================================================*create index Association_2_FK2 on buy_fromprovider (providerid ASC)go*==============================================================* * Index: Association_2_FK3 **==============================================================* create index Association_2_FK3 on buy_fromprovider (id ASC)go*==============================================================* * Table: buy_send **==============================================================* create table buy_send (workerid int not null,customerid int not null,id int not null,waternum int null,buynumber int null,回收桶数量int null,isfee char(1) null,sendtime datetime null,sendtype varchar(50) null,money decimal null,constraint PK_BUY_SEND primary key (workerid, customerid, id))go*==============================================================* * Index: Association_1_FK **==============================================================* create index Association_1_FK on buy_send (workerid ASC)go*==============================================================* * Index: Association_1_FK2 **==============================================================* create index Association_1_FK2 on buy_send (customerid ASC)go*==============================================================* * Index: Association_1_FK3 **==============================================================* create index Association_1_FK3 on buy_send (id ASC)go*==============================================================** Table: custom **==============================================================* create table custom (customerid int not null,customername varchar(50) null,customertelphone varchar(12) null,customeraddress varchar(200) null,comment varchar(200) null,constraint PK_CUSTOM primary key nonclustered (customerid))go*==============================================================* * Table: provider **==============================================================* create table provider (providerid int not null,providername varchar(50) null,providerphone varchar(12) null,constraint PK_PROVIDER primary key nonclustered (providerid))go*==============================================================* * Table: store **==============================================================* create table store (storeid int not null,storename varchar(50) null,storenum int null,constraint PK_STORE primary key nonclustered (storeid))go*==============================================================* * Table: store_water **==============================================================* create table store_water (id int not null,storeid int not null,storenum int null,storetime datetime null,constraint PK_STORE_W ATER primary key (id, storeid))go*==============================================================* * Index: Association_3_FK **==============================================================* create index Association_3_FK on store_water (id ASC)go*==============================================================** Index: Association_3_FK2 **==============================================================* create index Association_3_FK2 on store_water (storeid ASC)go*==============================================================* * Table: water **==============================================================* create table water (id int not null,name varchar(100) null,price decimal null,constraint PK_W ATER primary key nonclustered (id))go*==============================================================* * Table: worker **==============================================================* create table worker (workerid int not null,workername varchar(50) null,workersex char(1) null,workerage int null,workertelphone varchar(12) null,comment varchar(200) null,workertype varchar(50) null,constraint PK_WORKER primary key nonclustered (workerid))goalter table buy_fromprovideradd constraint FK_BUY_FROM_BUY_FROMP_PROVIDER foreign key (providerid)references provider (providerid)goalter table buy_fromprovideradd constraint FK_ASSOCIAT_ASSOCIATI_矿泉水3 foreign key (id) references water (id)goalter table buy_fromprovideradd constraint FK_BUY_FROM_BUY_FROMP_WORKER foreign key (workerid)references worker (workerid)goalter table buy_sendadd constraint FK_BUY_SEND_BUY_SEND_CUSTOM foreign key (customerid) references custom (customerid)goalter table buy_sendadd constraint FK_ASSOCIAT_ASSOCIATI_矿泉水2 foreign key (id)references water (id)goalter table buy_sendadd constraint FK_ASSOCIAT_ASSOCIATI_送水公司员工 2 foreign key (workerid)references worker (workerid)goalter table store_wateradd constraint FK_STORE_W A_STORE_W AT_STORE foreign key (storeid) references store (storeid)goalter table store_wateradd constraint FK_STORE_W A_STORE_W AT_WATER foreign key (id) references water (id)go5学习总结经过一周的数据库课程设计，我觉得我们挺不错的，虽然不敢说做得很好，但是都是我们自己认真的按照之前学习到知识来做的。