2021年订单销售数据库设计

五、逻辑设计

欧阳光明（2021.03.07）5.1 ER图

5.2 关系模型

供应商(供应商编号，名称，地址，联系电话)

产品(产品编号，产品名称，产品类别，售价，出厂价，存货量)供应(供应商编号，产品编号)

订购(产品编号，订单编号，数量)

客户(客户编号，姓名，通信地址，电话)

订单(订单编号，日期，客户编号，雇员编号)

雇员(雇员编号，姓名，联系电话，工资)

注：有下划线的表示该属性为主码。

六、物理设计

6.1 表汇总

6.2 表[1]:[ Suppliers表](供应商表)

6.3 表[2]:[ Product表](产品表)

6.4 表[3]:[ SP表](供应商产品供应表)

6.5 表[4]:[ Orders表](订单表)

6.6 表[5]:[ PO表](产品订购表)

6.7 表[6]:[ Employee表](雇员表)

6.8 表[7]:[ Customer表](客户表)

6.9 视图的设计

1、建立一张雇员信息的视图

目的：屏蔽雇员的工资信息。

功能：能够向管理员提供简明，直接的雇员信息展示。

意义：有利于数据库安全的维护，防止任意修改数据库中的雇员信息。

create view view_Employee--建立Employee视图，屏蔽工资属性

as

select Enumber,Ename,Etelnum

from Employee

with check option;

2、建立一张产品信息的视图

目的：屏蔽产品的出厂价信息。

功能：能够向管理员提供简明，直接的产品信息展示。

意义：有利于数据库安全的维护，防止任意修改数据库中的产品信息。

create view view_Producte--建立Product视图，屏蔽出厂价属性

as

select Pnumber,Pname,Pcategory,Pprice,Pinventory

from view_Producte

with check option;

6.10 存储过程的设计

1、查询订单数量

功能：查询订单的数量目的：存储查询订单数量的操作

意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤

create procedure procedure_SearchOrdersNum --查询订单的存储过程As select COUNT(*) '订单总数'--查询订单数量 from Orders;

2、查询客户的订单信息

功能：查询客户的订单信息目的：存储查询客户的订单信息的操作

意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤

create procedure procedure_CustomerOrders--查询某个客户订单信息@Cname char(10) as select Onumber,Odate,Enumber

from Orders,Customer

where Cname like @Cname and https://www.360docs.net/doc/3b12997655.html,umber like https://www.360docs.net/doc/3b12997655.html,umber;

3、查询订单最多的客户信息

功能：查询订单最多的客户信息目的：存储查询订单最多的客户信息的操作

意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤

create procedure procedure_SearchMaxOrders--查询订单最多的客户信息

as

select Cnumber,Cname,Caddress,Ctelnum

from Orders,Customer

where https://www.360docs.net/doc/3b12997655.html,unmer=https://www.360docs.net/doc/3b12997655.html,unmer

group by Cnumber having MAX(Cnumber);

4、查询客户信息

功能：查询客户信息目的：存储查询客户信息的操作

意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤

create procedure procedure_SearchCustomer--查询某客户信息

@Cname char(10)

as

select *

from Customer

where Cname=@Cname;

5、查询雇员信息

功能：查询雇员信息目的：存储查询雇员信息的操作

骤

create procedure procedure_SearchEmployee--查询某雇员信息

@Ename char(10)

as

select *

from Employee

where Ename=@Ename;

6、查询某订单对应的产品信息

功能：查询某订单对应的产品信息目的：存储查询某订单对应的产品信息的操作

意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤

create procedure procedure_SearchPO--查询某订单的产品信息

@Onumber char(12)

as

select

Onumber,Product.Pnumber,Pname,Pcategory,Pprice,Pex_price,Pinventor y

from Product,PO

where Onumber=@Onumber and PO.Pnumber=Product.Pnumber;

7、查询产品信息

功能：查询产品信息目的：存储查询产品信息的操作

骤

create procedure procedure_SearchProduct--查询某产品信息

@Pnumber char(12)

as

select Pnumber,Pname,Pcategory,Pprice,Pex_price,Pinventory,Snumber from Product,SP

where Pnumber=@Pnumber and SP.Pnumber=@Pnumber;

8、插入供应商信息

功能：插入供应商信息目的：存储插入供应商信息的操作

意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤

create procedure procedure_InsertSuppliers--插入供应商信息

@Snumber char(12),@Sname char(30),@Saddress char(30),@Stelnum int

as

IF EXISTS(SELECT * FROM Suppliers WHERE Snumber=@Snumber) print'该供应商记录已经存在'

ELSE

insert into Suppliers(Snumber,Sname,Saddress,Stelnum)

values(@Snumber,@Sname,@Saddress,@Stelnum);

9、插入订单信息

功能：插入订单信息目的：存储插入订单信息的操作

骤

create procedure procedure_InsertOrders--插入订单表

@Onumber char(12),@Odate date,,@Enumber char(12),@Cnumber char(12)

as

IF EXISTS(SELECT * FROM Orders WHERE Onumber=@Onumber) print'该订单记录已经存在'

ELSE

insert into Orders(Onumber,Odate,Enumber,Cnumber)

values(@Onumber,@Odate,@Enumber,@Cnumber);

10、插入产品订购信息

功能：插入产品订购信息目的：存储插入产品订购信息的操作意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤

create procedure procedure_InsertPO--插入产品订购表

@Pnumber char(12),@Onumber char(12),@Quantity int

as

IF EXISTS(SELECT * FROM PO WHERE Pnumber=@Pnumber and Onumber=@Onumber)--当该订单已经订购改产品时不予重复订购print'该产品订购订单记录已经存在'

IF EXISTS(SELECT * FROM Product WHERE Pinventory<@Quantity)--当库存量不足时不予订购

print'该产品库存不足，无法订购'

ELSE

insert into PO(Onumber,Pnumber,Quantity)

values(@Onumber,@Pnumber,@Quantity);

11、插入产品信息

功能：插入产品信息目的：存储产品的操作

意义：下次重复同样的动作时，可直接执行存储过程，简化操作步骤

create procedure procedure_InsertProduct --插入产品信息

@Pnumber char(12),@Pname char(30),@Pcategory char(15),@Pprice int,@Pex_price real,@Pinventory real

as

IF EXISTS(SELECT * FROM Product WHERE Pnumber=@Pnumber)--若有相同产品，只更新库存量

update Product

set Pinventory=Pinventory+@Pinventory

ELSE

insert into Product(Pnumber,Pname,Pcategory,Pprice,Pex_price,Pinventory) values(@Pnumber,@Pname,@Pcategory,@Pprice,@Pex_price,@Pinven tory);

12、完整的订单销售流程

功能：插入订单信息和订购信息以及修改产品库存等信息

目的：存储从客户下单到订单订购产品的流程的操作

意义：直观展示订单销售流程，使用户能够更简明的执行完整的订单销售流程

create procedure procedure_Order --完整的订单销售流程

@Onumber char(12),@Odate date,,@Enumber char(12),@Cnumber char(12)

@Pnumber char(12),@Onumber char(12),@Quantity int

as

IF EXISTS(SELECT * FROM Orders WHERE Onumber=@Onumber)--若有重复订单，则不予下单

print'该订单记录已存在，不能重复下订单'

ELSE

insert into Orders(Onumber,Odate,Enumber,Cnumber)

values(@Onumber,@Odate,@Enumber,@Cnumber);

IF EXISTS(SELECT * FROM PO WHERE Pnumber=@Pnumber and Onumber=@Onumber)--若有重复订购单，则不予订购

print'该产品订购记录已存在，不能重复订购'

ELSE

IF EXISTS(SELECT * FROM Product WHERE Pinventory<@Quantity)--若产品库存量不足，则不予订购

print'产品库存量不足，不能订购'

ELSE

insert into PO(Onumber,Pnumber,Quantity)

values(@Onumber,@Pnumber,@Quantity);

6.11 触发器的设计

1、建立触发器insert_PO：当插入了一条新的产品订购信息后，则激活该触发器，新的产品库存量等于旧库存量减去产品订购的数量。

功能：能够根据数据库中数据的变化来自动进行数据更新操作。目的：将必备的操作自动化。

意义：为数据库用户提供一种简便操作，省去了逐项更改数据的麻烦。

create trigger insert_PO--AFTER触发器，当订购产品后，自动减少库存量

on po after insert

for each row

as begin

update Product set new.Pinventory=old.Pinventory-new.Quantity; end;