A relationship is an association among two or more entities. For example, we may have the relationship that Murali works in the finance department. As with entities, we may wish to collect a set of similar relationships into a relationship set. A relationship can be thought of as a set of n-tuples. Each n-tuple denotes a relationship involving n entities e1 through en, where entity ei is in entity set Ei. In the following figure we can see the relationship set Works_In, in which each relationship indicates a department in which an employee works.
A relationship can also have descriptive attributes. Descriptive attributes are used to record information about the relationship, rather than about any one of the participating entities; for example, we may wish to record that Murali works in the pharmacy department as of January 1991. This information is captured in the above figure by adding an attribute, since, to Works_In. A relationship must be uniquely identified by the participating entities, without reference to the descriptive attributes. In the Works_In relationship set, for example, each Works_In relationship must be uniquely identified by the combination of employee ssn and department did. Thus, for a given employee-department pair, we cannot have more than one associated since value.
An instance of a relationship set is a set of relationships. Intuitively, an instance can be thought of as a `snapshot’ of the relationship set at some instant in time. An instance of the Works_In relationship set is shown in the following figure. Each Employees entity is denoted by its ssn, and each Departments entity is denoted by its did, for simplicity. The since value is shown beside each relationship.
As another example of an ER diagram, suppose that each department has offices in several locations and we want to record the locations at which each employee works. This relationship is ternary because we must record an association between an employee, a department, and a location. The ER diagram for this variant of Works_In, which we can call Works_In2, is shown in the following figure.
The entity sets that participate in a relationship set need not be distinct; sometimes a relationship might involve two entities in the same entity set. For example, consider the Reports_To relationship set that is shown in the following figure. Since employees report to other employees, every relationship in Reports_To is of the form (emp1; emp2), where both emp1 and emp2 are entities in Employees. However, they play different roles: emp1 reports to the managing employee emp2, which is reflected in the role indicators supervisor and subordinate in the following figure. If an entity set plays more than one role, the role indicator concatenated with an attribute name from the entity set gives us a unique name for each attribute in the relationship set. For example, the Reports_To relationship set has attributes corresponding to the ssn of the supervisor and the ssn of the subordinate, and the names of these attributes are supervisor ssn and subordinate ssn.