數(shù)據(jù)庫(kù)設(shè)計(jì)貫通邏輯及物理設(shè)計(jì)

數(shù)據(jù)庫(kù)設(shè)計(jì)貫通邏輯及物理設(shè)計(jì)第一頁(yè)，共四十二頁(yè)，編輯于2023年，星期三參考書(shū)目：數(shù)據(jù)庫(kù)系統(tǒng)概論高等教育出版社王珊薩師煊數(shù)據(jù)庫(kù)系統(tǒng)教程高等教育出版社施伯樂(lè)等數(shù)據(jù)庫(kù)設(shè)計(jì)、應(yīng)用開(kāi)發(fā)與管理電子工業(yè)出版社MichaelV.Mannino著唐常杰等譯UML和統(tǒng)一過(guò)程-實(shí)用面向?qū)ο蟮姆治龊驮O(shè)計(jì)機(jī)械工業(yè)出版社方貴賓等譯UML應(yīng)用建模實(shí)踐過(guò)程機(jī)械工業(yè)出版社尤克濱編著第二頁(yè)，共四十二頁(yè)，編輯于2023年，星期三邏輯結(jié)構(gòu)設(shè)計(jì)步驟ER圖向關(guān)系模型的轉(zhuǎn)換關(guān)系模型的優(yōu)化視圖（view）設(shè)計(jì)第三頁(yè)，共四十二頁(yè)，編輯于2023年，星期三ER圖向關(guān)系模型的轉(zhuǎn)換Entity1:MrelationshipN:MrelationshipIdentifyingrelationshipOptional1-MrelationshipGeneralizationHierarchy1-1Relationships第四頁(yè)，共四十二頁(yè)，編輯于2023年，星期三基本轉(zhuǎn)化規(guī)則

basicconversionrules規(guī)則一、Eachentitytypebecomesatable(實(shí)體轉(zhuǎn)化為表，實(shí)體的主碼成為表的主碼，不包括弱實(shí)體，屬性轉(zhuǎn)化成列)第五頁(yè)，共四十二頁(yè)，編輯于2023年，星期三基本轉(zhuǎn)化規(guī)則

basicconversionrules規(guī)則二、Each1-Mrelationshipbecomesaforeignkeyinthetablecorrespondingtothechildentity（一對(duì)多聯(lián)系轉(zhuǎn)化為子表中參照父表的外鍵，如果父表對(duì)子表來(lái)說(shuō)是強(qiáng)制mandatory的，則外鍵不能為NULL）或

1-MrelationshipbecomesatablewithaprimarykeyfromM-sideoftherelationship（也可轉(zhuǎn)化成獨(dú)立的關(guān)系，與該聯(lián)系相連的各實(shí)體的碼以及聯(lián)系本身的屬性組成關(guān)系的屬性，關(guān)系的碼為M端實(shí)體的碼）【少采用，比較規(guī)則五】第六頁(yè)，共四十二頁(yè)，編輯于2023年，星期三規(guī)則三、EachM-Nrelationshipbecomesanassociativetablewithacombinedprimarykey.(多對(duì)多聯(lián)系轉(zhuǎn)化成為一張獨(dú)立的表，其主碼由聯(lián)系兩端的實(shí)體的主碼共同組成)第七頁(yè)，共四十二頁(yè)，編輯于2023年，星期三規(guī)則四、Eachidentifyingrelationshipaddsacolumntoaprimarykey.（標(biāo)識(shí)依賴在轉(zhuǎn)化時(shí)，將父表的主碼添加到弱實(shí)體中，弱實(shí)體的主碼包括：1弱實(shí)體自己的主碼2標(biāo)識(shí)實(shí)體【也就是父表】的主碼）第八頁(yè)，共四十二頁(yè)，編輯于2023年，星期三規(guī)則五、Optional1-MRule（可選的一對(duì)多聯(lián)系，所謂“可選”，指“1”端，也就是父表端不強(qiáng)制，最小基數(shù)可為0的情況）如：第九頁(yè)，共四十二頁(yè)，編輯于2023年，星期三下圖表示是一個(gè)可選的一對(duì)多聯(lián)系，學(xué)生可以住宿舍，也可以不住宿舍（比如有回家住的學(xué)生），這里，dorm對(duì)student來(lái)說(shuō)是可選的，因此dormNo作為Student表的外碼，可以為空（Null）為了避免這種空值，我們將live聯(lián)系轉(zhuǎn)化成關(guān)系表Optional第十頁(yè)，共四十二頁(yè)，編輯于2023年，星期三該表的主碼是子表（Student實(shí)體）的主碼該表的外碼是兩端的實(shí)體的主碼的組合該表的外碼不允許空NULL注意：規(guī)則五可以避免NULL，但是多生成了一個(gè)表，增加了查詢的復(fù)雜性，許多應(yīng)用中，避免額外的表比避免NULL更重要第十一頁(yè)，共四十二頁(yè)，編輯于2023年，星期三規(guī)則六、轉(zhuǎn)化泛化層次結(jié)構(gòu)。RDBMS不直接支持泛化，各CASE工具的實(shí)現(xiàn)方法有別第十二頁(yè)，共四十二頁(yè)，編輯于2023年，星期三規(guī)則七、1:1relationship（轉(zhuǎn)化一對(duì)一聯(lián)系）方法一：直接將1：1聯(lián)系轉(zhuǎn)化成兩個(gè)外碼（可能會(huì)產(chǎn)生NULL）方法二：如果聯(lián)系的某一端實(shí)體是可選的（optional），則可以在另一個(gè)實(shí)體中取消外碼，以消除NULL舉例：第十三頁(yè)，共四十二頁(yè)，編輯于2023年，星期三方法一：冗余聯(lián)系第十四頁(yè)，共四十二頁(yè)，編輯于2023年，星期三方法二：在PowerDesigner中可通過(guò)設(shè)置dominant關(guān)系來(lái)實(shí)現(xiàn)第十五頁(yè)，共四十二頁(yè)，編輯于2023年，星期三CREATETABLEOffice(…PRIMARYKEY(OfficeNo),FOREIGNKEY(EmpNo)REFERENCESEmployee,UNIQUE(EmpNo))第十六頁(yè)，共四十二頁(yè)，編輯于2023年，星期三邏輯模型的優(yōu)化規(guī)范化分析數(shù)據(jù)依賴關(guān)系，消除部分依賴、傳遞依賴和多值依賴反規(guī)范化涉及到多個(gè)關(guān)系的查詢時(shí)，需進(jìn)行關(guān)系的連接運(yùn)算，產(chǎn)生很高的代價(jià)關(guān)系模式的分解80/20原則，大關(guān)系中，經(jīng)常被使用的數(shù)據(jù)只占所有數(shù)據(jù)的20%，把這20%分解出來(lái)，形成獨(dú)立的子關(guān)系水平分解和垂直分解注意保持無(wú)損連接性和函數(shù)依賴第十七頁(yè)，共四十二頁(yè)，編輯于2023年，星期三考慮查詢某筆貸款的貸款人和擔(dān)保人等情況在表Loan中存儲(chǔ)LenderNo和LenderName是不符合BCNF范式的，但可以減少表Loan和Lender的連接仔細(xì)定奪，考慮是否要在Loan表中增加來(lái)自Guarantor、Lender和Institution表中的Name如果需要，則反規(guī)范化就是一個(gè)好方法，

因?yàn)镚uarantor、Lender和Institution表是相對(duì)穩(wěn)定的雖然不符合BCNF范式，如果只查詢，不更新，則沒(méi)有更新異常的問(wèn)題第十八頁(yè)，共四十二頁(yè)，編輯于2023年，星期三視圖（view）設(shè)計(jì)定義視圖是一個(gè)虛擬表，其內(nèi)容由查詢定義同真實(shí)的表一樣，視圖包含一系列帶有名稱的列和行數(shù)據(jù)但是，視圖并不在數(shù)據(jù)庫(kù)中以存儲(chǔ)的數(shù)據(jù)值集形式存在。行和列數(shù)據(jù)來(lái)自由定義視圖的查詢所引用的表，并且在引用視圖時(shí)動(dòng)態(tài)生成對(duì)其中所引用的基礎(chǔ)表來(lái)說(shuō)，視圖的作用類似于篩選第十九頁(yè)，共四十二頁(yè)，編輯于2023年，星期三視圖定義視圖是一個(gè)虛擬表，其內(nèi)容由查詢定義從用戶角度來(lái)看一個(gè)視圖是從一個(gè)特定的角度來(lái)查看數(shù)據(jù)庫(kù)中的數(shù)據(jù)從數(shù)據(jù)庫(kù)系統(tǒng)內(nèi)部來(lái)看視圖是由一張或多張表中的數(shù)據(jù)組成的從數(shù)據(jù)庫(kù)系統(tǒng)外部來(lái)看視圖就如同一張表一樣，對(duì)表能夠進(jìn)行的一般操作都可以應(yīng)用于視圖，例如查詢，插入，修改，刪除操作等第二十頁(yè)，共四十二頁(yè)，編輯于2023年，星期三視圖的作用視點(diǎn)集中視圖集中即是使用戶只關(guān)心它感興趣的某些特定數(shù)據(jù)和他們所負(fù)責(zé)的特定任務(wù)設(shè)：產(chǎn)品（產(chǎn)品號(hào)，產(chǎn)品名，規(guī)格，單價(jià)，生產(chǎn)車間，負(fù)責(zé)人，產(chǎn)品成本，產(chǎn)品合格率，質(zhì)量等級(jí)）可在產(chǎn)品關(guān)系上建立兩個(gè)視圖：為一般顧客建立視圖：產(chǎn)品1（產(chǎn)品號(hào)，產(chǎn)品名，規(guī)格，單價(jià)）為產(chǎn)品銷售部門(mén)建立視圖：產(chǎn)品2（產(chǎn)品號(hào)，產(chǎn)品名，規(guī)格，單價(jià)，生產(chǎn)車間，負(fù)責(zé)人，產(chǎn)品成本）第二十一頁(yè)，共四十二頁(yè)，編輯于2023年，星期三視圖的作用簡(jiǎn)化操作視圖大大簡(jiǎn)化了用戶對(duì)數(shù)據(jù)的操作。因?yàn)樵诙x視圖時(shí)，若視圖本身就是一個(gè)復(fù)雜查詢的結(jié)果集，這樣在每一次執(zhí)行相同的查詢時(shí)，不必重新寫(xiě)這些復(fù)雜的查詢語(yǔ)句,只要一條簡(jiǎn)單的查詢視圖語(yǔ)句即可可見(jiàn)視圖向用戶隱藏了表與表之間的復(fù)雜的連接操作第二十二頁(yè)，共四十二頁(yè)，編輯于2023年，星期三視圖的作用定制數(shù)據(jù)視圖能夠?qū)崿F(xiàn)讓不同的用戶以不同的方式看到不同或相同的數(shù)據(jù)集。因此，當(dāng)有許多不同水平的用戶共用同一數(shù)據(jù)庫(kù)時(shí)，這顯得極為重要合并分割數(shù)據(jù)在有些情況下，由于表中數(shù)據(jù)量太大，故在表的設(shè)計(jì)時(shí)常將表進(jìn)行水平分割或垂直分割，但表的結(jié)構(gòu)的變化卻對(duì)應(yīng)用程序產(chǎn)生不良的影響。如果使用視圖就可以重新保持原有的結(jié)構(gòu)關(guān)系，從而使外模式保持不變，原有的應(yīng)用程序仍可以通過(guò)視圖來(lái)重載數(shù)據(jù)第二十三頁(yè)，共四十二頁(yè)，編輯于2023年，星期三視圖的作用安全性視圖可以作為一種安全機(jī)制通過(guò)視圖用戶只能查看和修改他們所能看到的數(shù)據(jù)，其它數(shù)據(jù)庫(kù)或表既不可見(jiàn)也不可以訪問(wèn)如果某一用戶想要訪問(wèn)視圖的結(jié)果集，必須授予其訪問(wèn)權(quán)限。視圖所引用表的訪問(wèn)權(quán)限與視圖權(quán)限的設(shè)置互不影響如：社會(huì)保險(xiǎn)基金表，可以用視圖只顯示姓名，地址，而不顯示社會(huì)保險(xiǎn)號(hào)和工資數(shù)等第二十四頁(yè)，共四十二頁(yè)，編輯于2023年，星期三視圖可以由以下任意一項(xiàng)組成:1一個(gè)基表的任意子集2兩個(gè)或兩個(gè)以上的基表的合集3兩個(gè)或兩個(gè)以上基表的交集4一個(gè)或者多個(gè)基表運(yùn)算的結(jié)果集合5另一個(gè)視圖的子集.第二十五頁(yè)，共四十二頁(yè)，編輯于2023年，星期三利用企業(yè)管理器創(chuàng)建與管理視圖用T-SQL語(yǔ)句創(chuàng)建與管理視圖創(chuàng)建視圖：CREATEVIEW<視圖名>【(列名組)】AS<子查詢>刪除視圖：DROPVIEW<索引名>第二十六頁(yè)，共四十二頁(yè)，編輯于2023年，星期三舉例：創(chuàng)建視圖，顯示學(xué)生的姓名和該學(xué)生的選課號(hào)和成績(jī)CREATEVIEWv_scASSELECTs.sn,o,sc.scoreFROMsinnerjoinscONs.sno=sc.sno練習(xí)：創(chuàng)建顯示學(xué)生姓名、課程名和成績(jī)的視圖第二十七頁(yè)，共四十二頁(yè)，編輯于2023年，星期三舉例現(xiàn)有‘員工表’和‘項(xiàng)目表’，選擇其中的部分字段和記錄創(chuàng)建視圖，并且限制表‘員工表’中的記錄只能是部門(mén)為“項(xiàng)目部”的記錄集合，視圖定義為view_part代碼：CREATEVIEWview_partASSELECT員工表.編號(hào),員工表.姓名,員工表.性別,員工表.所屬部門(mén),項(xiàng)目表.項(xiàng)目編號(hào),項(xiàng)目表.名稱FROM項(xiàng)目表INNERJOIN員工表ON項(xiàng)目表.負(fù)責(zé)人=員工表.編號(hào)WHERE員工表.所屬部門(mén)='項(xiàng)目部'第二十八頁(yè)，共四十二頁(yè)，編輯于2023年，星期三可以在設(shè)計(jì)視圖時(shí)，重新定義列的命名，使其與用戶命名習(xí)慣一致例：如第一題，但重新定義列名為中文CREATEVIEWv_sc(姓名,選課號(hào),成績(jī))ASSELECTs.sn,o,sc.scoreFROMsinnerjoinscONs.sno=sc.sno第二十九頁(yè)，共四十二頁(yè)，編輯于2023年，星期三兩個(gè)參數(shù)：Withencryption數(shù)據(jù)加密，顯示的是亂碼Withcheckoption強(qiáng)制視圖的修改操作必須符合select語(yǔ)句設(shè)定的條件，否則不予執(zhí)行第三十頁(yè)，共四十二頁(yè)，編輯于2023年，星期三Withcheckoption例：CREATEVIEWygb_viewASSELECT*FROM員工表WHERE員工表.性別=‘女’執(zhí)行下列語(yǔ)句,插入新記錄:INSERTINTOygb_view(姓名,性別,工資)values(‘李立三’,‘男’,2300)插入操作成功,但不合理!解決辦法:使用withcheckoption：CREATEVIEWygb_viewAS

SELECT*FROM員工表WHERE員工表.性別=‘女’Withcheckoption同樣,插入新記錄:INSERTINTOygb_view(姓名,性別,工資)values(‘李立三’,‘男’,2300)時(shí)，插入操作將失敗!第三十一頁(yè)，共四十二頁(yè)，編輯于2023年，星期三數(shù)據(jù)庫(kù)設(shè)計(jì)貫通

—物理設(shè)計(jì)部分第三十二頁(yè)，共四十二頁(yè)，編輯于2023年，星期三從索引說(shuō)起分類唯一索引

/非唯一索引唯一索引是不允許其中任何兩行具有相同索引值的索引主鍵索引/非主鍵索引主鍵索引是唯一索引的特定類型聚集索引表中行的物理順序與索引順序相同一個(gè)表只能包含一個(gè)聚集索引非聚集索引不改變行的物理順序由于在建立聚集索引時(shí)會(huì)隨時(shí)改變行的物理順序，應(yīng)在非聚集索引前建立聚集索引聚集索引比非聚集索引快第三十三頁(yè)，共四十二頁(yè)，編輯于2023年，星期三索引在“學(xué)生選課數(shù)據(jù)庫(kù)”中，創(chuàng)建索引如下：列名聚集索引唯一索引非聚集索引是否主鍵s.sno√√√s.sn√o√√√√√Sc.sno√√√So第三十四頁(yè)，共四十二頁(yè)，編輯于2023年，星期三方法一：企業(yè)管理器選中學(xué)生表，右擊“設(shè)計(jì)表”右擊“索引/鍵”打開(kāi)“屬性”對(duì)話框?yàn)閷W(xué)號(hào)sno創(chuàng)建聚集、唯一索引參見(jiàn)右圖第三十五頁(yè)，共四十二頁(yè)，編輯于2023年，星期三方法一：企業(yè)管理器選中學(xué)生表，右擊“設(shè)計(jì)表”右擊“索引/鍵”打開(kāi)“屬性”對(duì)話框?yàn)樾彰鹲n創(chuàng)建非聚集索引參見(jiàn)右圖第三十六頁(yè)，共四十二頁(yè)，編輯于2023年，星期三方法二：使用SQL命名為課程號(hào)cno創(chuàng)建聚集、唯一索引的命令如下：CreateuniqueclusteredindexIN_CNOonc(cno)Withpad_index,fillfactor=80為課程名cn創(chuàng)建非聚集、唯一索引的命令如下：CreateuniqueindexIN_CNonc(cnDESC)注：pad_index需與fillfactor一起使用Fillfactor=填充因子：用于指定在創(chuàng)建索引時(shí)，每個(gè)索引頁(yè)的數(shù)據(jù)占索引頁(yè)大小的百分比，范圍為1~100DESC表示為降序索引，默認(rèn)是升序第三十七頁(yè)，共四十二頁(yè)，編輯于2023年，星期三為選課表sc創(chuàng)建聚集、唯一索引的命令如下：CreateuniqueclusteredindexIN_SConsc(sno,cno