A relationship can also have descriptive attributes, to record additional information about the relationship (as opposed to about any one participating entity).
For example, I am an entity, as is the Department of Math/CS. A relationship exists in that I work in that department.
Similarly, a relationship set is a set of "similar" relationships (the similarity is based on the type of underlying entities involved in each such relationship) For example, if you have an entity set Employees and another entity set Departments, you might define a relationship set Works_In which associates members of those two entity sets.
In the ER diagrams, we will draw a relationship set is drawn as a shaded diamond:
A (binary) relationship set can further be classified as either
Sometimes, an additional constraint exists for a given relationship set, that any entity from one of the associated sets appears in at most one such relationship. For example, consider a relationship set "Manages" which associates departments with employees. If a department cannot have more than one manager, this is an example of a one-to-many relationship set (it may be that an individual manages multiple departments).
This type of constraint is called a key constraint. It is represented in the ER diagrams by drawing an arrow from an entity set E to a relationship set R when each entity in an instance of E appears in at most one relationship in (a corresponding instance of) R.
An instance of this relationship is given in Figure 2.7.
If both entity sets of a relationship set have key constraints, we would call this a "one-to-one" relationship set. In general, note that key constraints can apply to relationships between more than two entities, as in the following example.
An instance of this relationship:
If this is required, we call this a total participation constraint; otherwise the participation is partial. In our ER diagrams, we will represent a total participation constraint by using a thick line.
Usually, this is the case only because the information represented in such an entity set is only interesting when combined through an identifying relationship set with another entity set we call the identifying owner.
We will call such a set a weak entity set, and insist on the following:
In our ER diagrams, we will represent a weak entity set by outlining the entity and the identifying relationship set with dark lines. The required key constraint and total participation are diagrammed with our existing conventions. We underline the partial key with a dotted line.
Furthermore, we can impose additional constraints on such subclassing. By default, we will assume that two subclasses of an entity set are disjoint. However, if we wish to allow an entity to lie in more than one such subclass, we will specify an overlap constraint. (e.g. "Contract_Emps OVERLAPS Senior_Emps")
Dually, we can ask whether every entity in a superclass be required to lie in (at least) one subclass. By default we will not assume not, but we can specify a covering constraint if desired. (e.g. "Motorboats AND Cards COVER Motor_Vehicles")
A motivating example follows:
Adding a new entity set is more complex model. It should only be done when there is need for the complexity. For example, if some employees have multiple address to be associated, then the more complex model is needed. Also, representing addresses as a separate entity would allow a further breakdown, for example by zip code or city.
If the duration is described through descriptive attributes, only a single such duration can be modeled. That is, we could not express an employment history involving someone who left the department yet later returned.
Which scenario is represented by the following ER diagram? If you want the alternate interpretation, how would you adjust the model?
What if we wish to model the following additional requirements:
The best way to model this is to switch away from the ternary relationship set, and instead use two distinct binary relationship sets.
If we did not need the until or since attributes. In tihs case, we could model the identical setting using the following ternary relationship:
Let's compare these two models. What if we wanted to add an additional constraint to each, that each sponsorship (of a project by a department) be monitored by at most one employee. Can you add this constraint to either of the above models?