To complete the mapping from an ERD to relations we must consider the entity types, relationship types, and attributes that are specified for the model.
Entity Types
Each entity type is implemented with a separate relation. Entity types are either strong entity types or weak entity types.
Strong Entities
Strong, or regular, entity types are mapped to their own relation. The PK is chosen from the set of keys available.
Weak Entities
Weak entity types are mapped to their own relation, but the primary key of the relation is formed as follows. If there are any identifying relationships, then the PK of the weak entity is the combination of the PKs of entities related through identifying relationships and the discriminator of the weak entity type; otherwise the PK of the relation is the PK of the weak entity.
Relationship Types
The implementation of relationships involves foreign keys. Recall, as discussed in point 1.b) above, that if the relationship is identifying, then the primary key of an entity type must be propagated to the relation for a weak entity type. We must consider both the degree and the cardinality of the relationship. In the following (1), (2), and (3) deal with binary relationships and (4) concerns n-ary relationships.
Binary One-To-One (1)
In general, with a one-to-one relationship, a designer has a choice regarding where to implement the relationship. One may choose to place a foreign key in one of the two relations, or in both. Consider placing the foreign key such that nulls are minimized. If there are attributes on the relationship those can be placed in either relation.
Binary One-To-Many (2)
With a one-to-many relationship the designer must place a foreign key in the relation corresponding to the ‘many’ side of the relationship. Any other attributes defined for the relationship are also included on the ‘many’ side.
Binary Many-To-Many (3)
A many-to-many relationship must be implemented with a separate relation for the relationship. This new relation will have a composite primary key comprising the primary keys of the participating entity types and any discriminator attribute, plus other attributes of the relationship if any.
n-ary, n>2 (4)
A new relation is generated for an n-ary relationship. This new relation has a composite primary key comprising the n primary keys of the participating entity types and any discriminator attribute, plus any other attributes. There is one exception to the formation of the PK: if the cardinality related for any entity type is 1, then the primary key of that entity type is only included as a foreign key and not as part of the primary key of the new relation.
Attributes
All attributes, with the exception of derived and composite attributes, must appear in relations. You choose to include derived attributes if their presence will improve performance. In the following we consider attributes according to whether they are simple, atomic, multi-valued, or composite.
Simple, atomic
These are included in the relation created for the pertinent entity type, many-to-many relationship, or n-ary relationship.
Multi-valued
Each multi-valued attribute is implemented using a new relation. This relation will include the primary key of the original entity type. The primary key of the new relation will be the primary key of the original entity type plus the multi-valued attribute. Note that in this new relation, the attribute is no longer multi-valued.
[Composite and Derived attributes are not included].
The above constitutes the standard rules for mapping an ERD to relations. A designer may make other choices but one expects there would be good reasons for doing so.
