《资料库设计》PPT课件.ppt

資料庫設計,萬序恬 台北醫學大學 資訊服務中心,DBMS 觀念複習： What Is a DBMS?,一組大量且整合度高的資料 將真實世界模組化 實體：Entities (e.g., 學生, 課程) 關係：Relationships (e.g., 蔡一零選修電腦概論) 資料庫管理系統 (Database Management System，DBMS) ： 負責儲存與管理資料的套裝軟體.,DBMS 觀念複習： 檔案與DBMS的差異,為了處理大量的資料，必須對於資料的存取方式作特別的設計，甚至必須設計到硬體的支援上， 例如主記憶體的配置，硬碟使用規劃等(e.g., buffering, page-oriented access, 32-bit addressing, etc.) 查詢必須使用特殊的程式語法 於多人使用的環境下，仍舊必須保持資料的完整以及統一性 系統當機的復原機制設計 安全性及登入設計,DBMS 觀念複習： Why Study Databases?,從使用電腦計算到利用電腦提供資訊 work smart! 資料越來越多，也越來越多樣Datasets increasing in diversity and volume. Digital libraries, interactive video, Human Genome project, EOS project 需要資料庫管理系統支援 DBMS 是電腦科學發展的重心項目之一 OS, languages, theory, AI, multimedia, logic,?,資料模型(Data Models),資料模型(data model)：描述性資料的概念集合. 定義(schema )：使用資料模型對於特定資料集合的描述 關聯式模型(relational model )是最廣泛被使用的資料模型. 主要概念：relation-basically a table with rows and columns. Every relation has a schema, which describes the columns, or fields.,摘要程度,viewsconceptual (logical) schemaphysical schema. Views: 描述使用者看到資料的模樣 Conceptual schema (概念性定義): 定義邏輯性結構 Physical schema (實質性定義): 描述使用到的檔案及索引(indexes).,Schemas are defined using DDL; data is modified/queried using DML.,Physical Schema,Conceptual Schema,View 1,View 2,View 3,Example: University Database,Conceptual schema: Students(sid: string, name: string, login: string, age: integer, gpa:real) Courses(cid: string, cname:string, credits:integer) Enrolled(sid:string, cid:string, grade:string) Physical schema: Relations stored as unordered files. Index on first column of Students. External Schema (View): Course_info(cid:string,enrollment:integer),實體-關係模型,ER-model (Entity-Relationship Model),Overview,概念性設計 (Conceptual design) ER Model ER Model目標 定義企業中的實體entities與關係relationships 列出這些在資料庫儲存的實體以及關係的必備資訊 定出需要遵守商業法則 business rules 或是特殊限制 ER Model 中的database schema ，可以使用ER diagrams (實體關係圖，ERD)來表示,ER Model 基礎,實體 Entity: 真實世界的可獨立描述的物件， 屬性 attributes： 用以描述此物件的特性An entity is described (in DB) using a set of 實體集Entity Set: 上述實體的集合 E.g., all employees. 所有在實體集內的每一個實體都有相同的屬性選項. (Until we consider ISA hierarchies, anyway!) 每一個實體有一個鍵key, 用以指標至此實體 (鍵的分類見後說明),ER Model 基礎 (II),關係 Relationship: 兩個以上的實體之間的關係. E.g., 萬序恬在資訊服務中心上班. 關係集 Relationship Set: 類似的關係的集合 相同的實體集合可能當中有不同的角色而有不同的關係集合 (如圖二),lot,dname,budget,did,since,name,Works_In,Departments,Employees,ssn,Reports_To,lot,name,Employees,subor-dinate,super-visor,ssn,完整性限制 (Integrity Constraints, ICs),IC:可以通用在每一筆資料庫內的資料的規則e.g., domain constraints. 設定定義時必須遵守所有的IC. 有任何關係變動時也要檢查是否符合IC. 一筆合法legal的資料必須能滿足所有確認過的IC. 資料庫管理系統應該不能讓不合法的資料輸入. 如果DBMS能檢查 ICs, 則儲存在這裡面的資料才能和真實世界的對應. 當然也要避免輸入錯誤的資料!,主鍵限制 Key Constraints,每一個員工可以在很多個單位服務，每一個單位有很多名員工. 每一個部門只能有一位主管, 根據管理規章key constraint.,主鍵限制(Primary Key),主鍵定義: 1. 同一個(組)欄位內，沒有任何兩筆資料的值是重複的。 2. 其他的欄位都無法符合1 的情形。 Part 2 false? A superkey. 如果有一個欄位以上符合1的情形，則管理者可以自行指定一個欄位為主鍵primary key. E.g., sid is a key for Students. (What about name?) The set sid, gpa is a superkey.,Primary and Candidate Keys in SQL,Possibly many candidate keys (specified using UNIQUE), one of which is chosen as the primary key.,CREATE TABLE Enrolled (sid CHAR(20) cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid,cid) ),“For a given student and course, there is a single grade.” vs. “Students can take only one course, and receive a single grade for that course; further, no two students in a course receive the same grade.” Used carelessly, an IC can prevent the storage of database instances that arise in practice!,CREATE TABLE Enrolled (sid CHAR(20) cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid), UNIQUE (cid, grade) ),外部鍵 (Foreign KeysReferential Integrity),外部鍵Foreign key : 一個(組)和主鍵相關的欄位可以指向另外一個資料表中的某一筆資料(通常都是另外那筆資料的主鍵). E.g. sid is a foreign key referring to Students: Enrolled(sid: string, cid: string, grade: string) If all foreign key constraints are enforced, referential integrity is achieved, i.e., no dangling references. Can you name a data model w/o referential integrity? Links in HTML!,Where do ICs Come From?,ICs are based upon the semantics of the real-world enterprise that is being described in the database relations. We can check a database instance to see if an IC is violated, but we can NEVER infer that an IC is true by looking at an instance. An IC is a statement about all possible instances! From example, we know name is not a key, but the assertion that sid is a key is given to us. Key and foreign key ICs are the most common; more general ICs supported too.,參與限制 Participation Constraints,每一個部門現在都有一位主管嗎? 如果是，那就有participation constraint: the participation of Departments in Manages is said to be “total”. total 都有 partial 部分有 ERD表示：,lot,name,dname,budget,did,since,name,dname,budget,did,since,Manages,since,Departments,Employees,ssn,Works_In,使用ER Model進行概念性設計,設計步驟 : 該作為實體？還是屬性？ 該作為實體？還是關係？ 確認關係：是二重Binary 、三重ternary 、還是集合式Aggregation ? ER Model的限制: 可以描述絕大部分的狀況 但是在現實社會，仍舊會有部分規則是無法使用ERD表示出來的。,實體與屬性,地址address 應該是員工資料表的一個屬性，還是另外一個實體(和員工有關係的實體)? 必須看我們要如何處理以及使用地址這個資訊，以及這個資料的現實狀況: 如果每個員工都有很多個地址，那address 應該要設定為實體entity (因為屬性值不能另外再設定它的屬性). 如果我們想要利用地址當中的城市或街道作為分類或是搜尋的條件，address 就必須設定為實體entity (因為每一個屬性是單一且獨立不可再分的).,實體與屬性 (Sample),Works_In4 does not allow an employee to work in a department for two or more periods. Similar to the problem of wanting to record several addresses for an employee: We want to record several values of the descriptive attributes for each instance of this relationship. Accomplished by introducing new entity set, Duration.,Works_In4,from,to,budget,Departments,name,Departments,ssn,lot,Employees,Works_In4,Entity vs. Relationship,First ER diagram OK if a manager gets a separate discretionary budget for each dept. What if a manager gets a discretionary budget that covers all managed depts? Redundancy: dbudget stored for each dept managed by manager. Misleading: Suggests dbudget associated with department-mgr combination.,Manages2,name,dname,budget,did,Employees,Departments,ssn,lot,dbudget,since,dname,budget,did,Departments,Manages2,Employees,name,ssn,lot,since,Managers,dbudget,ISA,This fixes the problem!,Binary vs. Ternary Relationships,If each policy is owned by just 1 employee, and each dependent is tied to the covering policy, first diagram is inaccurate. What are the additional constraints in the 2nd diagram?,age,pname,Dependents,Covers,age,pname,Dependents,Purchaser,Bad design,Better design,Binary vs. Ternary Relationships (Contd.),Previous example illustrated a case when two binary relationships were better than one ternary relationship. An example in the other direction: a ternary relation Contracts relates entity sets Parts, Departments and Suppliers, and has descriptive attribute qty. No combination of binary relationships is an adequ