《SQLserver數據庫》課程設計范例講解學習

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

2、法，熟練掌握對于給定實際問題，為了 建立一個關系數據庫信息管理系統(tǒng)，必須得經過系統(tǒng)調研、需求分析、概念設 計、邏輯設計、物理設計、系統(tǒng)調試、維護以及系統(tǒng)評價的一般過程，為畢業(yè) 設計打下基礎1.3 設計內容運用基于E-R模型的數據庫設計方法和關系規(guī)范化理論做指導完成從系統(tǒng) 的分析到設計直至系統(tǒng)的最終實現，開發(fā)小型書店管理系統(tǒng)，完成小型書店管 理系統(tǒng)的全部功能。首先做好需求分析，并完成數據流圖和數據字典。 其次做概念分析，利用實體聯系的方法將需求分析的用戶需求抽象為信息 結構，得到 E-R 圖。然后就是邏輯結構設計，將 E-R 圖轉換為計算機系統(tǒng)所支持的邏輯模型2 需求分析2.1 功能分析首先，建

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

4、存數量；（存儲過程）查詢當月銷量最好的圖書信息；（存儲過程）按供應商名稱查詢訂購信息；（普通查詢）查詢購買次數超過3次的客戶信息。（普通查詢）接著根據需要建立觸發(fā)器、存儲過程、索引，實現對數據庫的優(yōu)化。最 后，進行過程功能的驗證。允許具有權限者對數據庫進行數據更新和查詢等合 法操作。數據流圖用流程圖來描述處理過程與數據的關系數據流圖如下：圖書目錄文件購買信息文件出版社檔案文件用戶用戶購買信息'一、供應商供應信息1 圖書需求 信息處理.圖書供應八一* :.信息處理丿供應商顧客購書檔案文件購買供應匯總文件圖2,1數據流圖數據字典用數據字典來描述數據數據字典如下：數據項:表2.1數據字典數據

5、 項名含義 說明別 名數據 類型長 度取值范圍取值含義與其他 數據項 的邏輯 關系圖書編 號唯一標識每本書char80000000099999999根據入庫時 間先后編號有時與 客戶編號 作為主碼圖書名 稱唯一標識 每種書書 名varchar30有時與 供應商 組成主碼作者名 稱每種書的 作者姓名char8出版社 名稱每種書的 出版社名稱char12供應商 名稱唯一標識 供應商char12地址供應商的地 址varchar50電話供應商的電 話char700000009999999聯系人通過聯系人 書店與供應 商進行溝通char8訂購時 間每種書的 訂購時間datetime應與圖書稱訂購單 價從

6、供應商處 進貨每種書 的每本價格int與供應 商 名稱 作為的 主碼 直接關 聯客戶編 號唯一標識 客戶char800000000099999999按購書先后 編號有時與 圖書編號 作為主碼客戶名 稱客戶姓名char8性別客戶性別char2女或男年齡客戶年齡int每年都要 增加一歲的銷售時 間每本書的 銷售時間datetime與圖書 編號 和客戶 編號 作為的 主碼 直接關 聯銷售單 價每本書的 銷售價格int數據結構:數據結構名 稱含義說明組成每種書指具有相同書名的書的集 合的基本信息圖書名稱、作者名稱、出版社 名稱每本書指圖書編號唯一標識的個體的歸屬圖書編號、圖書名稱供應商供應商的基本信息

7、供應商名稱、地址、電話、聯系人供應基表每種書在何處訂購的具體情況圖書名稱、供應商名稱、 訂購單價、訂購時間客戶客戶的基本信息客戶編號、客戶名稱、性別、 年齡銷售基表每本書被誰買的具體請況圖書編號、客戶編號、銷售單價、銷售時間概念結構設計概念結構設計的方法設計概念結構通常有四類方法：自頂向下，自底向上，逐漸擴張以及混合 策略。本系統(tǒng)采用的是自底向上的方法。即首先定義全局的概念結構的框架， 然后逐步細化。根據自頂向下地進行需求分析然后再自底向上地進行概念設 計。概念結構設計的步驟第一步：抽象數據；第二步：設計局部E-R圖；第三步：把各分E-R圖綜合成總體E-R圖，消除各種沖突； 第四步：對總體E-

8、R圖進行優(yōu)化，消除冗余數據和冗余聯系；抽象初步數據：（此處與優(yōu)化后E-R圖得出的實體聯系屬性很有可能不同）實體：圖書、供應商、客戶聯系：圖書訂購關系、圖書銷售關系圖書與供應商的關系是多對一的關系； 圖書與客戶的關系是多對一的關系屬性：圖書的屬性：圖書編號、圖書名稱、出版社名稱、作者名稱、供應商名稱;主碼為圖書編號供應商的屬性：供應商名稱、供應商地址、電話、聯系人；主碼為供應商名稱客戶的屬性：客戶編號、姓名、性別、年齡；主碼為客戶編號圖書訂購關系屬性：供應商名稱、圖書編號、訂購時間、訂購單價；主碼為供應商名稱和圖書編號圖書銷售關系屬性：客戶編號、圖書編號、銷售時間、銷售單價；主碼為客戶編號和圖書

9、編號分E-R圖：圖書E-R圖：供應商E-R圖:客戶E-R圖:初E-R圖:圖書編圖書名出版社爾作者名供應商名稱供應商圖書編圖書客戶編圖書編訂購時間銷售時訂購單價圖書訂購圖書銷銷售單供應商地供應商電話聯系年齡客戶性別客戶編姓名優(yōu)化后的總E-R圖:1N供應商名稱系人邏輯設計階段邏輯設計階段：將系統(tǒng)E-R模型轉換為關系模式，并對給出的關系模式進 行關系規(guī)范化處理（盡量滿足 3NF），得到系統(tǒng)全部的關系模式，并標明主 鍵、外鍵此部分一律采用英文命名。然后進行用戶外模式的設計，針對中國用 戶，此處用中文命名。將E R模式轉換為以下關系模式：1)book倂bname, writer_name, pressn

10、ame);主鍵： #bname2. ) everybook (# bno, #bname) ;主鍵： #bno外鍵： #bname3) supplier (#supplier_name, address, telephone, contact) ;主鍵： #supplier_name4) customer (#cno, cname, sex, age);主鍵： #cno5) book_order(#bname, #supplier_name, #order_time, order_price, order_count);主鍵： #bname, #supplier_name, #order_ti

11、me6) book_sell(#bno, #cno, sell_time, sell_price);主鍵： #bno發(fā)現這六個關系模式 3N 都滿足。因為元組中都是不可分割的數據，且所有非主屬性完全依賴其主碼，且它的任何一個非主屬性都不傳遞于任何主關鍵字。用戶外模式設計：基本信息視圖的建立：1. 單本銷售情況（圖書編號，圖書名稱客戶編號，客戶名稱，銷售單價， 銷售時間）；2. 同種銷售情況（書名，銷售數量，銷售金額）；3. 客戶信息（客戶編號，客戶名稱、性別，年齡，累計購書金額）；4. 供應商信息（供應商名稱，地址，電話，聯系人）；5. 圖書訂購信息（圖書名稱，供應商名稱，訂購時間，訂購數量，

12、訂購單價，訂購金額，出版社名稱，作者名稱）；物理設計階段物理設計階段：為一個邏輯數據模型選取一個最適合應用環(huán)境的物理結構(存儲結構與存取方法)，數據庫的物理設計通常分為兩步： A 、確定數據庫的物 理結構；B、對物理結構進行時間和空間效率評價。建立小型書店管理系統(tǒng)創(chuàng)建數據庫 book_shop_management- 創(chuàng)建小型書店管理系統(tǒng)create database book_shop_managementon primary (name = 'book_shop_management',filename ='d:program filesmicrosoft sql

13、servermssql.1mssqldata book_shop_management.mdf' , size = 10mb ,maxsize = unlimited , filegrowth =10%)log on(name = 'book_shop_management_log',filename ='d:program filesmicrosoft sql servermssql.1mssqldatabook_shop_management_log.ldf' , size =1mb, maxsize =5mb, filegrowth =3%) go

14、截圖得：2013-201303. . . nA匸.一 s4l 乙攝要|cireate dotcdoasc ktook liop lnetneigeincnton priniary (naine= 'LooRshopanag&nient',f i lenan>e= 1: prccjrani fi 1 pflXmtcTrosoft.只ql flFxrvrmHQl . 1 TrmsnldHtto o o lt_3 ho f_iftatiag eme nt, nd£'； slae-iOirio.fiiawsise utLlirtilted,1 ilero

15、uth-10-j)log on(name = 'too 口ho p_ii(ianagenie ntlog1 ,ti Lename=l proguaixi f I le3mic;i:osa£t sql 3&u¥Gr''mssqL IAtxim呂gl'dBtdbooE shop simaKaiEE CwHo.<>'i消島帝會已成功完戚.對各個基本表的建立(包括了各種完整性約束)建立表book-建立表bookcreate table book (#bname varchar (30) primary key ,write

16、r_namechar (8),press_namechar (12)建立表everybook-建立表 everybookcreate table everybook (#bno char ( 8) primarykey ,#bname varchar (30) not nullconstraintfk_everybook_bnameforeignkey references book ( #bname )建立表supplier-建立表 suppliercreate table supplier (#supplier_namechar (12) primary key ,address varc

17、har ( 50 )notnull,telephonechar ( 7)notnull,con tactchar ( 8) not n ull)建立表 customer- 建立表 customercreate table customer (#cno char ( 8) primary key ,cname char ( 8)not null,sex char ( 2),age int ,constraint ck_customer_sex check ( sex in( ' 男 ' , ' 女' )建立表 book_order- 建立表 book_orderc

18、reate tablebook_order (#bname varchar ( 30),#supplier_namechar ( 12 ),#order_timedatetime ,order_countint not null,order_priceint not null,constraintpk_book_order primarykey( #bname , #supplier_name , #order_time )建立表 book_sell建立表 book_sellcreatetablebook_sell#bnochar(8)primarykey ,#cnochar(8)not nu

19、ll,sell_timedatetimesell_price int)建立視圖建立基本信息視圖：建立單本銷售情況視圖- 建立單本銷售情況視圖create view view_ 單本銷售情況asselect book_sell . #bno as 圖書編號 , #bname as 圖書名稱 book_sell . #cno as 客戶編號 , cname as 客戶名稱 , sell_time as 銷售時間 , sell_price as 銷售單價from book_sell , customer , everybook where everybook . #bno = book_sell .

20、 #bno and customer . #cno = book_sell. #cno建立同種銷售情況視圖- 建立同種銷售情況視圖 create view view_ 同種銷售情況 as select 圖書名稱 as 書名 , count ( 圖書編號 ) as 銷售數量 , sum( 銷售單價 ) as 銷售金額 from view_ 單本銷售情況 group by 圖書名稱建立客戶信息視圖- 建立客戶信息情況 create view view_ 客戶信息 select customer . #cno as 客戶編號 , cname as 客戶名稱 , sex as 性別 , age as

21、年齡 ,( select sum( sell_price ) from book_sell group by #cno ) as 累計購書金額 from book_sell , customerwhere book_sell . #cno = customer . #cno建立供應商視圖- 建立供應商視圖 create view view_ 供應商信息 asselect #supplier_name as 供應商名稱 , address as 地址 , telephone as 電話 , contact as 聯系人 from supplier建立訂購信息視圖- 建立訂購信息視圖 create

22、 view view_ 圖書訂購信息asselect book_order. #bname as圖書名稱 ,#supplier_nameas 供應商名稱 ,#order_timeas訂購時間 ,order_priceas訂購單價 ,order_countas訂購數量 ,( order_price* order_count ) as訂購金額press_nameas出版社名稱 ,writer_nameas作者名稱from book ,book_orderwhere book. #bname = book_order. #bname建立存儲過程某種圖書庫存查詢存儲過程create procedure

23、 sp_ 某種圖書庫存查詢-p1 為要查詢的圖書名稱p1 varchar ( 30 )asbeginselect 訂購數 - 銷售數量 as 庫存from ( selectsum ( order_count ) as 訂購數, #bname as 書名from book_ordergroup by #bname ) as x , view_ 同種銷售情況where x . 書名 =p1 and view_ 同種銷售情況 . 書名 =p1 end go某段時間內的銷售業(yè)績查詢存儲過程 建立本月業(yè)績查詢存儲過程 由于不知其它信息，此處將銷售金額與營業(yè)金額等同 營業(yè)金額 =營業(yè)額 =成本 +利潤cr

24、eate procedure sp_ 查詢某段時間內的業(yè)績-p1 為起始時間 ,p2 為結束時間p1 datetime , p2 datetime as beginselect sum（ 銷售單價 ） as 銷售金額 from view_ 單本銷售情況 where 銷售時間 >=p1 and 銷售時間 <=p2end某段時間內的銷售最好圖書信息查詢存儲過程 建立某段時間內銷售最好圖書信息查詢存儲過程create procedure sp_ 查詢某段時間內銷售最好的圖書信息 p1 datetime , p2 datetime as begin select #bname as 圖書名

25、稱 , writer_name as 作者名稱 , press_name as 出版社名稱 from ( select 圖書名稱 as 書名 , count ( 圖書編號 ) as 銷售數量 from view_ 單本銷售情況 where 銷售時間 >=p1 and 銷售時間 <=p2 group by 圖書名稱 ) as x , book where #bname = 書名 and 銷售數量 >=all( select count ( 圖書編號 ) from view_ 單本銷售情況 where 銷售時間 >=p1 and 銷售時間 <=p2 group by

26、圖書名稱 ) end go建立觸發(fā)器(另外 5 個表也建立相應曾刪改觸發(fā)器，不過 此處從略，詳情見附件源代碼)建立一個customer的操作審核表，并建立相關觸發(fā)器，當向customer中插入、更新或刪除記錄時，生成一條操作記錄插入到該操作審核表中，內容包括客戶 編號、客戶名稱、操作方式(插入 /更新 /刪除)和操作時間 建一個操作審核表 create table customer_operate ( 客戶編號 char ( 8)not null, 客戶名稱 char ( 8)not null, 操作方式 char ( 4)not null, 操作時間 datetime not null) 建

27、立相關觸發(fā)器create trigger trigger_customer_insert on customerfor insert as begindeclarep1 char ( 8), p2 char ( 8)set p1=( select#cnofrominserted )set p2=( selectcnamefrominserted )print 'insert:'select* from insertedinsertinto customer_operatevalues ( p1, p2, ' 插入 'getdate ()endgocreate t

28、rigger trigger_customer_deleteon customerfor deleteasbegindeclare p1 char ( 8), p2 char ( 8) set p1 =( select #cno from deleted ) set p2 =( select cname from deleted ) print 'deleted:'select * from deletedgetdate ()insert into customer_operate values ( p1, p2, ' 刪除 end gocreate trigger t

29、rigger_customer_updateon customerfor updateasbegindeclare p1 char ( 8), p2 char ( 8) set p1 =( select #cno from deleted )set p2 =( select cname from deleted )print'inserted:'select* from insertedprint'deleted:'select* from deletedinsertinto customer_operatevalues ( p1, p2, ' 修改 &

30、#39;end, getdate ()建立索引（由于數據庫比較小，沒有必要建立索引）實現數據庫（有附件源代碼和該數據庫源文件）過程功能驗證#bn ameWriter namePress namd何以笙簫默顧漫晉江出版原來你還在 這里辛夷塢起點出版烈火如歌明曉溪晉江出版微微一笑很顧漫閱讀出版Ibook#bno#bn ame00000001何以笙簫默00000002何以笙簫默00000003原來你還在這里00000004烈火如歌00000005烈火如歌00000006烈火如歌00000007微微一笑很傾城everybook向基本表插入適量如下數據并顯示基本表信息#bno#c noSell time

31、Sell price00000001000000012013-6-162100000003000000022013-6-251900000007000000022013-7-92000000004000000032013-7-92300000005000000042013-7-1918book_sellcustomer#c nocn amesexAge00000001呂鵬女1800000002孫魯魯女1800000003王肖雨女1900000004邵麗君女19Book_order#bn ame#supplier nameOrder timeOrder cou ntOrder price何以笙簫

32、默大旗2013-6-10212原來你還在這里大旗2013-6-20110烈火如歌紅方2013-7-2110烈火如歌長城2013-7-329微微一笑很傾城高原2013-7-3111Suppilier#suppileraddresstelepho necon tact大旗上海1573234王平紅方上海1734272李景長城江蘇1564323張艷麗高原鄭州1242356殷風注：此處插入數據應當book在everybook 之前插入數據（外鍵）截圖得：摘要5elect select1口 okbook orderselect select selectfrom from froiDbook sellcu

33、stomer everybook2013-201303. . . nt=s.oul. zql結果.J消息ttbnaine血 terane press_name晉江出版社 晉江出版社 聞讀岀蔽社jiS«"p明曉溪4原來悔還在這里至夷塢起點出版社ttbnairiett$uppli?r_narriB ttorifer_tinnefder_pciceorder_co4jnt1訶訂笙謫大旗2013-0620 00:00:00.0002122斂扣歐2C13-07-03 00:00:00.0002g3烈火葩狀紅方2013-0702 00:00:00.0001ID4徽鍛一卿艮偵城BUS20

34、13-07-03 00:00:00.0001115原乘悔還在這里大塚20130702 00;00;00.000110ttbnott enosdl_timesell_pirice10000000100000001an 306-25 oaoaoaooo2120000000300000002201507-4)9 OOtODtOQOOO193ODDOOD40000000320137-Dy UOtODtOOLOOO23A00000005000000042013-07-19 OOtOJOOlOOO13/ 2013-201303- _占叩尊2013-201303. . - ntsoul. skjL 摘要se

35、lect 卞 trom cu3toioerselect 卞 t匚口m evetybcofccIegl * f匚cun fFupp 1 iec二結果匕消息甘七*7'"U" W *1 "*ju v- aw u u trttcnccn*mft sexage1! 000000011IS20000000213300000003呂鵬吉134(H000004呂鵬寶19ttbrwttbname1! 00000001何以笙襦默200000002何以笙讀默3OOOCOOU3原來禰還,"400000004烈火如荻500000005烈火如隸600000006烈火如歐70

36、0000007微徽一笑.#supplfer_naneadie?telephone!cent 西 t1i長城江蘇156«23張艷麗2大旗±ffl1573234王平3高原鄭州124235G4紅方上海1734272視圖顯示基本信息 用顯示視圖基本信息select*fromview_單本銷售情況select*fromview_供應商信息select*fromview_客戶信息select*fromview_同種銷售信息select*fromview圖書訂購信息截圖得:2013-201303.select *SClCGt *select *select *select *_ _ LQ

37、n.eryl. s ql# from view 單 fEom view twin viuW客 from view |q frem view 圖301>201303. . . nt_atul silH 摘要本銷售1 應商信 戶倍息 種銷售 書訂購勺青況青祝 信息<二箱果消息屋I書編號建書名稱容尸黑客尸名銷售時間銷酋單價1Q0000001何以笙簫賦QQOOOC01 呂幫201-05-25 QO;QQ;00.000 212OOOOOC03原來你還花這里00000(02 JJ1BW 2013-07-09 00:QO:OO.OCO 19300000004烈火如融(JOtXJOCOa 王肖雨2

38、01-3 07 09 00 00:00 00023400000005烈火如歌(J0(X)Q(C4 邵麗君 20l3(Fl9 0Q：)0：QQQai 18斥nnoooco?nnnoorn2 孫冉皆 2m-(vnq nnoo oo nm ?n供應商若稱地址電話聯系人14III江蘇 1564323張tfi用I 2上海 1573234王平3直原券州 12433564虹方上海 1734272李是客戶編呂疙勺名. 性 年雖計購韋金顫1OOQCOOOI呂解玄10?1230000000200000003孫昏首寶1SJ9王肖雨立1923400000004邵麗君女 19'8銷售璧銷售金輾1阿以笙簫默1Z1

39、20火如歌2413黴徽一奘1204匿來施1慳磴商名一一trm.tm金ft者若稱1大旗2013 06 20 00:00:00.000212E4晉江出版社顧漫2任城2013-0703 00:00:00.10029T6晉江出版社明癖3烈火如歌紅萬2013-07-02 00:00:00.10011U晉江出嚴社4槪徽一笑高原2013-07-03 00:00:00 000111T1聞讀岀牘社5原乘你還大旗2013-0702 00:00:00.100110TO起點出瓶社辛盲塢執(zhí)行存儲過程完成目標查詢某種圖書庫存查詢exec sp_某種圖書庫存查詢烈火如歌本月業(yè)績查詢exec sp_ 查詢某段時間內的業(yè)績20

40、13-7-1', '2013-7-31'本月銷售最好圖書信息查詢exec sp_查詢某段時間內銷售最好的圖書信息'2013-7-1','2013-7-31'截圖得:2013*21)1303-.t二ntil. sqlJ 2013-7-31 ' 2013-7-31'2013-7-J1息 -1信 -?書 13圖ft o y 屯2 W 尤-好 瓢韁杲 業(yè)售詢ft錯査內內-仔可可-種旬向- / i 1 i 1 一U#查- 一 一 一 -p p P s s S茫杲LJ消息J庫存I匸1銷百金額亙二國書若稱作者若.出版社名荊1理應.變.明

41、睨溪晉江出版社jiii用普通查詢完成目標查詢（兩次查詢同時顯示）訂購超3次客戶信息查詢訂購超次客戶信息查詢select* fromview_ 客戶信息where客戶編號=an y(select#c nofrom book_sellgroupby #cnohav ingcou nt(*)>3)按供應商名稱查詢訂購信息-按供應商名稱查詢訂購信息select * from view_圖書訂購信息order by供應商名稱截圖得:A2013-201303 LQuerrT.1*2013-201303. . .nt.sotl. sqljjhere 客尸編號 =eny(select #cuo from boo?: 3ell toy cnosaving ca4n-(Ti >3)技供應荀苦稱查詢訂購息 select rrcwi n單書訂購信息二第果u廿觸I蠱P褊呂 宮F客眸 年雖耳如金廝國毛若稱供應商名訂囪時間訂購單IT啦金出版社名稱作者名常1201306 20 00 00:00.00092 18晉汀舸怎社2何田舉蕭默2ni3-nfi-i r oooo：oo.oon12224晉訂舸版