Database Design Report of Supermarket Purchase, Sale and Inventory System
Note: This design report omits the requirement analysis section, entity description, contact description, index description, etc. It focuses on the database implementation process, sql language writing and other key presentations that I think are important.
In addition, the software PowerDesigner is mainly used in the early stage of the system, from requirement analysis to model design, constraints, views, business rules and so on.
Product Brief
Supermarket purchase, sale and storage system mainly provides online management functions for goods on shelves, sales and cash receipts, and warehouse storage.
Target customers: small and medium-sized supermarkets
Customer's business needs:
Change the traditional manual management to realize daily management informationization;
Through the quick inquiry and processing of inventory and sales information, the speed and scientificity of commodity procurement can be improved.
Improve the management level of supermarkets, reduce operating costs and improve work efficiency.
Overall framework of the system 
data model
BPM model 
CDM model 
PDM model 
Design of Partial Memory, Trigger and Function
I am responsible for the memory, trigger and function design of the inventory module. Here are some examples:
- Memory Design 1
/*Employees with the title of "purchasing clerk" get a 15% raise, tallyman 10%, cashier 5%, manager 20%.*/
create procedure proc_raise
as
declare cur cursor for select workerNum,job from t_user
declare @increment decimal(2,1)
declare @num int
declare @tjob varchar(50)
open cur
fetch next from cur into @num,@tjob
while(@@fetch_status=0)
begin
if @tjob='Buyer' set @increment=0.15
else if @tjob='Tally clerk'set @increment=0.1
else if @tjob='Cashier' set @increment=0.05
else if @tjob='manager' set @increment=0.2
update t_user
set salary=salary*(1+@increment)
where workerNum=@num
fetch next from cur into @num,@tjob
end
close cur
deallocate cur
goTest results:
Employee table before memory execution: 
After executing the payroll function of the memory:
execute proc_raise
- Memory design 2,
/*Empty Inventory Information Table: Commodity Information of t_inventory Clock Inventory Volume 0*/
create procedure proc_amount0
as
declare cur cursor for select goodsNum,amount from t_inventory
declare @gnum int
declare @gamount int
open cur
fetch next from cur into @gnum,@gamount
while(@@fetch_status=0)
begin
if @gamount=0
delete from t_inventory
where goodsNum=@gnum
fetch next from cur into @gnum,@gamount
end
close cur
deallocate curTest results:
t_inventory before memory execution: 
Execution memory:
execute proc_amount0
- User-defined function design 1,
/*Enter the commodity number and label it as'Promotion - Buy one get one free'on the t_goodsOn table.*/
create function dbo.func_num_cuxiao(@gnum int)
returns varchar(50)
as
begin
declare @gname varchar(50);
declare @gprice int;
select @gname=goodsName from t_goodsOn where t_goodsOn.goodsNum=@gnum;
select @gprice=price from t_goodsOn where t_goodsOn.goodsNum=@gnum;
set @gname=@gname+'Promotion-Buy 1 Get 1 FREE';
return @gname
end
declare @t varchar(50)
execute @t= dbo.func_num_cuxiao "01";
update t_goodsOn
set goodsName=@t where goodsNum=01 ;
select *from t_goodsOn
Test results: 
- User-defined function design 2,
/*For goods with inventory less than 10, add "stock tight!!!" in remark. Fields*/
create function dbo.func_less(@gnum int)
returns varchar(50)
as
begin
declare @mark varchar(50)
set @mark='The stock is tight!!!'+(select remark from t_inventory where goodsNum=@gnum)
return @mark
end
create procedure proc_tmp
as
declare cur cursor for select goodsNum from t_inventory
declare @gnum int
declare @ta int
declare @t varchar(50)
open cur
fetch next from cur into @gnum
while(@@fetch_status=0)
begin
select @ta=amount from t_inventory where goodsNum=@gnum
if @ta<10
begin
execute @t= dbo.func_less @gnum ;
update t_inventory
set remark=@t where goodsNum=@gnum ;
end
fetch next from cur into @gnum
end
close cur
deallocate cur
Results Test:
The original inventory information table t_inventory: 
execute proc_tmpAfter implementation: 
- Flip-flop design 1,
/*Trigger: t_goodsBuy commodity price changes, t_incentory same commodity price changes, price + (current price-original price), t_goodsSale commodity price also changes.*/
create trigger tri_alter_cost
on dbo.t_goodsBuy
after insert
as
begin
declare @num int;
declare cur cursor for select goodsNum from inserted;
open cur;
fetch from cur into @num;
while @@FETCH_STATUS=0
begin
update t_inventory
set cost=(select cost from inserted where goodsNum=@num)
where goodsNum=@num;
update t_inventory
set price=price+(select cost from inserted where goodsNum=@num)-(select cost from t_inventory where goodsNum=@num)
where goodsNum=@num;
update t_goodsSale
set price=price+(select cost from inserted where goodsNum=@num)-(select cost from t_inventory where goodsNum=@num)
where goodsNum=@num;
fetch next from cur into @num;
end
close cur;
end
Results Test:
Test results:
The original inventory table t_inventory: 
The original sales table t_goodsSale: 
/*After purchasing by Purchaser and adding t_goodsBuy:*/
insert into t_goodsBuy
values('01','Chewing gum','03','10','150','box','Green arrow company','2016/8/23'),
('02','Instant noodles','01','10','10','box','Master Kang Company','2016/8/23'),
('03','cola','02','15','12','box','Coca-Cola Company','2016/8/23'),
('04','Biscuits','04','11','15','box','Pleasure company','2016/8/23');
t_inventory inventory table: 
t_goodsSale sales table: 
Execute successful SQL source code:
- Create tables and indexes
/*==============================================================*/
/* Table: t_buyer */
/*==============================================================*/
create table t_buyer (
workerNum int not null,
workerName varchar(50) not null,
)
go
alter table t_buyer
add constraint PK_T_BUYER primary key nonclustered (workerNum)
go
/*==============================================================*/
/* Index: Index_workerNum */
/*==============================================================*/
create index Index_workerNum on t_buyer (
workerNum ASC
)
go
/*==============================================================*/
/* Index: Index_workerName */
/*==============================================================*/
create index Index_workerName on t_buyer (
workerName ASC
)
go
/*==============================================================*/
/* Table: t_cashier */
/*==============================================================*/
create table t_cashier (
workerNum int not null,
workerName varchar(50) not null,
workTime varchar(5) not null
)
go
alter table t_cashier
add constraint PK_T_CASHIER primary key nonclustered (workerNum)
go
/*==============================================================*/
/* Index: Index_workerNum */
/*==============================================================*/
create index Index_workerNum on t_cashier (
workerNum ASC
)
go
/*==============================================================*/
/* Index: Index_workerTime */
/*==============================================================*/
create index Index_workerTime on t_cashier (
workTime ASC
)
go
/*==============================================================*/
/* Table: t_goodsBuy */
/*==============================================================*/
drop table t_goodsBuy
create table t_goodsBuy (
goodsNum int not null,
goodsName varchar(50) not null,
workerNum int not null,
cost int not null,
amount int not null,
units varchar(50) not null,
suppler varchar(50) not null,
date varchar(50) not null
)
go
alter table t_goodsBuy
add constraint PK_T_GOODSBUY primary key nonclustered ()
go
/*==============================================================*/
/* Index: Index_goodsName */
/*==============================================================*/
create index Index_goodsName on t_goodsBuy (
goodsName ASC
)
go
/*==============================================================*/
/* Index: Index_date */
/*==============================================================*/
create index Index_date on t_goodsBuy (
date ASC
)
go
/*==============================================================*/
/* Index: Index_wokerNum */
/*==============================================================*/
create index Index_wokerNum on t_goodsBuy (
workerNum ASC
)
go
/*==============================================================*/
/* Table: t_goodsOn */
/*==============================================================*/
create table t_goodsOn (
goodsNum int not null,
goodsName varchar(50) not null,
cost int not null,
prcie int not null,
amount int not null,
units varchar(50) not null,
remark varchar(300) null,
workerNum int not null
)
go
alter table t_goodsOn
add constraint PK_T_GOODSON primary key nonclustered (goodsNum, workerNum)
go
/*==============================================================*/
/* Index: Index_amount */
/*==============================================================*/
create index Index_amount on t_goodsOn (
amount ASC
)
go
/*==============================================================*/
/* Table: t_goodsOnOut */
/*==============================================================*/
create table t_goodsOnOut (
workerNum int not null,
goodsNum int not null,
goodsName varchar(50) not null,
amount int not null
)
go
alter table t_goodsOnOut
add constraint PK_T_GOODSONOUT primary key nonclustered (workerNum, goodsNum, t_g_workerNum)
go
/*==============================================================*/
/* Index: Index_workerNum */
/*==============================================================*/
create index Index_workerNum on t_goodsOnOut (
workerNum ASC
)
go
/*==============================================================*/
/* Index: Index_goodsNum */
/*==============================================================*/
create index Index_goodsNum on t_goodsOnOut (
goodsNum ASC
)
go
/*==============================================================*/
/* Table: t_goodsSale */
/*==============================================================*/
drop table t_goodsSale
create table t_goodsSale (
workerNum int not null,
goodsNum int not null,
goodsName varchar(50) not null,
prcie int not null,
amount int not null,
units varchar(50) not null
)
go
alter table t_goodsSale
add constraint PK_T_GOODSSALE primary key nonclustered (goodsNum)
go
/*==============================================================*/
/* Index: Index_workerNum */
/*==============================================================*/
create index Index_workerNum on t_goodsSale (
workerNum ASC
)
go
/*==============================================================*/
/* Index: Index_profit */
/*==============================================================*/
--create index Index_profit on t_goodsSale (
--profit ASC
--)
--go
/*==============================================================*/
/* Index: Index_amount */
/*==============================================================*/
create index Index_amount on t_goodsSale (
amount ASC
)
go
/*==============================================================*/
/* Table: t_goosClass */
/*==============================================================*/
drop table t_goodsClass
create table t_goodsClass (
classNum int not null,
className varchar(50) not null,
goodsNum int not null,
goodsName varchar(50) not null,
workerNum int not null
)
go
alter table t_goodsClass
add constraint PK_T_GOOSCLASS primary key nonclustered (goodsNum)
go
/*==============================================================*/
/* Index: Index_goodsName */
/*==============================================================*/
create index Index_goodsName on t_goodsClass (
goodsName ASC
)
go
/*==============================================================*/
/* Index: Index_className */
/*==============================================================*/
create index Index_className on t_goodsClass (
className ASC
)
go
/*==============================================================*/
/* Table: t_inventory */
/*==============================================================*/
create table t_inventory (
goodsNum int not null,
goodsName varchar(50) not null,
className varchar(10) not null,
amount int not null,
cost int not null,
prcie int not null,
units varchar(50) not null,
remark varchar(300) null,
)
go
alter table t_inventory
add constraint PK_T_INVENTORY primary key nonclustered (goodsNum, workerNum)
go
/*==============================================================*/
/* Index: Index_className */
/*==============================================================*/
create index Index_className on t_inventory (
className ASC
)
go
/*==============================================================*/
/* Index: Index_amount */
/*==============================================================*/
create index Index_amount on t_inventory (
amount ASC
)
go
/*==============================================================*/
/* Table: t_manager */
/*==============================================================*/
create table t_manager (
workerNum int not null,
workerName varchar(50) not null
)
go
alter table t_manager
add constraint PK_T_MANAGER primary key nonclustered (workerNum)
go
/*==============================================================*/
/* Index: Index_workerNum */
/*==============================================================*/
create index Index_workerNum on t_manager (
workerNum ASC
)
go
/*==============================================================*/
/* Index: Index_workerName */
/*==============================================================*/
create index Index_workerName on t_manager (
workerName ASC
)
go
/*==============================================================*/
/* Table: t_tallyClerk */
/*==============================================================*/
create table t_tallyClerk (
workerNum int not null,
workerName varchar(50) not null
)
go
alter table t_tallyClerk
add constraint PK_T_TALLYCLERK primary key nonclustered (workerNum)
go
/*==============================================================*/
/* Index: Index_workerNum */
/*==============================================================*/
create index Index_workerNum on t_tallyClerk (
workerNum ASC
)
go
/*==============================================================*/
/* Index: Index_workerName */
/*==============================================================*/
create index Index_workerName on t_tallyClerk (
workerName ASC
)
go
/*==============================================================*/
/* Table: t_user */
/*==============================================================*/
drop table t_user
create table t_user (
workerNum int not null,
workerName varchar(50) not null,
workerSex varchar(5) check(workerSex in('male','female')) not null,
workerAge int check(workerAge>=18 and workerAge<=60) not null,
workTime varchar(5) not null,
job varchar(50) check(job in('Buyer','Cashier','Tally clerk','manager')) not null,
salary int not null
)
go
alter table t_user
add constraint PK_T_USER primary key nonclustered (workerNum)
go
/*==============================================================*/
/* Index: Index_workerNum */
/*==============================================================*/
create index Index_workerNum on t_user (
workerNum ASC
)
go
/*==============================================================*/
/* Index: Index_workerName */
/*==============================================================*/
create index Index_salary on t_user (
salary DESC
)
go
- Create view
Explanation: The function of view includes simplifying the operation of data, providing the security of database, etc. Therefore, three views are designed because of the need to timely check the number of goods on shelves, the quantity of inventory, and timely replenishment, at the same time, to view the goods with the highest sales volume, and to optimize the sales plan.
/*==============================================================*/
/* View: View_goodsOnAmount Query the quantity view of goods on the shelf */
/*==============================================================*/
create view lihuoyuan.View_goodsOnAmount as
select
goodsNum,
goodsName,
amount
from
t_goodsOn
with check option
go
/*==============================================================*/
/* View: View_goodsSale Query the Sales Quantity View of Goods */
/*==============================================================*/
create view View_goodsSale as
select
goodsNum,
goodsName,
amount
from
t_goodsSale
go
/*==============================================================*/
/* View: View_inventoryAmount Query Inventory View of Goods */
/*==============================================================*/
create view caigouyuan.View_inventoryAmount as
select
goodsNum,
goodsName,
amount
from
t_inventory
with check option
go