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

1、,參考書目： 數(shù)據(jù)庫(kù)系統(tǒng)概論 高等教育出版社 王珊 薩師煊 數(shù)據(jù)庫(kù)系統(tǒng)教程 高等教育出版社 施伯樂等 數(shù)據(jù)庫(kù)設(shè)計(jì)、應(yīng)用開發(fā)與管理 電子工業(yè)出版社 Michael V.Mannino 著 唐常杰 等譯 UML和統(tǒng)一過程-實(shí)用面向?qū)ο蟮姆治龊驮O(shè)計(jì) 機(jī)械工業(yè)出版社 方貴賓 等譯 UML應(yīng)用建模實(shí)踐過程 機(jī)械工業(yè)出版社尤克濱 編著,邏輯結(jié)構(gòu)設(shè)計(jì)步驟,ER圖向關(guān)系模型的轉(zhuǎn)換 關(guān)系模型的優(yōu)化 視圖（view）設(shè)計(jì),ER圖向關(guān)系模型的轉(zhuǎn)換,Entity 1:M relationship N:M relationship Identifying relationship Optional 1-M relati

2、onship Generalization Hierarchy 1-1 Relationships,基本轉(zhuǎn)化規(guī)則 basic conversion rules,規(guī)則一、Each entity type becomes a table(實(shí)體轉(zhuǎn)化為表，實(shí)體的主碼成為表的主碼，不包括弱實(shí)體，屬性轉(zhuǎn)化成列),基本轉(zhuǎn)化規(guī)則 basic conversion rules,規(guī)則二、Each 1-M relationship becomes a foreign key in the table corresponding to the child entity（一對(duì)多聯(lián)系轉(zhuǎn)化為子表中參照父表的外鍵，如果父表對(duì)

3、子表來說是強(qiáng)制mandatory的，則外鍵不能為NULL）或 1-M relationship becomes a table with a primary key from M-side of the relationship（也可轉(zhuǎn)化成獨(dú)立的關(guān)系，與該聯(lián)系相連的各實(shí)體的碼以及聯(lián)系本身的屬性組成關(guān)系的屬性，關(guān)系的碼為M端實(shí)體的碼）【少采用，比較規(guī)則五】,規(guī)則三、 Each M-N relationship becomes an associative table with a combined primary key.(多對(duì)多聯(lián)系轉(zhuǎn)化成為一張獨(dú)立的表，其主碼由聯(lián)系兩端的實(shí)體的主碼共同組成),

4、規(guī)則四、 Each identifying relationship adds a column to a primary key.（標(biāo)識(shí)依賴在轉(zhuǎn)化時(shí)，將父表的主碼添加到弱實(shí)體中，弱實(shí)體的主碼包括：1 弱實(shí)體自己的主碼 2標(biāo)識(shí)實(shí)體【也就是父表】的主碼）,規(guī)則五、Optional 1-M Rule （可選的一對(duì)多聯(lián)系，所謂“可選”，指“1”端，也就是父表端不強(qiáng)制，最小基數(shù)可為0的情況） 如：,下圖表示是一個(gè)可選的一對(duì)多聯(lián)系，學(xué)生可以住宿舍，也可以不住宿舍（比如有回家住的學(xué)生）， 這里，dorm對(duì)student來說是可選的，因此dormNo作為Student表的外碼，可以為空（Null） 為了避免

5、這種空值，我們將live聯(lián)系轉(zhuǎn)化成關(guān)系表,Optional,該表的主碼是子表（Student實(shí)體）的主碼 該表的外碼是兩端的實(shí)體的主碼的組合 該表的外碼不允許空NULL 注意：規(guī)則五可以避免NULL，但是多生成了一個(gè)表，增加了查詢的復(fù)雜性，許多應(yīng)用中，避免額外的表比避免NULL更重要,規(guī)則六、轉(zhuǎn)化泛化層次結(jié)構(gòu)。RDBMS不直接支持泛化，各CASE工具的實(shí)現(xiàn)方法有別,規(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 舉例：

6、,方法一：,冗余聯(lián)系,方法二： 在Power Designer中可通過設(shè)置dominant關(guān)系來實(shí)現(xiàn),CREATE TABLE Office ( PRIMARY KEY (OfficeNo) , FOREIGN KEY(EmpNo) REFERENCES Employee, UNIQUE (EmpNo) ),邏輯模型的優(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%分解出來，形成獨(dú)立的子關(guān)系 水平分解和垂直分解 注意

7、保持無損連接性和函數(shù)依賴,考慮查詢某筆貸款的貸款人和擔(dān)保人等情況 在表Loan中存儲(chǔ)LenderNo和LenderName是不符合BCNF范式的，但可以減少表Loan和Lender的連接 仔細(xì)定奪，考慮是否要在Loan表中增加來自Guarantor、Lender和Institution表中的Name 如果需要，則反規(guī)范化就是一個(gè)好方法， 因?yàn)镚uarantor、Lender和Institution表是相對(duì)穩(wěn)定的 雖然不符合BCNF范式，如果只查詢，不更新，則沒有更新異常的問題,視圖（view）設(shè)計(jì),定義 視圖是一個(gè)虛擬表，其內(nèi)容由查詢定義 同真實(shí)的表一樣，視圖包含一系列帶有名稱的列和行數(shù)據(jù) 但

8、是，視圖并不在數(shù)據(jù)庫(kù)中以存儲(chǔ)的數(shù)據(jù)值集形式存在。行和列數(shù)據(jù)來自由定義視圖的查詢所引用的表，并且在引用視圖時(shí)動(dòng)態(tài)生成 對(duì)其中所引用的基礎(chǔ)表來說，視圖的作用類似于篩選,視圖定義,視圖是一個(gè)虛擬表，其內(nèi)容由查詢定義 從用戶角度來看 一個(gè)視圖是從一個(gè)特定的角度來查看數(shù)據(jù)庫(kù)中的數(shù)據(jù) 從數(shù)據(jù)庫(kù)系統(tǒng)內(nèi)部來看 視圖是由一張或多張表中的數(shù)據(jù)組成的 從數(shù)據(jù)庫(kù)系統(tǒng)外部來看 視圖就如同一張表一樣，對(duì)表能夠進(jìn)行的一般操作都可以應(yīng)用于視圖，例如查詢，插入，修改，刪除操作等,視圖的作用,視點(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)

9、品成本，產(chǎn)品合格率，質(zhì)量等級(jí)） 可在產(chǎn)品關(guān)系上建立兩個(gè)視圖： 為一般顧客建立視圖： 產(chǎn)品1（產(chǎn)品號(hào)，產(chǎn)品名，規(guī)格，單價(jià)） 為產(chǎn)品銷售部門建立視圖： 產(chǎn)品2（產(chǎn)品號(hào)，產(chǎn)品名，規(guī)格，單價(jià)，生產(chǎn)車間，負(fù)責(zé)人，產(chǎn)品成本）,視圖的作用,簡(jiǎn)化操作 視圖大大簡(jiǎn)化了用戶對(duì)數(shù)據(jù)的操作。因?yàn)樵诙x視圖時(shí)，若視圖本身就是一個(gè)復(fù)雜查詢的結(jié)果集，這樣在每一次執(zhí)行相同的查詢時(shí)，不必重新寫這些復(fù)雜的查詢語(yǔ)句,只要一條簡(jiǎn)單的查詢視圖語(yǔ)句即可 可見視圖向用戶隱藏了表與表之間的復(fù)雜的連接操作,視圖的作用,定制數(shù)據(jù) 視圖能夠?qū)崿F(xiàn)讓不同的用戶以不同的方式看到不同或相同的數(shù)據(jù)集。因此，當(dāng)有許多不同水平的用戶共用同一數(shù)據(jù)庫(kù)時(shí)，這顯得極為

10、重要 合并分割數(shù)據(jù) 在有些情況下，由于表中數(shù)據(jù)量太大，故在表的設(shè)計(jì)時(shí)常將表進(jìn)行水平分割或垂直分割，但表的結(jié)構(gòu)的變化卻對(duì)應(yīng)用程序產(chǎn)生不良的影響。如果使用視圖就可以重新保持原有的結(jié)構(gòu)關(guān)系，從而使外模式保持不變，原有的應(yīng)用程序仍可以通過視圖來重載數(shù)據(jù),視圖的作用,安全性 視圖可以作為一種安全機(jī)制 通過視圖用戶只能查看和修改他們所能看到的數(shù)據(jù)，其它數(shù)據(jù)庫(kù)或表既不可見也不可以訪問 如果某一用戶想要訪問視圖的結(jié)果集，必須授予其訪問權(quán)限。視圖所引用表的訪問權(quán)限與視圖權(quán)限的設(shè)置互不影響 如：社會(huì)保險(xiǎn)基金表，可以用視圖只顯示姓名，地址，而不顯示社會(huì)保險(xiǎn)號(hào)和工資數(shù)等,視圖可以由以下任意一項(xiàng)組成: 1 一個(gè)基表的任

11、意子集 2 兩個(gè)或兩個(gè)以上的基表的合集 3 兩個(gè)或兩個(gè)以上基表的交集 4 一個(gè)或者多個(gè)基表運(yùn)算的結(jié)果集合 5 另一個(gè)視圖的子集.,利用企業(yè)管理器創(chuàng)建與管理視圖 用T-SQL語(yǔ)句創(chuàng)建與管理視圖 創(chuàng)建視圖： CREATE VIEW 【(列名組)】AS 刪除視圖： DROP VIEW ,舉例： 創(chuàng)建視圖，顯示學(xué)生的姓名和該學(xué)生的選課號(hào)和成績(jī) CREATE VIEW v_sc AS SELECT s.sn,o,sc.score FROM s inner join sc ON s.sno=sc.sno 練習(xí)： 創(chuàng)建顯示學(xué)生姓名、課程名和成績(jī)的視圖,舉例 現(xiàn)有員工表和項(xiàng)目表，選擇其中的部分字段和記錄創(chuàng)建視

12、圖，并且限制表員工表 中的記錄只能是部門為“項(xiàng)目部”的記錄集合，視圖定義為view_part 代碼： CREATE VIEW view_part AS SELECT 員工表.編號(hào), 員工表.姓名,員工表.性別,員工表.所屬部門, 項(xiàng)目表.項(xiàng)目編號(hào), 項(xiàng)目表.名稱 FROM 項(xiàng)目表 INNER JOIN 員工表 ON 項(xiàng)目表.負(fù)責(zé)人 = 員工表.編號(hào) WHERE 員工表.所屬部門=項(xiàng)目部,可以在設(shè)計(jì)視圖時(shí)，重新定義列的命名，使其與用戶命名習(xí)慣一致 例：如第一題，但重新定義列名為中文 CREATE VIEW v_sc (姓名,選課號(hào),成績(jī)) AS SELECT s.sn,o,sc.score FR

13、OM s inner join sc ON s.sno=sc.sno,兩個(gè)參數(shù)： With encryption 數(shù)據(jù)加密，顯示的是亂碼 With check option 強(qiáng)制視圖的修改操作必須符合select語(yǔ)句設(shè)定的條件，否則不予執(zhí)行,With check option例： CREATE VIEW ygb_view AS SELECT * FROM 員工表 WHERE 員工表.性別=女 執(zhí)行下列語(yǔ)句,插入新記錄: INSERT INTO ygb_view (姓名,性別,工資) values(李立三,男,2300) 插入操作成功,但不合理!解決辦法:使用with check option

14、： CREATE VIEW ygb_view AS SELECT * FROM 員工表 WHERE 員工表.性別=女With check option 同樣,插入新記錄:INSERT INTO ygb_view (姓名,性別,工資) values(李立三,男,2300)時(shí)，插入操作將失敗!,數(shù)據(jù)庫(kù)設(shè)計(jì)貫通 物理設(shè)計(jì)部分,從索引說起,分類 唯一索引 /非唯一索引唯一索引是不允許其中任何兩行具有相同索引值的索引 主鍵索引/非主鍵索引 主鍵索引是唯一索引的特定類型 聚集索引 表中行的物理順序與索引順序相同 一個(gè)表只能包含一個(gè)聚集索引 非聚集索引 不改變行的物理順序 由于在建立聚集索引時(shí)會(huì)隨時(shí)改變行的

15、物理順序，應(yīng)在非聚集索引前建立聚集索引 聚集索引比非聚集索引快,索引,在“學(xué)生選課數(shù)據(jù)庫(kù)”中，創(chuàng)建索引如下：,方法一：企業(yè)管理器 選中學(xué)生表，右擊“設(shè)計(jì)表” 右擊“索引/鍵”打開“屬性”對(duì)話框 為學(xué)號(hào)sno創(chuàng)建聚集、唯一索引 參見右圖,方法一：企業(yè)管理器 選中學(xué)生表，右擊“設(shè)計(jì)表” 右擊“索引/鍵”打開“屬性”對(duì)話框 為姓名sn創(chuàng)建非聚集索引 參見右圖,方法二：使用SQL命名 為課程號(hào)cno創(chuàng)建聚集、唯一索引的命令如下： Create unique clustered index IN_CNO on c(cno) With pad_index, fillfactor=80 為課程名cn創(chuàng)建非聚

16、集、唯一索引的命令如下： Create unique index IN_CN on c(cn DESC) 注：pad_index需與fillfactor一起使用 Fillfactor=填充因子：用于指定在創(chuàng)建索引時(shí)，每個(gè)索引頁(yè)的數(shù)據(jù)占索引頁(yè)大小的百分比，范圍為1100 DESC 表示為降序索引，默認(rèn)是升序,為選課表sc創(chuàng)建聚集、唯一索引的命令如下： Create unique clustered index IN_SC on sc(sno, cno) With pad_index, fillfactor=90 刪除索引 Drop index 索引名,索引的選擇規(guī)則,主關(guān)鍵字作為聚集索引的候選比較合適 為了支持表間的連接查詢，外關(guān)鍵字考慮建立索引 若果查詢存在等值條件，考慮在存在多值的條件（列）上建立索引 如：查詢高校中職稱=“教授”的信息，職稱是一個(gè)多值屬性，可以考慮為其建立索引 使用order by或group by子句檢索時(shí)，索引可顯著減少分組和排序的時(shí)間,索引的選擇規(guī)則,需要精確匹配的查詢（指使用where子句）考慮使用索引 如：where sname=張三