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

销售订单数据库管理系统 目录 1 1引言引言 1 1.1 编写目的.1 1.2 参考资料.1 2 2需求规约需求规约 2 2.1 业务描述.2 2.2 需求分析.2 3 3数据库环境说明数据库环境说明 4 4 4数据库的命名规则数据库的命名规则 5 4.1 数据库对象命名规则.5 4.2 数据项编码规则.5 5 5逻辑设计逻辑设计 7 5.1 er 图7 5.2 关系模式.7 6 6物理设计物理设计 10 6.1 表汇总.10 6.2 表1supplier表（供应商表）.10 6.3 表2product表（产品表）.11 6.4 表3sp表（供应关系表） 11 6.5 表4epc表（订单表） .12 6.6 表5epc_detailed表（订单细则表）13 6.7 表6eployee表（雇员表）.14 6.8 表7customer表（客户表）.15 6.9 视图的设计.16 6.10 存储过程、函数及触发器的设计.17 7 7总结总结 23 附录附录 24 1 1 1引言引言 1.11.1 编写目的编写目的 本文档是销售订单数据库管理系统设计文档的组成部分，编写数据库设计文档的目的是：明确数 据库的表名、字段名等数据信息，用来指导后期的数据库脚本的开发，本文档遵循sql server 2008 数据库设计和开发规范 。本文档的读者对象是需求人员、系统设计人员、开发人员、测试人员。设计 该数据库的目的是为了能够模拟完成一次订单销售流程。 1.21.2 参考资料参考资料 表 1.2.1 资料名称作者文件编号、版本 数据库系统概论 王珊、萨师煊2006 年 5 月第 4 版 数据库设计入门 经典 鲍威尔2007 年 3 月第 1 版 数据库原理克罗恩科2005 年 6 月第 1 版 2 2 2需求规约需求规约 2.12.1 业务描述业务描述 销售订单数据库管理系统的总目标是：在计算机网络，数据库和先进的开发平台上，利用现有的 软件，配置一定的硬件，开发一个具有开放体系结构的、易扩充的、易维护的、具有良好人机交互界 面的销售订单数据库管理系统，实现订单销售的自动化的计算机系统，为商品供应商提供准确、精细、 迅速的订单销售信息。具体功能为供应商供应产品、请假的申请，出差的记录输入到系统中，系统将 为员工记录这些信息。 提示：本部分完成此数据库系统的业务描述，例如： （1）数据库系统创建的背景 （2）数据库系统要完成的业务流程及工作内容 （3）揭示该数据库的资源需求和设计约束 2.22.2 需求分析需求分析 1、数据要求及数据关系 （1）数据结构：此销售订单数据库管理系统包括供应商、产品、客户、雇员 4 个实体。每个供应 商包含供应商编号、名称、地址、联系电话等信息；每种产品包含产品号、产品名称、产品类别等信 息；客户包含编号、姓名、通信地址、电话等信息；雇员包括编号、姓名、联系电话等信息。 （2）数据关系：供应商与产品有供应关系，包含销量和库存量；供应商与雇员有雇用关系；雇员， 客户，产品有订单关系，包含订单号，日期，订购量，总额；客户所下达的订单号的需求量如果多于 库存量，那么订单将不生成。 （3）对应关系：每个供应商可供应多种产品，每种产品可由多个供应商供应；一个客户可下多个 订单，每个订单只能由一个客户下；一个雇员可管理多个订单，每个订单只能由一个雇员管理；一个 订单订购多种产品每种产品可在不同的订单中订购；一个供应商能雇用多个雇员，一个雇员只能被一 个供应商雇用。 2、增删改查操作： （1）增加操作：此销售订单数据库管理系统能够插入供应商数据，能够插入产品数据，能够插入 客户信息，能够插入雇员信息，能够进行下订单操作插入订单信息。 （2）删除操作：此销售订单数据库管理系统能够删除供应商数据，能够删除产品数据，能够删除 客户信息，能够删除雇员信息，能够进行退订操作删除订单信息。 （3）更新操作：此销售订单数据库管理系统能够改变供应商数据，改变产品数据，改变客户信息， 改变雇员信息。 （4）查询操作：通过供应商编号能查询到供应商编号、名称、地址、联系电话等信息；通过产品 号能查询到产品号，产品名称，产品类别，库存数量，售价以；能通过客户编号能查询到客户所有客 户编号，姓名，通信地址，电话；通过雇员号能查询到雇员雇员编号，雇员姓名，雇员电话，雇员工 3 资；此外，通过供应商编号还能查询到所有雇员信息，通过雇员号可以查询到该雇员的供应商所有信 息；再通过供应商编号及产品号能过查询到供应商的产品的产品量，接着通过产品号分组号能够查询 所有产品的总销量，最畅销产品，通过供应商编号分组能够查询该供应商的所有产品的销量，最受欢 迎供应商；通过产品号跟订单号能够查询到订购量，及总额，再通过产品号分组能够可以查询到所有 产品的总订购量；通过雇员号能偶查询到所负责的订单信息，通过客户号能查询到所下达订单及所有 信息，通过订单号能够查询到所有订单的订购的所有产品及信息，所负责雇员及所有信息，所下达客 户及所有信息。 3、编写要求： （1）此销售订单数据库管理系统全部使用 sql 语句编写； （2）增删改查及下订单退订等操作通过存储过程来实现，定义级联删除约束，下订单及退订使用 到触发器，实现并发操作。 （3）给不同用户定义不同的视图，使他们看到是不同的内容。 4 3 3数据库环境说明数据库环境说明 表 3.1 数据库实 例 数据库系 统 数据库部 署环境 数据库设 计工具 数据库存放位 置 说明 销售订单数 据库管理系 统 sql server 2008 windows xp sql server 存放位置，绝对 路径/相对路径 用于订单销售管理 5 4 4数据库的命名规则数据库的命名规则 4.14.1 数据库对象命名规则数据库对象命名规则 表 4.1.1 数据库对象命名规则备注 表该表名称的英文字符串例如：supplier，供应商表 视图view_功能描述字符串例如：view_product 产品视图 触发器tri_功能描述字符串例如：tri_insert 更改库存触发器 存储过程pro_功能描述字符串例如：pro_insert_suppliers 存储过 程 4.24.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 6 简写 订单日期英文拼写的加功能 简写 可变长度字 符串 20 位order_time 客户姓名英文拼写的加功能 简写 可变长度字 符串 20 位customer_name 客户地址英文拼写的加功能 简写 可变长度字 符串 20 位customer_address 客户电话英文拼写的加功能 简写 整数20 位customer_phone 订购数量英文拼写的加功能 简写 整数order_amount 雇员姓名英文拼写的加功能 简写 可变长度字 符串 20 位employee_name 雇员电话英文拼写的加功能 简写 整数20 位employee_phone 7 5 5逻辑设计逻辑设计 5.15.1 erer 图图 产 产 产 产 产产 产 产 产 产 产 产 产 产 产 产 产 产 产 产 产 产 产 产 产产 产 产 产 mm 产 产 产 产 产 产 产 产 产 产 产 产产 产 产 产 产 产 产 产 产 产 产 产 产 产 产 产 n n n n mm p p 产 产 1 1 n n 产 产 产 产 产 产 产 产 产 产 产 产 产 产 产 产 产 产 产 产 产 产 图 5.1.1 5.25.2 关系模式关系模式 供应商(供应商编号，名称，地址，联系电话) 产品(产品号，产品名称，产品类别，售价) 供应(供应商编号，产品号，供应量，存货数量) 雇员(雇员号，姓名，联系电话，工资，供应商编号) 客户(客户编号，姓名，通信地址，电话) 订单(订单号，客户编号，雇员编号，日期) 订单细则表(产品号，数量，总额，订单号，供应商号) 注：有下划线的表示该属性为主码。 8 表 5.2.1 表 5.2.2 产品表（product） 字段中文名字段名数据类型是否为空约束 产品号product _novarchar(12)否primary key 产品名product _namevarchar(20)否 产品类别product_familyvarchar(20)否 产品价格product_pricesmallint否 表 5.2.3 供应表（sp） 字段中文名字段名数据类型是否为空约束 供应商编号supplier_novarchar(12)否 产品号product_novarchar(12)否 primary key foreign key 库存数量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 供应商表（supplier） 字段中文名字段名数据类型是否为空约束 供应商编号supplier_novarchar(12)否primary key 供应商名supplier_namevarchar(20)否 供应商地址supplier_addressvarchar(20)否 供应商电话supplier_phonevachar(12)否 9 订单表（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) 10 6 6物理设计物理设计 6.16.1 表汇总表汇总 表 6.1.1 表名功能说明 表 supplier供应商表，存储供应商的编号，名称，地址，联系电话信息 表 product产品表，存储产品号，产品名称，产品类别，售价信息 表 sp供应表，存储供应商编号，产品号，供应量信息，存货数量 表 employee雇员表，存储雇员号，姓名，联系电话，工资，供应商信息 表 customer客户表，存储客户编号，姓名，通信地址，电话信息 表 epc订单表，存储订单号，客户编号，雇员编号，产品号，日期等信息 表 epc_ detailed订单细则表，存储产品号，订单号，数量，总额信息 6.26.2 表表1supplier1supplier 表表 （供应商表）（供应商表） 表 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 ); -供应商表 11 6.36.3 表表2product2product 表表 （产品表）（产品表） 表 6.3.1 表名product（产品表） 数据库用户sa 主键product_no 其他排序字段无 索引字段无 序号字段名称数据类型（精 度范围） 允许 为空 y/n 唯一 y/n 区别度默认值约束条件/说明 1prduct_nochar(12)ny高无主键/产品编号 2product_na me char(20)nn高无产品名称 3product_cla ss char(20)nn低无产品类别 4prduct_pric e smallintnn中无产品售价 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 6.4.4 表表3sp3sp 表表 （供应关系表）（供应关系表） 表 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低无库存量 12 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 cascade on update cascade, foreign key(product_no)references product(product_no) on delete cascade on update cascade ); -供应表 6.56.5 表表4epc4epc 表表 （订单表）（订单表） 表 6.5.1 表名epc(订单表) 数据库用户sa 主键order_no 其他排序字段无 索引字段无 序号字段名称数据类型（精 度范围） 允许 为空 y/n 唯一 y/n 区别度默认值约束条件/说明 1order_nochar(6)ny高无主键/订单号 2order_timechar(12)nn低无订单日期 3employee_n o char(12)nn中无雇员号 4customer_n o char(12)nn高无客户号 13 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 cascade on update cascade, foreign key(customer_no)references customer(customer_no) on delete cascade on update cascade ); -订单表 6.66.6 表表5epc_detailed5epc_detailed 表表 （订单细则表）（订单细则表） 表 6.6.1 表名epc_detailed（订单细则表） 数据库用户sa 主键无 其他排序字段无 索引字段无 序号字段名称数据类型（精 度范围） 允许 为空 y/n 唯一 y/n 区别度默认值约束条件/说明 1order_nochar(6)ny高无订单编号 2supplier_nochar(6)nn高无供应商编号 3product_nochar(6)nn高无产品号 4order_amou nt smallintnn中无订购数量 5order_mone y smallintnn低无订购总额 14 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 cascade on update cascade, foreign key(product_no)references product(product_no) on delete cascade on update cascade, foreign key(order_no)references epc(order_no) ); -订单细则表 6.76.7 表表6eployee6eployee 表表 （雇员表）（雇员表） 表 6.7.1 表名eployee（雇员表） 数据库用户sa 主键employee_no 其他排序字段无 索引字段无 序号字段名称数据类型（精 度范围） 允许 为空 y/n 唯一 y/n 区别度默认值约束条件/说明 1employee_n o char(12)ny高无主键/雇员号 2employee_n ame char(20)nn中无雇员名称 3employee_p hone smallintny高无雇员电话 4employee_s alary smallintnn低无雇员工资 15 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 cascade on update cascade ); -雇员表 6.86.8 表表7customer7customer 表表 （客户表）（客户表） 表 6.8.1 表名customer（客户表） 数据库用户sa 主键customer_no 其他排序字段无 索引字段无 序号字段名称数据类型（精 度范围） 允许 为空 y/n 唯一 y/n 区别度默认值约束条件/说明 1customer_n o char(12)ny高无主键/客户编号 2customer_n ame char(20)nn中无客户名称 3customer_a ddress char(20)nn中无客户地址 4customer_p hone char(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 ); -客户表 16 6.96.9 视图的设计视图的设计 1、建立一张供应商查询视图 目的：供供应商查询 功能：能够查询到所有产品销售情况 意义：使供应商能够看到最畅销的产品，及时调整销售策略。 create view supplier_viwe as select product_no,sum(sp_amount)产品总销售量 from sp group by product_no 图 6.9.1 1、建立一张客户查询视图 目的：供客户查询 功能：能够查询到所有供应商所有产品的总销售量 意义：是客户可以看到最畅销的供应商，便于选择合适的供应商。 create view customer_viwe as select supplier_no,sum(sp_amount)产品总销售量 from sp group by supplier_no 17 图 6.9.2 2、建立一张雇员查询视图 目的：供雇员查询 功能：供雇员查询 意义：能够看到所有雇员信息及所在供应商 create view employee_viwe as select employee_no,employee_name,employee_phone,supplier_name from employee,supplier where supplier.supplier_no=employee.supplier_no 图 6.9.3 6.106.10 存储过程、函数及触发器的设计存储过程、函数及触发器的设计 1、插入供应商信息 插入供应商信息 功能：插入供应商信息 目的：存储查询插入供应商信息的操作 意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤 create procedure pro_insert_supplier supplier_no varchar(12), supplier_name varchar(20), 18 supplier_address varchar(20), supplier_phone varchar(12) as if(select count(*)from supplier where supplier_no=supplier_no)0 printerror! else insert 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.1 2、删除供应关系信息 功能：删除供应关系信息 目的：存储删除插入供应关系信息的操作 意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤 create procedure pro_delete_sp supplier_no varchar(12) as 19 if(select count(*) from sp where supplier_no=supplier_no)0 delete from sp where supplier_no=supplier_no exec pro_delete_sp1001 图 6.10.2 图 6.10.3 3、更新雇员信息 功能：更新雇员信息 目的：存储更新雇员信息的操作 意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤 create procedure pro_update_employee employee_no varchar(12), employee_name varchar(20), employee_phone varchar(12), employee_salary smallint , supplier_no varchar(12) as if exists(select * from employee where employee_no=employee_no) 20 update employee set employee_name=employee_name, employee_phone=employee_phone, employee_salary=employee_salary, supplier_no =supplier_no where employee_no=employee_no else print无此信息！ exec pro_update_employee1004,菜广1000,1001; 图 6.10.4 图 6.10.5 4、查询订单细则信息 功能：查询订单细则信息 目的：存储查询订单细则信息的操作 意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤 create procedure pro_select_epc_detailed order_no varchar(6) as select * from epc_detailed; where order_no=order_no exec pro_select_epc_detailed1001; 图 6.10.6 21 5、下订单流程 功能：下订单 目的：客户根据产品名下订单 意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤 create procedure pro_exec order_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 smallint as if(select count(*) from epc where order_no=order_no)0 print已经存在订单号！ 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)=0 print该供应商不提供应该产品 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,ord er_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下订单成功 end exec pro_exec1000,1001,1001,1001,电脑,20130601,1000,10000;-下订单 exec pro_delete_epc_detailed1000; -退订 图 6.10.7 22 图 6.10.8 5、触发器 功能：并发操作 目的：减少库存，增加销售量 意义：可并发执行，自动增加销售量，减少库存量 -删除触发器 create trigger tri_delete on epc_detailed after delete as update sp set rest_product=rest_product+(select order_amount from deleted), sp_amount=sp_amount-(select order_amount from deleted) from deleted,sp where duct_no=duct_no and sp.supplier_no=deleted.supplier_no -插入触发器 create trigger tri_insert on epc_detailed after insert as update sp set rest_product=rest_product-(select order_amount from inserted), sp_amount=sp_amount+(select order_amount from inserted) from sp,inserted where duct_no=duct_no and sp.supplier_no=inserted.supplier_no 23 图 6.10.9 图 6.10.10 图 6.10.11 24 7 7总结总结 经过了艰难的两个星期，终于把这个订单销售管理系统做好了。这次课程设计让我收获很多，首 先是思维方式，之前想得国语简单，以至于发现无法完成功能，是因为思维方式的局限性，没有站在 多角度看问题。这次课程设计后我学会了从多方面分析问题，严密了思维方式。然后是对 sql 更加熟 悉了，能够比较熟练的使用了，一开始，存储过程写得总是出错，建表也有出过错，写增删改查也是 不是出点错误，特别是触发器，由于一开始没有透彻的理解，所以写出了错的触发器，使得数据全部 出错。现在一般都不会出现低级错误了。 25 附录附录 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 tabl