,數據庫及數據倉庫精要,前言,數據庫扮演的角色 也叫聯機事務處理OLAP(Online Transactional Processing)，數據庫保存由日常管理過程中涉及的業務操作創建的操作型結構化數據，數據記錄系統管理行為（通過各種業務邏輯來交互）。反映細粒度的事務數據，保存時間短。主要依賴關系建模方法論。 數據倉庫扮演的角色 也叫聯機分析處理OLAP(Online Analytical Processing)，數據由聯機事務處理來，經過選擇和聚集，變為分析事實產生的因果，輔助決策制定（通過各種分析報表來交互）。反映大范圍的事實數據，保存時間長。主要依賴多維建模方法論,問題的導入,結構良好的表，范式，SQL語言及關系 基本表與中間表、臨時表不同，基本表及其字段之間的關系，應盡量滿足第三范式，是結構良好的表，它可以消除刪除行，改變行，修改行(實例)的錯誤和異常。它具有如下四個特性：(1) 原子性，基本表中的字段是不可再分解的。(2) 原始性，基本表中的記錄是原始數據（基礎數據）的記錄。(3) 演繹性，由基本表與代碼表中的數據，可以派生出所有的輸出數據。(4) 穩定性，基本表的結構是相對穩定的，表中的記錄是要長期保存的。(5)基本表的每個決定因子都必須是候選建。(6)菲基本表必須分解為兩個或多個基本表。 三個基本范式：(1) 1NF是對屬性的原子性約束，要求屬性具有原子性，不可再分解。(2) 2NF是對記錄的惟一性約束，要求記錄有惟一標識，即實體的惟一性。(3) 3NF是對字段冗余性的約束，即任何字段不能由其他字段派生出來，它要求字段沒有冗余 大多數結構不良好的表，會產生或包含大量的冗余數據，同時可能會出現刪除行，改變行，修改行的錯誤和異常，這都是都是使用了SQL DML CURD語句產生的。像中間表、報表和臨時表：(1) 中間表是存放統計數據的表，它是為數據倉庫、輸出報表或查詢結果而設計的，有時它沒有主鍵與外鍵(數據倉庫除外)。(2) 臨時表是程序員個人設計的，存放臨時記錄，為個人所用。(3) 基表和中間表由DBA維護，臨時表由程序員自己用程序自動維護。 關系是一個由行和列組成的二維表，不一定結構良好，特征為：行包括實體的數據，列包含實體性質的數據，表中的單元格存儲單個值，每列的所有實體類型一致，每列具有唯一名稱，列的順序任意，行的順序任意，任意兩行互不重復。這是最大的復合關系模式的條件，符合這個要求的表就是關系型表格。,統計，匯總，分析表自動用Excel做,目錄,E-R模型的概念與表示 實體-聯系方法(概念設計) E-R圖向關系表的轉換(邏輯設計),E-R模型的概念與表示,實體集-語義(名詞類性) 實體（Entity） 事物就是在行動影響下物質本身的改變，或者進行。客觀存在并可相互區別的事物稱為實體。實體可以是具體的，也可以是抽象的概念或聯系。 具有共性的一類實體可歸類為一個實體集(Entity set)。 屬性（Attribute） 實體所具有的某一特性稱為屬性。 一個實體可以由若干個屬性來刻畫。 域（Domain） 屬性的取值范圍或類型。 鍵或標識符（Key） 標識符是實體中一個或多個屬性的集合，可用來唯一標識實體中的一個實例。每個實體都必須至少有一個標識符。如果實體只有一個標識符，則它為實體的主標識符。如果實體有多個標識符，則其中一個被指定為主標識符，其余的標識符就是次標識符了,E-R模型的概念與表示,聯系集-語義(動詞類型) 實體之間可以通過聯系來相互關聯。與實體和實體集對應，聯系也可以分為聯系和聯系集，聯系集是實體集之間的聯系，聯系是實體之間的聯系，聯系是具有方向性的。 聯系具有方向性，每個方向上都有一個基數。 聯系的兩個方向上各自包含有一角色名，描述該方向聯系的作用。 按照實體類型中實例之間的數量對應關系，通常可將聯系分為4個基本聯系分為類，即一對一（ONE TO ONE）聯系、一對多（ONE TO MANY）聯系、多對一（MANY TO ONE）聯系和多對多聯系（MANY TO MANY）。 三個特殊聯系 每個實體類型都有自己的標識符，如果兩個實體集之間發生聯系，其中一個實體類型的標識符進入另一個實體類型并與該實體類型中的標識符共同組成其標識符時，這種聯系則稱為標定聯系，也叫依賴聯系。反之稱為非標定聯系，也叫非依賴聯系。 遞歸聯系是實體集內部實例之間的一種聯系，通常形象地稱為自反聯系。同一實體類型中不同實體集之間的聯系也稱為遞歸聯系。,E-R模型的概念與表示,E-R模型的概念與表示,E-R圖的設計步驟 第一步：針對特定的應用，確定實體、屬性和實體間的聯系，畫出局部E-R圖。 第二步：綜合各個局部E-R圖，產生反映數據庫整體概念的總體E-R圖。,E-R模型的概念與表示,弱實體集 有些實體集的所有屬性都不足以形成主碼，這樣的實體集稱為弱實體集(Weak Entity Set)，依賴于其它實體集而存在。 與此相對，其屬性可以形成主碼的實體集稱為強實體集。 弱實體集所依賴的實體集稱為標識實體集（identifying entity set），相應的關系為標識聯系（identifying relationship）。,E-R模型的概念與表示,弱實體集通常沒有主鍵。 以訂單的分項為例，訂單項實體集可能有編號（局部的編號）、商品名稱、數量、單價等屬性，但是這些屬性不足以識別一個定單項，因為完全有可能在另外一張訂單中出現相同的內容。 必須把訂單的關鍵字（如一個全局的訂單編號）和定單項的局部編號結合起來才能標示一個定單項。 弱實體集的屬性中，用來與標識實體集的鍵結合以識別一個弱實體集的屬性稱為部分鍵（partial key）。 弱實體集的主鍵=它的標識實體集的鍵+它的部分鍵,4.1 E-R模型的概念與表示,ER圖使用雙線矩形表示弱實體集，弱實體集與其標識實體集之間的聯系用雙線菱形表示，弱實體集的部分鍵使用虛下劃線表示。,E-R模型的概念與表示,實體集的層次關系 現實世界中的很多概念之間都具體層次關系。 ER模型使用實體集間的繼承和ISA關系來描述這種概念間的層次關系 實體集老師或學生都繼承自實體集人，并且實體集老師或學生與實體集人之間都滿足ISA關系，即老師或學生都是人的一種。 ISA關系可以從兩個方向進行設計 從自上而下的方向，首先設計出人這一實體，然后根據屬性的不同，將兩種不同的人具體化（specification）為老師或者學生。 從自下而上的方向，首先設計出老師或學生，然后將他們的共性提取出來，泛化（generalization）為人。,E-R模型的概念與表示,層次關系的約束 從子實體集之間是否相交角度，不相交（disjoin）泛化要求繼承自同一父輩的多個子實體集之沒有交集，重疊（overlapping）泛化則允許有交集。 從泛化是否完全角度，全參與泛化要求所有父輩實體都必須同時也是某一子輩實體，部分泛化則允許不是任何子輩實體的父輩實體存在。 例如，在采用會員制的銷售系統中，顧客被分為會員（VIP)與非會員(NONVIP)兩種，會員擁有消費積分(credit)，非會員擁有固定的折扣率(discount)。一個顧客要么是會員、要么是非會員，二者必取其一，因此為全參與不相交。,E-R模型的概念與表示,E-R圖例,實體-聯系方法,實體還是屬性 凡是滿足以下兩條準則的事物，一般均可作為屬性對待。 作為屬性，不能再具有需要描述的性質。屬性必須是不可分的數據項，不能包含其他屬性。 屬性不能與其他實體具有聯系，即E-R圖中所表示的聯系是實體之間的聯系。 例如書籍是一個實體，書號、書名、作者、出版社、定價是書籍的屬性，如果應用系統不再需要作者的其他信息，如電話、住址、個人主頁等，那么根據原則1可以將作者作為書籍的屬性對待。但是如果這些信息是必須的，那么作者作為一個實體看待更為恰當。,實體-聯系方法,實體-聯系方法,實體還是聯系 一般來說，實體對應現實世界中實際存在的事物，是名詞類型；聯系對應的概念一般是一種動作，是動詞類型。 例如： 書和作者都是現實世界中的名詞，因此作為實體。 而written_by表示作者寫書這一動作，因此作為聯系。 映射基數往往影響到一個概念是作為實體還是聯系的選擇。 若一項貸款只能由一個分行發放，并且只能由一個客戶借貸，則將Loan作為Customer與Branch之間的聯系比較合適。 但如果允許多個客戶共同借貸同一項貸款 ，在這種情況下，將Loan作為實體。,實體-聯系方法,二元關系還是多元關系 數據庫中使用得最多的是二元聯系。 通常，將多元關系轉換為二元關系。 如學校選課系統，涉及到學生、教室、教師、課程等多個實體，可表示為一個四元關系。,實體-聯系方法,但也有一些情況下使用多元聯系更好（如需要表達多個實體集間的約束時） 如學校選課系統中若一門課程可由多個教師教授，并且若課程和教師確定，則上課的地點也隨之確定。,實體-聯系方法,聯系屬性的放置 影響聯系屬性放置的主要因素是聯系的映射基數。 對于一對一或一對多聯系，選擇作為聯系屬性或實體屬性只是體現語義側重點的不同 如銷售系統需要記錄顧客（Customer）與訂單（Order）之間的關系（Possess）。由于一個訂單只能由一個顧客所有，因此為顧客與訂單之間為一對多關系。這時，記錄生成訂單日期的屬性（date）既可以作為聯系Possess的屬性，也可作為訂單的屬性。,實體-聯系方法,對于多對多聯系，聯系的屬性不能作為實體的屬性。 如，顧客與希望書籍之間的聯系希望購買(Wish_for)。 Wish_for有一屬性date，表示顧客發出購買意向的日期，這一屬性不能作為參與聯系的兩個實體Customer或Book的屬性。,實例在線書店數據庫,類似于Amazon的在線書店系統所用的數據庫 數據庫中要求存儲所有書籍的相關信息，并對書加以分類； 顧客的有關信息也要求存儲在數據庫中，并且允許用戶選擇自己感興趣的書籍類別及希望購買的圖書； 顧客在決定購買時可以發出訂單，同一訂單可以包含多種書，每種書可一次購買多本。顧客在訂單中提供送貨地址，系統根據訂單發貨。,實例在線書店數據庫,合并分E-R圖 各分E-R圖之間的沖突主要有三類: 屬性沖突 (1) 屬性域沖突，即屬性值的類型、取值范圍或取值集合不同。 例如：屬性“訂單號”有的定義為字符型，有的為數值型。 (2) 屬性取值單位沖突。 例如：屬性“庫存”有的以冊為單位，有的以千冊為單位。 命名沖突 (1) 同名異義。不同意義對象相同名稱。 例如：Author和Customer均有屬性name。 (2) 異名同義（一義多名）。同意義對象不相同名稱。 例如：“項目”和“課題”。,實例在線書店數據庫,結構沖突 (1) 同一對象在不同應用中具有不同的抽象。 例如： “作者”在某一局部應用中被當作實體，而在另一局部應用中則被當作屬性。 (2) 同一實體在不同局部視圖中所包含的屬性不完全相同，或者屬性的排列次序不完全相同。 (3) 實體之間的聯系在不同局部視圖中呈現不同的類型。 例如：實體E1與E2在局部應用A中是多對多聯系，而在局部應用B中是一對多聯系；又如在局部應用X中E1與E2發生聯系，而在局部應用Y中E1、E2、E3三者之間有聯系。 解決方法是根據應用的語義對實體聯系的類型進行綜合或調整。,E-R圖向表的轉換,通過實體聯系方法可以方便得得到現實世界的一個抽象模型，但這一模型并不能為數據庫管理系統接受。要完成從現實世界到信息世界的轉化，還必須將實體聯系方法所得的E-R圖轉化為關系表定義。,實體的轉換,將一個普通實體（非弱實體）轉換為表定義是相當直觀的，實體的每個屬性對應表中的一個字段，實體的主鍵對應表的主鍵。 如Book實體轉化到表的結果為： Book(isbn, title, price, press, stock),聯系的轉換,一個多對多聯系在轉換后也對應一個表，表中的屬性包括 參與聯系各實體的主鍵 聯系的描述屬性 參與聯系各實體的主鍵之和構成表的超鍵。 如多對多聯系Written_by轉化為表之后其主鍵將由參與該聯系的兩個實體Book和Author的主鍵構成，如下： Written_by(isbn, author#, serial),聯系的轉換,一對一和一對多聯系 A與B之間是一對多聯系，不轉換為一張單獨的表，而只在B轉換后的表中增加A的主鍵屬性（當然這些屬性將形成一個引用到A的主鍵的一個外鍵），以此表示某B實體所從屬的A實體。 這種方法可以產生更少的表，有利于提高數據庫性能，還可以表達更多的約束 如對于聯系Possess，將在Order表中增加一列customer#表示訂單從屬的顧客,弱實體的轉換,由于弱實體總是全參與它與它的標識實體之間的多對一聯系，因此可以采用上面提出的一對多聯系方法進行轉換。 弱實體轉換后生成的表的主鍵由標識實體的主鍵與弱實體本身的部分鍵組合而成。 如弱實體Item轉換后，構成如下： Item（order# ,item# , isbn ,qty),實體層次的轉換,將實體層次轉換為表定義時可采用兩種方法 父輩實體與子輩實體都轉換為單獨的表 通用方法，任何情況適用。 每一個子輩實體轉換為單獨的表，其中既包含各子輩實體的特殊屬性，也包含子輩與父輩實體的公有屬性。 只適用全參與泛化，因無法比哦啊是不從屬于任何子輩實體的父輩實體 如Customer與VIP、NONVIP之間的全參與泛化可用第二種方法轉換為： VIP(customer#,name,gender,birthday,city,address,email,credit) NONVIP( customer# ,name,gender,birthday,city,address,email, discount),一些實際的考慮,一般來說，在將ER圖轉換到表定義的過程中，需要考慮兩個實際的問題：性能與數據規范化。 提高數據庫性能的