CSC 426 Chapter 08, Fall 2011

Chapter 8 Mapping a Conceptual Design into a Logical Design Chapter 8 Outline  Conceptual Database Design  Relational Database Design Using ER-to- Relational Mapping  Mapping EER Model Constructs to Relations What is a design methodology?  A structured approach that uses procedures, techniques, tools, and documentation aids to support and facilitate the process of design. 3 Phases of database design  Three main phases: ◦ conceptual database design, ◦ logical database design, ◦ physical database design.  Conceptual database design ◦ The process of constructing a model of the data used in an organization independent of all physical considerations. 4 Phases of database design  Logical database design ◦ Process of constructing a model of data used in an organization based on a specific data model, but independent of a particular DBMS and other physical considerations. 5 Phases of database design  Physical database design ◦ Process of producing a description of the implementation of the database on secondary storage; it describes the base tables, file organizations, and indexes used to achieve efficient access to the data, and any associated integrity constraints and security restrictions. 6 Critical success factors in database design  Work interactively with the users as much as possible.  Follow a structured methodology throughout the data modeling process.  Employ a data-driven approach.  Incorporate structural and integrity considerations into the data models.  Use normalization and transaction validation techniques in the methodology. 7 Critical success factors in database design  Use diagrams to represent as much of the data models as possible.  Use a database design language (DBDL).  Critical success factors in database design  Build a data dictionary to supplement the data model diagrams.  Be willing to repeat steps. 8 Overview of the database design methodology 9 Step 1: Conceptual database design (Create ER model)  Step 1.1 Identify entities  Step 1.2 Identify relationships  Step 1.3 Identify and associate attributes with entities or relationships  Step 1.4 Determine attribute domains  Step 1.5 Determine candidate, primary, and alternate key attributes 10 Step 1: Conceptual database design methodology (Create ER model)  Step 1.6 Specialize/Generalize entities (optional step)  Step 1.7 Check model for redundancy  Step 1.8 Check model supports user transactions  Step 1.9 Review conceptual database design with users 11 Extract from the data dictionary 12 Main relationships for StayHome Online Rentals 13 First draft ER diagram 14 Multiplicity constraints for the relationships 15 Adding multiplicity constraints to the ER diagram 16 Extract from the data dictionary 17 ER diagram for StayHome Online Rentals showing primary keys 18 Check ER model supports data requirements 19 ER diagram showing all entities, relationships, and attributes 20 Relational Database Design by ER- and EER-to-Relational Mapping  Design a relational database schema  Based on a conceptual schema design  Seven-step algorithm to convert the basic ER model constructs into relations  Additional steps for EER model Relational Database Design Using ER-to-Relational Mapping ER-to-Relational Mapping Algorithm  COMPANY database example  Assume that the mapping will create tables with simple single-valued attributes  Step 1: Mapping of Regular Entity Types  For each regular entity type, create a relation R that includes all the simple attributes of E  Called entity relations • Each tuple represents an entity instance ER-to-Relational Mapping Algorithm (cont’d.)  Step 2: Mapping of Weak Entity Types  For each weak entity type, create a relation R and include all simple attributes of the entity type as attributes of R  Include primary key attribute of owner as foreign key attributes of R ER-to-Relational Mapping Algorithm (cont’d.) ER-to-Relational Mapping Algorithm (cont’d.)  Step 3: Mapping of Binary 1:1 Relationship Types  For each binary 1:1 relationship type R • Identify relations S and T that correspond to entity types participating in R  3 Possible approaches: • Foreign key approach :Choose the entity type with total participation –S, say-and include in S the primary key of T, and include all simple attributes of R • Merged relation approach: Merge the 2 entities S and T into a single relation(do it when both participation are total) • Cross-reference approach: Create a 3rd relation Rfor cross- referencing the primary keys of S and T (similar to step 5) 1:1 relationship mandatory both entities (a) ER diagram (b) table 28 1:1 relationship mandatory one entity only (a) ER diagram (b) tables 29 1:1 relationship mandatory one entity only (a) ER diagram (b) tables 30 1:1 relationship optional both entities (a) ER diagram (b) tables 31 ER-to-Relational Mapping Algorithm (cont’d.)  Step 4: Mapping of Binary 1:N Relationship Types  For each regular binary 1:N relationship type • Identify relation that represents participating entity type at N-side of relationship type • Include primary key of other entity type as foreign key in S • Include simple attributes of 1:N relationship type as attributes of S ER-to-Relational Mapping Algorithm (cont’d.)  Step 5: Mapping of Binary M:N Relationship Types  For each binary M:N relationship type • Create a new relation S • Include primary key of participating entity types as foreign key attributes in S • Include any simple attributes of M:N relationship type *:* relationship (a) ER diagram (b) tables 34 ER-to-Relational Mapping Algorithm (cont’d.)  Step 6: Mapping of Multivalued Attributes  For each multivalued attribute A • Create a new relation R • R includes an attribute corresponding to A, plus the primary Key attribute K (from the entity that has A a multivalue) as a foreign key in R • Primary key of R is the combination of A and K Multi-valued attribute (a) ER diagram (b) tables 36 ER-to-Relational Mapping Algorithm (cont’d.)  Step 7: Mapping of N-ary Relationship Types  For each n-ary relationship type R • Create a new relation S to represent R • Include primary keys of participating entity types as foreign keys • Include any simple attributes as attributes Complex (ternary) relationship (a) ER diagram (b) tables 38 Discussion and Summary of Mapping for ER Model Constructs Mapping EER Model Constructs to Relations  Extending ER-to-relational mapping algorithm Mapping of Specialization or Generalization  Step 8: Options for Mapping Specialization or Generalization (see pages 294-295)  Option 8A: Multiple relations—superclass and subclasses • For any specialization (total or partial, disjoint or overlapping)  Option 8B: Multiple relations—subclass relations only • Subclasses are total • Specialization has disjointedness constraint Mapping of Specialization or Generalization (cont’d.)  Option 8C: Single relation with one type attribute • Type or discriminating attribute indicates subclass of tuple • Subclasses are disjoint • Potential for generating many NULL values if many specific attributes exist in the subclasses  Option 8D: Single relation with multiple type attributes • Subclasses are overlapping • Will also work for a disjoint specialization Mapping of Shared Subclasses (Multiple Inheritance)  Apply any of the options discussed in step 8 to a shared subclass Summary  Map conceptual schema design in the ER model to a relational database schema  Algorithm for ER-to-relational mapping  Illustrated by examples from the COMPANY database  Include additional steps in the algorithm for mapping constructs from EER model into relational model