小型自选商场商品管理系统

一、系统总体功能设计

1、  功能需求分析：

售货员需要输入商品号、数量、顾客的现金，就得出应该找零的金额。

管理员需要在售货员卖掉商品的同时动态刷新库存、销售表。

进货员需要方便的输入商品号数量动态刷新库存、进货表。

统计员需要查询销售表、进货表，还要统计月销售。

老板需要查看日盘存月盘存。

2、  file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image001.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image002.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image003.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image004.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image004.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image004.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image005.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image006.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image007.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image008.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image009.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image010.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image011.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image012.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image013.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image014.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image015.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image016.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image017.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image018.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image019.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image020.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image021.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image022.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image023.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image024.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image025.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image026.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image027.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image028.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image029.png 功能设计

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

二、数据库设计

1、  file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image030.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image031.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image032.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image033.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image034.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image035.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image036.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image037.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image038.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image039.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image040.png概念结构图：

 

2、逻辑结构设计：

库存（kc）

名称	取值	约束
goodsid	Varchar(10)	主键
Name	Varchar(10)
place	vrchar(50)
price	money
quantity	int
suppliersid	Varchar(50)

进货（jh）

名称	取值	约束
goodsid	Varhcar(20)
price	money
quantity	int
money	money
time	datetime
purchasid	Varchar(10)	主键

供应（gy）

名称	取值	约束
suppliersid	Varchar(10)	主键
name	Varchar(20)
address	Varchar(50)
phonenumber	Varchar(11)

销售（xs）

名称	取值	约束
salesid	Varchar(10)	主键
goodsid	Varchar(10)
price	money
quantity	int
money	money
time	daterime

file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image041.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image042.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image043.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image044.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image045.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image046.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image047.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image048.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image049.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image050.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image051.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image052.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image053.png    3、具体的数据库逻辑结构设计：

file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image054.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image055.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image056.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image057.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image058.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image059.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image060.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image061.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image062.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image063.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image064.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image065.png file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image066.png  

 

 

 

 

 

 

 

 

 

 

 

 

 

 

	file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image067.png
	file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image068.png

 

 

 

 

三、系统实施

系统编程：sql语句：

--数据库建立名为：dzpitsoft

--有时候打不出中文就打英文多一点。

--我的表简单了一点是因为时间关系，我在想存储过程时浪费了较多的时间了。

--建立四个表gy:供应，jh:进货，kc:库存，xs:销售。

create table gy

(suppliersid varchar(10),

name varchar(10),

address varchar(10),

phonenumber varchar(11),

manager varchar(20)

)

create table jh

(purchaseid varchar(10),

goodsid varchar(10),

price money,

quantity int,

money money,

time datetime

)

create table kc

(goodsid varchar(10),

name varchar(20),

place varchar(50),

price money,

quantity int,

suppliersid varchar(10),

)

create table xs

(salesid varchar(10),

goodsid varchar(10),

price money,

quantity int,

money money,

time datetime

)

--向各表插入数据

insert into gy values('001', 'microsoft', 'American', '13267881693', 'bill')

insert into gy values('002', 'google', 'American', '13267881693', 'makke')

insert into gy values('003', 'IBM', 'American', '13267881693', 'bamise')

insert into gy values('004', 'facebook', 'American', '13267881693', 'makee')

insert into gy values('005', 'HP', 'American', '13267881693', 'jeson')

insert into gy values('006', 'apple', 'American', '13267881693', 'qiaobusi')

insert into gy values('007', 'sony', 'Japan', '13267881693', 'deng')

insert into gy values('008', 'sharp', 'Japan', '13267881693', 'ganhong')

insert into gy values('009', 'sansung', 'SouthKorea', '13267881693', 'zicen')

insert into gy values('0010', '360', 'China', '13267881693', 'zhou')

 

insert into jh values('001', '001', '2000', '300', '600000', '2010/12/04')

insert into jh values('002', '002', '3000', '520', '600000', '2010/12/06')

insert into jh values('003', '003', '4000', '2457', '600000', '2010/12/04')

insert into jh values('004', '004', '5000', '525', '600000', '2010/12/04')

insert into jh values('005', '005', '6000', '69', '600000', '2010/12/05')

insert into jh values('006', '006', '7000', '2157', '600000', '2010/12/04')

insert into jh values('007', '007', '8000', '124', '600000', '2010/12/05')

insert into jh values('008', '008', '9000', '578', '600000', '2010/12/04')

insert into jh values('009', '009', '1000', '52', '600000', '2010/12/01')

insert into jh values('0010', '0010', '2500', '6366', '600000', '2010/12/07')

 

insert into kc values('001', 'windows7', 'American', '40000', '300', '001')

insert into kc values('001', 'computer', 'American', '40000', '300', '001')

insert into kc values('001', 'moblephone', 'American', '40000', '300', '001')

insert into kc values('001', 'earth', 'American', '40000', '300', '001')

insert into kc values('001', 'cpu', 'American', '40000', '300', '001')

insert into kc values('001', 'iphone', 'American', '40000', '300', '001')

insert into kc values('001', 'motherboard', 'American', '40000', '300', '001')

insert into kc values('001', 'mouse', 'American', '40000', '300', '001')

insert into kc values('001', 'disk', 'American', '40000', '300', '001')

insert into kc values('001', 'keyboard', 'American', '40000', '300', '001')

 

insert into xs values('001', '001', '4000', '1', '4000', '2010/12/25')

--以下是存储过程

--收银台：

USE [dzpitsoft]

GO

ALTER PROCEDURE [dbo].[dzp收银台soft]

@商品id varchar(10),

@数量int,

@顾客付款现金int out

AS

select name '商品', price '优惠价', price*@数量'应付', @顾客付款现金'实收现金', @顾客付款现金-price*@数量'找零'

from kc

where goodsid=@商品id

insert into xs(goodsid, quantity)

values(@商品id, @数量)

update kc

set quantity=quantity-@数量

where goodsid=@商品id

--查询供应商的信息

USE [dzpitsoft]

GO

ALTER PROCEDURE [dbo].[查询供应商信息]

@供应商id varchar(10) out

AS

select *

from gy

where suppliersid=@供应商id

--进货记录

USE [dzpitsoft]

GO

ALTER PROCEDURE [dbo].[进货记录]

@进货id varchar(10) out

AS

select *

from jh

where purchaseid=@进货id

--销售情况

USE [dzpitsoft]

GO

ALTER PROCEDURE [dbo].[销售情况]

@销售id varchar(10) out

AS

select *

from xs

where salesid=@销售id

--新建进货单

USE [dzpitsoft]

GO

ALTER PROCEDURE [dbo].[新建进货单]

@供应商id varchar(10),

@商品id varchar(10),

@价格money,

@数量int,

@总价money,

@日期datetime out

AS

insert into jh

values(@供应商id, @商品id, @价格, @数量, @总价, @日期)

update kc

set quantity=quantity+@数量

where goodsid=@商品id

update kc

set quantity=quantity+@数量

where goodsid=@商品id

--月统计

USE [dzpitsoft]

GO

ALTER PROCEDURE [dbo].[月统计]

@年份varchar(10),

@月份varchar(10) out

AS

select COUNT(salesid) '销售次数', SUM(money) '总收入', SUM(quantity)

from xs

where YEAR(time)=@年份and MONTH(time)=@月份

四、实验总结

我在实习的时候遇到了很多的困难，但是经过我的翻书查找，我最终明白了该怎么做。这是平时的环境没有锻炼到得能力。我想在我的实习中的学习生活是很充实的，我们都应该努力提高自身的水平，为以后的学习工作打好基础。

 

 

file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image070.jpg file:///N:/Users/i/AppData/Local/Temp/msohtmlclip1/01/clip_image072.jpg

 

 

课程实习报告书