Chapter 9 ER and EER Mapping to Relational Schema CS 6360 Database Design Chris Irwin Davis Ph D Email cid021000 utdallas edu Phone 972 883 3574 O ce ECSS 4 705 Chapter 9 Outline Relational Database Design Using ER toRelational Mapping Mapping EER Model Constructs to Relations Relational Database Design by ERand 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 model COMPANY ER Diagram COMPANY Relation Schema ER 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 instance ER Diagram Step 1 Relational Model Step 1 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 Diagram Step 2 Essn Relational Model Step 2 ER 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 approach Step 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 3 Relational Model Step 3 Mgr ssn Step 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 Approach EMPLOYEE ssn fname PROFILE minit lname minit lname ssn EMPLOYEE ssn fname address dno address dno Step 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 EMPLOYEE ssn CROSS REFERENCE TABLE ssn dno start date DEPARTMENT dnumber 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 Diagram Step 4 Relational Model Step 4 EMPLOYEE Fname Minit Lname Ssn Bdate Address DEPARTMENT Dname Dnumber Mgr ssn Mgr start date DEPT LOCATIONS Dnumber Dlocation DEPENDENT Essn Dependent name Sex Bdate Relationship Sex Salary Super ssn Dno ER 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 relationships 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 ER Diagram Step 5 Relational Model Step 5 EMPLOYEE Fname Minit Lname Ssn Bdate Address DEPARTMENT Dname Dnumber Mgr ssn Mgr start date PROJECT Pname Pnumber Plocation Dnum WORKS ON Essn Pno Hours DEPENDENT Essn Dependent name Sex Bdate Relationship Sex Salary Super ssn Dno ER 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 components ER Diagram Step 6 Relational Diagram Step 6 EMPLOYEE Fname Minit Lname Ssn Bdate Address DEPARTMENT Dname Dnumber Mgr ssn Mgr start date DEPT LOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKS ON Essn Pno Hours DEPENDENT Essn Dependent name Sex Bdate Relationship Sex Salary Super ssn Dno Step 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 attributes Step 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 7 Discussion 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 key Discussion and Summary of Mapping for ER Model Constructs Mapping EER Model Constructs to Relations Extending ER to relational mapping algorithm Adds additional steps to mapping algorithm Mapping of Specialization or Generalization Step 8 Options for Mapping Specialization or Generalization see pages 294 295 Option 8A
View Full Document
Unlocking...