Relational Database Design and EER-to-Relational Mapping

EER Model Constructs Mapping

• Mapping Specialization/Generalization:

  • Options for mapping subclasses derived from a superclass:

  • Option 8A:

    • Create relation for superclass and separate relations for each subclass.

    • Works for any specialization (total/partial, disjoint/overlapping).

  • Option 8B:

    • Create relations for subclasses only.

    • Only works for total specializations with disjoint constraints.

  • Option 8C:

    • Create a single relation with a type attribute to indicate subclass.

    • Suited for disjoint subclasses only, potential for NULL values.

  • Option 8D:

    • Create a single relation with multiple Boolean type attributes, indicating subclass membership.

    • Applicable for overlapping subclasses as well.

Mapping Details

• Specialization Mapping (Step 8):

  • Use Attrs(R) for attributes of relation R and PK(R) for primary key of R.

  • Option 8A:

  • Example: L for superclass C and relations Li for subclasses each including the primary key of C.

  • Option 8B:

  • No superclass relation, must apply OUTER UNION to retrieve entities in superclass.

• NULL Values Management:

  • For options 8C and 8D, entities not in subclasses can show NULL for subclass attributes.

Mapping of Shared Subclasses

• Shared Subclass Example:

  • ENGINEERING_MANAGER subconnected to multiple superclasses.

  • Any mapping option may be applied as per restrictions.

Categories (Union Types) Mapping

• Union Types Overview:

  • Category is a subclass of a union of different superclasses.

  • New surrogate key is created for entities in the category.

  • Example: OWNER category as a union of PERSON, BANK, and COMPANY.

  • Foreign key added in each superclass' relation to maintain correspondence.