EER to Relational Mapping

EER to Relational Model Process

• Cyclical and iterative
• Model is refined as entities and relationships are added
• Mostly algorithmic with just a few decisions to make

​

​

EER to Relational Mapping Categories

1. Regular and weak entities
2. Non-Identifying relationships
3. Many-to-many relationships
4. Identifying relationships
5. Multi-valued attributes
6. Entities in a specialization hierarchy�(subclasses and superclasses)

​

The next six slides explain each category in detail.

(1) Mapping Entities

• A regular entity becomes a relation/table
• Regular entity attributes:
• A simple attribute becomes an column of the table
• All the components of a composite attribute become individual columns of the table
• The key attribute becomes the PK of the table
• A weak entity also becomes a table
• The simple and composite attributes behave the same

(2) Mapping Non-Identifying Relationships

• 1-to-N relationship:
• The PK from the table on the 1 side of the relationship becomes a FK in the table on the N side
• Any attributes on the relationship move into the table on the N side
• 1-to-1 relationship:
• The PK from either table can become a FK in the other table
• Any attributes on the relationship move into the table with the FK

(3) Mapping M-N Relationships

• M-to-N relationship:
• Create a new table
• The PK’s from both sides become FK’s in the new table
• The combination of the FK’s become the PK of the new table
• Any attributes on the relationship go into the new table

(4) Mapping Identifying Relationships

• 1-to-N identifying relationship:
• The PK from the table on the 1 side of the relationship becomes a FK in the table on the N side
• Any attributes on the relationship move into the table on the N side
• In the weak-side table, the combination of the new FK and the original partial key become the primary key of the new table
• 1-to-1 identifying relationship:
• The PK from identifying side becomes a FK and the PK in the weak-side table
• Any attributes on the relationship move into the table with the FK
• Another option is to merge the 2 tables

(5) Mapping Multi-valued Attributes

• A multi-valued attribute requires a new table which contains:
• The primary key of the table corresponding to the entity with the multi-valued attribute
• The attribute that was multi-valued
• The combination of all the attributes become the PK of the new table

(6) Mapping Specialization Hierarchies

• Multiple choices:
• Each subclass can become its own table
• Each with all the attributes of the superclass
• The superclass can disappear
• Not a good choice for overlapping specialization hierarchies
• Subclasses can be modeled the same way as if they have 1-to-1 identifying relationships with the superclass
• All classes in the hierarchy can be combined into one table, including all attributes, using boolean or type-flag fields to differentiate between the subclasses
• Only if the number of specific attributes is very small
• A combination of approaches depending on the context

Decisions (to make manually)

• How to handle sub- and super-class entities
• Which side of a non-identifying 1-1 relationship�gets the foreign key
• Whether or not to include derived attributes in the relational model

​

Group Activity: EER to relational mapping