ER Logical Schema Schema Integration 2020 08 updated

從 ER 到 Logical Schema ──兼談Schema Integration 國立中央大學 資訊管理系 范錚強 2020. 08 updated 中央大學。范錚強 1

資料模型和流程模型的整合 修訂模式 修改DFD中的data store，消除所有「迷糊帳」 所有data store相對到相關的實體或關係 建立資料典DD: Data Dictionary 平衡Balancing原則 Every data flow and data store must be defined in the DD, Vice Versa Every data store must correspond to an entity or relationship type Object names in the DD, ERD and DFD must match DD entries must apply to both the DFD and ERD model 中央大學。范錚強 3

充實、調整資料模型 Attributing and Refining Attributing all entity and relationship types Each attribute should associate with the corresponding entity or relationship type The value of the attribute should solely depends (fully functionally dependent, FDD) on the associated element in the entity or relationship type Identify an identifying attributes for each entity type and any compound entity type If there are many candidates, pick one that is the most appropriate. 中央大學。范錚強 4

充實、調整資料模型(2) Eliminate all multi-valued attributes with appropriate relationships Attribute the new E/R types Name n Name m Company 中央大學。范錚強 5

資料模型、邏輯資料庫設計、 實體資料庫設計 Process E-R data model --> Logical database design --> physical database design ANSI SPARC three tier DB architecture Enterprise views User views Physical views user Enterprise Physical 中央大學。范錚強 6

設計關連式資料庫 Logical Schema For each entity type: Each Entity type becomes a Table Every attribute becomes a column The identifying attribute for the entity type becomes the key for the Table For each m-n relationship type Each m-n relationship type becomes a Table Every attribute becomes a column The identifying attribute of the associated entity types becomes the compound keys 中央大學。范錚強 7

關連式資料庫 Logical Schema (2) For each 1 -n relationship type Same as m-n relationship Only the n-side identifier becomes the key, the 1 -side identifier becomes a non-key (foreign key) column For each 1 -1 relationship type Same as 1 -n relationship Pick the identifier from the most appropriate associated entity type as the key, the other a foreign key 中央大學。范錚強 8

關連式資料庫 Logical Schema (3) For each compound entity type For the compound entity types that do not have its own identifier, same as relationship Otherwise, use the identifier as the key, and take the identifying attribute of the associated entity types as non-key (foreign key) columns Annotate the foreign key “references” in the DD of the DBMS To preserve referential constraints 中央大學。范錚強 9

深入了解data behavior Examine the properties and behavior of tables and data Behavior? Access requirements for tables and attributes Access frequency Natural clusters in data processing Optimize systems performance based on data behavior 中央大學。范錚強 12

實體資料庫設計 單一資料庫 可考慮將單一欄位的Table取消，但注意 Referential constraints Domain specification 合併或分解Tables Combine tables with the same key or same partial keys as appropriate (may result in lower normal forms) Split a Table into multiple tables, to maximize operational efficiency 中央大學。范錚強 13