数据库销售订单管理系统课程设计.docx

数据库销售订单管理系统课程设计目 录1引言11.1编写目的11.2参考资料12.需求规约22.1 业务描述22.2 需求分析23.数据库环境说明24.数据库的命名规则24.1 数据库对象命名规则24.2 数据项编码规则25.逻辑设计25.1 ER图25.2 关系模型26.物理设计26.1表汇总26.2 表1: Suppliers表(供应商表)26.3 表2:product表(产品表)26.4 表3:sp表(供应商产品供应表)26.5 表4: orders表(订单表)26.6 表5:po表(产品订购表)26.7 表6: employee表(雇员表)26.8 表7:customer表(客户表)26.9 视图的设计26.10 存储过程的设计26.11 触发器的设计26.12函数的设计27.安全性设计27.1防止用户直接操作数据库的方法27.2用户帐号密码的加密方法27.3角色与权限28.数据库管理与维护说明2总结2附录21引言1.1 编写目的本文档是销售订单数据库管理系统设计文档的组成部分，编写数据库设计文档的目的是：明确数据库的表名、字段名等数据信息，用来指导后期的数据库脚本的开发，本文档遵循SQL SERVER 2008数据库设计和开发规范。本文档的读者对象是需求人员、系统设计人员、开发人员、测试人员。设计该数据库的目的是为了能够模拟完成一次订单销售流程。1.2 参考资料表1 参考资料表资料名称作者文件编号、版本数据库系统概论王珊、萨师煊2006年5月第4版数据库设计入门经典鲍威尔2007年3月第1版数据库原理克罗恩科2005年6月第1版2.需求规约2.1 业务描述销售订单数据库管理系统的总目标是：在计算机网络，数据库和先进的开发平台上，利用现有的软件，配置一定的硬件，开发一个具有开放体系结构的、易扩充的、易维护的、具有良好人机交互界面的销售订单数据库管理系统，实现订单的下发，数据的查询删除和修改。2.2 需求分析本销售订单数据库管理系统的需求如下，首先在供应商表、供应表、产品表、订购表、订单表、客户表、雇员表的基本表上建立增、删、改、查的存储过程。以便以后再次用到的时候可以直接用存储过程而不用反复的写代码。第二在实验中间建立触发器，当订购产品的时候可以查询库存，如果满足要求，那么就库存通过触发器实现库存的减少。第三在这次客场设计的基础上建立一些查询，比如说要求建立订单最多的客户信息的查询，平均工资的查询，库存最多的产品的查询。最后要求完成一次下订单的流程。此外，在一个完整的订单销售流程中，本数据库系统还提供查询某供应商供应的产品信息，查询某产品信息，查询某客户信息，查询某订单订购的产品的信息。对于客户而言，还提供了查询订单最多的客户的功能。例如当在订单的中只要知道订单的编号，就可以知道订单的产品的名字，产品的数量(产品的信息)，雇员的信息，客户的信息。在订购的表中知道订单号就可以知道订单中产品的数量。可以修改数据库里面的数据，例如条件知道供应商编号就可以对供应商的信息（编号，姓名，联系电话，地址等）进行修改，还可以对该供应商进行删除。知道客户的编号就可以对客户的数据 （编号，信息 姓名，联系电话等）进行修改，还可以对该用户进行删除。总的来说，本数据库系统能够达到模拟完成一次订单销售流程的要求。在本销售订单数据库管理系统中，有一些限制性条件，比如一个客户可以下多个订单，但一个订单只能由一个客户下，一个订单可以订购多种产品，每种产品可被不同订单订购等等，这些限制条件的加入能够使本数据库管理系统的功能更加完善，能够满足更高的要求。3.数据库环境说明表2 数据库环境说明表数据库实例数据库系统数据库部署环境数据库设计工具数据库存放位置说明销售订单数据库管理系统Sql Server 2008Windows 7Sql Server存放位置，绝对路径/相对路径用于订单销售管理4.数据库的命名规则4.1 数据库对象命名规则表3 数据库对象命名表数据库对象命名规则备注表S功能描述字符串例如：Suppliers供应商表视图view_功能描述字符串例如：view_Product产品视图触发器insert_功能描述字符串例如：insert_PO更改库存触发器存储过程Procedure_功能描述字符串例如：pro_sertSuppliers存储过程 4.2 数据项编码规则表4 数据项编码表数据项命名规则数据类型长度范围备注供应商编号两位整数序号（001000）定长字符串32位无产品号两 位 整 数 序 号（001000）定长字符串32位无订单号两位整数序号（001000）定长字符串32位无雇员号两位整数序号（001000）定长字符串32位无客户号两位整数序号（001000）定长字符串32位无5.逻辑设计5.1 ER图图5.1E-R图5.2 关系模型供应商(供应商编号，名称，地址，联系电话)产品(产品编号，产品名称，产品类别，出厂价，价格，库存)供应(供应商编号，产品编号)订购(产品编号，订单编号，数量)客户(客户编号，姓名，地址，电话)订单(订单编号，日期，客户编号，雇员编号，产品种类)雇员(雇员编号，姓名，电话，工资)6.物理设计6.1表汇总表5 汇总表表名功能说明表Suppliers供应商表，存储供应商的编号等信息表product产品表，存储产品的编号、数量等信息表Sp供应商产品供应表，存储供应商所供应的产品对应信息表orders订单表，存储订单的编号、日期等信息表po产品订购表，存储产品订购的对应信息表employee雇员表，存储雇员的编号等信息表customer客户表，存储客户的编号等信息6.2 表1: Suppliers表(供应商表)表名Suppliers（供应商表）数据库用户Sa主键S_number其他排序字段无索引字段无序号字段名称数据类型（精度范围）允许为空Y/N唯一Y/N区别度默认值约束条件/说明1S_numberchar(32)NY高无主键/供应商编号2S_namechar(32)NN中无供应商名称3S_addresschar(32)NN中无供应商地址4S_telnumChar(32)NN高无供应商电话sql脚本-建立供应商表create table Suppliers-供应商表( S_number char(32) not null primary key, S_name char(32) not null, S_adress char(32) not null, S_telnub char(32) not null, );6.3 表2:product表(产品表)表名Product（产品表）数据库用户sa主键P_number其他排序字段无索引字段无序号字段名称数据类型（精度范围）允许为空Y/N唯一Y/N区别度默认值约束条件/说明1P_numberchar(32)NY高无主键/产品编号2P_namechar(32)NY高无产品名称3P_priceintYN中无产品原价4P_expriceintYN中无产品出厂价5P_inventoryintYN中无产品库存量sql脚本-建立产品表create table product -产品表( p_number char(32) primary key not null, p_name char(32) not null, p_price int, -原价 p_exprice int, -售价 p_inventory int ,-库存);6.4 表3:sp表(供应商产品供应表)表名sp（供应商产品供应表）数据库用户sa主键S_number,P_number其他排序字段无索引字段无序号字段名称数据类型（精度范围）允许为空Y/N唯一Y/N区别度默认值约束条件/说明1S_numberchar(32)NY高无主键/供应商号2P_numberchar(32)NY高无主键/产品编号sql脚本-建立供应商产品供应表create table sup-供应表( S_number char(32) not null, p_number char(32) not null, primary key(S_number,p_number), foreign key(S_number)references Suppliers(S_number) on delete cascade on update cascade, foreign key(p_number)references product(p_number) on delete cascade on update cascade );6.5 表4: orders表(订单表)表名orders（订单表）数据库用户sa主键o_number其他排序字段无索引字段无序号字段名称数据类型（精度范围）允许为空Y/N唯一Y/N区别度默认值约束条件/说明1o_numberchar(32)NY高无主键/订单号2timesdateNN低无订单日期3e_numberchar(32)YN中无雇员号4c_numberchar(32)NN高无客户号5pnintYN中无产品种类数sql脚本-建立订单表create table orders -订单表( o_number char(32) not null primary key, empl_number char(32) not null, c_number char(32) not null, times date, pn int,-产品总数 foreign key(c_number)references customer(c_number) on delete cascade on update cascade, foreign key(empl_number)references employee(empl_number) on delete cascade on update cascade );6.6 表5:po表(产品订购表)表名po（产品订购表）数据库用户sa主键p_number o_number其他排序字段无索引字段无序号字段名称数据类型（精度范围）允许为空Y/N唯一Y/N区别度默认值约束条件/说明1p_numberchar(32)NY高无主键/产品号2o_numberchar(32)NY高无主键/订单号3quantintNN低无订购产品数量sql脚本-建立产品订购表create table po-订购表(p_number char(32),o_number char(32),quant int,primary key(p_number,o_number),foreign key(p_number)references product(p_number)on delete cascadeon update cascade,foreign key(o_number)references orders(o_number)on delete cascadeon update cascade, );6.7 表6: employee表(雇员表)表名employee（雇员表）数据库用户sa主键e_number其他排序字段无索引字段无序号字段名称数据类型（精度范围）允许为空Y/N唯一Y/N区别度默认值约束条件/说明1e_numberchar(32)NY高无主键/雇员号2e_namechar(32)YN中无雇员名称3e_telnumChar(32)YY高无雇员电话4e_realrealYN低无雇员薪资5e_addresChar(32)YN中无雇员的地址sql脚本-建立雇员表create table employee-雇员表( empl_name char(32), empl_number char(32) primary key, empl_addres char(32), empl_telnumb char(32), empl_real int,);6.8 表7:customer表(客户表)表名customer（客户表）数据库用户sa主键c_number其他排序字段无索引字段无序号字段名称数据类型（精度范围）允许为空Y/N唯一Y/N区别度默认值约束条件/说明1c_numberchar(32)NY高无主键/客户表2c_namechar(32)YN中无客户名称3c_addresschar(32)YN中无客户地址4c_telnubchar(32)YY高无客户电话sql脚本-建立客户表create table customer-客户表( c_number char(32) primary key, c_name char(32), c_adress char(32), c_telnub char(32),);6.9 视图的设计1、建立一张客户信息的视图目的：屏蔽客户的地址信息。功能：能够向管理员提供简明，直接的客户信息展示。意义：有利于数据库安全的维护，防止任意修改数据库中的雇员信息。create view v_coustom(name,number)asselect c_name,c_numberfrom customer;2、建立一张订单视图信息的视图目的：用来查询每个客户下的订单数和用来查找下订单数最多的客户。功能：能够向管理员提供简明，直接客户下订单的信息展示。意义：有利于数据的检索。create view view_ordersasselect c_number,count(o_number)总数from ordersgroup by c_number;6.10 存储过程的设计1、查询订单数量功能：查询订单的数量目的：存储查询订单数量的操作意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤create procedure procedure_SearchOrdersNum -查询订单的存储过程asselect COUNT(*) 订单总数-查询订单数量from Orders;图1 订单总数查询图2、查询订单信息功能：查询订单信息目的：存储查询订单信息的操作意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤create procedure procedure_select_orders-查询订单信息o_number char(32)asif not exists ( select * from orders where o_number=o_number ) print该订单不存在 elseselect orders.o_number,times,pn,c_name,empl_namefrom orders ,customer,employee wherecustomer.c_number=orders.c_numberandorders.empl_number=employee.empl_number and orders.o_number=o_number图2 订单查询图 3、查询订单最多的客户信息功能：查询订单最多的客户信息目的：存储查询订单最多的客户信息的操作意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤create view view_orderasselect c_number,count(o_number)总数from ordersgroup by c_number;drop view view_orders;create procedure procedure_select_max_oders -查询订单数最多的客户asselect *from customerwhere c_number in ( select c_number from view_orders where 总数 in( select MAX(总数 ) from view_orders ) ); 图3 最多订单客户效果图4、查询客户信息功能：查询客户信息目的：存储查询客户信息的操作意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤create procedure procedure_select_coustom -查询客户信息name char(32)asif not exists ( select * from customer where c_name=name ) print该客户不存在 elseselect *from customer where c_name=name;图4 查询客户信息图5、查询雇员信息功能：查询雇员信息目的：存储查询雇员信息的操作意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤create procedure procedure_select_empl -查询雇员信息name char(32)asif not exists ( select * from employee where empl_name=name ) print该客户不存在 elseselect *from employee where empl_name=name;图5 查询雇员信息图6更新客户信息功能：更新客户信息目的：存储更新客户信息的操作意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤create procedure update_coustome-更新客户信息 number char(32), tell char(32), address char(32) as if not exists(select * from customer where c_number=number ) print你输入的客户不存在 else update customer set c_adress=address,c_telnub=tell where c_number=number ;图6 更新客户信息图7、查询产品信息功能：查询产品信息目的：存储查询产品信息的操作意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤create procedure procedure_selec_produt pnumber char(32)-产品的查询as select *from productwhere p_number=pnumber;图7 查询产品图8、插入供应商信息功能：插入供应商信息目的：存储插入供应商信息的操作意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤create procedure inser_suply-增加供应商 Snumber char(32), Sname char(32), Sadress char(32), Stelnub char(32)asif exists(select * from Suppliers where S_number=Snumber)print 该供应商已经存在，无需加入。else insertinto Suppliersvalues( Snumber, Sname, Sadress , Stelnub );图8 插入供应商图9、插入订单信息功能：插入订单信息目的：存储插入订单信息的操作意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤create procedure procedure_insert_orders-插入订单信息o_number char(32),empl_number char(32),c_number char(32),time date,pn intasif exists ( select * from orders where o_number=o_number ) print该订单已经存在，不需重复下订单 elseinsertinto orders values(o_number ,empl_number,c_number,time ,pn) 图9 插入订单图10、插入产品订购信息功能：插入产品订购信息目的：存储插入产品订购信息的操作意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤create procedure procedure_oeder_pro -插入订单产品pnumber char(32),onumber char(32)，quany intasif not exists ( select * from product where p_number=pnumber ) print仓库里没有该产品，不予订购 elseif not exists ( select * from orders where o_number=onumber ) print你输入的订单号不对 elseif exists ( select * from po where p_number=pnumber and o_number =onumber ) print该产品已经在订单中 else if exists ( select * from product where p_inventoryquany and p_number=pnumber ) print该产品库存不足不予以订购 elseinsert into povalues(pnumber ,onumber,quany ); 图10 插入订购产品图11、插入产品信息功能：插入产品信息目的：存储产品的操作意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤create procedure procedure_insert_pro-插入产品number char(32),name char(32),price int,exprice int,inventory intasif exists(select * from product where p_number=number)update productset p_inventory=p_inventory+inventoryelseinsert into product values( number, name, price, exprice, inventory )图 11 插入产品图12、更新供应商的信息功能：更新供应商的信息目的：存储产品的操作意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤create procedure procedure_update_suply-更新供应商信息 snumber char(32), stell char(32), saddress char(32) as if not exists( select * from Suppliers where S_number=snumber ) print你输入的供应商不存在 else update Suppliers set S_adress=saddress,S_telnub=stell where S_number =snumber; exec procedure_update_suply 001大连;图12 更新供应商信息图13、删除供应商功能：删除供应商的信息目的：存储产品的操作意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤 create procedure procedure_delet_supply-删除供应商信息 number char(32) as if not exists(select * from Suppliers where S_number=number ) print你输入的供应商不存在 else delete from Suppliers where S_number =number ; exec procedure_delet_supply 111;图13 删除供应商图14、最多的库存产品查询功能：最多的库存产品查询目的：存储产品的操作意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤图14 库存量最大产品查询图15、供应商供应的产品查询功能：供应商供应的产品查询目的：存储产品的操作意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤create procedure procedure_selet_pro -查询商供应的产品snumber char(32)asif not exists(select s_number from Suppliers where S_number=snumber )print该供应商不存在elseselect S_name,p_name from Suppliers,sup,productwhere Suppliers.S_number=sup.S_number and Suppliers.S_number=snumber and sup.p_number=product.p_numberexec procedure_selet_pro 011;图16 查询供应商供应的产品图16、更新客户的信息功能：更新客户信息目的：存储产品的操作意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤 create procedure procedure_update_coustome-更新客户信息 number char(32), tell char(32), address char(32) as if not exists(select * from customer where c_number=number ) print你输入的客户不存在 else update customer set c_adress=address,c_telnub=tell where c_number=number ; exec procedure_update_coustome 01,07313936096,广州图16 更新客户信息图17、删除客户信息功能：删除客户信息目的：存储产品的操作意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤create procedure procedure_delet_coustome-删除客户信息 number char(32) as if not exists(select * from customer where c_number=number ) print你输入的客户不存在 else delete from customer where c_number=number ; exec procedure_delet_coustome 05 图17 删除客户信息图18、更新雇员信息功能：更新雇员信息目的：存储产品的操作意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤create procedure procedure_update_emp-更新雇员信息 enumber char(32), etell char(32), eaddress char(32), e_real int as if not exists(select * from employee where empl_number=enumber ) print你输入的雇员不存在 else update employee set empl_addres=eaddress,empl_telnumb=etell,empl_real=e_real where empl_number=enumber ; exec procedure_update_emp 001,1111111111,changsha,2999; select *图18 更新雇员信息图19删除雇员信息功能：删除雇员信息目的：存储产品的操作意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤 create procedure procedure_delet_emp-删除雇员信息 number char(32) as if not exists(select * from employee where empl_number=number ) print你输入的雇员不存在 else delete from employee where empl_number =number ; exec procedure_delet_emp 001图19 删除雇员信息图20查询雇员信息按工资高低排序select *from employee order by empl_real图20 雇员工资高低排序图21、完整的订单销售流程功能：插入订单信息和订购信息以及修改产品库存等信息目的：存储从客户下单到订单订购产品的流程的操作意义：直观展示订单销售流程，使用户能够更简明的执行完整的订单销售流程create procedure procedure_Ord-完整的订单销售流程onumber char(32),odate date,enumber char(32),cnumber char(32),quant int,pn int,pnumber char(32)as if exists ( select * from orders where o_number=onumber )print该订单已经存在，不需重复下订单 else if not exists ( select * from product where p_number=pnumber ) print仓库里没有该产品，不予订购else if not exists ( select * from customer where c_number=cnumber ) print你输入的客户不存在else if not exists ( select * from employee where empl_number=enumber ) print你输入的雇员不存在else if exists ( select * from product where p_inventoryquant and p_number=pnumber ) print该产品库存不足不予以订购elsebegininsertinto orders values(onumber ,enumber,cnumber ,odate ,pn)insert into povalues(pnumber ,onumber,quant)print订单下发成功end;图21 完整的订单流程图6.11 触发器的设计1、建立触发器update_invert：当插入了一条新的产品订购信息后，则激活该触发器，新的产品库存量等于旧库存量减去产品订购的数量，。当用户退订单的时候触发库存量加上订单退出时的产品数量。功能：能够根据数据库中数据的变化来自动进行数据更新操作。目的：将必备的操作自动化。意义：为数据库用户提供一种简便操作，省去了逐项更改数据的麻烦。create trigger update_invert实现当插人（退）产品的时候库存减少（增加）on pofor insert,deleteas update productset p_inventory=p_inventory+(select quantfrom deletedwhere product.p_number=deleted.p_number)from po,product,deletedwhere product.p_number=deleted.p_numberupdate productset p_inventory=p_inventory-(select quantfrom insertedwhere product.p_number=inserted.p_number)from po,product,insertedwhere product.p_number=inserted.p_number;6.12 函数的设计无7.安全性设计7.1 防止用户直接操作数据库的方法用户标识和鉴别是系统提供的最外层安全保护措施。其方法是由系统提供一定的方式让用户标识自己的名字或身份。每次用户要求进入系统时，由系统进行核对，通过鉴定后才提供机器使用权。还可以通过建立视图来控制对数据的访问。7.2 用户帐号密码的加密方法数据加密是防止数据库中数据在存储和传输中关系的有效字段。加密的基本思想是根据一定的算法讲原始数据变换为不可直接识别的格式，从而使得不知道解密算法的人无法获知数据的内容。例如：exec sp_addlogin fanyangcong, 123, xx-创建登入名exec sp_grantdbaccess fanyangcong , xx -在数据库中创建新用户exec sp_addsrvrolemember fanyangcong,sysadminsp_addrole fanyangcong , db_owner-创建数据库角色 GRANT CREATE TABLE TO fanyangcongGRANT CREATE VIEW TO fanyangcongGRANT SELECT ON employee TO fanyangcongWITH GRANT OPTIONREVOKE UPDATEON employeeFROM fanyangcongCA