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

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

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

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

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

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

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

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

9、名。然后進(jìn)行用戶外模式的設(shè)計(jì)，針對中國 用戶，此處用中文命名。將 E R 模式轉(zhuǎn)換為以下關(guā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)這六個(gè)關(guān)系模式 3N 都滿足。因?yàn)樵M中都是不可分割的數(shù)據(jù)，且所有非主屬性完全依賴其主碼，且它 的任何一個(gè)非主屬性都不傳遞于任何主關(guān)鍵字。用戶外模式設(shè)計(jì)：基本信息視圖的建立：1. 單本銷售情況（圖書編號，圖書名稱客戶編號，客戶名稱，銷售單價(jià)，銷售時(shí)間） ；2. 同種銷售情況（書名，銷售數(shù)量，銷售金額） ；3.

11、 客戶信息（客戶編號，客戶名稱、性別，年齡，累計(jì)購書金額） ；4. 供應(yīng)商信息（供應(yīng)商名稱，地址，電話，聯(lián)系人） ；5. 圖書訂購信息（圖書名稱，供應(yīng)商名稱，訂購時(shí)間，訂購數(shù)量，訂購單價(jià)，訂購金額，出版社名稱，作者名 稱）；物理設(shè)計(jì)階段物理設(shè)計(jì)階段：為一個(gè)邏輯數(shù)據(jù)模型選取一個(gè)最適合應(yīng)用環(huán)境的物理結(jié)構(gòu)（存儲(chǔ)結(jié)構(gòu)與存取方法） ，數(shù)據(jù)庫的物 理設(shè)計(jì)通常分為兩步： A、確定數(shù)據(jù)庫的物理結(jié)構(gòu)； B、對物理結(jié)構(gòu)進(jìn)行時(shí)間和空間效率評價(jià)。建立小型書店管理系統(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截圖得：對各個(gè)基本表的建立（包括了各種完整性約束）建立表 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 銷售時(shí)間 , sell_price as 銷售單價(jià) 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( 銷售單價(jià) ) 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 累計(jì)購書金額from book_sell , customerwhere book_sell . #cno = custo

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

20、 order_price* order_count ) as訂購金額press_nameas出版社名稱 ,writer_nameas作者名稱from book ,book_orderwhere book. #bname = book_order. #bname建立存儲(chǔ)過程某種圖書庫存查詢存儲(chǔ)過程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某段時(shí)間內(nèi)的銷售業(yè)績查詢存儲(chǔ)過程 建立本月業(yè)績查詢存儲(chǔ)過程 由于不知其它信息，此處將銷售金額與營業(yè)金額等同 營業(yè)金額 =營業(yè)額 =成本 +利潤create procedure sp_ 查詢某段時(shí)間內(nèi)的業(yè)績-p1 為起始時(shí)間 ,p2 為結(jié)束時(shí)間p1 datetime , p2 datetime as beginselect sum（ 銷售單價(jià) ） as 銷售金額 from view_ 單本銷售情況 where 銷售時(shí)間

22、>= p1 and 銷售時(shí)間 <=p2 end go某段時(shí)間內(nèi)的銷售最好圖書信息查詢存儲(chǔ)過程 建立某段時(shí)間內(nèi)銷售最好圖書信息查詢存儲(chǔ)過程 create procedure sp_ 查詢某段時(shí)間內(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 銷售時(shí)間 >= p1

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

24、括客戶編號、客戶名稱、操作方式（插入/更新 /刪除）和操作時(shí)間 建一個(gè)操作審核表create table customer_operate ( 客戶編號 char ( 8)not null,客戶名稱 char ( 8)notnull,操作方式 char ( 4)notnull,操作時(shí)間 datetimenot null) 建立相關(guān)觸發(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ù)庫比較小，沒有必要建立索引）實(shí)現(xiàn)數(shù)據(jù)庫（有附件源代碼和該數(shù)據(jù)庫源文件）過程功能驗(yàn)證向基本表插入適量如下數(shù)據(jù)并顯示基本表信息#bno#bn ame00000001何以笙簫默0000000

28、2何以笙簫默00000003原來你還在這里00000004烈火如歌00000005烈火如歌00000006烈火如歌00000007微微一笑很傾城everybookbook#bn ameWriter_ namePress n ame何以笙簫默顧漫晉江出版社:原來你還在這里:辛夷塢起點(diǎn)出版社烈火如歌明曉溪晉江出版社微微一笑很傾城顧漫閱讀出版社#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殷風(fēng)Suppilier注：此處插入數(shù)據(jù)應(yīng)當(dāng)book在everybook之前插入數(shù)據(jù)（外鍵）截圖得：視圖顯示基本信息- 用顯示視圖基本信息select*fromview_單本銷售情況select*fromview_供應(yīng)商信息select*fromview_客戶信息select*fromview_同種銷售信息select*fromview_圖書訂購信息截圖得：執(zhí)行存儲(chǔ)過程完成目標(biāo)查詢某種圖書庫存查詢exec sp_ 某種圖書庫存查詢 ' 烈火如歌 '本月業(yè)績查詢exec sp_ 查詢某段時(shí)間內(nèi)的業(yè)績 '2013-7-1' , '2013-7-31'本月銷售最好圖書信息查詢exec sp_ 查詢某段時(shí)間內(nèi)銷售最好的圖書信息 '2013-7-1' , '2013-7-31'截圖得：用普通查詢完成目標(biāo)查詢(兩次查詢同時(shí)顯示)訂購超 3 次