1、1 概述1.1 課題簡介書店書目書種繁多， 來源多樣， 購買者眾多， 圖書信息、 供應商信息、 客戶信息、 銷售信息龐大， 不易管理。 因此，很有必要創(chuàng)建一個小型書店管理系統(tǒng)，以便于書店對圖書的管理。1.2 設計目的應用對數(shù)據(jù)庫系統(tǒng)原理的理論學習， 通過上機實踐的方式將理論知識與實踐更好的結(jié)合起來， 鞏固所學知識。數(shù)據(jù)庫應用 課程實踐：實踐和鞏固在課堂教學中學習有關知識，熟練掌握對于給定結(jié)構(gòu)的數(shù)據(jù)庫的創(chuàng)建、 基本操作、程序系統(tǒng)的建立和調(diào)試以及系統(tǒng)評價。數(shù)據(jù)庫原理 軟件設計實踐：實踐和鞏固在課堂教學中學習的關于關系數(shù)據(jù)庫原理的有關知識和數(shù)據(jù)庫系統(tǒng) 的建立方法，熟練掌握對于給定實際問題，為了建立一

2、個關系數(shù)據(jù)庫信息管理系統(tǒng)，必須得經(jīng)過系統(tǒng)調(diào)研、需求 分析、概念設計、邏輯設計、物理設計、系統(tǒng)調(diào)試、維護以及系統(tǒng)評價的一般過程，為畢業(yè)設計打下基礎。1.3 設計內(nèi)容運用基于 E-R 模型的數(shù)據(jù)庫設計方法和關系規(guī)范化理論做指導完成從系統(tǒng)的分析到設計直至系統(tǒng)的最終實 現(xiàn)，開發(fā)小型書店管理系統(tǒng)，完成小型書店管理系統(tǒng)的全部功能。首先做好需求分析，并完成數(shù)據(jù)流圖和數(shù)據(jù)字典。其次做概念分析，利用實體聯(lián)系的方法將需求分析的用戶需求抽象為信息結(jié)構(gòu)，得到 E-R 圖。然后就是邏輯結(jié)構(gòu)設計，將 E-R 圖轉(zhuǎn)換為計算機系統(tǒng)所支持的邏輯模型2 需求分析2.1 功能分析首先，建立一些基本表（盡可能滿足3N）,對大部分基本

3、信息組合、存儲；其次通過建立視圖實現(xiàn)對冗余數(shù)據(jù)的有必要保留（查詢并計算基本表屬性得到新的作為視圖屬性）并實現(xiàn)對以下基本信息的顯示。 圖書信息：圖書名稱、訂購數(shù)量、訂購時間、訂購單價、金額、出版社名稱、作者名稱；供應商名稱等； 供應商信息：供應商名稱、地址、電話，聯(lián)系人；客戶信息：客戶編號、名稱、年齡、性別、累計購書金額等； 銷售信息：時間、銷售名稱、數(shù)量、銷售單價、客戶編號、客戶名稱、金額等。在此基礎上進行以下目標查詢， 由于有些查詢常用且較復雜， 為了簡化其應用， 所以將它們定義為存儲過程。 查詢當月書店銷售金額、營業(yè)金額； （存儲過程）查詢某種圖書庫存數(shù)量； （存儲過程） 查詢當月銷量最好

4、的圖書信息； （存儲過程）按供應商名稱查詢訂購信息；（普通查詢）查詢購買次數(shù)超過 3次的客戶信息。（普通查詢）接著根據(jù)需要建立觸發(fā)器、存儲過程、索引，實現(xiàn)對數(shù)據(jù)庫的優(yōu)化。最后，進行過程功能的驗證。允許具有 權限者對數(shù)據(jù)庫進行數(shù)據(jù)更新和查詢等合法操作。數(shù)據(jù)流圖用流程圖來描述處理過程與數(shù)據(jù)的關系數(shù)據(jù)流圖如下：圖2,1數(shù)據(jù)流圖數(shù)據(jù)字典用數(shù)據(jù)字典來描述數(shù)據(jù)數(shù)據(jù)字典如下：數(shù)據(jù)項：表2.1數(shù)據(jù)字典數(shù)據(jù) 項名含義 說明別 名數(shù)據(jù) 類型長度取值范圍取值含義與其他數(shù)據(jù) 項的邏輯關系圖書編號唯一標識 每本書char800000000根據(jù)入庫時間 先后編號有時與客 戶編號作 為主碼圖書名稱唯一標識 每種書書 名va

5、rchar30有時與供應 商組成主碼作者名稱每種書的 作者姓名char8出版社名 稱每種書的 出版社名稱char12供應商名 稱唯一標識 供應商char12地址供應商的地址varchar50電話供應商的電話char700000009999999聯(lián)系人通過聯(lián)系人 書店與供應 商進行溝通char8訂購時間每種書的 訂購時間datetime應與圖書稱 與供應商 名稱作為的主碼 直接關聯(lián)訂購單價從供應商處 進貨每種書 的每本價格int客戶編號唯一標識char8000000000按購書先后有時與圖客戶編號書編號作 為主碼客戶名稱客戶姓名char8性別客戶性別char2女或男年齡客戶年齡int每年都要 增

6、加一歲的銷售時間每本書的銷售時間datetime與圖書編號 和客戶編號 作為的主碼 直接關聯(lián)銷售單價每本書的 銷售價格int數(shù)據(jù)結(jié)構(gòu):數(shù)據(jù)結(jié)構(gòu)名稱含義說明組成每種書指具有相同書名的書的集 合的基本信息圖書名稱、作者名稱、出版社名稱每本書指圖書編號唯一標識的個體的歸屬圖書編號、圖書名稱:供應商供應商的基本信息供應商名稱、地址、電話、聯(lián)系人供應基表每種書在何處訂購的具體情況圖書名稱、供應商名稱、訂購單價、訂購時間客戶客戶的基本信息客戶編號、客戶名稱、性別、年齡銷售基表每本書被誰買的具體請況圖書編號、客戶編號、 銷售單價、銷售時間概念結(jié)構(gòu)設計概念結(jié)構(gòu)設計的方法設計概念結(jié)構(gòu)通常有四類方法：自頂向下，自

7、底向上，逐漸擴張以及混合策略。本系統(tǒng)采用的是自底向上的方法。即首先定義全局的概念結(jié)構(gòu)的框架，然后逐步細化。根據(jù)自頂向下地進行需求分析然后再自底向上地進行概念設計。概念結(jié)構(gòu)設計的步驟第一步：抽象數(shù)據(jù)；第二步：設計局部 E-R圖；第三步：把各分 E-R圖綜合成總體E-R圖，消除各種沖突；第四步：對總體 E-R圖進行優(yōu)化，消除冗余數(shù)據(jù)和冗余聯(lián)系；抽象初步數(shù)據(jù)：（此處與優(yōu)化后E-R圖得出的實體聯(lián)系屬性很有可能不同）實體：圖書、供應商、客戶聯(lián)系：圖書訂購關系、圖書銷售關系圖書與供應商的關系是多對一的關系；圖書與客戶的關系是多對一的關系。屬性： 圖書的屬性：圖書編號、圖書名稱、出版社名稱、作者名稱、供應商

8、名稱； 主碼為圖書編號供應商的屬性：供應商名稱、供應商地址、電話、聯(lián)系人；主碼為供應商名稱 客戶的屬性：客戶編號、姓名、性別、年齡； 主碼為客戶編號圖書訂購關系屬性：供應商名稱、圖書編號、訂購時間、訂購單價； 主碼為供應商名稱和圖書編號圖書銷售關系屬性：客戶編號、圖書編號、銷售時間、銷售單價； 主碼為客戶編號和圖書編號分E-R圖：圖書 E-R 圖：供應商E-R圖：客戶E-R圖：初E-R圖：優(yōu)化后的總E-R圖：邏輯設計階段邏輯設計階段： 將系統(tǒng) E-R 模型轉(zhuǎn)換為關系模式， 并對給出的關系模式進行關系規(guī)范化處理 (盡量滿足 3NF)， 得到系統(tǒng)全部的關系模式，并標明主鍵、外鍵此部分一律采用英文命

9、名。然后進行用戶外模式的設計，針對中國 用戶，此處用中文命名。將 E R 模式轉(zhuǎn)換為以下關系模式：1) book(#bname, writer_name, press_name);主鍵： #bname2. ) everybook (# bno, #bname) ;主鍵： #bno外鍵： #bname3) supplier (#supplier_name, address, telephone, contact) ;主鍵： #supplier_name4) customer (#cno, cname, sex, age);主鍵： #cno5) book_order(#bname, #suppli

10、er_name, #order_time, order_price, order_count);主鍵： #bname, #supplier_name, #order_time6) book_sell(#bno, #cno, sell_time, sell_price);主鍵： #bno發(fā)現(xiàn)這六個關系模式 3N 都滿足。因為元組中都是不可分割的數(shù)據(jù)，且所有非主屬性完全依賴其主碼，且它 的任何一個非主屬性都不傳遞于任何主關鍵字。用戶外模式設計：基本信息視圖的建立：1. 單本銷售情況（圖書編號，圖書名稱客戶編號，客戶名稱，銷售單價，銷售時間） ；2. 同種銷售情況（書名，銷售數(shù)量，銷售金額） ；3.

11、 客戶信息（客戶編號，客戶名稱、性別，年齡，累計購書金額） ；4. 供應商信息（供應商名稱，地址，電話，聯(lián)系人） ；5. 圖書訂購信息（圖書名稱，供應商名稱，訂購時間，訂購數(shù)量，訂購單價，訂購金額，出版社名稱，作者名 稱）；物理設計階段物理設計階段：為一個邏輯數(shù)據(jù)模型選取一個最適合應用環(huán)境的物理結(jié)構(gòu)（存儲結(jié)構(gòu)與存取方法） ，數(shù)據(jù)庫的物 理設計通常分為兩步： A、確定數(shù)據(jù)庫的物理結(jié)構(gòu)； B、對物理結(jié)構(gòu)進行時間和空間效率評價。建立小型書店管理系統(tǒng)創(chuàng)建數(shù)據(jù)庫 book_shop_management- 創(chuàng)建小型書店管理系統(tǒng)create database book_shop_managementon

12、primary （name ='book_shop_management' ,filename = 'd:program filesmicrosoft sql servermssql.1mssqldatabook_shop_management.mdf' ,size =10mb ,maxsize =unlimited ,filegrowth =10 %）log on（name ='book_shop_management_log' ,filename = 'd:program filesmicrosoft sqlservermssql.1

13、mssqldatabook_shop_management_log.ldf' ,size =1mb,maxsize =5mb,filegrowth =3%）go截圖得：對各個基本表的建立（包括了各種完整性約束）建立表 book- 建立表 bookcreate table book (#bname varchar ( 30) primary key writer_name char ( 8), press_name char ( 12)建立表 everybook- 建立表 everybookcreate table everybook ( #bno char ( 8) primary k

14、ey , #bname varchar ( 30) not null constraint fk_everybook_bnameforeign key references book ( #bname )建立表supplier- 建立表suppliercreatetable supplier(#supplier_name char( 12 )primarykey ,addressvarchar ( 50 )not null,telephonechar ( 7)notnull,contactchar ( 8) notnull)建立表customer- 建立表customercreatetable

15、 customer(#cno char ( 8) primarykey>cname char ( 8)not null,sex char( 2),age int>constraintck_customer_sexcheck( sex建立表book_order- 建立表book_ordercreatetable book_order (#bnamevarchar ( 30),#supplier_name char( 12 ),#order_time datetime>order_count int notnull,order_price int notnull,constrai

16、ntpk_book_orderprimarykeyin( ' 男 ' , ' 女' )( #bname , #supplier_name , #order_time )建立表 book_sell- 建立表 book_sellcreatetablebook_sell(#bnochar( 8)primarykey#cnochar( 8)notnull,sell_timedatetimesell_price int)建立視圖建立基本信息視圖：建立單本銷售情況視圖- 建立單本銷售情況視圖create view view_ 單本銷售情況asselect book_sell

17、 . #bno as 圖書編號 , #bname as 圖書名稱 book_sell . #cno as 客戶編號 , cname as 客戶名稱 , sell_time as 銷售時間 , sell_price as 銷售單價 from book_sell , customer , everybookwhere everybook. #bno = book_sell . #bnoand customer . #cno = book_sell . #cno建立同種銷售情況視圖- 建立同種銷售情況視圖create view view_ 同種銷售情況asselect 圖書名稱 as 書名 ,cou

18、nt ( 圖書編號 ) as 銷售數(shù)量 ,sum( 銷售單價 ) as 銷售金額from view_ 單本銷售情況group by 圖書名稱建立客戶信息視圖- 建立客戶信息情況create view view_ 客戶信息select customer . #cno as 客戶編號 ,cname as 客戶名稱 ,sex as 性別 ,age as 年齡 ,( select sum( sell_price ) from book_sellgroup by #cno ) as 累計購書金額from book_sell , customerwhere book_sell . #cno = custo

19、mer . #cno建立供應商視圖- 建立供應商視圖create view view_ 供應商信息asselect #supplier_name as 供應商名稱 address as 地址 , telephone as 電話 , contact as 聯(lián)系人from supplier建立訂購信息視圖- 建立訂購信息視圖create viewview_ 圖書訂購信息asselect book_order. #bname as圖書名稱#supplier_nameas 供應商名稱 ,#order_timeas訂購時間 ,order_priceas訂購單價 ,order_countas訂購數(shù)量 ,(

20、 order_price* order_count ) as訂購金額press_nameas出版社名稱 ,writer_nameas作者名稱from book ,book_orderwhere book. #bname = book_order. #bname建立存儲過程某種圖書庫存查詢存儲過程create procedure sp_ 某種圖書庫存查詢-p1 為要查詢的圖書名稱p1 varchar ( 30)asbeginselect 訂購數(shù) - 銷售數(shù)量 as 庫存from ( select sum( order_count ) as 訂購數(shù) , #bname as 書名 from book

21、_ordergroup by #bname ) as x , view_ 同種銷售情況where x . 書名 =p1 and view_ 同種銷售情況 . 書名 =p1 end某段時間內(nèi)的銷售業(yè)績查詢存儲過程 建立本月業(yè)績查詢存儲過程 由于不知其它信息，此處將銷售金額與營業(yè)金額等同 營業(yè)金額 =營業(yè)額 =成本 +利潤create procedure sp_ 查詢某段時間內(nèi)的業(yè)績-p1 為起始時間 ,p2 為結(jié)束時間p1 datetime , p2 datetime as beginselect sum（ 銷售單價 ） as 銷售金額 from view_ 單本銷售情況 where 銷售時間

22、>= p1 and 銷售時間 <=p2 end go某段時間內(nèi)的銷售最好圖書信息查詢存儲過程 建立某段時間內(nèi)銷售最好圖書信息查詢存儲過程 create procedure sp_ 查詢某段時間內(nèi)銷售最好的圖書信息 p1 datetime , p2 datetime as begin select #bname as 圖書名稱 , writer_name as 作者名稱 , press_name as 出版社名稱 from （ select圖書名稱 as 書名 ,count （ 圖書編號 ） as 銷售數(shù)量 from view_ 單本銷售情況 where 銷售時間 >= p1

23、and 銷售時間 <=p2 group by 圖書名稱 ） as x , book where #bname = 書名and 銷售數(shù)量 >=all（ select count （ 圖書編號 ） from view_ 單本銷售情況 where 銷售時間 >= p1 and 銷售時間 <=p2 group by 圖書名稱 ） end go建立觸發(fā)器（另外 5 個表也建立相應曾刪改觸發(fā)器，不過此處從略，詳情 見附件源代碼）建立一個 customer 的操作審核表，并建立相關觸發(fā)器，當向 customer 中插入、更新或刪除記錄時，生成一條操 作記錄插入到該操作審核表中，內(nèi)容包

24、括客戶編號、客戶名稱、操作方式（插入/更新 /刪除）和操作時間 建一個操作審核表create table customer_operate ( 客戶編號 char ( 8)not null,客戶名稱 char ( 8)notnull,操作方式 char ( 4)notnull,操作時間 datetimenot null) 建立相關觸發(fā)器create trigger trigger_customer_insert on customerfor insertasbegindeclare p1 char ( 8), p2 char ( 8) set p1 =( select #cno from in

25、serted )set p2 =( select cname frominserted)print 'insert:'select*from insertedinsertinto customer_operatevalues( p1, p2, ' 插入 ' , getdate ()endgocreatetrigger trigger_customer_deleteon customerfor deleteasbegindeclare p1 char ( 8), p2 char ( 8)set p1 =( select #cno from deleted )set

26、 p2 =( select cname from deleted )print 'deleted:'select * from deletedinsert into customer_operate values ( p1, p2, ' 刪除 ' , getdate () end gocreate trigger trigger_customer_updateon customerfor updateasbegindeclare p1 char ( 8), p2 char ( 8)set p1 =( select #cno from deleted )set p

27、2 =( select cname from deleted )print 'inserted:'select *from insertedprint 'deleted:'select *from deletedinsert into customer_operate values ( p1, p2, ' 修改 ' , getdate () end建立索引（由于數(shù)據(jù)庫比較小，沒有必要建立索引）實現(xiàn)數(shù)據(jù)庫（有附件源代碼和該數(shù)據(jù)庫源文件）過程功能驗證向基本表插入適量如下數(shù)據(jù)并顯示基本表信息#bno#bn ame00000001何以笙簫默0000000

28、2何以笙簫默00000003原來你還在這里00000004烈火如歌00000005烈火如歌00000006烈火如歌00000007微微一笑很傾城everybookbook#bn ameWriter_ namePress n ame何以笙簫默顧漫晉江出版社:原來你還在這里:辛夷塢起點出版社烈火如歌明曉溪晉江出版社微微一笑很傾城顧漫閱讀出版社#bno#c noSell_timeSell_price00000001000000012013-6-162100000003000000022013-6-251900000007000000022013-7-92000000004000000032013-7

29、-92300000005000000042013-7-1918book_sell#c nocn amesexAge00000001呂鵬女1800000002孫魯魯女1800000003王肖雨女1900000004邵麗君女19customer#bn ame#supplier_ nameOrder_timeOrder_cou ntOrder_price何以笙簫默大旗2013-6-10212原來你還在這里大旗2013-6-20110烈火如歌紅方2013-7-2110烈火如歌長城2013-7-329微微一笑很傾城高原2013-7-3111Book_order#suppileraddressteleph

30、o necon tact大旗上海1573234王平紅方上海1734272李景長城P江蘇1564323張艷麗高原鄭州1242356殷風Suppilier注：此處插入數(shù)據(jù)應當book在everybook之前插入數(shù)據(jù)（外鍵）截圖得：視圖顯示基本信息- 用顯示視圖基本信息select*fromview_單本銷售情況select*fromview_供應商信息select*fromview_客戶信息select*fromview_同種銷售信息select*fromview_圖書訂購信息截圖得：執(zhí)行存儲過程完成目標查詢某種圖書庫存查詢exec sp_ 某種圖書庫存查詢 ' 烈火如歌 '本月業(yè)績查詢exec sp_ 查詢某段時間內(nèi)的業(yè)績 '2013-7-1' , '2013-7-31'本月銷售最好圖書信息查詢exec sp_ 查詢某段時間內(nèi)銷售最好的圖書信息 '2013-7-1' , '2013-7-31'截圖得：用普通查詢完成目標查詢(兩次查詢同時顯示)訂購超 3 次