服装销售系统数据库设计.doc

服装销售系统数据库设计

一：需求描述
某服装连锁店要开发一套服装销售管理软件，对日常的服装销售进行信息化管理，要求该软件实现服装销售行业的采购订货、退货、前台零售、批发业务、促销管理、会员管理、库存管理、库存盘点等各个业务流程，把服装销售行业的进货、退货、销售、库存、财务等业务实现一体化管理。
开发该软件有两大部分工作要做：第一后台数据库的设计；第二前端界面功能开发。现阶段我们要完成后台数据库的设计。为了方便起见，我们实现对服装销售核心业务“销售管理”子模块的设计。该模块实现“服装的基础信息维护、服装销售、常见查询、利润统计、销售冠军排名”等功能。

二：开发环境
开发工具：SQL Server2005 企业版。
三：技能点
（1）、在SQL Server中创建库、建表、建约束。
（2）、INSERT、UPDATE、DELETE、SELECT语句。
（3）、统计函数：如SUM、COUNT、AVG等。
（4）、多表关联查询。
（5）、GROUP By分组统计。
四：问题分析
为了实现服装销售管理，需要建立数据库及数据表对数据进行有效存储。经过分析，涉及到的数据表至少有服装基础表和服装销售表。以下为服装基础表和服装销售表。
1、 服装基础表(clBaseInfo)

字段名称
数据类型
描述
SectionCode
varchar(5)
款号，服装款型编号，必填项。只能是5位字符，并且前两为是“MC”。如“MC201”。
BarCode
char(8)
服装条形码，服装的唯一标识，主键。规则“款号+3位流水号”，一款服装可能会有不同颜色或尺码。
clName
varchar(100)
服装名称，必添项。
Type
varchar(20)
服装类别。如男鞋、女鞋、上衣、裤子。
Brand
varchar(100)
品牌，服装的品牌信息。
Fabric
varchar(20)
面料。如“纯棉、皮、革、”默认“纯棉”
clSize
varchar(20)
尺码。只能是“L、S、M、XL”中的一种，因为L号较为常见，默认为“L”。
Color
varchar(50)
颜色
SalesPrice
float
销售单价
InPrice
float
进货价格
表3.1 服装表(clBaseInfo)
2、 服装销售表(clSales)
字段名称
数据类型
描述
ID
int
标识列，主键。
ShopName
varchar(100)
连锁店名，必填项。默认“人百店”
SalesMan
varchar(30)
销售员姓名
SalesDate
datetime
销售日期。默认为系统当天。
SalesCode
varchar(10)
销售单号，必填项。长度是10位字符串。且前4位为“2010”，中间2位为分店简码，后4位为数字型流水号。
BarCode
char(8)
服装条形码。外键。
SalesCount
int
销售数量，必填项。数值范围大于0.
Rebeat
float
折扣，默认为1，代表不打折。
payType
varchar(20)
付款方式：可以是“现金、银行卡、信誉卡、代金券”，默认“现金”
Total
money
实收金额
表3.2 服装销售表(clSales)

五：任务实现
1、 创建数据库
在本机D盘新建文件夹SalesProject，在SQL Server Managerment Studio中按要求创建数据库SalesDB，保存在SalesProject文件夹下。主数据库文件增长率为12%，日志文件增长率为5%，并且日志文件最大为50M。
2、 创建数据表
（1）、在数据库中创建服装基础表clBaseInfo，逐一设置字段名、字段长度、数据类型
等。
（2）、在数据库中创建服装销售表clSales。
3、 增加约束
根据表3.1说明部分要求，设置各列约束：
（1）、必填项，表明该字段应添加非空约束。
（2）、服装条形码，为主键列，应增加主键约束。
（3）、有默认值的应增加默认约束。
（4）、款号列应配合LIKE关键字增加检查约束。
根据表3.2说明部分要求，设置各列约束：
（1）、ID增加标识列约束，标识种子为1，标识增量为1；并设置主键约束。
（2）、ShopName、SalesDate、payType列应增加默认约束。
（3）、SalesCode、SalesCount列增加检查约束。
（4）、BarCode设置外键约束，对应主表clBaseInfo中BarCode主键列。
（5）、BarCode设置外键约束，对应主表clBaseInfo中BarCode主键列。
4、 建立表间关系
两表通过BarCode列建立表间关系。
5、 插入测试数据
编写INSERT INTO…语句向clBaseInfo和clSales表插入测试数据，测试数据分别如3.3和3.4所示。

SectionCode
BarCode
clName
Type
Brand
Fabric
clSize
Color
InPrice
SalesPrice
MC201
MC201001
长袖衫
衬衫
雅戈尔
纯棉
XL
白色
105
180
MC201
MC201002
长袖衫
衬衫
雅戈尔
纯棉
L
红色条纹
68
102
MCK15
MCK15001
休闲夹克
夹克
苹果
涤纶
XL
黑色
400
600
MCK15
MCK15002
休闲夹克
夹克
苹果
涤纶
S
褐色
400
600
MCBZ0
MCBZ0001
连衣裙
女裙
宝姿
丝绸
M
灰色
60
120
MCBZ1
MCBZ1001
连衣裙
女裙
宝姿
纯棉
L
白色
70
140
MCON1
MCON1001
牛仔裤
女裤
ONLY
涤纶
XL
蓝色
98
198
表3.3 服装基础表测试数据

ShopName
SalesMan
SalesDate
SalesCode
BarCode
SalesCount
Rebeat
payType
Total
中山店
李晓娜
2010-01-02
20100001
MCBZ0001
1
1
现金
120
中山店
郝晓英
2010-01-02
20100002
MCBZ1001
2
0.9
信誉卡
214
新华店
李晓
2010-01-03
20100003
MCK15002
1
1
现金
600
中山店
李晓娜
2010-01-02
20100004
MC201001
3
0.8
银行卡
432
人百店
东林
2010-01-02
20100005
MC201002
2
1
现金
204
人百店
米晓
2010-01-21
20100006
MCK15001
10
0.6
现金
3600
中山店
郝晓英
2010-02-06
20100007
MC201001
1
0.8
银行卡
144
新华店
李晓
2010-02-15
20100008
MCBZ1001
2
1
现金
280
中山店
李晓娜
2010-03-01
20100009
MCK15002
1
1
代金券
102
表3.4 服装销售测试数据

6、 查询数据
(1) 查询2个表中插入的数据是否正确.
执行效果如图3.1和3.2所示：

图3.1 服装基本信息表

图3.2 服装销售记录表
（2）查询品牌是"雅戈尔"的服装基本信息。

（3）查询单笔销售数量为3件以上的销售人员，只显示姓名。要求姓名列不能出
现重复信息。

（4）查询2010年1月份，销售信息，要求显示分店名、销售员、销售日期、销
售额。
（5）统计2010年1月的总销售额。

（6）统计条款号为‘MCK15’的服装各销售员的总销售额,并按销售额降序显示。

（7）统计2010年1季度各分店的销售情况，要求显示分店名、销售额。
（8）显示2010年1季度总销售冠军，要求显示分店名,销售员，销售额。

（9）查询各种服装销售的详细信息要求显示店名、销售人、服装名称、品牌、销售
日期、销售金额,并按分店名称排序。
（10）统计各类服装销售数量，要求显示服装名称、销售数量,并按销售数量升序排
序，如果某些服装没有销售过，销售数量显示NULL。

统计结果如图3.3所示：

图3.3 统计结果
（11）统计各类服装销售数量，要求显示服装名称、销售数量,并按销售数量升序排
序，如果某些面料的服装没有销售过，销售数量显示NULL。
（12）统计尺码为“L”号的服装销售数量。

7、 修改数据
将条码号为'MCBZ0001'的服装，面料由'丝绸'改成'针织'。

8、 索引
送货方式和付款方式两个表都只有几行数据，因此，无需创建索引。
订单表和订单明细表数据量将会比较大，且对它们的查询也比较多，应该考虑适当创建索引。经分析，在订单表上，基于订单状态和订单提交时间的查询会较多，因此，在这两列上分别创建索引，命名为order_state_idx和order_time_idx。在订单明细表上，基于订单号和手机号的查询会较多，因此，在这两列上分别创建索引，并分别命名为orderdet_orderid_idx和orderdet_mobid_idx。

9、 存储过程、函数
1) 插入订单表时，必须同时插入订单明细，两个动作在一个事务里
2) 删除送货方式时，必须检查订单表里是否使用，未使用才可以删除
3) 在用户提交订单时，要求系统自动产生一个20位长的包含年月日时分秒信息的字符串，为了实现这一功能，可以设计一个函数，当向订单表中插入数据时调用该函数
4) 为了显示某个订单的详细信息，即订单明细，需要根据订单编号进行显示，为完成此功能，设计一个存储过程，以订单编号作输入参数，动态游标作输出参数，返回某个订单的明细信息
10、 触发器
1) 创建订单表上的UPDATE触发器
2) 当订单完成时，订单的状态需要被修改为3，同时应该自动为会员增加积分，利用带条件的UPDATE触发器完成此功能。
11、 创建登录帐号和分配权限
数据库用户分为销售员和采购员，进货员负责采购服装并将服装信息录入到clBaseInfo表中，销售员负责销售，并将销售信息保存到clSales表中。不同角色人员负责不同工作，不同角色对不同的表有不同的操作权限，具体权限如下：
采购员：对服装基本信息表clBaseInfo具备增、删、改、查的权限，而对服装销售表
clSales表只能查看，不能修改。
销售员：对服装销售表clSales表具备增、删、改、查的权限，而对服装基本信息表只
能查看，不能修改。
为此需要在SQL Server2005中分别创建登录账户，并分配相应的权限，步骤为：
（1） 创建登录账户，采购员登录名为：Buyers；销售员登录名为：SalesPerson。

图 3.4 创建登录账户
（2） 赋予相应的权限。

图 3.5 给用户设置权限
（3） 测试权限是否正确。
1、使用SalesPerson用户登录SQL Server。

图 3.6 登录SQL Server2005
2、SalesPerson用户无权对clBaseInfo进行更新操作

图3.7 无权限操作
12、 导出数据
在D盘文件夹SalesProject下新建SalesDBExcel文件，将clBaseInfo和clSales两
表的数据导出到此Excel文件中。导出完成后，查看导出数据是否正确。

13、 导出脚本
需要将clBaseInfo和clSales两个表的表结构导出生成脚本文件，并将脚本文件命
名为“SalesDB.sql”保存到D:\SalesProject文件夹下。生成的脚本文件中，全部都是
T-SQL的DDL代码，关于DDL(数据定义语言)将在下学期进行深入学习。

图3.8 生成脚本
14、 备份数据库
使用SQL Server2005向导，备份SalesDB数据库，备份文件命名为
“SalesDBBack.bak”并保存在D:\SalesProject文件夹下。
最后将D:\SalesProject文件夹提交到老师所指定的服务器指定目录中。

六：课时安排
1、 需求讲解(15分钟)
学员阅读项目需求，对项目整体要求做到心中有数。
老师讲解需求。
2、 任务实现(165分钟)
建库、建表、增加约束、建立表间关系。(30分钟)
编写SQL语句插入数据，查询数据、修改数据。(100分钟)
创建登录账户、测试权限、导出数据、生成脚本、备份数据库。(35分钟)
3、 项目总结(20分钟)