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

销售管理系统数据库设计班级______________________姓名______________________学号______________________成绩______________________日期______________________装订线考生答题不得超过此线销售管理系统数据库设计说明书一、数据库系统功能简介（5分）:商品销售管理数据库是一个用来管理商品销售信息的数据库系统。该数据库可以用来管理企业的商品信息、部门信息、员工信息、供应商信息、客户信息以及采购信息和销售信息，实现信息的自动化管理和利用。二、需求分析（10分）销售管理数据库的数据流程图三、概念结构设计（10分）销售管理数据库的E-R图四、逻辑结构设计（5分）销售管理数据库关系模式：（1）部门（部门编号，部门名称，部门主管，备注）（2）员工（员工编号，姓名，性别，出生年月，聘任日期，工资，部门编号）（3）商品（商品编号，商品名称，单价，库存量，已销售量）（4）客户（客户编号，公司名称，联系人姓名，电话，地址，Email）（5）供应商（供应商编号，供应商名称，联系人姓名，电话，地址，Email）（6）采购订单（采购订单号，商品编号，员工号，供应商编号，订购数量，订购日期）（7）销售订单（销售订单号，商品编号，员工号，客户号，订购数量，订购日商品员工客户供应商采购销售1mnm订购日期订购数量订购日期订购数量Email性别员工编号姓名奖金nmmn商品号商品名称单价库存量已售数量供应商编号号供应商名称联系人姓名电话地址Email出生年月聘任日期工资部门名称部门主管公司名称联系人姓名电话地址客户编号Email属于部门部门编号备注1m期）五、物理结构设计（5分）为销售管理数据库设计一个数据文件，一个日志文件，放在D盘的data文件夹下；销售管理数据库里面包含7个表，分别为商品、部门、员工、供应商、客户以及采购订单和销售订单；数据库和数据表的结构定义请看后面创建对象的SQL语句。部门表列名数据类型宽度为空性说明部门编号int主关键字部门名称varchar30唯一键部门主管char8√备注varchar50√员工表列名数据类型宽度为空性说明员工编号int主关键字姓名varchar20性别char2√取值为男或女出生年月smalldatetime√聘任日期smalldatetime√工资money√取值范围800-100000部门编号int√来自“部门表”的外键供应商表列名数据类型宽度为空性说明供应商编号int主关键字供应商名称varchar50联系人姓名char8√电话varchar13√电话长度为7-13位字符地址varchar100√E-mailvarchar30√E-mail地址包含“@”客户表列名数据类型宽度为空性说明客户编号int主关键字公司名称varchar50联系人姓名char8√电话varchar13√电话长度为7-13位字符地址varchar100√E-mailvarchar30√E-mail地址包含“@”商品表列名数据类型宽度为空性说明商品编号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:\data\Sale_data.mdf',size=10mb,maxsize=unlimited,filegrowth=10%)logon(name=Sales_log,filename='d:\data\Sale_log.ldf',size=3mb,maxsize=5mb,filegrowth=1mb)2.创建数据表并给表添加数据（20分）use销售管理gocreatetable部门(部门编号intprimarykey,部门名称varchar(30)notnullconstraintUQ_DNameunique,部门主管char(8),备注varchar(50))gocreatetable员工(员工编号intprimarykey,姓名varchar(20)notnull,性别char(2)default'男'check(性别='男'or性别='女'),出生年月smalldatetime,聘任日期smalldatetime,工资moneycheck(工资between800and100000),部门编号intforeignkey(部门编号)references部门(部门编号))gocreatetable供应商(供应商编号intprimarykey,供应商名称varchar(50)notnull,联系人姓名char(8),电话char(13)check(len(电话)=7andlen(电话)=13),地址varchar(100),E-mailvarchar(30)check(E-maillike'%@%'))gocreatetable客户(客户编号intprimarykey,公司名称varchar(50)notnull,联系人姓名char(8),电话char(13)check(len(电话)=7andlen(电话)=13),地址varchar(100),E-mailvarchar(30)check(E-maillike'%@%'))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分）：创建一个客户订购情况的视图，包括客户编号，公司名称，商品编号，商品名称，单价和订购数量，并对视图进行加密。createview客户订购情况withencryptionasselecta.客户编号,公司名称,c.商品编号,商品名称,单价,订购数量from客户ajoin销售订单asbona.客户编号=b.客户编号join商品asconc.商品编号=b.商品编号b.创建索引（5分）：在销售管理系统中，经常要按照客户名称查询信息，通过给客户表的公司名称创建一个唯一非聚集索引来提高查询速度。Createuniqueindexix_cnameOn客户(公司名称)c.创建用户定义函数（5分）：创建标量函数，根据商品名称统计某种商品销售的次数createfunction销售次数(@商品名称varchar(50)='路由器')returnsintbegindeclare@销售次数intselect@销售次数=count(*)from商品join销售订单on商品.商品编号=销售订单.商品编号where商品名称=@商品名称return@销售次数end执行函数：selectdbo.销售次数('鼠标')as销售次数selectdbo.销售次数(default)d.创建存储过程（5分）:创建一个名为客户订购信息存储过程，用于查询指定客户的联系人姓名、联系方式、以及该公司订购产品的明细表。createproc客户订购信息(@客户名称varchar(50)='通恒机械有限公司')asselect公司名称,联系人姓名,商品名称,单价,订购数量,订购日期from客户join销售订单On客户.客户编号=销售订单.客户编号join商品on商品.商品编号=销售订单.商品编号where公司名称=@客户名称执行输入参数为默认值的存储过程：exec客户订购信息执行带输入参数的存储过程：exec客户订购信息'三川实业有限公司e.创建触发器（5分）:创建一个触发器实现当向销售订单表添加一条记录时，自动修改商品表的库存数量和已售数量。createtrigger销售on销售订单afterinsertasupdate商品set库存数量=库存数量-订购数量,已售数量=已售数量+订购数量from商品joininsertedon商品.商品编号=inserted.商品编号4.执行查询（共15分）(1)简单查询（2分）：从客户表中检索所有客户的公司名称、联系人姓名和地址。select公司名称,联系人姓名,地址from客户(2)条件查询（3分）：在销售管理数据库的销售订单表中，查询员工编号为1、5和7的员工接受订单信息。select*from销售订单where员工编号in(1,5,7)（3）统计查询（2分）：统计所有员工的平均工资、最高工资、最低工资和工资总和。selectavg(工资)as平均工资,max(工资)as最高工资,min(工资)as最低工资,sum(工资)as工资总和from员工（4）分组查询（3分）：查询男女员工的平均工资。select性别,avg(工资)as平均工资from员工groupby性别（5）多表查询（2分）：查询“国皓科技有限公司”订购的商品信息，包括客户编号，公司名称，商品编号，商品名称，商品价格，订购数量。selecta.客户编号,公司名称,c.商品编号,商品名称,单价,订购数量from客户ajoin销售订单asbona.客户编号=b.客户编号join商品asconc.商品编号=b.商品编号where公司名称='国皓科技有限公司‘（6）嵌套查询（3分）：查询比平均工资高的员工的姓名和工资。select姓名,工资from员工where工资(selectavg(工资)from员工)