销售管理系统数据库设计及说明书

1、-. z.销售管理系统数据库设计班级 _ _* _成绩 _日期 _ 装 订 线 考 生 答 题 不 得 超 过 此 线 销售管理系统数据库设计说明书一、数据库系统功能简介（5分）:商品销售管理数据库是一个用来管理商品销售信息的数据库系统。该数据库可以用来管理企业的商品信息、部门信息、员工信息、供应商信息、客户信息以及采购信息和销售信息，实现信息的自动化管理和利用。需求分析（10分）销售管理数据库的数据流程图三、概念结构设计（10分）销售管理数据库的E-R图商品号商品名称单价库存量已售数量客户编号联系人*公司名称出生年月联系人*供应商名称供应商编号号商品员工客户供应商采购销售1mnm订购日期订购

2、数量订购日期订购数量Email性别员工编号*奖金nmmnm工资聘任日期Email地址Email地址属于部门主管部门编号1备注部门名称部门四、逻辑结构设计（5分）销售管理数据库关系模式：（1）部门（部门编号，部门名称，部门主管，备注）（2）员工（员工编号，性别，出生年月，聘任日期，工资，部门编号）（3）商品（商品编号，商品名称，单价，库存量，已销售量） （4）客户（客户编号，公司名称，联系人，地址，Email）（5）供应商（供应商编号，供应商名称，联系人，地址，Email）（6）采购订单（采购订单号，商品编号，员工号，供应商编号，订购数量，订购日期） （7）销售订单（销售订单号，商品编号，员工号

3、，客户号，订购数量，订购日期）五、物理结构设计（5分）为销售管理数据库设计一个数据文件，一个日志文件，放在D盘的data文件夹下；销售管理数据库里面包含7个表，分别为商品、部门、员工、供应商、客户以及采购订单和销售订单；数据库和数据表的结构定义请看后面创建对象的SQL语句。部门表列名数据类型宽度为空性说明部门编号int主关键字部门名称varchar30唯一键部门主管char8备注varchar50员工表列名数据类型宽度为空性说明员工编号int主关键字varchar20性别char2取值为男或女出生年月smalldatetime聘任日期smalldatetime工资money取值围800-100

4、000部门编号int来自部门表”的外键供应商表列名数据类型宽度为空性说明供应商编号int主关键字供应商名称varchar50联系人char8varchar13长度为7-13位字符地址varchar100varchar30地址包含”客户表列名数据类型宽度为空性说明客户编号int主关键字公司名称varchar50联系人char8varchar13长度为7-13位字符地址varchar100varchar30地址包含”商品表列名数据类型宽度为空性说明商品编号int主关键字商品名称varchar50单价Decimal(8,2)单价大于0库存量int库存量为非负数已售数量int已售数量为非负数采购订单表

5、列名数据类型宽度为空性说明采购订单号int主关键字商品编号int来自商品表的外键员工编号int来自员工表的外键供应商编号int来自供应商表的外键订购数量int订购日期smalldatetime销售订单表列名数据类型宽度为空性说明销售订单号int主关键字商品编号int来自商品表的外键员工编号int来自员工表的外键客户编号int来自客户表的外键订购数量int订购日期smalldatetime六、数据库的实施（共65分）创建数据库各种对象和执行查询的SQL语句1.创建数据库（5分）createdatabase销售管理onprimary(name=Sales_data,filename=d:dataS

6、ale_data.mdf,size=10mb,ma*size=unlimited,filegrowth=10%)logon(name=Sales_log,filename=d:dataSale_log.ldf,size=3mb,ma*size=5mb,filegrowth=1mb)2.创建数据表并给表添加数据（20分）use 销售管理gocreatetable 部门(部门编号intprimarykey, 部门名称varchar(30)notnullconstraint UQ_DName unique, 部门主管char(8), 备注varchar(50)gocreatetable 员工(员工编

7、号intprimarykey,varchar(20)notnull, 性别 char(2)default男check(性别=男or 性别=女), 出生年月smalldatetime, 聘任日期smalldatetime, 工资 moneycheck(工资between 800 and 100000), 部门编号intforeignkey(部门编号)references 部门(部门编号)gocreatetable 供应商(供应商编号intprimarykey, 供应商名称 varchar(50)notnull, 联系人 char(8),char(13)check(len()=7 andlen()

8、=7 andlen()0), 库存量 intcheck(库存量=0), 已销售量intcheck(已销售量=0)gocreatetable 采购订单(采购订单号intprimarykey, 商品编号intreferences 商品(商品编号), 员工编号intreferences 员工(员工编号),供应商编号intreferences 供应商(供应商编号),订购数量int,订购日期smalldatetimedefaultgetdate() gocreatetable 销售订单(销售订单号intprimarykey, 商品编号intreferences 商品(商品编号), 员工编号intrefe

9、rences 员工(员工编号),客户编号intreferences 客户(客户编号),订购数量int,订购日期smalldatetimedefaultgetdate()表添加的数据如下图所示：3.创建数据库各种对象（共25分）a.创建试图（5分）：创建一个客户订购情况的视图，包括客户编号，公司名称，商品编号，商品名称，单价和订购数量，并对视图进行加密。create view 客户订购情况with encryptionasselect a.客户编号,公司名称,c.商品编号,商品名称,单价,订购数量 from 客户a join 销售订单as b on a.客户编号=b.客户编号 join 商品as

10、 c on c.商品编号=b.商品编号b.创建索引（5分）：在销售管理系统中，经常要按照客户名称查询信息，通过给客户表的公司名称创建一个唯一非聚集索引来提高查询速度。Create unique inde* i*_ameOn 客户(公司名称)c.创建用户定义函数（5分）：创建标量函数，根据商品名称统计*种商品销售的次数create function 销售次数(商品名称 varchar(50)=路由器)returns intbegindeclare 销售次数 intselect 销售次数=count(*)from 商品 join 销售订单on 商品.商品编号=销售订单.商品编号where 商品名称

11、=商品名称return 销售次数end执行函数：select dbo.销售次数(鼠标) as 销售次数select dbo.销售次数(default)d. 创建存储过程（5分）: 创建一个名为客户订购信息存储过程，用于查询指定客户的联系人、联系方式、以及该公司订购产品的明细表。create proc 客户订购信息(客户名称varchar(50)=通恒机械)asselect 公司名称,联系人,商品名称,单价,订购数量,订购日期from 客户join 销售订单On 客户.客户编号=销售订单.客户编号join 商品on 商品.商品编号=销售订单.商品编号where 公司名称=客户名称执行输入参数为默

12、认值的存储过程：e*ec 客户订购信息执行带输入参数的存储过程：e*ec 客户订购信息 三川实业e. 创建触发器（5分）: 创建一个触发器实现当向销售订单表添加一条记录时，自动修改商品表的库存数量和已售数量。create trigger 销售on 销售订单after insertasupdate 商品set 库存数量=库存数量-订购数量,已售数量=已售数量+订购数量from 商品join insertedon 商品.商品编号=inserted.商品编号4.执行查询（共15分）(1)简单查询（2分）：从客户表中检索所有客户的公司名称、联系人和地址。 select 公司名称,联系人,地址from

13、客户(2) 条件查询（3分）：在销售管理数据库的销售订单表中，查询员工编号为1、5和7的员工接受订单信息。 select * from 销售订单 where 员工编号in(1,5,7)（3）统计查询（2分）：统计所有员工的平均工资、最高工资、最低工资和工资总和。select avg(工资) as 平均工资,ma*(工资) as 最高工资,min(工资) as 最低工资,sum(工资) as 工资总和 from 员工（4）分组查询（3分）：查询男女员工的平均工资。select 性别,avg(工资) as 平均工资 from 员工 group by 性别（5）多表查询（2分）：查询国皓科技”订购的商品信息，包括客户编号，公司名称，商品编号，商品名称，商品价格，订购数