數(shù)據(jù)庫設(shè)計-網(wǎng)上書店ppt課件.ppt

數(shù)據(jù)庫設(shè)計網(wǎng)上書店,一、需求分析二、系統(tǒng)功能與流程設(shè)計三、數(shù)據(jù)庫概念結(jié)構(gòu)設(shè)計四、數(shù)據(jù)庫邏輯結(jié)構(gòu)設(shè)計五、數(shù)據(jù)庫物理結(jié)構(gòu)設(shè)計六、提高數(shù)據(jù)庫效率的設(shè)計七、數(shù)據(jù)庫的實施,1,一、需求分析,1.能通過互聯(lián)網(wǎng)(Internet)訪問網(wǎng)上書店B/S結(jié)構(gòu)選擇合適的開發(fā)工具2.能在網(wǎng)頁中瀏覽圖書目錄，可按圖書類別分類瀏覽。(1)圖書應(yīng)當分類，按何種標準分類?分為幾類?是否還有其他分類標準?又如何分類?(2)哪些信息構(gòu)成圖書目錄?(3)是否需要圖書搜索功能?能按哪些關(guān)鍵詞搜索?圖書名稱、圖書號、出版社、單價,2,一、需求分析,3.可瀏覽所選圖書的詳細信息。具有包括哪些信息?ISBN，書名，作者，出版社，出版日期，簡介，封面，庫存數(shù)量，單價4.瀏覽圖書時可訂購圖書，生成并提交訂單(1)在瀏覽的哪些環(huán)節(jié)可以訂購?瀏覽圖書目錄、詳細信息時(2)一個“訂單”應(yīng)包含哪些信息?訂單號，書號，顧客號，數(shù)量，金額，訂購日期訂單管理：創(chuàng)建、增加、修改、刪除、確認、結(jié)算,3,一、需求分析,5.根據(jù)訂單和支付信息發(fā)出圖書，形成發(fā)貨信息（“支付信息”暫不考慮）“發(fā)貨信息”應(yīng)當包括哪些信息?是否需要獨立的“發(fā)貨單”?“發(fā)貨信息”如何與“訂單信息”聯(lián)動?,4,一、需求分析,6.顧客收到圖書之后，對收貨予以確認，形成收貨信息“收貨信息”應(yīng)當包括哪些信息?是否需要獨立的“收貨單”?“收貨信息”如何與“發(fā)貨信息”聯(lián)動?,5,一、需求分析,7.顧客必須注冊后才能訂購圖書(1)顧客登錄用的信息注冊賬號，密碼(2)顧客自身的基本信息姓名，性別，年齡，地址，郵編，電話，郵箱8.應(yīng)當提供管理員對圖書、顧客、訂單、發(fā)貨、收貨等信息進行定期維護?！肮芾韱T”信息：管理員ID、姓名、類型“管理員”分類：系統(tǒng)預設(shè)的管理員、自定義的管理員,6,二、系統(tǒng)功能與流程設(shè)計,1.系統(tǒng)功能,網(wǎng)上書店,前臺瀏覽模塊,后臺管理模塊,匿名用戶,注冊用戶,瀏覽/查找圖書,用戶注冊,瀏覽/查找圖書,訂購圖書/維護訂單,支付貨款,用戶資料維護,(管理員),圖書管理,用戶管理,訂單管理,思考：初步設(shè)計網(wǎng)站首頁布局?,7,二、系統(tǒng)功能與流程設(shè)計,2.操作流程總體流程,網(wǎng)上書店首頁,網(wǎng)上書店概況,瀏覽/搜索圖書,訂購圖書,用戶登錄,用戶注冊,導航,返回首頁,8,二、系統(tǒng)功能與流程設(shè)計,3.操作流程購書流程,網(wǎng)上書店首頁,瀏覽圖書,訂購圖書,用戶管理,登錄,收貨確認,支付,發(fā)貨,9,三、數(shù)據(jù)庫概念結(jié)構(gòu)設(shè)計(E-R圖),主要E-R圖,管理員,圖書,客戶,管理,管理,訂購,賬號,密碼,書號,書名,作者,單價,數(shù)量,日期,賬號,密碼,數(shù)量,姓名,地址,電話,1,n,n,1,n,m,類別,姓名,10,四、數(shù)據(jù)庫邏輯結(jié)構(gòu)設(shè)計(關(guān)系),E-R圖轉(zhuǎn)化為關(guān)系模式的方法注：“關(guān)系模式”是指一個未確定各個屬性的數(shù)據(jù)類型、長度等參數(shù)的二維表，稱為一個“關(guān)系”。例：管理員(賬號，姓名，密碼)(1)每一個實體對應(yīng)一個關(guān)系(2)一對多的聯(lián)系當“聯(lián)系”并無獨有的“屬性”時，通常不單獨轉(zhuǎn)換為一個關(guān)系模式，而是將“一方”的主碼增加到“多方”關(guān)系模式中，構(gòu)成其外碼。例如：客戶(賬號，密碼，姓名，管理員ID),11,四、數(shù)據(jù)庫邏輯結(jié)構(gòu)設(shè)計(關(guān)系),(3)多對多的聯(lián)系：轉(zhuǎn)換為一個關(guān)系模式關(guān)系的屬性：n方的主鍵、m方的主鍵、“聯(lián)系”的屬性關(guān)系的主鍵：組合(n方的主鍵、m方的主鍵)例如：訂單(客戶號，書號，訂購數(shù)量，訂購日期)(4)三個以上的多元聯(lián)系：轉(zhuǎn)換為一個關(guān)系模式類似于多對多聯(lián)系的轉(zhuǎn)換方法例如：,供應(yīng)(供應(yīng)商號,項目號,零件號,數(shù)量),12,五、數(shù)據(jù)庫物理結(jié)構(gòu)設(shè)計(存儲結(jié)構(gòu)),包括：(一)物理結(jié)構(gòu)是指含有字段類型、字段長度等參數(shù)的二維表結(jié)構(gòu)(包括主鍵、外鍵)(二)字段約束主鍵(primarykey)、外鍵(foreignkey)約束空/非空約束(null,notnull)默認值(default)約束唯一(unique)約束自動增長(identify)約束檢查(CHECK)約束,13,五、數(shù)據(jù)庫物理結(jié)構(gòu)設(shè)計(存儲結(jié)構(gòu)),(一)表結(jié)構(gòu)定義1.管理員表(admins)2.顧客表(customers)3.圖書表(books)4.圖書分類表(booktypes)5.訂單表(orders),寫出各個關(guān)系表的結(jié)構(gòu)定義,14,五、數(shù)據(jù)庫物理結(jié)構(gòu)設(shè)計(存儲結(jié)構(gòu)),(二)表的約束1.SQLServer約束的分類(1)實體完整性約束主要通過“主鍵約束”來實現(xiàn)，以確保表內(nèi)任意行記錄數(shù)據(jù)是可區(qū)分的，是不相同的。主鍵約束(PrimaryKey):不重復、不為空(2)引用完整性約束主要是通過“外鍵約束”來實現(xiàn)，即將一個表的主鍵添加到另一個表中，稱為外鍵(ForeignKey)，使兩個表聯(lián)合操作時能準確識別表內(nèi)各條記錄。外鍵(ForeignKey):,15,五、數(shù)據(jù)庫物理結(jié)構(gòu)設(shè)計(存儲結(jié)構(gòu)),(2)域完整性約束:給定列的輸入正確性和有效性。A.唯一性約束(UNIQUE):要求某列任意兩行的值不能相同B.標識約束(IDENTIFY):能自動產(chǎn)生唯一的標識值，一般用于主鍵。C.非空約束：NotNullD.默認值約束:Default，為字段規(guī)定默認值E.檢查約束：CHECK，為字段的值規(guī)定檢查機制，如范圍F.觸發(fā)器、存儲過程中定義的約束機制說明：除觸發(fā)器、存儲過程外，有些約束在定義字段時給出，當某個字段具有多個約束，可緊跟其后，排列不分次序。有些也可在表結(jié)構(gòu)定義的末尾單獨給出約束定義。,16,五、數(shù)據(jù)庫物理結(jié)構(gòu)設(shè)計(存儲結(jié)構(gòu)),2.常用約束的書寫方法(1)主鍵約束用法1：獨立書寫constraintprimarykey(字段)用法2：緊跟字段定義語句字段名類型constraintprimarykey(2)外鍵約束用法1：獨立書寫constraintforeignkey(字段)references表名(字段名)用法2：類似于primarykey.,17,五、數(shù)據(jù)庫物理結(jié)構(gòu)設(shè)計(存儲結(jié)構(gòu)),(3)唯一性約束(Unique)置于字段之后，用法：Unique主鍵約束自動擁有唯一性；一個表只能有一個主鍵約束，但可以有多個唯一性約束。(4)空或非空約束置于字段定義之后，用法：Null，NotNull(5)唯一標識值約束(Identify)置于字段定義之后，用法：Identify(初值,步長)(6)默認值約束(Default)置于字段定義之后，用法：Default常量/函數(shù)例如：bqintnulldefault1-字段bq:購買數(shù)量orderdatedatedefaultGetDate(),18,五、數(shù)據(jù)庫物理結(jié)構(gòu)設(shè)計(存儲結(jié)構(gòu)),(7)檢查約束(check)用法:check(表達式)例：ID_Pintnotnullcheck(ID_P0)ageintcheck(age=10andage=20),19,六、提高數(shù)據(jù)庫效率的設(shè)計,包括：(一)表的索引(二)表的視圖(三)存儲過程(四)觸發(fā)器,20,六、提高數(shù)據(jù)庫效率的設(shè)計,(一)表的索引1.索引的分類(1)唯一索引以原表為基礎(chǔ)，按給定字段創(chuàng)建任意兩行值不重復的索引。說明：創(chuàng)建了唯一約束，將自動創(chuàng)建唯一索引。盡管唯一索引有助于提高查找信息的速度，但為了獲得最佳性能，建議使用主鍵約束或唯一約束。(2)主鍵索引為表定義一個主鍵將自動創(chuàng)建主鍵索引，主鍵索引是唯一索引的特殊類型。,21,六、提高數(shù)據(jù)庫效率的設(shè)計,(3)聚集索引在聚集索引中，表中各行的物理順序與鍵值的邏輯（索引）順序相同。表只能包含一個聚集索引。例如：漢語字（詞）典默認按拼音排序編排字典中的每頁頁碼。拼音字母a，b，c，dx，y，z就是索引的邏輯順序，而頁碼1，2，3就是物理順序。默認按拼音排序的字典，其索引順序和邏輯順序是一致的。,22,六、提高數(shù)據(jù)庫效率的設(shè)計,(4)非聚集索引對非聚集索引，表中各行的物理順序與鍵值的邏輯順序不匹配。聚集索引比非聚集索引（nonclusteredindex）有更快的數(shù)據(jù)訪問速度。例如，按筆畫排序的索引就是非聚集索引，“1”畫的字（詞）對應(yīng)的頁碼可能比“3”畫的字（詞）對應(yīng)的頁碼大（靠后）。說明：SQLServer中，一個表只能創(chuàng)建1個聚集索引，多個非聚集索引。設(shè)置某列為主鍵，該列就默認為聚集索引。,23,六、提高數(shù)據(jù)庫效率的設(shè)計,2.索引的創(chuàng)建方法CREATEUNIQUECLUSTERED|NONCLUSTEREDINDEX索引名ON表名(字段名)WITHFILLFACTOR=n說明：UNIQUE表示唯一索引，可選CLUSTERED、NONCLUSTERED表示聚集索引還是非聚集索引，可選FILLFACTOR表示填充因子，指定一個0到100之間的值，該值指示索引頁填滿的空間所占的百分比,24,六、提高數(shù)據(jù)庫效率的設(shè)計,3.使用索引的幾條原則(1)可以創(chuàng)建索引的情形該列用于頻繁搜索該列用于對數(shù)據(jù)進行排序(2)不要創(chuàng)建索引的情形列中僅包含幾個不同的值。表中僅包含幾行。為小型表創(chuàng)建索引可能不太劃算，因為SQLServer在索引中搜索數(shù)據(jù)所花的時間比在表中逐行搜索所花的時間更長,25,六、提高數(shù)據(jù)庫效率的設(shè)計,(二)視圖的使用1.什么是視圖？視圖是一種數(shù)據(jù)庫對象，為用戶提供了一種查詢數(shù)據(jù)表中數(shù)據(jù)的方式。視圖的定義是由select語句構(gòu)成，并存儲在數(shù)據(jù)庫中。引用視圖實質(zhì)上是引用select語句的查詢結(jié)果。視圖是虛擬表，具有數(shù)據(jù)表的一些特性，可以對視圖進行查詢、修改、刪除等操作，還能成為另一個視圖所引用的表。對視圖的數(shù)據(jù)進行修改時，基表的數(shù)據(jù)也會發(fā)生變化，反之亦然。,26,六、提高數(shù)據(jù)庫效率的設(shè)計,2.使用視圖的優(yōu)點(1)簡化查詢語句可以把經(jīng)常使用的聯(lián)接、投影和查詢語句定義為視圖，當查詢時，只需簡單地查詢視圖而隱藏對基表的復雜查詢操作。(2)增加可讀性視圖可以定制數(shù)據(jù)內(nèi)容、可以使用字段別名。(3)保證數(shù)據(jù)邏輯獨立性當數(shù)據(jù)表結(jié)構(gòu)改變時，只需更改視圖定義的查詢語句，不需更改應(yīng)用程序代碼，保證了數(shù)據(jù)的邏輯獨立性。(4)增加數(shù)據(jù)的安全性和保密性針對不同用戶可以創(chuàng)建不同視圖，真正的數(shù)據(jù)表是不可見、不可訪問的。,27,六、提高數(shù)據(jù)庫效率的設(shè)計,3.視圖的創(chuàng)建CreateView數(shù)據(jù)庫名.視圖名(列名1,列名2,)WITHENCRYPTIONASSELECT語句WITHCHECKOPTION參數(shù)說明“列名1,列名2,”:是視圖的列名，若省略則取數(shù)據(jù)表的列名“withencryption”:加密視圖“withcheckoption”：強制對視圖執(zhí)行的所有數(shù)據(jù)修改語句都必須符合條件。,28,六、提高數(shù)據(jù)庫效率的設(shè)計,例：創(chuàng)建網(wǎng)上圖書的簡要信息視圖簡要信息：書名、作者、ISBN、出版社、出版日期創(chuàng)建視圖CreateviewbSimpInfo(書名,作者,ISBN,出版社,出版日期)ASselectbName,bAuthor,bISBN,bPub,bDatefrombooks引用視圖在視圖bSimpInfo中查詢書名含有“數(shù)據(jù)庫”的圖書select*frombSimpInfowhere書名like%數(shù)據(jù)庫%,29,六、提高數(shù)據(jù)庫效率的設(shè)計,4.視圖的管理(1)修改視圖定義:Alterview(2)更改視圖名:利用SQLServer提供的系統(tǒng)存儲過程Execsp_rename,(3)刪除視圖Dropview(4)對視圖插入、更新、刪除記錄數(shù)據(jù)(insert,update,delete)基表數(shù)據(jù)會立即改變，仍然要滿足基表對數(shù)據(jù)的操作要求.,30,六、提高數(shù)據(jù)庫效率的設(shè)計,(三)存儲過程1.存儲過程的概念和分類存儲過程是由一系列對數(shù)據(jù)庫進行復雜操作的SQL語句、流程控制語句和函數(shù)組成的。經(jīng)過編譯后，可以象系統(tǒng)類函數(shù)一樣作為一個獨立的數(shù)據(jù)庫對象進行管理，提供給應(yīng)用程序調(diào)用。存儲過程具有參數(shù)傳遞、判斷和聲明變量、返回信息、擴充標準SQL語言、可以嵌套調(diào)用等特點。存儲過程可以分為系統(tǒng)存儲過程和用戶自定義存儲過程系統(tǒng)存儲過程：由系統(tǒng)提供，可直接使用用戶自定義存儲過程：由用戶編寫，需先定義后使用,31,六、提高數(shù)據(jù)庫效率的設(shè)計,2.使用存儲過程的優(yōu)缺點優(yōu)點：執(zhí)行速度快(創(chuàng)建時經(jīng)過了語法檢查和編譯)有利于模塊化程序設(shè)計便于應(yīng)用程序的維護管理減少網(wǎng)絡(luò)通信量(在SQLServer服務(wù)器范圍內(nèi)執(zhí)行)保證系統(tǒng)的安全性缺點不能實現(xiàn)復雜的邏輯操作(SQL語言所限制)并不能實現(xiàn)全部或某些特殊的功能不宜太多，不然難于記憶和使用。,32,六、提高數(shù)據(jù)庫效率的設(shè)計,3.創(chuàng)建存儲過程Createprocedure形參變量數(shù)據(jù)類型=默認值outputwithrecompile|encryptionASselect語句“過程名”：最多128個字符，必須唯一“形參變量”：必須以開頭，最多1024個參量“默認值”：只能是常量或空值，不能是表名/列名等“output”：規(guī)定該變量是用以將執(zhí)行結(jié)果返回的Recomplie:重編譯,encryption:加密,33,六、提高數(shù)據(jù)庫效率的設(shè)計,例：創(chuàng)建存儲過程pr_sb，使其按書名查找該書的作者,出版社,出版日期。Createprocedurepr_sb(bnmvarchar(50),bauvarchar(20)output,bpbvarchar(50)output,bdtdateoutput)ASselectbau=bName,bpb=bPub,bdt=bDatefrombookswherebName=bnm,34,六、提高數(shù)據(jù)庫效率的設(shè)計,4.存儲過程的執(zhí)行EXECUTE例：Usebooksexecutepr_sb數(shù)據(jù)庫技術(shù)說明：執(zhí)行存儲過程前應(yīng)當先打開某個數(shù)據(jù)表,35,六、提高數(shù)據(jù)庫效率的設(shè)計,5.存儲過程的管理(1)修改存儲過程：alterprocedure，用法同創(chuàng)建(2)存儲過程重命名：executesp_rename,(3)刪除存儲過程：dropprocedure,36,六、提高數(shù)據(jù)庫效率的設(shè)計,(四)觸發(fā)器1.觸發(fā)器的概念與特點它是一段能自動執(zhí)行的程序，是特殊的存儲過程。其特殊性在于：不允許使用參數(shù)，沒有返回值。不允許用戶調(diào)用，當對表進行插入、刪除、修改等操作時由系統(tǒng)自動調(diào)用并執(zhí)行。,37,六、提高數(shù)據(jù)庫效率的設(shè)計,2.觸發(fā)器的主要用途：實現(xiàn)更復雜的完整性約束(1)實現(xiàn)數(shù)據(jù)庫中多個表的級聯(lián)修改當修改刪除某個表的數(shù)據(jù)時，其他表的相應(yīng)數(shù)據(jù)能自動修改外鍵約束也具有這種作用，且效率更高。(2)擴展約束、默認對象和規(guī)則對象的完整性檢查例如“check約束”在限制數(shù)據(jù)輸入時不能參照其他表中的數(shù)據(jù)，如在計算“銷售金額=數(shù)量*單價”時，“數(shù)量”不應(yīng)超過“庫存數(shù)量”，此時用check約束不能實現(xiàn)這種約束。(3)自動生成數(shù)據(jù)(4)自定義復雜的安全權(quán)限。,38,六、提高數(shù)據(jù)庫效率的設(shè)計,3.觸發(fā)器的觸發(fā)方式為數(shù)據(jù)表的某個字段設(shè)置觸發(fā)器后，當該字段的數(shù)據(jù)被insert(插入),delete(刪除),update(修改)時，觸發(fā)器被激活并執(zhí)行。按激活的時機分為“后觸發(fā)”和“替代觸發(fā)”兩種方式。,39,六、提高數(shù)據(jù)庫效率的設(shè)計,(1)后觸發(fā)若引發(fā)觸發(fā)器執(zhí)行的語句通過了各種約束檢查，成功執(zhí)行后才激活并執(zhí)行觸發(fā)器程序，稱為后觸發(fā)。特點：若引發(fā)觸發(fā)器執(zhí)行的語句違反了某種約束，該語句不會執(zhí)行，則后觸發(fā)方式的觸發(fā)器也不被激活。后觸發(fā)方式只能創(chuàng)建在數(shù)據(jù)表上，不能創(chuàng)建在視圖上。一個表可以有多個后觸發(fā)器。,40,六、提高數(shù)據(jù)庫效率的設(shè)計,(2)替代觸發(fā)若激活觸發(fā)器的語句僅僅起到激活觸發(fā)器的作用，一旦激活觸發(fā)器后該語句即停止執(zhí)行，立即轉(zhuǎn)去執(zhí)行觸發(fā)器的程序，激活觸發(fā)器的語句并不被執(zhí)行，相當于禁止某種操作，這種方式稱為替代觸發(fā)。特點：可以創(chuàng)建在表上，也可以創(chuàng)建在視圖上。一個表只能有一個替代觸發(fā)器。,41,六、提高數(shù)據(jù)庫效率的設(shè)計,4.SQLServer系統(tǒng)對觸發(fā)器的管理(1)觸發(fā)器作為一種數(shù)據(jù)庫對象，在syscomment系統(tǒng)表中存儲有其完整的定義信息，在sysobject系統(tǒng)表中有該對象的記錄。(2)觸發(fā)器被激活時會創(chuàng)建兩個臨時表：inserted表和deleted表，以確保對數(shù)據(jù)的安全操作。兩個表的結(jié)構(gòu)與激活觸發(fā)器的原數(shù)據(jù)表相同。用insert語句插入記錄激活觸發(fā)器時，系統(tǒng)在原表插入記錄的同時，也會自動把記錄插入到inserted表中。用delete語句刪除記錄激活觸發(fā)器時，系統(tǒng)在原表刪除記錄的同時，會把刪除的記錄添加到deleted表中。用update語句修改記錄激活觸發(fā)器時，系統(tǒng)先在原表刪除原有記錄，刪除的記錄被添加到deleted表中，再在原表插入新的記錄，并同時插入到inserted表。用戶可以用select語句查詢這兩個表，但不允許修改。觸發(fā)器執(zhí)行完后，這兩個表將自動刪除。,42,六、提高數(shù)據(jù)庫效率的設(shè)計,5.創(chuàng)建觸發(fā)器Createtriggerowner.觸發(fā)器名onowner.表名/視圖名for/after/insteadofinsert,update,deletewithencryptionnorforreplicationASsetnocount-不返回給變量賦值的結(jié)果SQL語句系列rollbacktransaction-事務(wù)回滾,43,六、提高數(shù)據(jù)庫效率的設(shè)計,說明(1)for/after/insteadof:用于指定觸發(fā)方式For與after相同，是為了與以前版本兼容。其后可以指定“insert,update,delete”命令中的一個或多個(2)norforreplication:在復制過程中不激