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

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

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

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

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

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

6、growth=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 员工(员工编号intprimarykey,varchar(20)notnull, 性别 char(2)de

7、fault男check(性别=男or 性别=女), 出生年月smalldatetime, 聘任日期smalldatetime, 工资 moneycheck(工资between 800 and 100000), 部门编号intforeignkey(部门编号)references 部门(部门编号)gocreatetable 供给商(供给商编号intprimarykey, 供给商名称 varchar(50)notnull, 联系人 char(8),char(13)check(len()=7 andlen()=7 andlen()0), 库存量 intcheck(库存量=0), 已销售量intchec

8、k(已销售量=0)gocreatetable 采购订单(采购订单号intprimarykey, 商品编号intreferences 商品(商品编号), 员工编号intreferences 员工(员工编号),供给商编号intreferences 供给商(供给商编号),订购数量int,订购日期smalldatetimedefaultgetdate() gocreatetable 销售订单(销售订单号intprimarykey, 商品编号intreferences 商品(商品编号), 员工编号intreferences 员工(员工编号),客户编号intreferences 客户(客户编号),订购数量

9、int,订购日期smalldatetimedefaultgetdate()表添加的数据如下列图所示：3.创立数据库各种对象共25分a.创立试图5分：创立一个客户订购情况的视图，包括客户编号，公司名称，商品编号，商品名称，单价和订购数量，并对视图进展加密。create view 客户订购情况with encryptionasselect a.客户编号,公司名称,c.商品编号,商品名称,单价,订购数量 from 客户a join 销售订单as b on a.客户编号=b.客户编号 join 商品as c on c.商品编号=b.商品编号b.创立索引5分：在销售管理系统中，经常要按照客户名称查询信息

10、，通过给客户表的公司名称创立一个唯一非聚集索引来提高查询速度。Create unique inde* i*_ameOn 客户(公司名称)c.创立用户定义函数5分：创立标量函数，根据商品名称统计*种商品销售的次数create function 销售次数(商品名称 varchar(50)=路由器)returns intbegindeclare 销售次数 intselect 销售次数=count(*)from 商品 join 销售订单on 商品.商品编号=销售订单.商品编号where 商品名称=商品名称return 销售次数end执行函数：select dbo.销售次数(鼠标) as 销售次数sel

11、ect dbo.销售次数(default)d. 创立存储过程5分: 创立一个名为客户订购信息存储过程，用于查询指定客户的联系人、联系方式、以及该公司订购产品的明细表。create proc 客户订购信息(客户名称varchar(50)=通恒机械)asselect 公司名称,联系人,商品名称,单价,订购数量,订购日期from 客户join 销售订单On 客户.客户编号=销售订单.客户编号join 商品on 商品.商品编号=销售订单.商品编号where 公司名称=客户名称执行输入参数为默认值的存储过程：e*ec 客户订购信息执行带输入参数的存储过程：e*ec 客户订购信息 三川实业e. 创立触发器

12、5分: 创立一个触发器实现当向销售订单表添加一条记录时，自动修改商品表的库存数量和已售数量。create trigger 销售on 销售订单after insertasupdate 商品set 库存数量=库存数量-订购数量,已售数量=已售数量+订购数量from 商品join insertedon 商品.商品编号=inserted.商品编号4.执行查询共15分(1)简单查询2分：从客户表中检索所有客户的公司名称、联系人和地址。 select 公司名称,联系人,地址from 客户(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分：查询国皓科技订购的商品信息，包括客户编号，公司名称，商品编号，商品名称，商品价格，订购数