entity relationship diagram

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

Identifying Relationships on the entity relationship diagram.
You should now appreciate the different types of relationship that are possible. In order to develop the entity relationship diagram, both the entities and their relationships have to be identified. When conducting the analysis it is the verbs that arise in interviews and system documentation that may help to highlight relationships. Remember, it is the presence of nouns that are used to assist in the identification of entities. There are two strategies that can be employed when attempting to identify relationships. Firstly, a rigorous approach - in which all possible entity relationships are considered. This approach is based on the entity cross reference grid. Alternatively a less formal approach can be adopted, in which likely relationships are considered as the data model evolves.

Formal Cross Referencing.
In the formal cross reference approach each entity is marked along both the horizontal and vertical axis of a grid, so that each intersection in the grid represents a potential relationship. For each pair of entities the possibility of a 'direct' and 'relevant' relationship is considered. In this context 'direct' means that a link exists between them that does not rely on an intermediate entity, and 'relevant' means that the relationship is of interest to the investigation. Consider a system with three entities: Parent, Student & University. There is a direct relationship between parent and student - that of parentage, and between student and university - students attend university. However, when investigating the relationship between parent and university we are asking: "Is there a possible association between parent and university that is not dependent on intermediate entities?" In this grid the black boxes represent duplicate relationship options and these don't need to be considered. The 'O's at the bottom of each column show that the direct relationships between entity occurrences of a given type are not of interest to this investigation in other words we are not concerned with any possible recursive relationships. Each 'X' represents a relationship that is both direct and relevant. Each 'O' shows that there is no relationship between the entities. In this example the only relationship between parent and university that was identified was via the link entity - student. Therefore, there is no direct relationship between parent and university. The black boxes represent duplicate relationships and need not be considered. The 'O's at the bottom of each column show that the direct relationships between entity occurrences of a given type are not of interest to this investigation.

Spurious Relationships.
Unfortunately this rigorous entity cross reference approach may result in spurious direct relationships being identified. Consider part of an airline reservation system, involving the three entities; Passenger, Ticket and Flight. Using an entity cross reference grid, like the one shown, together with your own knowledge of an airline system, mark the direct relationships on the grid. When you have drawn your own solution for this, go to the next page to see the suggested solution.
This grid represents the suggested solution, it shows that:
1. Passenger has a direct relationship with ticket, as passengers have tickets.
2. Ticket has a direct relationship with flight, as flights have tickets, and
3. Passenger has a direct relationship with flight, as passengers are booked on flight.
However, in this example the grid approach may have led to the identification of relationships that do not accurately reflect the system.

Informal Relationship Identification.
A less formal approach to relationship identification can be adopted, in which likely relationships are considered in parallel to the drawing of initial data model diagrams. Even though the informal approach to relationship identification lacks the thorough regime of the entity cross reference grid, it will generally be quicker. Also the associated direct drawing of the data model allows indirect relationships to be seen more easily. Consider again the airline reservation system for which you have just drawn an entity cross reference grid. Try drawing the data model for this system without referring to the cross reference grid. When you have drawn your own solution for this, go to the next page to see the suggested solution. This diagram shows an alternative set of relationships that might be identified using this less formal approach. It shows that a flight possesses a number of tickets, each of which is held by a passenger. Furthermore, it shows that, as far as the reservations system is concerned, the link between passenger and flight is not direct and relevant. The formal and informal approach to relationship identification may well produce different relationship models. An experienced analyst should be able to identify the relationships accurately by the use of either approach, but less experienced staff should consider using both methods and comparing the two results. Furthermore, in a real-world system where a large number of entities may be included on the diagram, the final translation from a grid to a data model may result in a spiders' web of relationship lines, which can make editing of the diagram difficult.

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