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

2、題，為了建立一個關系數據庫信息管理系統，必須得經過系統調研、需求分析、概念設計、邏輯設計、物理設計、系統調試、維護以及系統評價的一般過程，為畢業設計打下基礎。 設計內容 運用基于E-R 模型的數據庫設計方法和關系規范化理論做指導完成從系統的分析到設計直至系統的最終實現，開發小型書店管理系統，完成小型書店管理系統的全部功能。 首先做好需求分析，并完成數據流圖和數據字典。 其次做概念分析，利用實體聯系的方法將需求分析的用戶需求抽象為信息結構，得到 E-R 圖。 然后就是邏輯結構設計，將E-R 圖轉換為計算機系統所支持的邏輯模型 2 需求分析 功能分析 首先，建立一些基本表（盡可能滿足3N），對大部

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

4、圖書信息；（存儲過程） 按供應商名稱查詢訂購信息；（普通查詢） 查詢購買次數超過3次的客戶信息。（普通查詢） 接著根據需要建立觸發器、存儲過程、索引，實現對數據庫的優化。最后，進行過程功能的驗證。允許具有權限者對數據庫進行數據更新和查詢等合法操作。 數據流圖 用流程圖來描述處理過程與數據的關系 數據流圖如下： 圖2,1 數據流圖 數據字典 用數據字典來描述數據 數據字典如下： 數據項： 表數據字典 數據結構： 概念結構設計 概念結構設計的方法 設計概念結構通常有四類方法：自頂向下，自底向上，逐漸擴張以及混合策略。本系統采用的是自底向上的方法。即首先定義全局的概念結構的框架，然后逐步細化。根據自

5、頂向下地進行需求分析然后再自底向上地進行概念設計。 概念結構設計的步驟 第一步：抽象數據； 第二步：設計局部E-R圖； 第三步：把各分E-R圖綜合成總體E-R圖，消除各種沖突； 第四步：對總體E-R圖進行優化，消除冗余數據和冗余聯系； 抽象初步數據：（此處與優化后E-R圖得出的實體聯系屬性很有可能不同） 實體：圖書、供應商、客戶 聯系：圖書訂購關系、圖書銷售關系 圖書與供應商的關系是多對一的關系； 圖書與客戶的關系是多對一的關系。 屬性： 圖書的屬性：圖書編號、圖書名稱、出版社名稱、名稱、供應商名稱；主碼為圖書編號 供應商的屬性：供應商名稱、供應商地址、電話、聯系人； 主碼為供應商名稱 客戶的

6、屬性：客戶編號、姓名、性別、年齡； 主碼為客戶編號 圖書訂購關系屬性：供應商名稱、圖書編號、訂購時間、訂購單價； 主碼為供應商名稱和圖書編號 圖書銷售關系屬性：客戶編號、圖書編號、銷售時間、銷售單價； 主碼為客戶編號和圖書編號 分E-R圖： 圖書E-R圖： 供應商E-R圖： 客戶E-R圖： 初E-R圖： 優化后的總E-R圖： 邏輯設計階段 邏輯設計階段：將系統E-R模型轉換為關系模式，并對給出的關系模式進行關系規范化處理（盡量滿足3NF），得到系統全部的關系模式，并標明主鍵、外鍵此部分一律采用英文命名。然后進行用戶外模式的設計，針對中國用戶，此處用中文命名。 將ER模式轉換為以下關系模式： 1

7、) book(#bname, writer_name, press_name); 主鍵：#bname 2.) everybook (# bno, #bname) ; 主鍵：#bno 外鍵：#bname 3) supplier (#supplier_name, address, telephone, contact) ; 主鍵：#supplier_name 4) customer (#cno, cname, sex, age); 主鍵：#cno 5) book_order(#bname, #supplier_name, #order_time, order_price, order_count)

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

9、稱，地址，電話，聯系人）； 5. 圖書訂購信息（圖書名稱，供應商名稱，訂購時間，訂購數量，訂購單價，訂購金額， 出版社名稱，名稱）； 物理設計階段 物理設計階段：為一個邏輯數據模型選取一個最適合應用環境的物理結構（存儲結構與存取方法），數據庫的物理設計通常分為兩步：A、確定數據庫的物理結構；B、對物理結構進行時間和空間效率評價。 建立小型書店管理系統 創建數據庫 book_shop_management -創建小型書店管理系統 create database book_shop_management on primary( name=book_shop_management, filename

10、=d:program filesmicrosoft sql servermssqldata , size=10mb, maxsize=unlimited, filegrowth=10%) log on( name=book_shop_management_log, filename=d:program filesmicrosoft sql servermssqldata, size=1mb, maxsize=5mb, filegrowth=3%) go 截圖得： 對各個基本表的建立（包括了各種完整性約束） 建立表 book -建立表book create table book( #bname

11、varchar(30)primary key, writer_name char(8), press_name char(12) 建立表 everybook -建立表everybook create table everybook( #bno char(8)primary key, #bname varchar(30)not null constraint fk_everybook_bname foreign key references book(#bname) -建立表supplier create table supplier( #supplier_name char(12)primar

12、y key, address varchar(50)not null, telephone char(7)not null, contact char(8)not null) 建立表 customer -建立表customer create 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 create table book_order( #bname v

13、archar(30), #supplier_name char(12), #order_time datetime, order_count int not null, order_price int not null, constraint pk_book_order primary key(#bname,#supplier_name,#order_time)建立表 book_sell -建立表book_sell create table book_sell( #bno char(8)primary key, #cno char(8)not null, sell_time datetime,

14、 sell_price int) 建立視圖 建立基本信息視圖： 建立單本銷售情況視圖 -建立單本銷售情況視圖 create view view_單本銷售情況 as select 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.#bno and customer.#cno=book_sell.#cno

15、建立同種銷售情況視圖 -建立同種銷售情況視圖 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年齡, (select sum(sell_price)from book_sell group by #cno)as累計購書金額 from

16、 book_sell,customer where book_sell.#cno=customer.#cno 建立供應商視圖 -建立供應商視圖 create view view_供應商信息 as select #supplier_name as供應商名稱, address as地址, telephone as電話, contact as聯系人 from supplier 建立訂購信息視圖 -建立訂購信息視圖 create view view_圖書訂購信息 as select book_order.#bname as圖書名稱, #supplier_name as供應商名稱, #order_tim

17、e as訂購時間, order_price as訂購單價, order_count as訂購數量, (order_price*order_count)as訂購金額, press_name as出版社名稱, writer_name as名稱 from book,book_order where book.#bname=book_order.#bname 建立存儲過程 某種圖書庫存查詢存儲過程 create procedure sp_某種圖書庫存查詢 -p1為要查詢的圖書名稱 p1 varchar(30) as begin select訂購數-銷售數量as庫存 from(select sum(or

18、der_count)as訂購數,#bname as書名 from book_order group by #bname)as x,view_同種銷售情況 where x.書名=p1 and view_同種銷售情況.書名=p1 end go 某段時間內的銷售業績查詢存儲過程 建立本月業績查詢存儲過程 由于不知其它信息，此處將銷售金額與營業金額等同 營業金額=營業額=成本+利潤 create procedure sp_查詢某段時間內的業績 -p1為起始時間,p2為結束時間 p1 datetime,p2 datetime as begin select sum(銷售單價)as銷售金額from view_單本銷售情況where銷售時間=p1 and銷售時間=p1 and銷售時間=all(select count(圖書編號) from view_單本銷售情況 where銷售時間=p1 and銷售時間3) 按供應商名稱查詢訂購信息 按供應商名稱查詢訂購信息 select*from view_圖書訂購信息 order by供應商名稱 截圖得： 注：數據檢查得，確實沒有訂購超3次的客戶。 驗證觸發器 -驗證觸發器 insert into customer values(00000005,陳利,女,21) go update customer set c