Database(14) -- The Entity/Relationship Model

Roles in Relationships

Each line to the entity set represents a different role that the entity set plays in the relationship.

We assume that a movie may have many sequels, but for each sequel there is only one original movie. Thus, the relationship is many-one from Sequel movies to Original movies.

Attributes on Relationships

We may place one or more attributes on any relationship.

For example, we can associate a unique salary with the (star, movie, studio) triple in the relationship set for the Contracts relationship.

We can also invent a new entity set. In general, when we do a conversion from attributes on a relationship to an additional entity set, we place an arrow into that entity set.

Converting Multiway Relationships to Binary

Introduce a new entity set whose entities we may think of as tuples of the relationship set for the multiway relationship.

We call this entity set a connecting entity set.

Subclasses in the E/R Model

We connect an entity set to its subclasses using a relationship called isa. Use a triangle.

Design Principles

Faithfulness

Should reflect reality. Consider, for instance, entity sets Courses and Instructors, with a relationship Teaches between them. Is Teaches many-one from Courses to Instructors? It depends.

Avoiding Redendancy

We should be careful to say everything once only.

Simplicity Counts

Choosing the Right Relationships

Picking the Right Kind of Element

Suppose E is an entity set. Here are conditions that E must obey in order for us to replace E by an attribute or attributes of several other entity sets.

1. All relationships in which E is involved must have arrows entering E. That is, E must be the “one” in many-one relationships, or its generalization for the case of multiway relationships.
2. If E has more than one attribute, then no attribute depends on the other attributes, the way address depends on name for Studios. That is, the only key for E is all its attributes.
3. No relationship involves E more than once.

Constraints in the E/R Model

Keys in the E/R Model

A key for an entity set E is a set K of one or more attributes such that, given any two distinct entites e1 and e2 in E, e1 and e2 cannot have identical values for each of the attributes in the key K.

• Every entity set must have a key, although in some cases – isa-hierarchies and “weak” entity sets, the key actually belongs to another entity set.
• There can be more than one possible key for an entity set. However, it is customary to pick one key as the “primary key,” and to act as if that were the only key.
• When an entity set is involved in an isa-hierarchy, we require that the root entity set have all the attributes needed for a key, and that the key for each entity is found from its component in the root entity set, regardless of how many entity sets in the hierarchy have components for the entity.

In our E/R-diagram notation, we underline the attributes belonging to a key for an entity set.

We underline only the primary key.

Referential Integrity

Suppose R is a relationship from entity set E to entity set F. A rounded arrowhead pointing to F indicates not only that the relationship is many-one from E to F, but that the entity of set F related to a given entity of set E is required to exist.

Degree Constraints

In the E /R model, we can attach a bounding number to the edges that connect a relationship to an entity set, indicating limits on the number of entities that can be connected to any one entity of the related entity set.

As another example, we can think of the arrow as a synonym for the constraint “< 1,” and we can think of the rounded arrow as standing for the constraint “= 1.”