1. 数据库需求分析

1.1项目提出

1.2.调查使用该药品存储信息数据库的用户的实际需求

1.3 功能需求

1．供应商基本信息模块，完成对供应商基本信息的输入、修改和查询；

2．员工基本信息模块，完成对员工基本情况的输入、修改和查询；

3．药品基本信息模块，完成对药品基本信息的输入、修改和查询；

4．客户基本信息模块，完成对客户基本信息的输入、修改和查询

5．药品库存信息模块，完成对仓库基本信息的输入、修改和查询；

6. 供应信息模块，完成对药品供应情况的输入、修改和查询；

7. 销售信息模块，完成对药品销售情况的输入、修改和查询；

1.4 数据字典

Supplier供应商基本情况表：

Medicine药品信息基本情况表：

Warehouse仓库信息基本情况表：

Staff员工信息基本情况表：

Customer客户信息基本情况表：

SuppList供应信息基本情况表：

SaleList销售信息基本情况表：

导入：

数据库概念结构设计

2.1.实体E-R图

药品信息实体图：

供应商信息实体图：仓库信息实体图：员工信息实体图：客户信息实体图：

2.2系统E-R图

（1）合并。解决各分E-R图之间的冲突，将各分E-R图合并起来生成初步的E-R图。

（2）修改和重构。消除不必要的冗余。

实体与联系的E-R图：完整E-R图：

数据库逻辑结构设计

将E-R图转换为关系模型

（1）药品（药品编号，药品名称，生产厂家，生产日期，保质期，价格，用途，经手人）

此为药品实体对应的关系模式。

（2）供应商（供应商编号，供应商名称，供应商地址，供应商联系方式 ）

此为供应商类别实体对应的关系模式。

（3）仓库（仓库编号，药品编号，药品位置，药品库存）

此为仓库类别实体对应的关系模式。

（4）员工（员工编号，员工姓名，员工性别，员工年龄，员工学历，员工职务）

此为员工类别实体对应的关系模式。

（5）客户（客户编号，客户姓名，客户联系方式，客户性别）

此为客户类别实体对应的关系模式。

（6）供应（供应商编号，药品编号，供应数量，供应时间，供应订单）

此为联系“供应”对应的关系模式。

（7）销售（员工编号，客户编号，药品编号，销售时间，销售数量， ……）

此为联系“销售”对应的关系模式。

（8）入库（药品编号，入库编号，供应时间，供应数量， ……）

此为联系“入库”对应的关系模式。

（9）出库（药品编号，出库编号，出库时间，出库数量，……）

此为联系“出库”对应的关系模式。

数据库物理结构设计

为数据库中各基本表建立的索引如下:

属性：（药品编号，供应商编号，客户编号）经常在查询条件里出现，故而在这些属性上建立索引，从而优化数据库，使查询的速度更快。

为数据库中各基本表建立的索引如下:属性：（药品编号，供应商编号，客户编号）经常在查询条件里出现，故而在这些属性上建立索引，从而优化数据库，使查询的速度更快。create unique index Supid_index on Supplier(Supid)create unique index Medid_index on Medicine(Medid)create unique index Cusid_index on Customer(Cusid)

数据库操作代码部分

5.1 数据库创建代码

创建数据库：

create database 药品存销信息管理系统;

5.2 创建表代码

创建供应商信息表：

create table Supplier

(

Supid char(10) primary key,

Supname char(50) not null,

Supaddress char(50) not null,

Suptel char(20) not null

)

创建药品信息表：

create table Medicine

(

Medid char(10) primary key,

Medname char(20) not null,

Medfactory char(50) not null,

Medborn date not null,

Meddate char(20) not null,

Medpurpose char(100) not null,

Medprice float not null，

Medper char(20) not null

)

创建仓库信息表：

create table Warehouse

(

Warid char(10),

Warmedid char(10) not null,

Warposition char(20) not null,

Warnum int not null,

primary key(Warid,Warmedid)

)

创建员工信息表：

create table Staff

(

Staid char(10) primary key,

Staname char(20) not null,

Stasex char(2) check(Stasex in(‘男’,’女’)) not null,

Staage int not null,

Staedu char(20) not null,

Staduty char(20) not null

)

创建客户信息表：

create table Customer

(

Cusid char(10) primary key,

Cusname char(20) not null,

Custel char(20) not null,

Cussex char(2) check(Cussex in(‘男’,’女’)) not null

)

创建供应信息表：

create table SuppList

(

Supno char(20) primary key,

Supid char(10),

Supmedid char(10),

Inwarid char(10),

Supnum int not null,

Suptime date not null

)

创建销售信息表：

create table SaleList

(

Salno char(20) primary key,

Salstaid char(10) not null,

Salcusid char(10) not null,

Salmedid char(10) not null,

Outwarid char(10) not null,

Salnum int not null,

Saltime date not null,

)

5.3 数据库的查询操作代码

1. 查询所有供应商的名称和地址信息

select Supname,Supaddress

from Supplier

2. 查询采购于贵州国泰医药有限公司有限公司的所有药品的名称与价格

select Medname,Medprice

from Medicine

where Medfactory = ‘贵州国泰医药有限公司’;

3. 首先查询库存量从20到50的药品的信息（名称、价格、仓库编号），再将查询结果按照药品价格由低到贵的顺序进行排序

select Medname,Warid,Medprice

from Medicine,Warehouse

where Medicine.Medid = Warehouse.Warmedid and Warehouse.Warnum between 20 and 50

order by Medprice;

4. 查询购买了药品阿莫西林的客户信息（姓名、性别、联系方式、药品名称）

select Cusname,Cussex,Custel,Medname

from Customer,SaleList,Medicine

where Customer.Cusid = SaleList.Salcusid

and SaleList.Salmedid = Medicine.Medid

and Medicine.Medname = ‘阿莫西林’;

5.4视图创建代码

1. 建立药品与其用途的视图

create view Med_purpose

as

select Medname,Medpurpose

from Medicine

2. 建立购买药品三清双黄连的客户信息的视图

create view Cus_sanqingshuanghuanglian

as

select Cusname,Cussex,Custel

from Customer,SaleList,Medicine

where Customer.Cusid = SaleList.Salcusid and SaleList.Salmedid = Medicine.Medid

and Medicine.Medname = ‘三清双黄连’;

3. 建立客户购买的药品名称和数量的视图

create view Cus_Med

as

select Cusname,Medname,Salnum

from Customer,Medicine,SaleList

where Customer.Cusid = SaleList.Salcusid and SaleList.Salmedid = Medicine.Medid

系统规范化设计

数据库的完整性设计(你创建了哪些约束和触发器，要有触发器的代码)

约束：

实体完整性：主码唯一且不能为空

参照完整性：设置外码

触发器：

1. 出现新的供应药品订单时，需要对供应信息表进行更新，此时供应数量不大于0的话不符合实际情况，触发器将激活并提示“供应数量不得小于等于0！”。而数量大于0时，对供应信息表进行更新，药品的数量增加，需要对仓库表中药品的数量进行更改，触发器将会激活完成相应的操作。

create trigger In_Warehouse on SuppList

for Insert

as

begin

if exists(select*from SuppList

where supnum<=0)

begin

print’供应数量大于0！’

rollback transaction

end

else

begin

update Warehouse

set Warnum=Warnum+(select Supnum from inserted)

where Warehouse.Warmedid=(select Supmedid from inserted)

and Warehouse.Warid=(select Inwarid from inserted)

end

end

2. 对药品信息表进行插入或者更新操作，当药品价格不大于0时，触发器将激活，中止该操作并提示“药品价格不能小于等于0！”

create trigger Insert_Med on Medicine

for insert,update

as

begin

if exists(select*from Medicine

where Medprice<=0)

begin

print’药品价格不能小于等于0！’

rollback transaction

end

else

begin

print’插入或者更新成功！’

end

end

3. 出现新的销售订单，需对销售信息表进行更新，若顾客购买数量不大于0则不符合实际情况，触发器将激活并提示“顾客购买数量不得小于等于0！”。当顾客购买数量比药店仓库库存量大时也不符合实际情况，触发器将激活并提示“库存不足！”。若数量大于0且小于库存量，在对销售信息表进行更新时，药品的数量减少，还需对仓库表中药品的数量进行更改，触发器则激活完成相应的操作。

create trigger Out_Warehousee on SaleList

for Insert

as

begin

if exists(select*from Salelist

where Salnum<=0)

begin

print’顾客购买数量小于等于0!’

rollback transaction

end

if exists(select*from Salelist,Warehouse

where Salelist.Salnum>Warehouse.Warnum AND

Salelist.Outwarid=Warehouse.Warid AND

Salelist.Salmedid=Warehouse.Warmedid)

begin

print’库存不足!’

rollback transaction

end

else

begin

update Warehouse

set Warnum=Warnum-(select Salnum from inserted)

where Warehouse.Warmedid=(select Salmedid from inserted)

and Warehouse.Warid=(select Outwarid from inserted)

end

end

4. 在药品信息表中对药品信息进行删除操作时，仓库表中对应药品的信息也将被删除

create trigger drop_med on Medicine

after delete

as

begin

delete from Warehouse where Warmedid=(select Medid from deleted)

end

数据库的维护和安全性设计（你设置了哪些用户，他们的权限是怎样的）

角色：经理，销售员，仓库管理员

用户：经理，销售员，仓库管理员

1.创建用户：经理

create login manager with password = ‘111’

create user manager1 for login manager

2.创建用户：销售员

create login salesperson with password = ‘222’

create user salesperson1 for login salesperson

3.创建用户：仓库管理员

create login warekeeper with password = ‘333’

create user warekeeper1 for login warekeeper

4.创建角色：经理，他拥有对所有表的查询，更新，插入和删除的权限。将经理角色Rmanager授予用户lhh1。

create role Rmanager

grant select,update,insert,delete

on dbo.Supplier

to Rmanager

with grant option

grant select,update,insert,delete

on dbo.Medicine

to Rmanager

with grant option

grant select,update,insert,delete

on dbo.Warehouse

to Rmanager

with grant option

grant select,update,insert,delete

on dbo.Staff

to Rmanager

with grant option

grant select,update,insert,delete

on dbo.Customer

to Rmanager

with grant option

grant select,update,insert,delete

on dbo.SuppList

to Rmanager

with grant option

grant select,update,insert,delete

on dbo.SaleList

to Rmanager

with grant option

exec sp_addrolemember ‘Rmanager’,’lhh1′

5.创建角色：销售员，他拥有对药品信息表Medicine和客户信息表Customer的查询权限和对销售信息表SalesList的查询和插入的权限。将销售员角色Rsaleperson授予用户salesperson1

create role Rsaleperson

grant select

on dbo.Medicine

to Rsaleperson

with grant option

grant select

on dbo.Customer

to Rsaleperson

with grant option

grant select,insert

on dbo.SaleList

to Rsaleperson

with grant option

exec sp_addrolemember ‘Rsaleperson’,’salesperson1′

6.创建角色：仓库管理员，他拥有对仓库信息表Warehouse的查询，插入，更新和删除的权限。将仓库管理员的角色–Rwarekeeper授予给用户warekeeper1。

create role Rwarekeeper

grant select,insert,update,delete

on dbo.Warehouse

to Rwarekeeper

exec sp_addrolemember ‘Rwarekeeper’,’warekeeper1′