藥品進銷存管理系統(tǒng)數(shù)據(jù)庫設(shè)計

1、目錄 1. 摘要和需求分析32. 數(shù)據(jù)庫結(jié)構(gòu)設(shè)計2.1 概念設(shè)計全局/整體e-r圖72.2 邏輯設(shè)計2.2.1建立關(guān)系模式92.2.2用戶子模式建立92.2.3關(guān)系模式邏輯結(jié)構(gòu)定義103. 數(shù)據(jù)庫物理設(shè)計 3.1 索引10 3.2 觸發(fā)器10 3.3 存儲過程114.數(shù)據(jù)庫入庫與測試 4.1數(shù)據(jù)入庫12 4.2 數(shù)據(jù)庫測試12參考文獻12附錄 sql語句1 基本表132 視圖143 索引154 觸發(fā)器155 存儲過程18藥品進銷存管理系統(tǒng)數(shù)據(jù)庫設(shè)計摘要：隨著計算機技術(shù)、通信技術(shù)和網(wǎng)絡(luò)技術(shù)的迅猛發(fā)展，人類社會已經(jīng)進入了信息化時代。信息資源成為最重要、最寶貴的資源之一，數(shù)據(jù)庫技術(shù)已經(jīng)成為信息社會中

2、對大量數(shù)據(jù)進行組織與管理的信息系統(tǒng)核心技術(shù)和網(wǎng)絡(luò)信息化管理系統(tǒng)的重要基礎(chǔ)。是一種計算機輔助管理數(shù)據(jù)的方法，它研究如何組織和存儲數(shù)據(jù)，如何高效地獲取和處理數(shù)據(jù)。是通過研究數(shù)據(jù)庫的結(jié)構(gòu)、存儲、設(shè)計、管理以及應(yīng)用的基本理論和實現(xiàn)方法，并利用這些理論來實現(xiàn)對數(shù)據(jù)庫中的數(shù)據(jù)進行處理、分析和理解的技術(shù)。目前中小型的藥店營業(yè)規(guī)模不斷擴大，藥品的數(shù)量和種類不斷增加，人工管理的難度也越來越大，如果開發(fā)出藥品進銷存管理系統(tǒng)，通過使用計算機對物質(zhì)信息進行管理，可以很大程度上方便管理人員對藥品的管理，實現(xiàn)管理的高效化和統(tǒng)一化。從而給工作帶來了巨大的方便，給藥店帶來了巨大的經(jīng)濟利益。1. 需求分析需求分析階段就是分析用

3、戶的需求，這也是設(shè)計數(shù)據(jù)庫的第一步。藥品進銷存管理系統(tǒng)是中小型藥店管理人員方便藥店的各個流程的工作為目的的。需求分析這個階段，主要確定包括業(yè)務(wù)流程，數(shù)據(jù)流程，以及要實現(xiàn)的流程和目標，通過數(shù)據(jù)的分析，可以得到市場的信息，便于管理人員提高，優(yōu)化管理水平。讓銷售更加方便，節(jié)約成本，帶來經(jīng)濟效益。1.1.業(yè)務(wù)流圖包括藥品的采購，庫存管理和銷售，主要是闡述整個藥品的進存的流水作業(yè)過程。如圖1-1所示：存單提貨提貨單發(fā)貨單發(fā)貨提貨單缺貨查看銷售部客戶訂貨訂貨單提貨單提貨存檔庫存單退貨單退貨倉庫采購員采購缺貨單缺貨供應(yīng)廠家發(fā)貨發(fā)貨單驗貨入庫藥店采購?fù)素泦瓮素浫霂靻稳霂?.2 .數(shù)據(jù)流程圖是反映 藥店作業(yè)過程

4、的數(shù)據(jù)去向和流向。通過數(shù)據(jù)流程圖，抽象現(xiàn)實的數(shù)據(jù)到藥品進銷存的物理模型。再根據(jù)這個物理模型抽象出信息流，將物理模型轉(zhuǎn)化成邏輯模型，反映信息在系統(tǒng)中的流動，處理，存儲情況。流程圖分為頂層數(shù)據(jù)流圖，第一層數(shù)據(jù)流圖，第二層數(shù)據(jù)流圖。e1供應(yīng)廠家p6進銷存e2客戶s1庫存臺帳圖1-2 頂層數(shù)據(jù)流程圖e1 供應(yīng)廠家p1進貨p4盤存p3報損p2銷售e2客戶s1 庫存臺帳e3庫房圖1-3 第一層數(shù)據(jù)流程圖e2 客戶p2.1 銷售s1 庫存合賬p2.2 確認退貨p2.3 退貨s2 收支帳務(wù)圖1-4第二層數(shù)據(jù)流程圖 （銷售） 庫房 e3p3.1 庫房報損s1 庫存臺帳p2 銷售p3.2 銷售報損圖1-5 第二層

5、數(shù)據(jù)流圖 （庫存管理）數(shù)據(jù)庫結(jié)構(gòu)設(shè)計 主要分為概念設(shè)計和邏輯設(shè)計兩個部分。2.1 概念設(shè)計1. e-r圖設(shè)計如下：供應(yīng)存儲藥品倉庫供應(yīng)廠家退貨購買客戶屬性如下所示：供應(yīng)廠家（供應(yīng)廠編號，供應(yīng)廠名稱，供應(yīng)廠廠址，供應(yīng)廠電話，供應(yīng)廠網(wǎng)站）藥品（藥品編碼，藥品商品名，通用名，藥品分類，藥品劑型，生產(chǎn)廠家，單價，生產(chǎn)日期，保質(zhì)期）倉庫（倉庫編號，倉庫類別，倉庫地址）職員（職工編號，姓名，性別，年齡）客戶（客戶編號，名稱，性別，電話）供應(yīng)（供應(yīng)量）購買（購買量）退貨（退貨原因，退貨數(shù)量）2.2邏輯設(shè)計邏輯設(shè)計的任務(wù)是把概念結(jié)構(gòu)設(shè)計好的基本e-r圖轉(zhuǎn)為與選用dbms產(chǎn)品所支持數(shù)據(jù)模型相符合的邏輯結(jié)構(gòu)。藥品

6、管理系統(tǒng)的設(shè)計采用關(guān)系模型。將e-r圖中的實體的屬性和實體型之間的聯(lián)系轉(zhuǎn)化為關(guān)系模式。對于實體型間的聯(lián)系有以下不同的情況：（1） 一個1：1聯(lián)系可以轉(zhuǎn)換為一個獨立的關(guān)系模式，也可以以任一端對應(yīng)的模式合并。如果轉(zhuǎn)換為一個獨立的關(guān)系模式，則與該聯(lián)系相連的各實體的碼以及聯(lián)系的本身屬性都轉(zhuǎn)換為關(guān)系的屬性。（2） 一個1：n聯(lián)系可以轉(zhuǎn)換為一個獨立的關(guān)系模式，也可以與任一端對應(yīng)的模式合并。如果轉(zhuǎn)換為一個獨立的關(guān)系模式，則與該聯(lián)系相連的各實體的碼以及聯(lián)系的本身屬性都轉(zhuǎn)換為關(guān)系的屬性。（3） 一個m:n聯(lián)系轉(zhuǎn)換為一個關(guān)系模式，與該聯(lián)系相連的個實體的碼以及聯(lián)系本身的屬性轉(zhuǎn)換為關(guān)系屬性，各實體的碼組成關(guān)系的碼或關(guān)

7、系的碼的一部分。藥品管理系統(tǒng)涉及的關(guān)系模式的幾種情況主要來自以下幾種聯(lián)系 ：供應(yīng)廠家和藥品信息的關(guān)系（m:n），藥品和倉庫的關(guān)系(m:1)，藥品通過銷售部門和客戶建立的聯(lián)系（m:n）。2.2.1 建立關(guān)系模式藥品進銷存系統(tǒng)涉及的關(guān)系主要有：供應(yīng)廠家的藥品為m:n（多對多）的關(guān)系，將它們之間的聯(lián)系轉(zhuǎn)化為獨立的關(guān)系模式。倉庫和藥品的關(guān)系為1:n（一對多），將其之間的聯(lián)系與n端實體合并。職工和倉庫的關(guān)系為1:n（一對多）的關(guān)系，也將其之間的聯(lián)系與n端實體合并。客戶和藥品之間的聯(lián)系為m:n（多對多）的關(guān)系，將它們之間的聯(lián)系轉(zhuǎn)換為獨立的關(guān)系模式。具體的轉(zhuǎn)換如下圖所示，主鍵或外鍵已標注：供應(yīng)廠家（供應(yīng)廠編

8、號，供應(yīng)廠名稱，供應(yīng)廠廠址，供應(yīng)廠電話，供應(yīng)廠網(wǎng)站）藥品(藥品編碼，藥品商品名，通用名，藥品分類，藥品劑型，單價，生產(chǎn)日期，保質(zhì)期，庫存量，報損量)倉庫（倉庫編號，倉庫類別，倉庫管理人員）管理（職工號，倉庫號）客戶（客戶編號，名稱，性別，電話）供應(yīng)（供應(yīng)廠家編號，藥品編號，供應(yīng)量）銷售（藥品編號，客戶編號，銷售量）退貨（藥品編號，客戶編號，退貨原因，退貨數(shù)量）2.2.2 用戶子模式建立根據(jù)需求分析研究建立滿足不同需求的用戶子模式，各個子模式的建立在更大程度上方便數(shù)據(jù)操作。用戶子模式的定義如表所示。具體實現(xiàn)過程見附錄3-2 用戶子模式定義編號 用戶子模式（view） 作用v-1 vi suppl

9、ier方便查詢藥品供應(yīng)商信息v-2 vi medicine方便查詢各類藥品的信息v-3 vi storehouse 方便查詢倉庫基本信息v-4 vi storage方便查詢庫存信息v-5 vi sale方便查詢藥品銷售信息2.2.3關(guān)系模式邏輯結(jié)構(gòu)定義藥品進銷存管理系統(tǒng)涉及的8張表，分別是 supplier, medicine, storehouse, customer, buy, storage, sale, back。如下表所示：具體定義見對其對應(yīng)附錄。 編號 基本表定義 安全性和完整性t-1 supplier 見附錄t-2 medicine 見附錄t-3 storehouse 見附錄t-

10、4 customer 見附錄t-5 buy 見附錄t-6 storage 見附錄 t-7 sale 見附錄t-8 back 見附錄3.數(shù)據(jù)庫物理設(shè)計數(shù)據(jù)庫在物理設(shè)備上的存儲結(jié)構(gòu)與存取方法就是數(shù)據(jù)庫的物理結(jié)構(gòu)，它依賴與選定的數(shù)據(jù)庫管理系統(tǒng)。為給一個給定的邏輯數(shù)據(jù)模型選取一個最合適的應(yīng)用環(huán)境的物理結(jié)構(gòu)的過程，就是數(shù)據(jù)庫的物理設(shè)計。這一階段任務(wù)是確定數(shù)據(jù)庫的物理結(jié)構(gòu)，并不斷的進行優(yōu)化處理，主要建立索引，觸發(fā)器，存儲過程。3.1 索引建立索引是加快查詢的有效手段。建立的索引如下表所示。編號 名稱 定義 備注1-1 in supname 見附錄1-2 in medno 見附錄1-3 in cusname

11、 見附錄3.2觸發(fā)器 觸發(fā)器是用戶定義在關(guān)系表上的一類由事件驅(qū)動的過程。觸發(fā)器的定義可以更加便捷的實現(xiàn)數(shù)據(jù)的操作，增加數(shù)據(jù)操作的靈活性，對數(shù)據(jù)有更大的控制能力。如進貨量，藥品的入庫保值期的檢測。建立的觸發(fā)器見下表。編號 觸發(fā)器名稱 定義 作用t-1 tri med1 見附錄 限制更新藥品保質(zhì)期大于6個月t-2 tri med2 見附錄 限制更新藥品進價不小于1元t-3 tri med date 見附錄 限制過期藥品不能插入t-4 tri cus1 見附錄 限制將sale表的購買量小于10的改為10t-5 tri acc 見附錄 限制某一藥品倉庫存量不大于400t-6 tri cus2 見附錄

12、限制刪除c-4020客戶的退貨記錄t-7 tri buy1 見附錄 限制采購量更新量不小于1003.3存儲過程存儲過程可以實現(xiàn)數(shù)據(jù)庫操作的增刪改功能，在執(zhí)行時需要調(diào)用。藥品的進存銷過程部分存儲過程如下所示。編號 存儲過程名稱 定義 作用p-1 pro sel sup 見附錄 查詢供貨商信息p-2 pro sel med1 見附錄 查詢某一藥品生產(chǎn)日期p-3 pro sel med2 見附錄 查詢呼吸系統(tǒng)藥物p-4 pro upd med3 見附錄 更新五官科藥品保質(zhì)期p-5 pro sel sale1 見附錄 查詢中草藥的銷售量p-6 pro sel back1 見附錄 查詢某種退貨藥品的退貨

13、量p-7 pro add sup 見附錄 插入一條供應(yīng)商信息p-8 pro add med 見附錄 插入一條藥品信息p-9 pro add cus 見附錄 插入一條客戶信息p-10 pro del med 見附錄 刪除一條藥品記錄p-11 pro del back見附錄 刪除一條退貨信息p-12 pro order sale見附錄 按藥品銷量排序4.數(shù)據(jù)入庫與測試4.1.1 數(shù)據(jù)入庫各個主要數(shù)據(jù)表如藥品信息，供應(yīng)廠家信息記錄在20條以上，經(jīng)過整理收集在excel中，設(shè)置所需格式用批量導入的方式進行導入到數(shù)據(jù)庫中。先導入基本表，再導入關(guān)系表，成功證明導入后的數(shù)據(jù)表可以打開，導入失敗要檢驗是否符合

14、完整性和邏輯要求。進行修改重新導入。4.1.2 數(shù)據(jù)庫測試 數(shù)據(jù)庫測試是對建立的數(shù)據(jù)庫及數(shù)據(jù)庫對象進行測試。設(shè)計的測試案例主要有基本信息表數(shù)據(jù)的查詢，插入，刪除和更新操作。插入不滿足約束條件的數(shù)據(jù)以檢測觸發(fā)器觸發(fā)，基本操作都是以存儲過程的形式實現(xiàn)代碼的封裝，執(zhí)行時調(diào)用存儲過程。 參考文獻【1】 薩師煊 王珊 數(shù)據(jù)庫系統(tǒng)概論（第四版） 北京 高教出版社 【2】附錄 sql 語句1 基本表create database drugstroecreate table supplier(supno char(10) primary key,supname char(40) not null,supadd

15、r char(10),suptel char(15),supnet char(20)）；create table medicine(medno char(10) primary key,medname char(20) not null,medtong char(20)not null,medkind char(10),medtype char(10),medpri float,meddate char(10),medexp char(10);create table customer(cusno char(10) primary key,cusname char(10) not null,c

16、ussex char(2) check(cussex in(man, woman)，custel char(15) not null);create table buy(supno char(10),medno char(10),buyqua smallint,primary key(supno, medno),foreign key(supno) references supplier(supno),foreign key(medno)references medicine(medno);create table storage(medno char(10),stono char(10),s

17、toqua int,lostqua int,primary key (stono,medno),foreign key (stono) references storehouse (stono),foreign key(medno) reference medicine (medno);create table sale(medno char(10)，cusno char(10),salqua int,foreign key(medno) references medicine (medno),foreign key(medno) reference customer (cusno);crea

18、te table back(medno char(10),cusno char(10),backsake char(40),backqua int,foreign key(medno) references medicine (medno),foreign key(cusno) references customer (cusno);2.視圖-建立視圖create view vi_supplierasselect supnofrom suppliercreate view vi_medicineasselect mednofrom medicinecreate view vi_stroehou

19、seasselect stonofrom storehousecreate view vi_storageasselect stonofrom storagecreate view vi_saleasselect medname 藥名，salequa銷售量from medicine, salewhere medicine.medno = sale.mednoorder by salequa desc3.索引create nonclustered index in_supnameon supplier (supname)create nonclustered index in_mednoon m

20、edicine (medno)create nonclustered index in_cusnameon customer(cusname)2. 觸發(fā)器觸發(fā)器的建立-創(chuàng)建限制插入藥品保質(zhì)期大于6個月create triger tri_med1on medicineafter insert ,updataasif exists(select medexpfrom medicinewhere medexp 6)begin print 保質(zhì)期必須大于6個月 rollbackend 限制更新的藥品進價不小于1元，小于一元的改為1元create trigger tri_med2on medicinea

21、fter insert ,updateas declaremedno char(10)，medipri floatselectmedno=medno,medipri=mediprifrom insertedifmedipri medexp)beginprint藥品已過期rollbackend-限制將表的購買量小于10的改為10create trigger tri_cus1on salefor updateasif updateasif update (salequa)if exists（ select*from inserted join deleted on inserted.medno =

22、 deleted.mednowhere inserted.salequa = 10and deleted.salequa 400print 存儲量大于400-限制刪除c-4020客戶的退貨記錄create trigger tri_cus2on backafter deleteas ifexists( select*from deletedwhere cusno = c-4020）beginprint刪除記錄end -限制采購量更新量不小于100create trigger tri_buy1on buyfor insert, updateasif exist(select*from insert

23、edwhere buyqua 100) beginprint 采購量小于100end5存儲過程-建立存儲過程-查詢供貨商信息create procedure pro_sel_supasselect *from supplier-測試執(zhí)行結(jié)果execute pro_sel_sup-查詢藥品信息-create pro pro_medasselect *medicine-查詢某一藥品生產(chǎn)日期create pro pro_sel_medl medname char(20)asselect medname藥品名, meddate 生產(chǎn)日期from medicinewhere medname = medn

24、ame測試執(zhí)行結(jié)果（帶參數(shù)）exec pro_sel_med1護彤顆粒-查詢呼吸系統(tǒng)藥create proc pro_sel_med2asselect medno編號, medname 藥品名稱, medtong 通用名 from medicinewhere medkind=呼吸系統(tǒng)藥 -測試執(zhí)行結(jié)果exec pro_sel_med2-更新五官科藥品保質(zhì)期create proc pro_upd_med3asupdate medicineset medexp = medexp + 12where medkind = 五官科藥-測試執(zhí)行結(jié)果exec pro_upd_med3-查詢中成藥的銷售量cr

25、eate proc pro_sel_saleasselect medicine藥名, salequa 銷量from medicine, salewhere medicine.medno = sale.medno and medkind =中成藥-測試執(zhí)行結(jié)果exec pro_ sel_sale-查詢某種原因的藥品的退貨量create proc pro_sel_back backsake char(40)as select medname 藥名,backqua 退貨量from medicine, backwhere medicine.medno = back.medno and backsake-測試執(zhí)行結(jié)果exec pro_sel_back 過期exec pro_sel_back 數(shù)量有誤-插入一條藥品信息create proc pro_add_medmedno char(10),medname char（20）,medtong char (20),m