identifying relationships

This content is an extract from GetAhead in Business Analysis - the FULL course is in eBook and multimedia CD-Rom format.

Identifying Relationships.
This diagram represents the beginnings of a data model where the relationship between a manager and a department needs to be defined. The entities on data models are linked by relationship lines and together these are the only two components that make up a data model diagram. A relationship is an association between two entities to which all of the occurrences of those entities must conform. Identifying relationships is a vital part of the data modeling process and is described in detail below.

Every relationship line shows two reciprocal relationships:
That of the first entity with respect to the second and that of the second entity with respect to the first. In this example a manager is responsible for a department and a department is the responsibility of a manager.
Each relationship line has three distinct properties: Firstly the relationship link phrase, secondly the degree or cardinality of the relationship and thirdly the participation, or optionality, of the relationship. These three properties combine to form the relationship statement.

Relationship Link Phrase.
The first property of the relationship statement is the relationship link phrase. This should be a short description of the nature of the relationship, typically between three and five words long. It is always read clockwise with respect to the entities that it links, so in this example:
“Manager is responsible for department”, and
“Department is responsibility of manager”.
If the same relationship were to be drawn with department on the left hand side then the positions of the link phrases would have to be reversed, as shown.

Degree or Cardinality.
The second property of the relationship statement is the degree, or maximum cardinality, of the relationship. If an entity has a crows-foot symbol drawn against it, then many occurrences of that entity may relate to the other entity. Conversely if no crows-foot is drawn against it, at most one occurrence of that entity may relate to the other entity. In this example: “Each company employs one or more employees, but Each employee is employed by only one company.” This is called a one-to-many relationship. Maximum cardinalities may be combined to give another two relationship types. In this example: “Each manager is responsible for only one department and each department is the responsibility of only one manager.” This is called a one-to-one relationship. And in this example: “Each lecturer teaches one or more courses and each course is taught by one or more lecturers.” This is called a many-to-many relationship. To recap, three different relationship types have been illustrated, one-to-many, one-to-one and many-to-many.

Participation or Optionality.
The third and final property of the relationship statement is the participation or optionality. A solid line shows that an entity occurrence must be associated with each occurrence of the other entity. In this example:
“Each passenger must possess a ticket, and Each ticket must belong to a passenger.”
A dotted line shows that an entity occurrence may be associated with each occurrence of the other entity. In this example:
“Each book may be borrowed by a borrower, and
Each borrower may borrow one or more books.”
Furthermore, these symbols can be combined. In this example:
“Each book may be recalled by a reservation, but
Each reservation must be recalling a book.”

Identifying the Degree.
In order to identify the degree of the relationship between the entities X and Y the following two questions need to be asked.
Question 1.
Can an occurrence of X be associated with more than one occurrence of Y?
Question 2.
Can an occurrence of Y be associated with more than one occurrence of X?
Each of these questions can be answered 'Yes' or 'No' and both questions must be answered. This means that there are four possible outcomes as shown in the table. The nature of the relationship associated with each outcome is discussed next.

Two Questions – Four Outcomes.
Option 1, Question1 equals Yes, Question2 equals No.
In this case a one-to-many relationship has been identified, represented by the relationship line shown above.
Option 2, Question1 equals No, Question2 equals Yes.

As in the first case a one-to-many relationship has been identified, represented by the relationship line shown.
In a one-to-many relationship the entity at the 'one' end is normally referred to as the master, and the entity at the 'many' end referred to as the detail entity. Some analysts adopt the 'no dead crows' rule and avoid drawing a crows-foot symbol pointing upwards. This ensures that detail entities are shown below the master entities to which they belong. This makes the diagram clearer, although congestion may make this rule difficult to enforce throughout the data model. Option 3, Question1 equals Yes, Question2 equals Yes.

In this case a many-to-many relationship has been identified.
Many-to-many relationships may be shown in the early 'preliminary' data model in order to aid the clarity of these early diagrams. However, such relationships are invalid and are therefore always re-modeled using 'link entities' in later diagrams. This process is explained later in the course.
Option 4, Question1 equals No, Question2 equals No.

In this case a one-to-one link has been identified.
Legitimate one-to-one relationships are rare and it is likely that this relationship is one that needs to be rationalized. The methods used to investigate one-to-one relationships and to re-model them where necessary are explained later in the course.

The 3 Properties of a Relationship.
Every relationship on a data model is defined by a relationship statement. This is the formal description of the relationship and this diagram shows how it is derived. The relationship statement encompasses the three properties of the relationship. The first property is the relationship link phrase, the second the degree or cardinality of the relationship and the third; the participation or optionality of the relationship.

Defining Relationship Statements.
This data model represents part of a hospital system. As mentioned earlier, on preliminary diagrams all relationship types can be shown in order to aid clarity. This preliminary diagram, for example, shows; one-to-one, many-to-many and one-to-many relationships. Think about the relationships in turn and try to derive the formal relationship statement for each one. Remember, that a relationship statement addresses the relationship from the perspective of the entities at either end of it. When you have written down your four relationship statements, turn to the next page to check your answers.

The relationship statement for Nurse and Ward:
“Each Nurse must work on one and only one Ward
Each Ward must be staffed by one or more Nurses.”

The relationship statement for Ward and Patient:
“Each Ward may treat one or more Patients
Each Patient must reside in one and only one Ward.”

The relationship statement for Medical Record and Patient:
“Each Medical Record belongs to one and only one Patient
Each Patient has one and only one Medical Record.”

The relationship statement for Patient and Drugs:
“Each Patient may receive one or more Drugs
Each Drug may be given to one or more Patients.”

This content is an extract from GetAhead in Business Analysis - the FULL course is in eBook and multimedia CD-Rom format. TOP