1、第 1 页 共 12 页医院药品进销存系统数据库设计一 需求分析1.1 需求调查由于对医院和药品编码等信息不是很熟悉，我们在网上和附近的医院进行了系统的调查，以使我们的设计更符合实际包括以下几个方面：1、医院药品进销存业务状况：系统需求、组织结构、管理内容、业务过程等。2、数据的规范与统一：详细了解了药品统一编码的规范，对于同一种编码的药品它的通用名，剂型，规格是相同的。而与其它属性(质量层次，价格等)无关。3、其他要求：对数据保密性、数据完整性的要求，对数据精度和数据吞吐量的要求，对来功能、应用范围扩展性的要求等。1.2 基本功能分析本设计要实现的是医院药品进销存系统,在设计该系统时,应尽可
2、能贴近实际、便于用户操作.系统在实现上应该具有如下功能:1.系统要示用户必须输入正确的用户名和密码才能进入系统.2.主要功能模块A.新药品的入库。B.过期药品的出库登记、处理记录。C.药品库存检索。D.供货商信息检索。E.药品采购记录管理。F.药品用药说明信息管理。G.输出相应的数据报表。H.*具有数据备份和数据恢复功能。其功能模块图如下：医院药品进销存系统新药品入库药品出库管理(包括过期处理)药品库存检索供货商信息检索药品采购记录管理药品用药说明信息管理第 2 页 共 12 页二 概念设计在需求分析的基础上，我们对医院药品进销存系统有了一定的了解。在分析设计概念模型时，首先找出模型所需的实体
3、，然后找到各实体之间的关系，画出 ER 模型图。2.1、实体及其间的关系设计对于医院药品进销存系统，我们设计了药品，供货商，仓库，操作员四个实体。结合实际情况及对数据库设计的方便，各个实体之间的关系如下：供货商和药品之间应该是存在 Offer 关联，它们之间为多对多关系。供货商，仓库，药品之间存在 Order 关联，它们之间为多对多关系。药品，仓库之间存在 Own 关联，它们之间为多对多关系。药品，操作员，仓库之间存在 InStore 和 OutStore 关联，它们之间为多对多关系。药品和操作员之间存在 Medicine_Useinfo 关联，它们之间为多对多关系。2.2 E-R 模型图的设
4、计根据较为详细的需求分析,我们设计出了以下 E-R 模型图如下.第 3 页 共 12 页三 逻辑设计逻辑结构设计的目的是将 ER 模型向关系模型转换，注意转换时关系的主键、外键的设置以保持原有的 ER 模型中实体与实体之间的关系，另外还应当进行规范化处理以消除数据冗余。3.1 ER 图向关系模型的转化（主键已标出下划线）Medicine(M_NO,M_ID，M_Name, M_Type,M_Spec,M_Qlevel,M_Price,M_Date,M_Date,M_Funtime）存在冗余，根我们把它拆分成两张表Medicine(M_ID,M_Name,M_Type,M_Spec)Medici
5、ne_Sub(M_NO,M_ID,M_Price,P_ID,M_Date,M_Date,M_OutTime,M_Qlevel)注：M_ID 为外键其他关系模型如下StoreRoom(S_ID,S_Addr)第 4 页 共 12 页Operator(O_ID,O_Name,O_sex)Provider(P_ID,P_Name,P_Addr,P_Post,P_Tel,P_Email,P_Fax,P_Conp,P_ConTel)Offer(M_ID,P_ID)注 M_ID,P_ID 为外键Own(M_NO,S_ID,Own_Mount)注：M_NO,S_ID 为外键InStore(S_ID,O_ID
6、,In_Mount,In_Date)注：S_ID，O_ID 为外键OutStore(O_ID,S_ID,Out_Mount,Out_Date,Out_Type)注：O_ID，S_ID 为外键Order(P_ID,S_ID,Od_ID,Od_Mount,Od_Date,Od_Price)注：P_ID，S_ID 为外键Medicine_Useinfo(M_NO,O_ID,Patient_Name,Use_Mount,Use_Price,Use_Date)注：M_NO,O_ID 为外键3.2、ER 图转换成关系模型所遵循的原则我们把 ER 图转换成关系模型所遵循的原则：1） 每一个实体类型转换成一个
7、关系模式。如实体Medicine，StoreRoom，Operator，Provider，都可以转化成对应的一个关系模式。关系模型的主键是 ER 模型的标识符，其他属性一样。2） 一个联系可转化为一个关系模式，那么，两端关系的标识符及该联系属性为关系的属性，而关系的标识符为两端实体标识符的组合。3)三个或三个以上的多对多的联系可转化为一个关系模式，那么，该关系的标识符及联系的属性为关系的属性，而关系的标识符为各实体标识符的组合。4)我们还涉及到了引用完整性约束，也就是外键的约束，外码的约束贯穿着我们设计的始终，它把我们建立的关系紧密的联系在了一起。5） 我们对关系模式进行了消除数据冗余的处理。
8、应符合第三范式，不允许出现传递依赖、冗余、异常等等。在逻辑设计中形成了关系表后需要对关系作规范化处理，使每个关系表至少满足第三范式的要求。对违反第三范式的关系我们进行了分析并作了相应的调整。对各关系模式之间的数据依赖进行了极小化处理，消除了冗余。对违反第三范式的关系模式进行了必要的分解和合并。3.3 数据表的详细信息以下是各个数据表的详细信息（还附加了一个表来存放管理员的信息.以便于管理员用户的登录操作）:第 5 页 共 12 页Medicine 信息表Medicine_Sub 信息表Provider 信息表Operator 信息表StoreRoom 信息表第 6 页 共 12 页DealOu
9、tDate 表Own 信息表Orders 信息表InStore 信息表第 7 页 共 12 页OutStore 信息表Offer 信息表Medicine_Useinfo 信息表UserList 信息表四 物理设计4.1.索引设计关系属性 A 上的索引是一种数据结构，它可以提高查找在属性 A 上具有某个特定值的元祖的效率。索引通常有助于包含有属性 A 和常量的查询，但当关系变得很大时，通过扫描关系中所有的元祖来找出那些匹配给定条件的元祖的操作方式代价太高。故我们设计索引需要对一下两方面折中选择。首先，对某个属性使用索引能极大的提高对该属性值的检索效率，使用该属性时，还可以加快连接。其次，对关系上
10、某个属性的索引会使得对关系的插入、删除、修改变得复杂和费时。第 8 页 共 12 页因此经过分析本系统的功能和需求可知，本系统的药品表中的记录可能相当的庞大，而且基于药品名称或编码的查询和修改相当的频繁，加之在数据库更新和维护过程中对供货商的更新和查询也比较的频繁，而且供货商的数量也可能比较大，综合考量后应为本系统建立如下 3 个索引，已达到操作的便捷和系统功能的优化。create unique index M_ID_index on Medicine(M_ID);create unique index P_ID_index on Provider(P_ID);create index M_N
11、ame_index on Medicine(M_Name);五 安全性设计5.1 安全性设计分析SQLServer 的安全性管理是建立在认证和访问许可两者机制上的。认证是指确定登陆SQLServer 的用户的登录账号和密码是否正确，以此来验证其是否具有链接 SQL Server 的权限。但是登录并不被授予访问数据库的许可。第二个用户级才是用户，用户在数据库内具有访问数据库的权限之后才能对数据库进行权限许可下的各种操作。而具有相同的许可的用户可以组成一个角色。考虑到该系统可能有不同的用户，而每个用户对该数据库的访问权限应该是不一样的。我们把该系统的用户角色大致分为：管理员和不同的操作员。结合实际
12、情况，管理员可以对整个系统进行所有的操作，包括查询、删除、修改、插入。操作员可以进行他所允许范围内的操作。例如入库操作员可以进行查询药品信息的添加修改，第 9 页 共 12 页库房信息的添加修改。出库操作员可以进行用药信息的管理，过期药品处理的管理和库房信息的修改删除。由于考虑到引用完整性约束，也即是外键约束。在删除、修改时用级联原则。当一个表的某一些值被修改时，引用这个表的属性的表的相应的属性值随着改变。5.2 添加用户和角色添加用户步骤一,从 SQL Server 程序组中运行 SQL Server Enterprise Manager。步骤二,在窗口左半部中将使用的服务器,将其展开。步骤
13、三,选择要添加用户的数据库(Medicine 数据库),将其展开。步骤四,选择用户目录，单击右键，选择新建用户，弹出数据库用户属性窗口。步骤五,从登录名下拉框中选择用户所属的登陆。(或者新建一个登陆，方法为：选择下拉框中新建，在弹出的对话框中填入名称，选择 SQL Server 身份验证，填写密码。-确定-填写新密码-确定)步骤六,在数据库角色允许选择 Public。步骤七,单击“确定”生成新的用户,添加用户后,在用户子目录下看到该用户。添加角色 步骤一,从 SQL Server 程序组中运行 SQL Server Enterprise Manager。步骤二,在窗口左半部中将使用的服务器,将
14、其展开。步骤三,选择要添加用户的数据库(Medicine 数据库),将其展开。步骤四,选择角色子目录，单击右键，选择新建角色，弹出数据库角色属性窗口。 步骤七,在名称框中输入角色名。步骤八,如果要往角色中添加现有的用户,单击添加按纽将用户添加到角色中。步骤九,单击 OK 将角色添加到数据库中,此时 SQL Server Enterprise Manager 窗口的右半部窗口中会显示新添加的角色。步骤十,用鼠标右键单击新添加的角色,从弹出的快捷菜单中选择属性,右键单击打开。 步骤十一单击权限按钮,打开数据库角色属性对话框，在要赋予它权限设置上面打钩。单击仅列出该角色具有权限的对象，检查赋予的权限
15、是否正确。步骤十二,检查无误，单击“确定“按纽返回到 SQL Server Enterprise Manager。六 数据库实现6.1 数据表的创建及其完整性说明:-=-* 供货商 Provider 表 *create table Provider(P_ID char(5) primary key,P_Name varchar(50) unique not null,P_Addr varchar(50) not null,P_Post char(6),P_Tel varchar(14) not null,P_Email varchar(50) not null,P_Fax varchar(10
16、) ,P_ConP varchar(20),P_ConTel varchar(14)第 10 页 共 12 页-=-* 药品 Medicine 表 *create table Medicine(M_ID char(16) primary key,M_Name varchar(50) not null,M_Type varchar(10) not null,M_Spec varchar(10) not null,) -=-* 药品 Medicine 子表 *create table Medicine_Sub(M_NO char(8) primary key,M_ID char(16) refer
17、ences Medicine(M_ID) not null,M_Price numeric(10,2),P_ID char(5) references Provider(P_ID) not null,M_Date datetime not null,M_OutTime datetime not null,M_QLevel varchar(10) not null)-=-* 库房 StoreRoom 表 *create table StoreRoom(S_ID char(5) primary key,S_Addr varchar(50) not null)/=-* 库房拥有药品 Own 表 *c
18、reate table Own(M_ID char(16) references Medicine(M_ID) not null,S_ID char(5) references StoreRoom(S_ID) not null,Own_Mount int not null)-=-* 订单 Orders 表 *create table Orders(Od_ID char(8),P_ID char(5) references Provider(P_ID) not null,S_ID char(5) references StoreRoom(S_ID) not null,M_ID char(16)
19、references Medicine(M_ID) not null,Od_Mount int not null,Od_Date datetime not null,Od_Price numeric(10,2) not null)-=第 11 页 共 12 页-* 操作员 Operator 表 *create table Operator(O_ID char(5) primary key,O_Name varchar(20) not null,O_Sex char(1) check(O_Sex in (F,M) default(M)-=-* 出库 OutStore 表 *create tabl
20、e OutStore(O_ID char(5) references Operator(O_ID) not null,S_ID char(5) references StoreRoom(S_ID) not null,M_ID char(16) references Medicine(M_ID) not null,Out_Mount int not null,Out_Date datetime not null,Out_Type varchar(20) check(Out_Type in (Sell,OutOfDate) not null)-=-* 供货商提供药品 Offer 表 *create
21、 table Offer(P_ID char(5) references Provider(P_ID) not null,M_ID char(16) references Medicine(M_ID) not null,)-=-*用药信息 Medicine_Useinfo 表 *create table Medicine_Useinfo(M_ID char(16) references Medicine(M_ID) not null,O_ID char(5) references Operator(O_ID) not null,Patient_Name varchar(20) not null
22、,Use_Mount int not null,Use_Price numeric(10,2) not null,Use_Date datetime not null)-=-*入库 InStore 表 *create table InStore(S_ID char(5) references StoreRoom(S_ID) not null,O_ID char(5) references Operator(O_ID) not null,M_ID char(16) references Medicine(M_ID) not null,In_Mount int not null,In_Date d
23、atetime not null)第 12 页 共 12 页-=-*过期药品处理 DealOutDate 表 *create table DealOutDate(S_ID char(5) references StoreRoom(S_ID) not null,M_ID char(16) references Medicine(M_ID) not null,O_ID char(5) references Operator(O_ID) not null,Deal_Type varchar(50) not null,Deal_Date datetime not null,Deal_Addr varc
24、har(50) not null,Deal_Mount int not null)-=-*过期药品 OutOfDateMedicine 视图 *Create view OutOfDateMedicine as select *from Medicine_Subwhere M_OutTimegetCurrentDate-=-*库存总量 StoreroomTotalAmount 视图 *Create view StoreroomTotalAmount as Select S_ID,sum(Own_Mount) as totalAmountfrom Owngroup by S_ID-=-*订单总价 OrdersTotalPrice 视图 *Create view OrdersTotalPrice as Select Od_ID,sum(Od_Price) as totalPricefrom Ordersgroup by Od_ID七 参考文献数据库系统基础教程 （中、英文版，第 2 版） ，美国斯坦福大学，机械工业出版社 2006.07精通 SQL-结果会查询语言详解高守传，人民有点出版社，2006 数据库设计教程，何玉洁等翻译，机械工业出版社，2003 数据库系统教程 ，王能斌著，电子工业出版社，2002
