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

-销售管理系统数据库设计班级 _ _* _成绩 _日期 _ 装 订 线 考 生 答 题 不 得 超 过 此 线 销售管理系统数据库设计说明书一、数据库系统功能简介（5分）:商品销售管理数据库是一个用来管理商品销售信息的数据库系统。该数据库可以用来管理企业的商品信息、部门信息、员工信息、供应商信息、客户信息以及采购信息和销售信息，实现信息的自动化管理和利用。二、 需求分析（10分）销售管理数据库的数据流程图三、概念结构设计（10分）销售管理数据库的E-R图商品号商品名称单价库存量已售数量客户编号联系人*公司名称出生年月联系人*供应商名称供应商编号号商品员工客户供应商采购销售1mnm订购日期订购数量订购日期订购数量Email性别员工编号*奖金nmmnm工资聘任日期Email地址Email地址属于部门主管部门编号1备注部门名称部门四、逻辑结构设计（5分）销售管理数据库关系模式：（1）部门（部门编号，部门名称，部门主管，备注）（2）员工（员工编号，性别，出生年月，聘任日期，工资，部门编号）（3）商品（商品编号，商品名称，单价，库存量，已销售量） （4）客户（客户编号，公司名称，联系人，地址，Email）（5）供应商（供应商编号，供应商名称，联系人，地址，Email）（6）采购订单（采购订单号，商品编号，员工号，供应商编号，订购数量，订购日期） （7）销售订单（销售订单号，商品编号，员工号，客户号，订购数量，订购日期）五、物理结构设计（5分）为销售管理数据库设计一个数据文件，一个日志文件，放在D盘的data文件夹下；销售管理数据库里面包含7个表，分别为商品、部门、员工、供应商、客户以及采购订单和销售订单；数据库和数据表的结构定义请看后面创建对象的SQL语句。部门表列名数据类型宽度为空性说明部门编号int主关键字部门名称varchar30唯一键部门主管char8备注varchar50员工表列名数据类型宽度为空性说明员工编号int主关键字varchar20性别char2取值为男或女出生年月smalldatetime聘任日期smalldatetime工资money取值围800-100000部门编号int来自"部门表”的外键供应商表列名数据类型宽度为空性说明供应商编号int主关键字供应商名称varchar50联系人char8varchar13长度为7-13位字符地址varchar100varchar30地址包含"”客户表列名数据类型宽度为空性说明客户编号int主关键字公司名称varchar50联系人char8varchar13长度为7-13位字符地址varchar100varchar30地址包含"”商品表列名数据类型宽度为空性说明商品编号int主关键字商品名称varchar50单价Decimal(8,2)单价大于0库存量int库存量为非负数已售数量int已售数量为非负数采购订单表列名数据类型宽度为空性说明采购订单号int主关键字商品编号int来自商品表的外键员工编号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,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 员工(员工编号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()<=13), 地址varchar(100), "" varchar(30)check("" like'%')gocreatetable 客户(客户编号intprimarykey, 公司名称 varchar(50)notnull, 联系人 char(8),char(13)check(len()>=7 andlen()<=13), 地址varchar(100), "" varchar(30)check("" like'%')gocreatetable 商品(商品编号intprimarykey, 商品名称 varchar(50)notnull, 单价 decimal(8,2)check(单价>0), 库存量 intcheck(库存量>=0), 已销售量intcheck(已销售量>=0)gocreatetable 采购订单(采购订单号intprimarykey, 商品编号intreferences 商品(商品编号), 员工编号intreferences 员工(员工编号),供应商编号intreferences 供应商(供应商编号),订购数量int,订购日期smalldatetimedefaultgetdate() gocreatetable 销售订单(销售订单号intprimarykey, 商品编号intreferences 商品(商品编号), 员工编号intreferences 员工(员工编号),客户编号intreferences 客户(客户编号),订购数量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分）：在销售管理系统中，经常要按照客户名称查询信息，通过给客户表的公司名称创建一个唯一非聚集索引来提高查询速度。Create unique inde* i*_ameOn 客户(公司名称)c.创建用户定义函数（5分）：创建标量函数，根据商品名称统计*种商品销售的次数create function 销售次数(商品名称 varchar(50)='路由器')returns intbegindeclare 销售次数 intselect 销售次数=count(*)from 商品 join 销售订单on 商品.商品编号=销售订单.商品编号where 商品名称=商品名称return 销售次数end执行函数：select dbo.销售次数('鼠标') as 销售次数select dbo.销售次数(default)d. 创建存储过程（5分）: 创建一个名为客户订购信息存储过程，用于查询指定客户的联系人、联系方式、以及该公司订购产品的明细表。create proc 客户订购信息(客户名称varchar(50)='通恒机械')asselect 公司名称,联系人,商品名称,单价,订购数量,订购日期from 客户join 销售订单On 客户.客户编号=销售订单.客户编号join 商品on 商品.商品编号=销售订单.商品编号where 公司名称=客户名称执行输入参数为默认值的存储过程：e*ec 客户订购信息执行带输入参数的存储过程：e*ec 客户订购信息 '三川实业e. 创建触发器（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分）：查询"国皓科技”订购的商品信息，包括客户编号，公司名称，商品编号，商品名称，商品价格，订购数量。 select a.客户编号,公司名称,c.商品编号,商品名称,单价,订购数量 from 客户 a join 销售订单 as b on a.客户编号=b.客户编号 join 商品 as c on c.商品编号=b.商品编号 where 公司名称='国皓科技（6）嵌套查询（3分）：查询比平均工资高的员工的和工资。select ,工资 from 员工where 工资>(select avg(工资) from 员工). z.