Chris Irwin Davis, Ph.D.!!Email: [email protected]!Phone: (972) 883-3574!Office: ECSS 4.705Chapter 9: ER and EER Mapping to Relational SchemaCS-6360 Database DesignChapter 9 Outline§Relational Database Design Using ER-to-Relational Mapping!§Mapping EER Model Constructs to RelationsRelational 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!§COMPANY database example!§Assume that the mapping will create tables with simple single-valued attributes!§Additional steps for EER modelCOMPANY ER DiagramCOMPANY Relation SchemaER-to-Relational Mapping Algorithm§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 instanceER Diagram: Step 1Relational Model: Step 1ER-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 RER Diagram: Step 2EssnRelational Model: Step 2ER-to-Relational Mapping Algorithm (cont’d.)§Step 3: Mapping of Binary 1:1 Relationship Types!§For each binary 1:1 relationship type !•Identify relations that correspond to entity types participating in R §Possible approaches: !• Foreign key approach • Merged relationship approach • Crossreference or relationship relation approachStep 3: Foreign Key Approach§Step 3: Mapping of Binary 1:1 Relationship Types!§Foreign key approach!§Choose one of the relations—S, say—and include as a foreign key in S the primary key of T. It is better to choose an entity type with total participation in R in the role of S. !§Include all the simple attributes (or simple components of composite attributes) of the 1:1 relationship type R as attributes of S.ER Diagram: Step 3Relational Model: Step 3Mgr_ssnStep 3: Merged Relationship Approach§Step 3: Mapping of Binary 1:1 Relationship Types!§Merged relationship approach!§An alternative mapping of a 1:1 relationship type is to merge the two entity types and the relationship into a single relation.!§This is possible when both participations are total, as this would indicate that the two tables will have the exact same number of tuples at all times.Step 3: Merged Relationship ApproachssnEMPLOYEEssnPROFILEfname minit address dnolnamessnEMPLOYEEfname minit address dnolnameStep 3: Merged Relationship Approach§Step 3: Mapping of 1:1 Relationship Types!§Crossreference or relationship relation approach!§The third option is to set up a third relation R for the purpose of cross-referencing the primary keys of the two relations S and T representing the entity types. !§As we will see, this approach is required for binary M:N relationships. The relation R is called a relationship relation (or “lookup table”), because each tuple in R represents a relationship instance that relates one tuple from S with one tuple from T.ER Diagram: Step 3 (optional)Crossreference or relationship relation approach (Step 3 alternative)ssn dno dnumberssnEMPLOYEE DEPARTMENTstart_dateCROSS REFERENCE TABLEER-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 SER Diagram: Step 4Relational Model: Step 4Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn DnoDnameDnumberMgr_ssn Mgr_start_dateDlocationDnumberEssn Dependent_name BdateSex RelationshipEMPLOYEEDEPARTMENTDEPT_LOCATIONSDEPENDENTER-to-Relational Mapping Algorithm (cont’d.)§Alternative approach !•Use the relationship relation (cross-reference “relationship table”) option as in the third option for binary 1:1 relationshipsER-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 typeER Diagram: Step 5Relational Model: Step 5Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn DnoDname Mgr_ssn Mgr_start_dateDnumberPname Pnumber Plocation DnumEssn Pno HoursEssn Dependent_name BdateSex RelationshipEMPLOYEEDEPARTMENTPROJECTWORKS_ONDEPENDENTER-to-Relational Mapping Algorithm (cont’d.)§Step 6: Mapping of Multivalued Attributes!§For each multivalued attribute!•Create a new relation!•Primary key of R is the combination of A and K •If the multivalued attribute is composite, include its simple componentsER Diagram: Step 6Relational Diagram: Step 6Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn DnoDnameDnumberMgr_ssn Mgr_start_dateDlocationDnumberPname Pnumber Plocation DnumEssn Pno HoursEssn Dependent_name BdateSex RelationshipEMPLOYEEDEPARTMENTDEPT_LOCATIONSPROJECTWORKS_ONDEPENDENTStep 7: Mapping of N-ary Relationship Types§Step 7: Mapping of N-ary Relationship Types!§For each n-ary relationship type R •Create a new relation (i.e. table) S to represent R •Include primary keys of participating entity types as foreign keys!•Include any simple attributes as attributesStep 7: Mapping of N-ary Relationship Types§Step 7: Mapping of N-ary Relationship Types!§The primary key of S is usually a combination of all the foreign keys that reference the relations representing the participating entity types. !§However, if the cardinality constraints on any of the entity types E participating in R is 1, then the primary key of S should not include the foreign key attribute that references the relation Eʹ′ corresponding to E.Step 7Discussion and Summary of Mapping for ER Model Constructs (cont’d.)§In a relational schema, ER Relationship Types are not always represented explicitly!§Depends on Cardinality and Participation!§May represented by having two attributes A and B: one a primary key and the other a foreign
View Full Document
Unlocking...