Database - Chapter 4 Part 4

Define a recursive foreign key

A foreign key in a relation that references the primary key in the same relation

What to do for unary relationships (M:N) when transforming into a relation

Create two relations: One, "R", representing the entity in the relationship, and the other, "S", to represent the M:N relationship itself The primary key for "S" is a composite key consisting of two copies of the primary key of "R"; each copy is a FK into "R"

What to do for unary relationships (1:M) when transforming into a relation

Create a relation "R" corresponding to the entity as normal Include a foreign key in "R" that references the primary key value in "R"

What to do for binary relationships (M:N) when transforming into a relation

Create the relations for the two entities as normal Create a new relation "c" (this is the associative entity) Primary key for "c" is the composite of the attributes making up the primary keys of the two original entities Include in "c" any nonkey attributes that are associated with the M:N relationship Note: The primary keys of the two original entities, when placed in the new relation "c", become foreign keys into their respective relations.

What to do if you have an associative entity

Create three relations: two for the participating entity types and one for the associative entity

What to do for binary relationships (1:M) when transforming into a relation (2 things)

Create the relations for the two entities as normal Include the primary key attributes of the entity on the one-side of the relationship as a FK in the relation on the many-side of the relation The primary key migrates to the many side

What to do for binary relationships (1:1) when transforming into a relation

Create the two entities as normal Include the primary key of one of the relations (choose either one) as a foreign key in the other relation If one direction is optional one and the other is mandatory one, then it is better to include the primary key of the relation on the mandatory side as a FK in the relation on the optional side (then the FK will never be null) If the relationship has an attribute, place that attribute in the same relation that contains the FK

What to do when an associative entity has a partial identifier

The primary key for the associative entity is the composite key consisting of the attributes making up the two primary keys of the other two relations, along with the partial identifier of the relation corresponding to the associative entity. The components of the primary key of the associative entity that were originally primary keys in other relations become foreign keys into their respective relations

What to do if the associative entity does not have an identifier

Then primary key for this entity will be the composite key consisting of the attributes making up the two primary keys of the other two relations. The components of the primary key of the associative entity become foreign keys into their respective relations

What to do for ternary relationships when transforming into a relation

This relationship should be modeled as an associative entity Create four relations, one for each of the three participating entity types and one for the associative entity The default primary key for the relation corresponding to the associative entity consists of the primary key attributes for the three participating entities The primary key attributes of the participating entity types become foreign keys in the relation corresponding to the associative entity. Attributes of the associative entity type become attributes of the new relation Note: Additional attributes may sometimes be necessary, or a surrogate identifier may be used

What to do when the associative entity has a full identifier

When it has a full identifier, this is called a "surrogate key"; it is the primary key of the relation corresponding to the associative entity. Primary keys of the other two relations are included in the relation for the associative entity as FKs