數(shù)據(jù)庫設(shè)計實例網(wǎng)上書店

1、第七章 數(shù)據(jù)庫設(shè)計 實例數(shù)據(jù)庫設(shè)計實例網(wǎng)上書店1）需求分析2）概念結(jié)構(gòu)設(shè)計3）邏輯結(jié)構(gòu)設(shè)計4）數(shù)據(jù)庫的物理設(shè)計5）數(shù)據(jù)庫實施6）數(shù)據(jù)庫運行與維護網(wǎng)上書店需求分析用戶提出以下要求：1）我希望我的用戶可以瀏覽書目，并在因特網(wǎng)上提交訂單。2）目前，我通過電話獲取訂單，我讓大多數(shù)的公司客戶給我打電話并指出所要書的ISBN和數(shù)量。用戶通常使用信用卡來支付。3）然后我準備一項出貨，其中包括他們所定購的書。如果我沒有足夠數(shù)量的庫存，我會再增加定購所需書目，并推遲出貨直到新訂的書到貨。網(wǎng)上書店需求分析用戶提出以下要求：4）我喜歡一次發(fā)出一個客戶的所有訂單。6）對于每一本書，書目信息包括該書的ISBN，書名，作

2、者，購入價，賣出價，以及該書的出版年份。5）我在網(wǎng)上的書目中包括我所賣的所有的書。7）我的大多數(shù)客戶都是常客，我有他們的姓名和地址的記錄。網(wǎng)上書店需求分析用戶提出以下要求：8）新的顧客必須先打電話給我并建立一個賬號，然后才能使用我的網(wǎng)站。9）在我的網(wǎng)站上，顧客必須首先通過他們唯一的顧客標(biāo)識號來確認身份，然后他們就可以瀏覽書目，并可以在線提交訂單。網(wǎng)上書店概念結(jié)構(gòu)設(shè)計最初概念結(jié)構(gòu)設(shè)計（E-R圖）如下圖所示：書ISBN書名作者現(xiàn)存數(shù)量出版年份價格Cid客戶姓名地址數(shù)量訂單訂貨日期發(fā)貨日期信用卡號網(wǎng)上書店出現(xiàn)的新問題A：如果一個客戶在一天之內(nèi)給同一本書提交兩筆訂單會怎樣？B：第一筆訂單將被處理為生成

3、一個新的“訂單”聯(lián)系，而第二筆訂單將被處理為更新這個聯(lián)系的數(shù)量屬性。A：如果一個客戶在一天之內(nèi)給不同的書提交兩筆訂單呢？B：沒問題，每一個訂單關(guān)系集的實例將這個用戶關(guān)聯(lián)到不同的書上去。A：但是，如果一個客戶在不同的日子里給同一本書提交兩筆訂單會怎樣呢？網(wǎng)上書店出現(xiàn)的新問題B：書和客戶的屬性必須共同為訂單包含一個關(guān)鍵字。因此，這個設(shè)計不允許一個客戶在不同的日期里為同一本書提交多筆訂單。A：你說的有道理，我們暫且以這個設(shè)計為準。這次討論到此為止。網(wǎng)上書店邏輯結(jié)構(gòu)設(shè)計邏輯結(jié)構(gòu)設(shè)計：將E-R圖映射為關(guān)系模型，生成如下表格：Create Table Books ( isbn char(10), titl

4、e char(80), author char(80), qty_in_stock integer, price real, year_published integer, primary key (isbn) )網(wǎng)上書店邏輯結(jié)構(gòu)設(shè)計邏輯結(jié)構(gòu)設(shè)計：將E-R圖映射為關(guān)系模型，生成如下表格：Create Table Orders ( isbn char(10), cid integer, cardnum char(16), qty integer, order_data data, ship_data data, Primary key (isbn, cid), Forgign key (isbn

5、) references books, Forgign key (cid) references customers)網(wǎng)上書店邏輯結(jié)構(gòu)設(shè)計邏輯結(jié)構(gòu)設(shè)計：將E-R圖映射為關(guān)系模型，生成如下表格：Create Table customers ( cid integer, cname char(80), address char（200）， primary key (cid) )網(wǎng)上書店邏輯結(jié)構(gòu)設(shè)計此時，書店主人又提出以下一些要求：1）客戶應(yīng)該能夠在一個訂單中購買若干不同的書。例如：一個客戶想買3本“數(shù)據(jù)庫系統(tǒng)概論”和5本“計算機網(wǎng)絡(luò)”，那么他可以在一筆訂單中購買這兩本書。網(wǎng)上書店邏輯結(jié)構(gòu)設(shè)計此時，

6、書店主人又提出以下一些要求：3）客戶應(yīng)該可以在一天中提交不止一筆訂單，而且他們可以區(qū)分出這些提交的訂單。2）發(fā)貨方法如下：一旦有一種書的存貨足夠，則將其發(fā)貨，即使一個訂單中包含多種書。因此如果書店現(xiàn)在有3本“數(shù)據(jù)庫系統(tǒng)概論”，那么就可以發(fā)貨，而“計算機網(wǎng)絡(luò)”不夠5本，則等到新定購書到貨后再發(fā)貨。網(wǎng)上書店邏輯結(jié)構(gòu)設(shè)計數(shù)據(jù)庫設(shè)計人員對用戶的需求進行了分析，得到如下兩個新的需求：1）客戶可以在一個訂單中定購若干種不同的書；2）一個客戶可以區(qū)分其在一天中提交的若干筆訂單。為了滿足這些新需求，在orders表中引入了一個新的屬性ordernum（訂單號），它唯一的標(biāo)識了一筆訂單，并且客戶據(jù)此提交訂單。但

7、是，因為在一筆訂單中可以購買若干種書，需要ordernum和isbn一起來確定orders表中的qty（定購數(shù)量）和ship_data（發(fā)貨日期）。修改后的orders表如下定義：網(wǎng)上書店邏輯結(jié)構(gòu)設(shè)計修改后的orders表：Create Table Orders ( ordernum integer, isbn char(10), cid integer, cardnum char(16), qty integer, order_data data, ship_data data, Primary key (isbn, cid), Forgign key (isbn) references b

8、ooks, Forgign key (cid) references customers)網(wǎng)上書店邏輯結(jié)構(gòu)設(shè)計此時，書店老板再次提出新的要求：老板希望他的雇員能夠查看客戶訂單的細節(jié)，這樣他們就可以對客戶的查詢進行響應(yīng)了。但是，用戶信用卡信息是保密的，不能被雇員看到。數(shù)據(jù)庫分析設(shè)計員提出如下解決方法：生成一個如下的視圖：定義雇員可以看到的視圖Create view orderinfo (ordernum, isbn, cid, qty, order_data , ship_data) AS select O.ordernum, O.isbn, O.cid, O.qty, O.order_data

9、, O.ship_data) From orders O這樣，老板的雇員只能查看這張視圖（通過授權(quán)），而不是整個Orders 表。網(wǎng)上書店邏輯結(jié)構(gòu)設(shè)計因此得到了網(wǎng)上書店的數(shù)據(jù)庫模式如下：Customers ( cid :integer, cname: char(80), address char(200)Books ( isbn: char(10) , title: char(80), author: char（80），qty_in_stock: integer, price: real, year_published: integer)Orders ( ordernum:integer, i

10、sbn:char(10), cid:integer, cardnum:char(16), qty:integer, order_data:data, ship_data:data)對以上關(guān)系模式進行分析，考慮其規(guī)范化程度，可能出現(xiàn)的冗余、插入刪除異常等。Books關(guān)系模式中只有一個碼isbn，并且所有屬性都是依賴于isbn的，因此它屬于BCNF。customers關(guān)系模式中只有一個碼cid，并且在該表上沒有其他函數(shù)依賴，因此屬于BCNF。關(guān)系模式的規(guī)范化orders關(guān)系模式中，定義（ordernum, isbn）為碼。此外，因為某個訂單由某個客戶在某天用某個信用卡號定下的，所以在orders表

11、中還有以下函數(shù)依賴：ordernumcid ordernum order_data ordernum cardnum因此，orders不是2NF（存在非主屬性對碼的部分函數(shù)依賴。關(guān)系模式的規(guī)范化于是將orders分解為如下2個關(guān)系模式：1） Orders（ordernum:integer, cid:integer, cardnum:char(16), order_data:date）2）Orderlists（ordernum:integer, isbn:char(10) , qty:integer, ship_date:date）訂單實體聯(lián)系這2個關(guān)系模式都是BCNF。對應(yīng)的，給出關(guān)系模式Or

12、ders和 Orderlists的SQL定義語句：Create table orders ( ordernum integer, cid integer, cardnum char(16), order_date date, Primary key ( ordernum), Foreign key(cid) references customers)對應(yīng)的，給出關(guān)系模式Orders和 Orderlists的SQL定義語句：Create table orderlists ( ordernum integer, isbn char(10), qty integer, Ship_date date, Primary key ( ordernum, isbn), Foreign key(isbn) references books)思考：如何確定orderlists上包含哪些屬性？修改后的E-R圖如下：booksISBN書名作者現(xiàn)存數(shù)量出版年份價格Cid客戶姓名地址Place_orderorder_dateordersordernumcardnumqtyOrder_listsShip_date網(wǎng)上書店物理結(jié)構(gòu)設(shè)計對于books表：1）書店主任希望大部分客戶能夠在訂書之前通過ISBN號查詢，因此，為了加速通過ISBN的查詢，可以在ISBN上建立一個非聚簇的哈希索引。2）客戶還有可能通過書名