How can we connect the relationship or the association between two entities?

A relationship is an association that exists between two entities. For example, Instructor teaches Class or Student attends Class. Most relationships can also be stated inversely. For example, Class is taught by Instructor.

Inhaltsverzeichnis Show

Can there be two relationships between two entities?
What represents the relationship between two entities?
What is used to show associations relationships among entities?
How are relationships between entities represented in the relational data model?

The relationships on an Entity-Relationship Diagram are represented by lines drawn between the entities involved in the association. The name of the relationship is placed either above, below, or beside the line.

Relationships Between Entities

There can be a simple relationship between two entities. For example, Student attends a Class:

Some relationships involve only one entity. For example, Employee reports to Employee:

This type of relationship is called a recursive relationship.

There can be a number of different relationships between the same two entities. For example:

· Employee is assigned to a Project,

· Employee bills to a Project.

One entity can participate in a number of different relationships involving different entities. For example:

·          Project Manager manages a Project,

·          Project Manager reports to Project Director,

·          Project Manager approves Employee Time.

Characteristics of Relationships

A relationship may be depicted in a variety of ways to improve the accuracy of the representation of the real world. The major aspects of a relationship are:

RELATIONSHIP TITLE

Naming the Relationship

Place a name for the relationship on the line representing the relationship on the E-R diagram. Use a simple but meaningful action verb (e.g., buys, places, takes) to name the relationship. Assign relationship names that are significant to the business or that are commonly understood in everyday language.

Bi-directional Relationships

Whenever possible, use the active form of the verb to name the relationship. Note that all relationships are bi-directional. In one direction, the active form of the verb applies. In the opposite direction, the passive form applies.

For example, the relationship Employee operates Machine is named using the active verb operates:

However, the relationship Machine is operated by Employee also applies. This is the passive form of the verb.

By convention, the passive form of the relationship name is not included on the E-R diagram. This helps avoid clutter on the diagram.

Tips and Hints

When a simple name for the relationship is not apparent and the relationship is assigned a complex title, look for entities that might be hidden within the relationship title. For example, the relationship Customer "sends order to" Vendor is obscuring the existence of an Order entity.

RELATIONSHIP CARDINALITY

Definition

Relationship cardinality identifies the maximum number of instances in which an entity participates in a relationship.

There are three types of relationship cardinality:

·          one-to-one,

·          one-to-many,

·          many-to-many.

One-to-One (1:1)

A one-to-one relationship between two entities indicates that each occurrence of one entity in the relationship is associated with a single occurrence in the related entity. There is a one-to-one mapping between the two, such that knowing the value of one entity gives you the value of the second. For example, in this relationship an Employee uses a maximum of one Workstation:

One‑to‑Many (1:M), Many‑to‑One (M:1)

A one-to-many or a many-to-one relationship between two entities indicates that a single occurrence of one entity is associated with one or more occurrences of the related entity. The example indicates that there is one Project Manager associated with each Project, and that each Project Manager may be associated with more than one Project.

Many‑to‑Many (M:M)

A many-to-many relationship between two entities indicates that either entity participating in the relationship may occur one or several times. The example indicates that there may be more than one Employee associated with each Project, and that each Employee may be associated with more than one Project at a time. That is, projects may share employees.

It is appropriate to identify and illustrate many-to-many relationships at the conceptual level of detail. Such relationships are broken down to one-to-many relationships at the logical level of detail. For example, at the logical level the many-to-many relationship above is better represented by introducing a new entity such as Assignment and splitting the many-to-many into two one-to-many relationships. The new entity Assignment contains the primary keys of Project and Employee.

In this manner, useful data regarding a specific employee's contribution to a specific project is accommodated in the Assignment entity.

This refinement is a normal part of entity analysis leading to the discovery of new entities. Many-to-many relationships must be decomposed into one-to-many relationships to implement the physical data model. An associative entity is created to record the relationship between the two entities.

Foreign Keys

To relate one entity to another, make the primary key of one entity an attribute of the other entity (foreign key).

In a one-to-one relationship the foreign key may be placed in either of the entities. In a one-to-many or many-to-one relationship the foreign key is placed in the entity that has the many relationship.

RELATIONSHIP DEPENDENCY

Types of Relationship Dependencies

Three relationship dependencies are possible:

·          mandatory,

·          optional,

·          contingent.

Relationship dependencies may be of different degrees. Each relationship dependency is illustrated differently.

Mandatory Relationship

A mandatory relationship indicates that for every occurrence of entity A there must exist an entity B, and vice versa.

When specifying a relationship as being mandatory one-to-one, you are imposing requirements known as integrity constraints Opens a new window. For example, there is one Project Manager associated with each Project, and each Project Manager is associated with one Project at a time. A Project Manager may not be removed if the removal causes a Project to be without a Project Manager. If a Project Manager must be removed, its corresponding project must also be removed. A Project may not be removed if it leaves a Project Manager without a Project. A new project may be added if it can be managed by an existing Project Manager. If there is no Project Manager to manage the Project, a Project Manager must be added with the addition of a new Project.

When specifying a relationship as being mandatory one-to-many or many-to-one, you are imposing integrity constraints. For example, an Employee is assigned one to many tasks and a task is assigned to one and only one Employee. There would not be a Task without an Employee, and there would not be an Employee without a Task. Similarly, if an Employee is added, Task must be added.

Some relationships are naturally or inherently mandatory. For example, consider the relationship Mother has Child. There would not be a Child without a Mother, nor would there be a Mother without a Child.

Other relationships are mandatory due to legislative or business rules, such as "a project is not considered to exist until it has been assigned a budget." This type of mandatory relationship should be analyzed to assess whether or not the rule is a temporary or unnecessary restriction.

Optional Relationship

An optional relationship between two entities indicates that it is not necessary for every entity occurrence to participate in the relationship. In other words, for both entities the minimum number of instances in which each participates, in each instance of the relationship is zero (0).

As an example, consider the relationship Man is married to Woman. Both entities may be depicted in an Entity-Relationship Model because they are of interest to the organization. However, not every man, or woman, is necessarily married. In this relationship, if an employee is not married to another employee in the organization, the relationship could not be shown.

The optional relationship is useful for depicting changes over time where relationships may exist one day but not the next. For example, consider the relationship "Employee attends Training Seminar." There is a period of time when an Employee is not attending a Training Seminar or a Training Seminar may not be held.

Optional relationships may be unnecessary if an entity can be subdivided into subtypes or entirely different entities. For example, the entity Person could represent both employees and dependents in a superannuation system. In the wider aspects of a personnel system, an optional relationship would be necessary to link Person to Job. However, breaking down the Person entity into the separate entities Employee (which would have a mandatory relationship to Job), and Dependent (which would not be involved in such a relationship), provides a clearer representation.

Contingent Relationship

A contingent relationship represents an association which is mandatory for one of the involved entities, but optional for the other. In other words, for one of the entities the minimum number of instances that it participates in each instance of the relationship is one (1), the mandatory association, and for the other entity the minimum number of instances that it participates in each instance of the relationship is zero (0), the optional association.

For example, consider the relationship Man fathers Child. Not all occurrences of the entity Man will have produced a child. However, if an occurrence of the Child entity exists, it must be related to a Man entity. This is an inherent or natural contingent relationship.

Contingent relationships may exist due to business rules, such as Project is staffed by Consultant.

In this case, a Project may or may not be staffed by a Consultant. However, if a Consultant is registered in the system, a business rule may state that a Consultant must be associated with a Project.

RELATIONSHIP COMBINATIONS WITH OTHER RELATIONSHIPS

Types of Relationship Combinations

Entities are often involved in a variety of relationships. Optional relationships are often affected by the existence of another relationship.

Clarify the nature of two or more relationships concerned with a particular entity by one of the following combinations:

·          inclusive OR (either or both),

·          exclusive OR (either, but not both),

·          AND (both must exist).

Inclusive OR

An inclusive OR relationship indicates that entity A is related to either entity B or entity C or both B and C.

In the example, the project team may be composed of employees, consultants, or both.

Exclusive OR

An exclusive OR relationship indicates that entity A is related to either entity B or entity C but not both B and C.

In the example, a project can only be managed by one person, either an employee or a consultant.

AND

An AND relationship indicates that entity A is related to both entity B and entity C.

In the example, the pay notification contains both the stub and the cheque. Note that this is a different situation to identifying entity subtypes in that the cheque is not simply one type of notification.

Also see my previous posts on:

Interpreting Cardinality and Dependency on an E-R Diagram Opens a new window

Recursion in Relationships Opens a new window

Handling the Different Interests of Customers in an Organization

The use of relationships can solve a problem that often occurs when consolidating the perspectives of different customers in an organization. For instance, an entity may be viewed as an entity subtype of two different entities depending on the interests of the customers. Consider the entity Accountant which is a subtype of the entity Employee, while also being a subtype of the entity Professional Institute Member. In this example, the subtype Accountant could be removed from Employee and a relationship could be established between Employee and the Accountant subtype of Professional Institute Member:

Opens a new window Opens a new window

Can there be two relationships between two entities?

There can be more than one relationships between two entities. Your first linking table is not required. The DepartmentID in DepartmentEmploys can simply be a FK in the Employee table. Also, the second linking table is acceptable only if a Department can have multiple Employee as managers.

What represents the relationship between two entities?

6. What is a relationship called when it is maintained between two entities? Explanation: Binary word usually represents two attributes.

What is used to show associations relationships among entities?

ERD stands for entity relationship diagram. People also call these types of diagrams ER diagrams and Entity Relationship Models. An ERD visualizes the relationships between entities like people, things, or concepts in a database. An ERD will also often visualize the attributes of these entities.

How are relationships between entities represented in the relational data model?

entity relationship (ER) data model: also called an ER schema, are represented by ER diagrams. These are well suited to data modelling for use with databases. ternary relationship: a relationship type that involves many to many relationships between three tables.