销售管理系统数据库设计班级 ______________________ 姓名 ______________________学号 ______________________ 成绩 ______________________日期______________________销售管理系统数据库设计说明书一、数据库系统功能简介(5分):商品销售管理数据库是一个用来管理商品销售信息的数据库系统。
该数据库可以用来管理企业的商品信息、部门信息、员工信息、供应商信息、客户信息以及采购信息和销售信息,实现信息的自动化管理和利用。
二、需求分析(10分)销售管理数据库的数据流程图三、概念结构设计(10分)销售管理数据库的E-R图销售管理数据库关系模式:(1)部门(部门编号,部门名称,部门主管,备注)(2)员工(员工编号,姓名,性别,出生年月,聘任日期,工资,部门编号)(3)商品(商品编号,商品名称,单价,库存量,已销售量)(4)客户(客户编号,公司名称,联系人姓名,电话,地址,Email)(5)供应商(供应商编号,供应商名称,联系人姓名,电话,地址,Email)(6)采购订单(采购订单号,商品编号,员工号,供应商编号,订购数量,订购日期)(7)销售订单(销售订单号,商品编号,员工号,客户号,订购数量,订购日期)五、物理结构设计(5分)为销售管理数据库设计一个数据文件,一个日志文件,放在D盘的data文件夹下;销售管理数据库里面包含7个表,分别为商品、部门、员工、供应商、客户以及采购订单和销售订单;数据库和数据表的结构定义请看后面创建对象的SQL 语句。
六、数据库的实施(共65分)创建数据库各种对象和执行查询的SQL语句1.创建数据库(5分)create database销售管理on primary(name=Sales_data,filename='d:\data\Sale_data.mdf', size=10mb,maxsize=unlimited,filegrowth=10%)log on(name=Sales_log,filename='d:\data\Sale_log.ldf', size=3mb,maxsize=5mb,filegrowth=1mb)2.创建数据表并给表添加数据(20分)use销售管理gocreate table部门(部门编号int primary key,部门名称varchar(30)not null constraint UQ_DName unique,部门主管char(8),备注varchar(50))gocreate table员工(员工编号int primary key,姓名 varchar(20)not null,性别char(2)default'男'check(性别='男'or性别='女'),出生年月smalldatetime,聘任日期smalldatetime,工资money check(工资between 800 and 100000),部门编号int foreign key(部门编号)references部门(部门编号)) gocreate table供应商(供应商编号int primary key,供应商名称varchar(50)not null,联系人姓名char(8),电话char(13)check(len(电话)>=7 and len(电话)<=13),地址varchar(100),"E-mail" varchar(30)check("E-mail" like'%@%'))gocreate table客户(客户编号int primary key,公司名称varchar(50)not null,联系人姓名char(8),电话char(13)check(len(电话)>=7 and len(电话)<=13),地址varchar(100),"E-mail" varchar(30)check("E-mail" like'%@%'))gocreate table商品(商品编号int primary key,商品名称varchar(50)not null,单价decimal(8,2)check(单价>0),库存量int check(库存量>=0),已销售量int check(已销售量>=0))gocreate table采购订单(采购订单号int primary key,商品编号int references商品(商品编号),员工编号int references员工(员工编号),供应商编号int references供应商(供应商编号),订购数量int,订购日期smalldatetime default getdate())gocreate table销售订单(销售订单号int primary key,商品编号int references商品(商品编号),员工编号int references员工(员工编号),客户编号int references客户(客户编号),订购数量int,订购日期smalldatetime default getdate())表添加的数据如下图所示: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 index ix_cnameOn 客户(公司名称)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 公司名称=@客户名称执行输入参数为默认值的存储过程:exec 客户订购信息执行带输入参数的存储过程:exec 客户订购信息 '三川实业有限公司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 平均工资,max(工资) 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 员工)。