ER and EER Mapping to Relational Schema

Chapter 9: ER and EER Mapping to Relational Schema

Author: Chris Irwin Davis
University: The University of Texas at Dallas, Erik Jonsson School of Engineering and Computer Science

Chapter 9 Outline

• Relational Database Design Using ER-to-Relational Mapping

• Mapping EER Model Constructs to Relations

Overview of ER- and EER-to-Relational Mapping

• Objective: Design a relational database schema based on a conceptual schema design.

• Algorithm: A seven-step algorithm to convert the basic Entity-Relationship (ER) model constructs into relations.

• Example: Implementation using the COMPANY database example, assuming that mapping creates tables with simple single-valued attributes.

• EER Model: Additional steps are provided specifically for Extended Entity-Relationship (EER) models.

COMPANY ER Diagram

• Structural Elements of the COMPANY Database:

  • Employee Attributes:

  • Fname

  • Minit

  • Lname

  • Bdate

  • Address

  • Salary

  • Ssn

  • Sex

  • Department Attributes:

  • Name

  • Numberofemployees

  • Locations

  • Project Attributes:

  • Name

  • Location

  • Number

  • Dependent Attributes:

  • Name

  • Sex

  • Birth_date

  • Relationship

  • Relationships:

  • WORKS_FOR

  • MANAGES

  • WORKS_ON

  • SUPERVISION

COMPANY Relation Schema

• Relations Created from ER Schema:

  • EMPLOYEE Relation:

  • Attributes: Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno

  • DEPARTMENT Relation:

  • Attributes: Dname, Dnumber

  • DEPT_LOCATIONS Relation:

  • Attributes: Dnumber, Dlocation, Mgrssn, Mgrstart_date

  • PROJECT Relation:

  • Attributes: Pname, Pnumber, Plocation, Dnum

  • WORKS_ON Relation:

  • Attributes: Essn, Pno, Hours

  • DEPENDENT Relation:

  • Attributes: Essn, Dependent_name, Sex, Bdate, Relationship

ER-to-Relational Mapping Algorithm

Step 1: Mapping of Regular Entity Types

• Process: For each regular entity type, create a relation R that includes all the simple attributes of E.

• Diagram: Each tuple represents an entity instance.

• Resulting Relations:

  • EMPLOYEE: Fname, Minit, Lname, Ssn, Bdate, Address, Salary, Sex

  • DEPARTMENT: Dname, Dnumber

  • PROJECT: Pname, Pnumber, Plocation

Step 2: Mapping of Weak Entity Types

• Process: For each weak entity type, create a relation R including all simple attributes as attributes of R and include primary key attribute of owner as foreign key attributes of R.

Step 3: Mapping of Binary 1:1 Relationship Types

• Identification: Identify relations that correspond to entity types participating in R.

• Approaches:

  • Merged Relationship Approach: Merge two entity types and the relationship into a single relation.

  • Requirements: Both participations must be total (exact same number of tuples).

  • Foreign Key Approach: Choose one relation (e.g., S), and include the primary key of T as a foreign key in S. Must choose an entity type with total participation in R as S.

  • Crossreference or Relationship Relation Approach: Set up a third relation to cross-reference primary keys of the two relations representing the entity types.

Step 4: Mapping of Binary 1:N Relationship Types

• Process: Identify relation representing the N-side of relationship; include primary key of other entity type as foreign key in S, including simple attributes of the 1:N relationship type as attributes of S.

Step 5: Mapping of Binary M:N Relationship Types

• Process: Create a new relation S for each binary M:N relationship type including primary keys of participating entity types as foreign key attributes in S, plus any simple attributes of the M:N relationship type.

Step 6: Mapping of Multivalued Attributes

• Process: Create a new relation for each multivalued attribute. The primary key of R is the combination of attribute A and its key K. If composite, include simple components.

Step 7: Mapping of N-ary Relationship Types

• Process: Create a new relation S to represent the n-ary relationship R, including primary keys as foreign keys and any simple attributes as attributes.

• Primary Key: Usually a combination of all foreign keys referencing the participating entity types. If any participation is 1, do not include that foreign key in S.

Discussion and Summary of Mapping for ER Model Constructs

• Key Points:

  • In a relational schema, not all ER relationship types are explicitly represented. This depends on cardinality and participation.

  • May be represented by having two attributes A (primary key) and B (foreign key).

Comparison Table: ER and Relational Models

Mapping EER Model Constructs to Relations

• Additional Steps Required: Extending the ER-to-relational mapping algorithm to include additional constructs.

Mapping of Specialization or Generalization

Step 8: Options for Mapping Specialization or Generalization

• Option 8A: Multiple relations for superclass and subclasses - applicable for partial or total specialization, disjoint or overlapping.

• Option 8B: Multiple relations for subclass relations only, suitable when subclasses are total.

• Option 8C: Single relation with one type attribute indicating subclass of tuple - may lead to many NULL values in subclasses.

• Option 8D: Single relation with multiple type attributes mapping superclass; applies for overlapping and disjoint specializations.

Example Diagram: Mapping Specialization

• Specialization Note: Continuous monitoring and triggers may be required to enforce various constraints (e.g., disjointness, partial participation).

Mapping of Shared Subclasses (Multiple)

• An illustrative example for shared subclasses and categories, outlining complexities of union types (categories).

Conclusion and Summary

• Objective: To map conceptual schema design in the ER model to a relational database schema.

• Includes an algorithm for ER-to-relational mapping illustrated by examples from the COMPANY database.

• Additional steps for mapping constructs from EER models into relational models are presented.

Figures and Diagrams

• Include various figures demonstrating ER diagrams, relational models, and mapping techniques across steps 1 to 9.

Note to Student: This study guide serves as a comprehensive resource reflecting the contents of the original source material, covering essential concepts and detailed mappings necessary for understanding ER and EER to relational schema transformation.