書店銷售系統(tǒng)數(shù)據(jù)庫分析與設計.doc

東方學院實 驗（實訓）報 告項 目 名 稱 書店銷售系統(tǒng)數(shù)據(jù)庫分析與設計 所屬課程名稱 數(shù)據(jù)庫原理及應用 項 目 類 型 設計、綜合型 實驗(實訓)日期 2014.52014.6 班 級 12信息1班 學 號 1220400103 1220400105 1220400110 1220400122 1220400125 1220400127 1220400139 1220400143 姓 名 李珺茹 鄭章章 徐瑩瑩 巫日崢 馮星 傅媛 徐斌 林良澤 分工表1、需求分析（鄭章章、李珺茹）書店的部門組成、各部門的業(yè)務說明、基本信息管理安全性要求與完整性要求2.概念結構設計（徐斌、林良澤）3邏輯結構設計（徐瑩瑩）E-R圖向關系模型的轉換、數(shù)據(jù)模型的優(yōu)化4.物理結構設計（巫日崢）數(shù)據(jù)庫物理設計原理、相關表的物理結構5.數(shù)據(jù)庫實施（馮星）數(shù)據(jù)庫建立代碼、初始數(shù)據(jù)的輸入、視圖的建立、觸發(fā)器的建立6、數(shù)據(jù)庫的試運行（傅媛）7、數(shù)據(jù)庫的維護目錄一、設計目的及要求21.1、目的21.2、背景21.3概述21.4可行性分析2二、課程設計內容說明31、需求分析31.1書店的部門組成31.2各部門的業(yè)務說明31.3基本信息管理41.4安全性要求與完整性要求52.概念結構設計53邏輯結構設計（）93.1 E-R圖向關系模型的轉換93.2 數(shù)據(jù)模型的優(yōu)化104.物理結構設計104.1數(shù)據(jù)庫物理設計原理104.2 相關表的物理結構115.數(shù)據(jù)庫實施145.1數(shù)據(jù)庫建立代碼145.2初始數(shù)據(jù)的輸入165.3視圖的建立195.4觸發(fā)器的建立206、數(shù)據(jù)庫的試運行227、數(shù)據(jù)庫的維護22三、結論及體會22一、設計目的及要求1.1、目的在科學飛速發(fā)展的今天，人們的視野越來越廣，數(shù)據(jù)量急劇增加。過去人們把數(shù)據(jù)存放在文件柜里，現(xiàn)在人們借助計算機和數(shù)據(jù)庫技術科學地保存和管理大量的復雜數(shù)據(jù)，以便能方便而充分地利用這些寶貴的信息資源。 本系統(tǒng)的設計目標就是要為中小型書店提供一套運行穩(wěn)定、安全可靠、操作簡便的管理系統(tǒng)。幫助書店銷售業(yè)務處理日常工作業(yè)務，規(guī)范管理各項工作和操作流程。從根本上改變以前那種舊的管理模式，真正提高管理和服務的水平。1.2、背景隨著社會信息量的迅速增長，計算機處理的數(shù)據(jù)量不斷增加，在我國，管理信息系統(tǒng)是計算機應用最廣泛的領域之一。隨著我國經(jīng)濟建設突飛猛進，管理科學化與管理手段的現(xiàn)代化已經(jīng)提到非常重要的地位，企、事業(yè)單位為了提高自身的管理水平和競爭能力，紛紛投入人力物力，開發(fā)適合本單位需求的管理信息系統(tǒng)。中國的信息化沿著企業(yè)信息化和政府信息化這兩根鏈條正在飛速的發(fā)展,在全國的各級教育單位中,都在進行管理方式的改革,由以前的人工管理方式在向現(xiàn)在的人工加計算機技術的過渡中,在現(xiàn)在的這種模式中可以很大的提高工作人員的工作效率,提高了正確性。1.3概述這次所選的設計題目是書店銷售管理系統(tǒng)。數(shù)據(jù)庫環(huán)境選擇SQL server 2005中文版。1.4可行性分析管理上可行性書店管理其實是不能說是簡單，但是很瑣碎。每天的銷售還有最后的書本數(shù)量的核對會耗費大量的人力和時間。而進行計算機信息技術上的自動化管理不僅提高了管理的準確性而且省時省力。經(jīng)濟上可行性只要這個書店管理系統(tǒng)就好，不需要額外的支出。技術上可行性在大學我們已經(jīng)學習了有關數(shù)據(jù)庫，c語言等一系列計算機技術，在相對寬裕的時間內是可以編寫出程序的。二、課程設計內容說明1、需求分析1.1書店的部門組成對于書店來說，目的是銷售圖書，所以包含銷售部門；同時為書店進書的部門稱為進貨部門。1.2各部門的業(yè)務說明1.2.1銷售部門對圖書銷售情況進行結算，對客戶資源進行管理。如，對某一段時間的圖書銷售情況進行統(tǒng)計；對訂書的顧客信息進行記錄，統(tǒng)計等。因此，需要銷售人員，圖書，購書實體：顧客，以及聯(lián)系：銷售，購買。1.2.2進貨部門記錄圖書供應商的信息，以便獲得最新的圖書進貨信息。通過供應商，為書店購進新的圖書。需要實體供貨商，聯(lián)系：進貨。 1.3基本信息管理1.3.1該模塊實現(xiàn)圖書情況的分類，包括書號，書名，作者，售價等。便利店員查找書籍，為顧客提供更好的建議。1.3.2銷售情況管理該模塊對每本出售的書本進行統(tǒng)計，記錄每本書售出的日期，購買人，經(jīng)辦人和銷售的金額。在這里店員可以很方便的加入銷售記錄，修改記錄。便于每天書店清賬。而且在這里總金額的計算公式是事先設定好的，這樣避免了人為的計算錯誤。1.3.3銷售、進貨、書本信息管理銷售、進貨和書本庫存三者結合起來管理整個書店的圖書數(shù)量。庫存實現(xiàn)對書店剩余書本的管理，知道什么書還剩幾本。而進貨清楚讓人知道進了什么書，售價是什么還有哪個供應商提供的。而且在這里總金額的計算公式是事先設定好的，這樣避免了人為的計算錯誤。1.3.4書店會員管理 實現(xiàn)系統(tǒng)相關會員信息的注冊及身份，同時也提供對應會員的資料更新。該模塊還可以收集會員相關的消費額度，提供不同的折扣力度。1.3.5書店職員管理實現(xiàn)書店職員的相關的管理，收集每個員工的相關信息，更好的了解員工還有實現(xiàn)不同職位不同的工資管理。1.4安全性要求與完整性要求安全性要求：系統(tǒng)應設置訪問用戶都預先約定好一個計算過程或者函數(shù)，鑒別用戶身份時，系統(tǒng)提供一個隨機數(shù)，用戶根據(jù)自己預先約定的計算過程或者函數(shù)進行計算，系統(tǒng)根據(jù)用戶計算結果是否正確進一步鑒別用戶身份；系統(tǒng)應對不同的訪問級別，限制訪問用戶可查詢和處理數(shù)據(jù)的類別和內容；系統(tǒng)應對不同用戶設置不同的權限，區(qū)分不同的用戶，如區(qū)分會員，職員。完整性要求：各種信息記錄的完整性，信息記錄內容不為空，主碼值唯一；列值非空，列值唯一，列值滿足一個布爾表達式；用戶的操作違背了完整性約束條件時，采取一定的動作，如拒絕執(zhí)行該操作；各種數(shù)據(jù)間相互聯(lián)系的正確性；相同的數(shù)據(jù)在不同的記錄中的一致性。2.概念結構設計部分E-R圖圖書基本信息類別出版日期出版社作者書名書號價格庫存數(shù)量圖2.1 書本基本信息E-R圖供應商信息地址電話名稱聯(lián)系人代號圖2.2供應商信息E-R圖職工信息職位住址電話姓名工號ID性別年齡圖2.3 職工信息E-R圖會員信息入會時間累計消費電話姓名會員號折扣性別年齡圖2.4會員信息E-R圖時間會員書價書號工號折扣銷售銷售單號數(shù)量總價圖2.5銷售信息E-R圖供貨商代號書號書名時間工號書價進貨數(shù)量進貨單號總價圖2.6進貨信息E-R圖 nnn11111n m1圖書顧客會員職員供應商購買注冊銷售供應進貨1n銷售記錄mn總體E-R圖3邏輯結構設計 3.1 E-R圖向關系模型的轉換邏輯設計的目的是把概念設計階段設計的全局E-R模式轉換層與選用的具體機器上DBMS鎖支持的數(shù)據(jù)模型相符合的邏輯結構。邏輯設計的步驟主要步驟有以下五步：（1） 初始模式的形成（2） 自模式設計（3） 應用程序設計梗概（4） 模式評價（5） 修正模式E-R圖向關系模型轉換，首先要進行數(shù)據(jù)的規(guī)范化。所謂規(guī)范化是指在一個數(shù)據(jù)結構中沒有重復出現(xiàn)的項組。規(guī)范化是在關系數(shù)據(jù)庫中減少數(shù)據(jù)冗余的過程。3.1.1實體書本（書號，書名，作者，價格，出版社，出版日期，類別，庫存數(shù)量）屬于BCNF供應商（供應商代號，供應商名字，聯(lián)系人，電話，地址） 屬于BCNF員工（工號，ID，姓名，電話，住址，職位，性別，年齡） 屬于BCNF會員（會員號，姓名，年齡，電話，累積消費，折扣力度，入會時間，性別） 屬于BCNF3.1.2關系進貨（書號，時間，供應商代號，工號，書名，進貨單號，金額，數(shù)量，總價）屬于BCNF銷售（書號，銷售單號，書價，購買者，工號，折扣，時間，數(shù)量） 屬于3NF3.2 數(shù)據(jù)模型的優(yōu)化 關系模式會員號，姓名，書名，供應商號，職工號不存在非主屬性對主屬性的部分函數(shù)依賴，也不存在傳遞函數(shù)依賴，已經(jīng)達到了3NF，但是銷售關系模式、進貨關系模式、庫存信息關系模式中存在著一些數(shù)據(jù)冗余，現(xiàn)將四個關系模型進行合并，消除冗余，優(yōu)化為：進貨（書號，時間，供應商代號，工號，書名，進貨單號，金額，數(shù)量）4.物理結構設計4.1數(shù)據(jù)庫物理設計原理數(shù)據(jù)庫物理設計階段的任務是根據(jù)具體計算機系統(tǒng)(DBMS和硬件等)的特點，為給定的數(shù)據(jù)庫系統(tǒng)確定合理的存儲結構和存取方法。所謂的“合理”主要有兩個含義：一個是要使設計出的物理數(shù)據(jù)庫占用較少的存儲空間，另一個對數(shù)據(jù)庫的操作具有盡可能高的速度。主要體現(xiàn)在后者。4.1.1建立索引：根據(jù)本系統(tǒng)的特點，采用B+樹索引存取方法建立索引對圖書，會員，職員，供應商，進貨，銷售和庫存建立索引對圖書，會員，職員，銷售建立組合索引對進貨，銷售，圖書建立組合索引對圖書，供應商，進貨建立組合索引對其進行的操作：查找查找以典型的方式進行，類似于二叉查找樹。起始于根節(jié)點，自頂向下遍歷樹，選擇其分離值在要查找值的任意一邊的子指針。在節(jié)點內部典型的使用二分查找來確定這個位置。插入節(jié)點要處于違規(guī)狀態(tài)，它必須包含在可接受范圍之外數(shù)目的元素。 首先，查找要插入其中的節(jié)點的位置。接著把值插入這個節(jié)點中。 如果沒有節(jié)點處于違規(guī)狀態(tài)則處理結束。 如果某個節(jié)點有過多元素，則把它分裂為兩個節(jié)點，每個都有最小數(shù)目的元素。在樹上遞歸向上繼續(xù)這個處理直到到達根節(jié)點，如果根節(jié)點被分裂，則建立一個新根節(jié)點。為了使它工作，元素的最小和最大數(shù)目典型的必須選擇為使最小數(shù)不大于最大數(shù)的一半。 刪除首先，查找要刪除的值。接著從包含它的節(jié)點中刪除這個值。 如果沒有節(jié)點處于違規(guī)狀態(tài)則處理結束。 如果節(jié)點處于違規(guī)狀態(tài)則有兩種可能情況：它的兄弟節(jié)點，就是同一個父節(jié)點的子節(jié)點，可以把一個或多個它的子節(jié)點轉移到當前節(jié)點，而把它返回為合法狀態(tài)。如果是這樣，在更改父節(jié)點和兩個兄弟節(jié)點的分離值之后處理結束。 它的兄弟節(jié)點由于處在低邊界上而沒有額外的子節(jié)點。在這種情況下把兩個兄弟節(jié)點合并到一個單一的節(jié)點中，而且我們遞歸到父節(jié)點上，因為它被刪除了一個子節(jié)點。持續(xù)這個處理直到當前節(jié)點是合法狀態(tài)或者到達根節(jié)點，在其上根節(jié)點的子節(jié)點被合并而且合并后的節(jié)點成為新的根節(jié)點。4.1.2存儲結構確定數(shù)據(jù)庫的存儲結構主要指確定數(shù)據(jù)的存放位置和存儲結構，包括確定關系、索引、日志、備份等的存儲安排及存儲結構，以及確定系統(tǒng)存儲參數(shù)的配置。將日志文件和數(shù)據(jù)庫對象(表、索引等)分別放在不同的磁盤，可以改進系統(tǒng)的性能，提高系統(tǒng)的安全性。所以，系統(tǒng)應將日志文件和數(shù)據(jù)文件存放在不同磁盤上。4.2 相關表的物理結構字段名稱數(shù)據(jù)類型主鍵或外鍵是否允許為空備注書號char(5)主鍵，外鍵不允許書名varchar(30)不允許作者char(10)允許價格decimal(5,2)不允許出版日期smalldatetime允許出版社varchar(6)不允許庫存數(shù)量int 不允許數(shù)量=0類別char(5)不允許表4.1書本物理結構字段名稱數(shù)據(jù)類型主鍵或外鍵是否允許為空備注供應商代號varchar(6)主鍵不允許供應商名稱varchar(20)不允許聯(lián)系人char(10)不允許電話varchar(13)不允許地址varchar(30)允許表4.2供應商物理結構字段名字數(shù)據(jù)類型主鍵或外鍵是否允許為空備注工號varchar(6)主鍵不允許姓名char(10)不允許性別varchar(2)不允許男/女年齡varchar(12)允許職位char(5)不允許電話char(13)不允許IDvarchar(18)不允許住址varchar(30)允許表4.3職員物理結構字段名字數(shù)據(jù)類型主鍵或外鍵是否允許為空備注會員號varchar(6)主鍵不允許姓名Char(10)不允許性別varchar(12)不允許男/女年齡varchar(12)允許入會時間datetime不允許折扣char(5)不允許0.90/0.80/0.70累計消費decimal(5,2)允許電話varchar(12)不允許表4.4會員物理結構字段名字數(shù)據(jù)類型主鍵或外鍵是否允許為空備注銷售單號char(6)主鍵不允許書號char(5)外鍵不允許會員號varchar(6)外鍵允許折扣decimal(5,2)不允許0/0.90/0.80/0.70書價decimal(5,2)不允許時間datetime不允許數(shù)量int 不允許數(shù)量=1工號varchar(6)外鍵不允許總價decimal(5,2)不允許折扣*數(shù)量*書價表4.5銷售物理結構字段名字數(shù)據(jù)類型主鍵或外鍵是否允許為空備注進貨單號char(6)主鍵不允許供應商代號varchar(6)外鍵不允許書號char(5)外鍵不允許書名varchar(30)不允許書價decimal(5,2)不允許時間smalldatetime不允許數(shù)量int 不允許數(shù)量=1總價decimal(5,2)不允許書價*數(shù)量工號varchar(6)外鍵不允許表4.6進貨物理結構5.數(shù)據(jù)庫實施5.1數(shù)據(jù)庫建立代碼5.1.1數(shù)據(jù)庫的創(chuàng)建（sql語句代碼）create database bookstroeuse bookstroe5.1.2表的創(chuàng)建書本信息關系表的創(chuàng)建create table 書本(書號 char(5) not null primary key,書名 varchar(30) not null ,作者 char(10) , 價格 decimal(5,2) ,出版社 varchar(6) not null ,出版日期 smalldatetime,類別 char(5) not null,庫存數(shù)量 int check(庫存數(shù)量=0),)供應商信息關系表的創(chuàng)建create table 供應商(供應商代號varchar(6) not null primary key,供應商名字varchar(20) not null, 聯(lián)系人char(10) not null,電話varchar(13) not null, 地址varchar(30),)員工信息關系表的創(chuàng)建create table 員工(工號 varchar(6) not null primary key,姓名 Char(10) not null,性別 varchar(2) not null check(性別=男 or 性別=女), 年齡 varchar(12) ,職位 char(5) not null,電話 varchar(13) not null ,ID varchar(18) not null,住址 varchar(30) ,)會員信息關系表的創(chuàng)建create table 會員(會員號 varchar(6) primary key not null,姓名 char(10) not null,性別 varchar(2) not null check(性別=男 or 性別=女),年齡varchar(12),入會時間 datetime not null, 折扣 char(5) not null check(折扣in (0.90,0.80,0.70), 累計消費 decimal(5,2),電話 varchar(12) not null,)銷售信息關系表的創(chuàng)建create table 銷售( 銷售單號char(6) not null primary key , 書號char(5) not null ,會員號varchar(6),折扣decimal(5,2) not null check(折扣in (1,0.90,0.80,0.70),書價decimal(5,2) not null, 工號varchar(6) not null , 時間 datetime not null, 銷售數(shù)量int check (銷售數(shù)量=1), 銷售總價 as 折扣*銷售數(shù)量*書價,foreign key(會員號) references 會員(會員號),foreign key(書號) references 書本(書號), foreign key(工號) references 員工(工號),)進貨信息關系表的創(chuàng)建create table 進貨( 進貨單號 char(6) not null primary key,供應商代號 varchar(6) not null,書號 char(5) not null,書名 varchar(30) not null,書價 decimal(5,2) not null,時間 smalldatetime not null,進貨數(shù)量 int check (進貨數(shù)量=1), 工號 varchar(6) not null,總價 as 書價*進貨數(shù)量,foreign key(書號) references 書本(書號),foreign key(供應商代號) references 供應商(供應商代號),foreign key(工號) references 員工(工號),)5.1.3各個表的關系圖圖5.1.3數(shù)據(jù)庫關系圖5.2初始數(shù)據(jù)的輸入數(shù)據(jù)庫以及表創(chuàng)建之后，要輸入一些實際的數(shù)據(jù)。一下是一些數(shù)據(jù)輸入的代碼。l 向供應商表輸入數(shù)據(jù)insert into 供應商values(01,a1,張三,上海,12345)insert into 供應商values(02,a2,李四,杭州,54321)5.2.1輸入數(shù)據(jù)后的供應商表l 向書本信息表輸入數(shù)據(jù)insert into 書本values(11021,光榮日,韓寒,20.00,01,2001.06.13,小說)insert into 書本values(11022,一個人的好天氣,青山七惠,18.00,01,2001.07.15,小說)5.2.2輸入數(shù)據(jù)后的書本信息表l 向員工信息表輸入數(shù)據(jù)insert into 員工 values(001,錢小五,女,25,店員,88733227,310000000000000000,杭州)insert into 員工values(003,王小二,男,21,店長,88733221,310000000000000021,杭州)5.2.3輸入數(shù)據(jù)后的員工信息表l 向會員信息表輸入數(shù)據(jù)insert into 會員values(no43,王菲,女,19,2010.02.03,0.90,103.3,88733226)insert into 會員values(no98,劉朵朵,女,20,2011.12.03,0.80,403.3,88733228)5.2.4輸入數(shù)據(jù)后的會員信息表l 向進貨關系表輸入數(shù)據(jù)insert into 進貨values(b1,01,11021,光榮日,20.00,2011.12.12,50,001)insert into 進貨values(b2,02,11022,一個人的好天氣,18.00,2011.12.13,40,001)5.2.5輸入數(shù)據(jù)后的進貨關系表l 向銷售關系表輸入數(shù)據(jù)insert into 銷售values(s1, 11021,no98,0.80,20.0,003, 2013.01.12 ,2 )insert into 銷售values(s2 ,11022,no43,0.90,18.0,003, 2013.01.12,3)4.2.7輸入數(shù)據(jù)后的銷售關系表5.3視圖的建立1.這個視圖是總結了銷售情況，當銷售輸入數(shù)據(jù)時視圖會把賣出的書的數(shù)量加起來，那么要看某天的某本書的銷售量就很方便了。代碼：create view 銷售情況as select 時間, 書號,sum(銷售數(shù)量) 銷售數(shù)量,sum(銷售總價) 銷售總價from 銷售 group by 書號,時間5.3.1銷售情況視圖2在銷售情況視圖的基礎上建立了書本數(shù)量的視圖，當銷售輸入數(shù)據(jù)時視圖里的銷售數(shù)量會動態(tài)的改變。代碼：create view 書本數(shù)量as select 書本.書名,進貨.進貨數(shù)量,銷售.銷售數(shù)量,書本.庫存數(shù)量from 書本,進貨,銷售where 書本.書號=進貨.書號and 進貨.書號=銷售.書號5.3.2書本數(shù)量視圖5.4觸發(fā)器的建立1輸入的員工年齡不可以小于16歲create trigger tr_員工_insert on 員工for insert asdeclare 年齡 varchar(12) select 年齡=年齡from insertedif 年齡16beginraiserror(員工年齡應該大于,16,1)rollback transactionend5.3.1輸入數(shù)據(jù)觸發(fā)觸發(fā)器后2當向進貨信息中輸入新的書本信息，那么在庫存中自動加入該書的書號還