8.1.2: Relationship Types

The implementation of relationships involves foreign keys. Recall, as discussed under Weak Entities (previous page), 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. The first three bullet-points deal with binary relationships and the last bullet-point 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 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.