2015 销售订单数据库管理系统.doc

.1引言1.1 编写目的本文档是销售订单数据库管理系统设计文档的组成部分，编写数据库设计文档的目的是：明确数据库的表名、字段名等数据信息，用来指导后期的数据库脚本的开发，本文档遵循SQL SERVER 2008数据库设计和开发规范。本文档的读者对象是需求人员、系统设计人员、开发人员、测试人员。设计该数据库的目的是为了能够模拟完成一次订单销售流程。1.2 参考资料表 1.2.1资料名称作者文件编号、版本数据库系统概论王珊、萨师煊2006年5月第4版数据库设计入门经典鲍威尔2007年3月第1版数据库原理克罗恩科2005年6月第1版 2需求规约2.1 业务描述销售订单数据库管理系统的总目标是：在计算机网络，数据库和先进的开发平台上，利用现有的软件，配置一定的硬件，开发一个具有开放体系结构的、易扩充的、易维护的、具有良好人机交互界面的销售订单数据库管理系统，实现订单销售的自动化的计算机系统，为商品供应商提供准确、精细、迅速的订单销售信息。具体功能为供应商供应产品、请假的申请，出差的记录输入到系统中，系统将为员工记录这些信息。提示：本部分完成此数据库系统的业务描述，例如：（1）数据库系统创建的背景（2）数据库系统要完成的业务流程及工作内容（3）揭示该数据库的资源需求和设计约束2.2 需求分析1、数据要求及数据关系（1）数据结构：此销售订单数据库管理系统包括供应商、产品、客户、雇员4个实体。每个供应商包含供应商编号、名称、地址、联系电话等信息；每种产品包含产品号、产品名称、产品类别等信息；客户包含编号、姓名、通信地址、电话等信息；雇员包括编号、姓名、联系电话等信息。（2）数据关系：供应商与产品有供应关系，包含销量和库存量；供应商与雇员有雇用关系；雇员，客户，产品有订单关系，包含订单号，日期，订购量，总额；客户所下达的订单号的需求量如果多于库存量，那么订单将不生成。（3）对应关系：每个供应商可供应多种产品，每种产品可由多个供应商供应；一个客户可下多个订单，每个订单只能由一个客户下；一个雇员可管理多个订单，每个订单只能由一个雇员管理；一个订单订购多种产品每种产品可在不同的订单中订购；一个供应商能雇用多个雇员，一个雇员只能被一个供应商雇用。2、增删改查操作：（1）增加操作：此销售订单数据库管理系统能够插入供应商数据，能够插入产品数据，能够插入客户信息，能够插入雇员信息，能够进行下订单操作插入订单信息。（2）删除操作：此销售订单数据库管理系统能够删除供应商数据，能够删除产品数据，能够删除客户信息，能够删除雇员信息，能够进行退订操作删除订单信息。（3）更新操作：此销售订单数据库管理系统能够改变供应商数据，改变产品数据，改变客户信息，改变雇员信息。（4）查询操作：通过供应商编号能查询到供应商编号、名称、地址、联系电话等信息；通过产品号能查询到产品号，产品名称，产品类别，库存数量，售价以；能通过客户编号能查询到客户所有客户编号，姓名，通信地址，电话；通过雇员号能查询到雇员雇员编号，雇员姓名，雇员电话，雇员工资；此外，通过供应商编号还能查询到所有雇员信息，通过雇员号可以查询到该雇员的供应商所有信息；再通过供应商编号及产品号能过查询到供应商的产品的产品量，接着通过产品号分组号能够查询所有产品的总销量，最畅销产品，通过供应商编号分组能够查询该供应商的所有产品的销量，最受欢迎供应商；通过产品号跟订单号能够查询到订购量，及总额，再通过产品号分组能够可以查询到所有产品的总订购量；通过雇员号能偶查询到所负责的订单信息，通过客户号能查询到所下达订单及所有信息，通过订单号能够查询到所有订单的订购的所有产品及信息，所负责雇员及所有信息，所下达客户及所有信息。3、编写要求：（1）此销售订单数据库管理系统全部使用sql语句编写；（2）增删改查及下订单退订等操作通过存储过程来实现，定义级联删除约束，下订单及退订使用到触发器，实现并发操作。（3）给不同用户定义不同的视图，使他们看到是不同的内容。3数据库环境说明表 3.1数据库实例数据库系统数据库部署环境数据库设计工具数据库存放位置说明销售订单数据库管理系统Sql Server 2008Windows XPSql Server 存放位置，绝对路径/相对路径用于订单销售管理4数据库的命名规则4.1 数据库对象命名规则表 4.1.1数据库对象命名规则备注表该表名称的英文字符串例如：supplier，供应商表视图view_功能描述字符串例如：view_product产品视图触发器tri_功能描述字符串例如：tri_insert更改库存触发器存储过程Pro_功能描述字符串例如：pro_insert_suppliers存储过程4.2 数据项编码规则表 4.2.1数据项命名规则数据类型长度范围备注供应商编号英文拼写的加功能简写可变长度字符串12位supplier_no产品号英文拼写的加功能简写可变长度字符串12位product_no订单号英文拼写的加功能简写可变长度字符串6位ordero雇员号英文拼写的加功能简写可变长度字符串12位Employee_no客户号英文拼写的加功能简写可变长度字符串12位Customer_no供应商名称英文拼写的加功能简写可变长度字符串20位supplier_name供应商电话英文拼写的加功能简写可变长度字符串20位supplier_no供应商地址英文拼写的加功能简写可变长度字符串30位supplier_address产品名称英文拼写的加功能简写可变长度字符串20位product_name产品类型英文拼写的加功能简写可变长度字符串20位product_class售价英文拼写的加功能简写整数product_price存货量英文拼写的加功能简写整数rest_product订单日期英文拼写的加功能简写可变长度字符串20位order_time客户姓名英文拼写的加功能简写可变长度字符串20位customer_name客户地址英文拼写的加功能简写可变长度字符串20位customer_address客户电话英文拼写的加功能简写整数20位customer_phone订购数量英文拼写的加功能简写整数order_amount雇员姓名英文拼写的加功能简写可变长度字符串20位employee_name雇员电话英文拼写的加功能简写整数20位employee_phone5逻辑设计5.1 ER图图 关系模式供应商(供应商编号，名称，地址，联系电话) 产品(产品号，产品名称，产品类别，售价) 供应(供应商编号，产品号，供应量，存货数量)雇员(雇员号，姓名，联系电话，工资，供应商编号) 客户(客户编号，姓名，通信地址，电话) 订单(订单号，客户编号，雇员编号，日期) 订单细则表(产品号，数量，总额，订单号，供应商号)注：有下划线的表示该属性为主码。 表5.2.1供应商表（supplier）字段中文名字段名数据类型是否为空约束供应商编号supplier_novarchar(12)否Primary key供应商名supplier_namevarchar(20)否供应商地址supplier_addressvarchar(20)否供应商电话supplier_phonevachar(12)否表5.2.2产品表（product）字段中文名字段名数据类型是否为空约束产品号product _novarchar(12)否Primary key产品名product _namevarchar(20)否产品类别product_familyvarchar(20)否产品价格product_pricesmallint否表 5.2.3供应表（sp）字段中文名字段名数据类型是否为空约束供应商编号supplier_novarchar(12)否primary keyforeign key产品号product_novarchar(12)否库存数量rest_productsmallint否销售量sp_amountsmallint否表 5.2.4客户表（customer）字段中文名字段名数据类型是否为空约束客户号customer_novarchar(12)否primary key客户名customer _namevarchar(20)否通信地址rest_productvarchar(20)否电话sp_amountvarchar(12)否表 5.2.5雇员表（employee）字段中文名字段名数据类型是否为空约束雇员号employee_novarchar(12)否primary key雇员姓名employee_namevarchar(20)否雇员电话supplier_phonevarchar(12)否雇员工资employee_salarysmallint否供应商编号supplier_novarchar(12)否表 5.2.6订单表（epc）字段中文名字段名数据类型是否为空约束雇员号employee_novarchar(12)否foreign key客户号customer_novarchar(20)否foreign key订单号order_novarchar(12)否primary key日期order_timevarchar(20)否表 5.2.7订单细则（epc_detailed）字段中文名字段名数据类型是否为空约束产品号product_novarchar(12)否foreign key数量employee_namesmallint否订单号order_novarchar(12)否primary key总额epc_amountsmallint否供应商编号supplier_novarchar(12)6物理设计6.1 表汇总表6.1.1表名功能说明表supplier供应商表，存储供应商的编号，名称，地址，联系电话信息表product产品表，存储产品号，产品名称，产品类别，售价信息表sp供应表，存储供应商编号，产品号，供应量信息，存货数量表employee雇员表，存储雇员号，姓名，联系电话，工资，供应商信息表customer客户表，存储客户编号，姓名，通信地址，电话信息表epc订单表，存储订单号，客户编号，雇员编号，产品号，日期等信息表epc_ detailed订单细则表，存储产品号，订单号，数量，总额信息6.2 表1supplier表（供应商表）表 6.2.1表名supplier（供应商表）数据库用户sa主键supplier_no其他排序字段无索引字段无序号字段名称数据类型（精度范围）允许为空Y/N唯一Y/N区别度默认值约束条件/说明1supplier_nochar(12)NY高无主键/供应商号2supplier_namechar(20)NN中无供应商名称3supplier_addresschar(20)NN中无供应商地址4supplier_phonechar(20)NN高无供应商电话sql脚本create table supplier( supplier_no varchar(12)primary key,supplier_name varchar(20)not null,supplier_address varchar(20)not null,supplier_phone varchar(12)not null);-供应商表6.3 表2product表（产品表）表 6.3.1表名product（产品表）数据库用户sa主键product_no其他排序字段无索引字段无序号字段名称数据类型（精度范围）允许为空Y/N唯一Y/N区别度默认值约束条件/说明1prduct_nochar(12)NY高无主键/产品编号2product_namechar(20)NN高无产品名称3product_classchar(20)NN低无产品类别4prduct_pricesmallintNN中无产品售价sql脚本create table product( product_no varchar(12)primary key,product_name varchar(20)not null,product_family varchar(20)not null,product_price smallint not null);-产品表6.4 表3sp表（供应关系表）表 6.4.1表名sp（供应关系表）数据库用户sa主键supplier_no,product_no其他排序字段无索引字段无序号字段名称数据类型（精度范围）允许为空Y/N唯一Y/N区别度默认值约束条件/说明1supplier_nochar(12)NN低无主键/供应商号2product_nochar(12)NN低无主键/产品编号3sp_amountsmallintNN低无销售量4rest_productsmallintNN低无库存量sql脚本create table sp( supplier_no varchar(12)not null,product_no varchar(12)not null,sp_amount smallint not null,rest_product smallint not null,primary key(supplier_no,product_no),foreign key(supplier_no)references supplier(supplier_no)on delete cascadeon update cascade,foreign key(product_no)references product(product_no)on delete cascadeon update cascade);-供应表6.5 表4epc表（订单表）表 6.5.1表名epc(订单表)数据库用户sa主键order_no其他排序字段无索引字段无序号字段名称数据类型（精度范围）允许为空Y/N唯一Y/N区别度默认值约束条件/说明1order_nochar(6)NY高无主键/订单号2order_timechar(12)NN低无订单日期3employee_nochar(12)NN中无雇员号4customer_nochar(12)NN高无客户号sql脚本create table epc( order_no varchar(6)primary key,order_time varchar(20)not null,customer_no varchar(12)not null,employee_no varchar(12)not null,foreign key(employee_no)references employee(employee_no)on delete cascadeon update cascade,foreign key(customer_no)references customer(customer_no)on delete cascadeon update cascade);-订单表6.6 表5epc_detailed表（订单细则表）表 6.6.1表名epc_detailed（订单细则表）数据库用户sa主键无其他排序字段无索引字段无序号字段名称数据类型（精度范围）允许为空Y/N唯一Y/N区别度默认值约束条件/说明1order_nochar(6)NY高无订单编号2supplier_nochar(6)NN高无供应商编号3product_nochar(6)NN高无产品号4order_amountsmallintNN中无订购数量5order_moneysmallintNN低无订购总额sql脚本create table epc_detailed( order_no varchar(6) not null,product_no varchar(12)not null,order_amount smallint not null,order_money smallint not null,supplier_no varchar(12)not null,foreign key(supplier_no)references supplier(supplier_no)on delete cascadeon update cascade,foreign key(product_no)references product(product_no)on delete cascadeon update cascade,foreign key(order_no)references epc(order_no);-订单细则表6.7 表6eployee表（雇员表）表 6.7.1表名eployee（雇员表）数据库用户sa主键employee_no其他排序字段无索引字段无序号字段名称数据类型（精度范围）允许为空Y/N唯一Y/N区别度默认值约束条件/说明1employee_nochar(12)NY高无主键/雇员号2employee_namechar(20)NN中无雇员名称3employee_phonesmallintNY高无雇员电话4employee_salarysmallintNN低无雇员工资sql脚本create table employee( employee_no varchar(12)primary key,employee_name varchar(20)not null,employee_phone varchar(12)not null,employee_salary smallint not null,supplier_no char(12),foreign key(supplier_no)references supplier(supplier_no)on delete cascadeon update cascade);-雇员表6.8 表7customer表（客户表）表 6.8.1表名customer（客户表）数据库用户sa主键customer_no其他排序字段无索引字段无序号字段名称数据类型（精度范围）允许为空Y/N唯一Y/N区别度默认值约束条件/说明1customer_nochar(12)NY高无主键/客户编号2customer_namechar(20)NN中无客户名称3customer_addresschar(20)NN中无客户地址4customer_phonechar(12)NN高无客户电话sql脚本create table customer( customer_no varchar(12)primary key, customer_name varchar(20)not null, customer_phone varchar(12)not null, customer_address varchar(20)not null);-客户表6.9 视图的设计1、建立一张供应商查询视图目的：供供应商查询功能：能够查询到所有产品销售情况意义：使供应商能够看到最畅销的产品，及时调整销售策略。Create view supplier_viweasselect product_no,sum(sp_amount)产品总销售量from spgroup by product_no图 6.9.11、建立一张客户查询视图目的：供客户查询功能：能够查询到所有供应商所有产品的总销售量意义：是客户可以看到最畅销的供应商，便于选择合适的供应商。Create view customer_viweasselect supplier_no,sum(sp_amount)产品总销售量from spgroup by supplier_no图 6.9.22、建立一张雇员查询视图目的：供雇员查询功能：供雇员查询 意义：能够看到所有雇员信息及所在供应商create view employee_viweasselect employee_no,employee_name,employee_phone,supplier_namefrom employee,supplierwhere supplier.supplier_no=employee.supplier_no图 0 存储过程、函数及触发器的设计 1、插入供应商信息插入供应商信息功能：插入供应商信息目的：存储查询插入供应商信息的操作意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤create procedure pro_insert_suppliersupplier_no varchar(12),supplier_name varchar(20),supplier_address varchar(20),supplier_phone varchar(12)as if(select COUNT(*)from supplier where supplier_no=supplier_no)0printerror!elseinsert into supplier(supplier_no,supplier_name,supplier_address,supplier_phone)values(supplier_no,supplier_name,supplier_address,supplier_phone)exec pro_insert_supplier 1000,沃尔玛,美国exec pro_insert_supplier 1001,英国石油,英国exec pro_insert_supplier 1002,埃克森美孚,美国exec pro_insert_supplier 1003,皇家壳牌石油,英国exec pro_insert_supplier 1004,通用汽车,德国exec pro_insert_supplier 1005,戴姆勒克莱斯勒,美国exec pro_insert_supplier 1006,丰田汽车,日本exec pro_insert_supplier 1007,福特汽车,美国exec pro_insert_supplier 1008,通用电气,德国exec pro_insert_supplier 1009,海尔,中国exec pro_insert_supplier 1009,海尔,中国图 6.10.12、删除供应关系信息功能：删除供应关系信息目的：存储删除插入供应关系信息的操作意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤create procedure pro_delete_spsupplier_no varchar(12)asif(select COUNT(*) from sp where supplier_no=supplier_no)0deletefrom spwhere supplier_no=supplier_noexec pro_delete_sp1001图 6.10.2图 6.10.33、更新雇员信息功能：更新雇员信息目的：存储更新雇员信息的操作意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤create procedure pro_update_employeeemployee_no varchar(12),employee_name varchar(20),employee_phone varchar(12),employee_salary smallint ,supplier_no varchar(12)asif exists(select * from employee where employee_no=employee_no)update employeesetemployee_name=employee_name,employee_phone=employee_phone,employee_salary=employee_salary,supplier_no =supplier_no where employee_no=employee_noelseprint无此信息！exec pro_update_employee1004,菜广1000,1001;图 6.10.4图 6.10.54、查询订单细则信息功能：查询订单细则信息目的：存储查询订单细则信息的操作意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤create procedure pro_select_epc_detailedorder_no varchar(6)asselect * from epc_detailed;where order_no=order_noexec pro_select_epc_detailed1001;图 6.10.65、下订单流程功能：下订单目的：客户根据产品名下订单意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤create procedure pro_execorder_no varchar(6),customer_no varchar(12),employee_no varchar(12),supplier_no varchar(12),product_name varchar(20),order_time varchar(20),order_amount smallint,order_money smallintasif(select COUNT(*) from epc where order_no=order_no)0print已经存在订单号！else if(select COUNT(*) from product where product_name=product_name and product_no in(select product_no from sp where supplier_no=supplier_no)=0print该供应商不提供应该产品else if(select rest_product from sp where supplier_no=supplier_no and product_no=(select product_no from product where product_name=product_name)order_amount print库存量不足！ else begin insert into epc(order_no,customer_no,employee_no,order_time)values(order_no,customer_no,employee_no,order_time)insert into epc_detailed(order_no,supplier_no,product_no,order_amount,order_money)values(order_no,supplier_no,(select product_no from product where product_name=product_name),order_amount,order_money)print下订单成功endexec pro_exec1000,1001,1001,1001,电脑,20130601,1000,10000;-下订单exec pro_delete_epc_detailed1000; -退订 图 6.10.7图 6.10.85、触发器功能：并发操作目的：减少库存，增加销售量意义：可并发执行，自动增加销售量，减少库存量-删除触发器create trigger tri_deleteon epc_detailedafter deleteas update spset rest_product=rest_product+(select order_amount from deleted),sp_amount=sp_amount-(select order_amount from deleted)from deleted,spwhere duct_no=duct_no and sp.supplier_no=deleted.supplier_no-插入触发器create trigger tri_inserton epc_detailedafter insert as update spset rest_product=rest_product-(select order_amount from inserted),sp_amount=sp_amount+(select order_amount from inserted)from sp,insertedwhere duct_no=duct_no and sp.supplier_no=inserted.supplier_no图 6.10.9图 6.10.10图 6.10.117总结经过了艰难的两个星期，终于把这个订单销售管理系统做好了。这次课程设计让我收获很多，首先是思维方式，之前想得国语简单，以至于发现无法完成功能，是因为思维方式的局限性，没有站在多角度看问题。这次课程设计后我学会了从多方面分析问题，严密了思维方式。然后是对sql更加熟悉了，能够比较熟练的使用了，一开始，存储过程写得总是出错，建表也有出过错，写增删改查也是不是出点错误，特别是触发器，由于一开始没有透彻的理解，所以写出了错的触发器，使得数据全部出错。现在一般都不会出现低级错误了。附录create database ling;use ling;create table supplier( supplier_no varchar(12)primary key, supplier_name varchar(20)not null, supplier_address varchar(20)not null, supplier_phone varchar(12)not null);-供应商表create table product( product_no varchar(12)primary key, product_name varchar(20)not null, product_family varchar(20)not null, product_price smallint not null );-产品表create table sp( supplier_no varchar(12)not null, product_no varchar(12)not null, sp_amount smallint not null, rest_product smallint not null, primary key(sup