医药超市管理系统.docx

一、 需求分析
根据用户对该系统需求的描述，系统包括以下几个子系统：
1. 职工管理
要求能够查询某家门店的职工情况，职工的详细信息；
能够添加、删除及更新。
2. 门店管理
要求能够提供销售情况。如某门店在某天销售了哪些药品，销售额是多少，某月各门店日平均销售额是多少；
能够添加、删除及更新。
3. 药品管理
要求能够查询某家门店的某药品存量，或某药品在哪个门店有货，有多少；
能够添加、删除及更新。
4. 供应商管理
要求能够查询某门店的某种药品来自于哪个供应商，来自于哪个城市；
能够添加、删除及更新。
综合以上需求，设计如下数据结构：
1. 数据结构：职工

含义：记录职工的基本信息。
组成：编号，姓名，所属门店编号，性别，年龄，职务
2. 数据结构：门店
含义：记录门店的基本信息。
组成：编号，地址，邮编，电话。
3. 数据结构：销售额
含义：记录各门店的每日销售情况。
组成：门店编号，药品编号，销售数量，单价，日期。
4. 数据结构：药品
含义：记录药品的基本信息。
组成：编号，名称，条码。
5. 数据结构：存货量
含义：记录药品在各门店的存货情况。
组成：药品编号，门店编号，存货量。
6. 数据结构：供应商
含义：记录供应商的基本信息。
组成：编号，名称，城市，地址，电话。
7. 数据结构：供应
含义：记录各门店的进货情况。
组成：门店编号，药品编号，供应商编号，进货单价，进货数量，进货日期。

数据字典：
数据项
类型
长度
取值范围
与其他数据项的逻辑关系
职工编号
字符型
5
5Byte
非空且可以唯一标识一个职工
职工姓名
字符型
6
0-6Byte
非空
职工性别
字符型
2
2Byte
非空
职工年龄
无符号整型
1
1 Byte
非空且不小于18
职工职务
字符型
10
0-10 Byte
非空
门店编号
字符型
5
5 Byte
非空且可以唯一标识一个门店
门店地址
字符型

50
0-50 Byte
非空
门店邮编
字符型
6
6 Byte

门店电话
字符型
15
0-15 Byte
非空
药品编号
字符型
5
0-5 Byte
非空且可以唯一标识一种药品
药品名称
字符型
20
0-20 Byte
非空
药品条码
字符型
20
0-20 Byte
非空且唯一
销售数量
无符号整型
5
0-5 Byte
不小于0
销售单价
浮点型
10
0-10 Byte
不小于0
销售日期
DATE型
20
20 Byte
非空
存货量
无符号整型
5
0-5 Byte
不小于0

供应商编号
字符型
5
5 Byte
非空且可以唯一标识一个供应商
供应商名称
字符型
20
0-20 Byte
非空
供应商城市
字符型
20
0-20 Byte

供应商地址
字符型
20
0-20 Byte

供应商电话
字符型
20
0-20 Byte

进货单价
浮点型
10
0-10 Byte
非空且不小于0
进货数量
无符号整型
5
0-5 Byte
非空且不小于0
进货日期
DATE型
20
20 Byte
非空

二、 ER图
编号
姓名
性别
年龄
职务

职工

编号

m

工作

地址

1

邮编

日期
电话
门店

m
单价
数量
日期
m

数量
m

供应
单价
销售
储存
存货量

p

n
n

n
编号

药品

名称

供应商
编号
名称
电话
地址
城市

条码

三、 关系模式
根据ER图设计关系模式如下：
将每个实体转化为一个关系模式，职工与门店的m:1的联系与m端的关系模式合并，门店与药品的m:n的销售联系和储存联系转化为单独的关系模式，门店、药品与供应商之间的m：n：p的联系转化为单独的关系模式，得到所有的关系模式如下（下划线的属性为主码）：
职工（编号，姓名，性别，年龄，职务，所属门店编号）
Employee ( Eno ,Ename , Esex ,Eage , Estate , SHno )；
门店（编号，地址，邮编，电话）
Shop ( SHno , SHaddress , SHpost , SHtel )；
药品（编号，名称，条码）
Drug ( Dno , Dname , Dnum )；
供应商（编号，名称，城市，地址，电话）
Supplier ( SUno , SUname , SUcity , SUaddress , SUtel );
销售（门店编号，药品编号，数量，单价，日期）
Sale ( SHno , Dno , SAamout , SAprice , SAdate );
储存（门店编号，药品编号，存货量）
Store ( SHno , Dno , STamout );
供应（门店编号，供应商编号，药品编号，单价，数量，日期）
Supply ( SHno , SUno , Dno , Sprice , Samout ,Sdate)。
四、 规范化
1. 职工（编号，姓名，性别，年龄，职务，所属门店编号）
每个职工的编号是唯一的，允许同名。
该关系模式的函数依赖：{ 编号→姓名，编号→性别，编号→年龄，编号→职务，编号→所属门店编号}
非主属性完全函数依赖于码，并且不存在非主属性对码的传递依赖，属3NF。
2. 门店（编号，地址，邮编，电话）
每个门店的编号是唯一的。
该关系模式的函数依赖：{ 编号→地址，编号→邮编，编号→电话}
非主属性完全函数依赖于码，并且不存在非主属性对码的传递依赖，属3NF。
3. 药品（编号，名称，条码）
每种药品的编号是唯一的，条码也是唯一的。
该关系模式的函数依赖：{ 编号→名称，编号→条码，条码→名称，条码→编号}
非主属性完全函数依赖于码，并且不存在非主属性对码的传递依赖，属3NF。
4. 供应商（编号，名称，城市，地址，电话）
该关系模式的函数依赖：{ 编号→名称，编号→城市，编号→地址，编号→电话}
非主属性完全函数依赖于码，并且不存在非主属性对码的传递依赖，属3NF。
5. 销售（门店编号，药品编号，数量，单价，日期）
该关系模式的函数依赖：{ （门店编号，药品编号，日期）→数量，（门店编号，药品编号，日期）→单价}
非主属性完全函数依赖于码，并且不存在非主属性对码的传递依赖，属3NF。
6. 储存（门店编号，药品编号，存货量）
该关系模式的函数依赖：{ （门店编号，药品编号）→存货量}
非主属性完全函数依赖于码，并且不存在非主属性对码的传递依赖，属3NF。
7. 供应（门店编号，供应商编号，药品编号，单价，数量，日期）
该关系模式的函数依赖：{ （门店编号，供应商编号，药品编号，日期）→单价，（门店编号，供应商编号，药品编号，日期）→数量}
非主属性完全函数依赖于码，并且不存在非主属性对码的传递依赖，属3NF。
五、 建表语句
create table Employee
( Eno CHAR(5) PRIMARY KEY,
Ename VARCHAR(6) NOT NULL,
Esex CHAR(2) NOT NULL,
Eage SMALLINT(1) CHECKEage>=18),
Estate VARCHAR(10) NOT NULL,
SHno CHAR(5) NOT NULL,
FOREIGN KEY(SHno) REFERENCES Shop(SHno)
);

create table Shop
( SHno CHAR(5) PRIMARY KEY,
SHaddress VARCHAR(50) NOT NULL,
SHpost CHAR(6),
SHtel VARCHAR(15) NOT NULL
);

create table Drug
( Dno CHAR(5) PRIMARY KEY,
Dname VARCHAR(20) NOT NULL,
Dnum VARCHAR(20) NOT NULL UNIQUE
);

create table Supplier
( SUno CHAR(5) PRIMARY KEY,
SUname VARCHAR(20) NOT NULL,
SUcity VARCHAR(20),
SUaddress VARCHAR(20),
SUtel VARCHAR(20)
);

create table Sale
( SHno CHAR(5),
Dno CHAR(5),
SAamout SMALLINT CHECK(SAamout>=0),
SAprice FLOAT(10) CHECK(SAprice>=0),
SAdate DATE NOT NULL,
PRIMARY KEY (SHno , Dno , SAdate)
FOREIGN KEY (SHno) REFERENCES Shop(SHno),
FOREIGN KEY (Dno) REFERENCES Drug(Dno)
);

create table Store
( SHno CHAR(5),
Dno CHAR(5),
STamout SMALLINT CHECK(STamout>=0),
PRIMARY KEY (SHno , Dno),
FOREIGN KEY (SHno) REFERENCES Shop(SHno),
FOREIGN KEY (Dno) REFERENCES Drug(Dno)
);

create table Supply
( SUno CHAR(5),
Dno CHAR(5),
Sprice FLOAT(5) NOT NULL CHECK(Sprice>=0),
Samout SMALLINT NOT NULL CHECK(Samout>=0),
Sdate DATE NOT NULL,
PRIMARY KEY (SUno , Dno , Sdate),
FOREIGN KEY (SUno) REFERENCES Supplier(SUno),
FOREIGN KEY (Dno) REFERENCES Drug(Dno)
)

六、 查询
1. 查询某家门店的职工情况，职工的详细信息。
例如查询编号为“11111”的店的职工信息：
select *
from Employee
where SHno=’11111’;

2. 查询某家门店的某药品存量？或某药品在哪个门店有货？有多少？
例如查询编号为“11111”的门店的编号为“22222”的药品存量：
select STamout
from Store
where SHno=’11111’ and Dno=’22222’;
例如查询编号为“22222”的药品在那个门店有货及存货量：
select SHno , STamout
from Store
where Dno=’22222’ and STamout>0;

3. 提供销售情况。如某门店在某天销售了哪些药品？销售额是多少？某月各门店日平均销售额是多少？
例如查询编号为“11111”的门店在2010年11月1号销售了哪些药品（显示药品编号及名称），销售额是多少：
select Sale.Dno , Dname , SAamout , SAprice , SAamout*SAprice as Total
from Sale , Drug
where SHno=’11111’ and SAdate=2010-11-1 and Sale.Dno =Drug.Dno;

4. 能够查询某门店的某种药品来自于哪个供应商？来自于哪个城市？
例如查询编号为“11111”的门店的编号为“22222”的药品来自于哪个供应商(显示编号及名称)，来自于哪个城市：

select distinct SUno , SUname , SUcity
from Supply , Supplier
where SHno=’11111’ and Dno=’22222’ and Supply.SUno=Supplier.SUno;
5. 需要具有数据插入，删除以及更新功能。
例如向Drug表中插入编号为22222、名称为板蓝根冲剂、条码为123456789SA的药品：
insert
into Drug
values (‘22222’,’板蓝根冲剂’,’123456789SA’);

例如删除编号为11111的供应商信息：
delete
from Supplier
where SUno=’11111’;

例如将编号为11111的门店中的编号为22222的药品存货量改为12
update Store
set STamout=12
where SHno=’11111’ and Dno=’22222’;