cmiller1137 and Ron McFadyen
We use an Entity Relationship Diagram to represent the informational needs of a system. When we are convinced it is satisfactory, we map the Entity Relationship Diagram (ERD) to a relational database and implement it as a physical database. In general, relations are used to hold entity sets and to hold relationship sets. The considerations to be made are listed below. After we present the mapping rules, we illustrate their application in a few examples.
8.1 MAPPING RULES
To complete the mapping from an Entity Relationship Diagram (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 primary key (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) above. 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 examples. examples 1 – 3 deal with binary relationships and example 4 concerns n-ary relationships.
-
- Binary One-To-One
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
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
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
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 o