<ch25 toc ch27>

26 Entity-relationship diagrams

26.1 Purpose
26.2 Strengths, weaknesses, and limitations
26.3 Inputs and related ideas
26.4 Concepts
26.4.1 Entities and relationships
26.4.2 Cardinality
26.4.3 Analyzing relationships
26.4.4 Creating an entity-relationship diagram
26.4.5 Documenting composites and attributes
26.5 Key terms
26.6 Software
26.7 References

26.1 Purpose

Entity-relationship diagrams were first proposed as a means of quickly obtaining, with minimum effort, a good sense of the structure of a database. They are used to plan and design a database and to model a system’s data.

26.2 Strengths, weaknesses, and limitations

An entity-relationship diagram is an excellent tool for planning and designing a database, particularly when used in conjunction with data normalization. The entity-relationship model starts with the entities, data normalization starts with the attributes, and the two tools tend to verify each other. The entity-relationship model’s entities, attributes, and relationships map smoothly to a physical database.

During the systems analysis phase, an entity-relationship diagram gives the analyst a clear, high-level view of the data. Used in conjunction with data flow diagrams, an entity-relationship model gives the analyst an alternative logical view of the system. If a great deal is known about the data but not much about the processes, an entity-relationship diagram is an excellent starting point for modeling the system.

An entity-relationship model is data driven. The model implies processes but does not clarify the processes. Non-technical people find entity-relationship models difficult to understand and the nature of a relationship (one, many) confusing, and numerous notational variations sometimes make it difficult for even an experienced person to quickly grasp a particular diagram.

26.3 Inputs and related ideas

Before creating an entity-relationship diagram, the analyst must have at least a preliminary sense of the system’s logical entities, attributes, and data structures. The necessary information is obtained during the information gathering and problem definition stage (Part II). Data structures are discussed in # 43. Other key data concepts are found in #s 25, 44, and 45. Entity-relationship diagrams are important tools in the structured requirements methodology (# 4) and in database design (# 45). They are often used in conjunction with data flow diagrams (# 24) and data normalization (# 28).

26.4 Concepts

Entity-relationship diagrams are used to plan and design a database and to model a system’s data.

26.4.1 Entities and relationships

An entity is an object (a person, group, place, thing, or activity) about which data are stored. A relationship links two entities and is shown by drawing a line between them (Figure 26.1).

Logically, a relationship can be stated in the form of a sentence with a verb linking the two entities, for example,


Figure 26.1  A relationship between two entities is shown by drawing a line between them.

Sales transactions are composed of products.

or

Products make up sales transactions.

The act of creating such sentences is a good test of the relationship’s validity. In cases where the relationship is unclear, the sentence might be written alongside the relationship line as shown in Figure 26.1. A given relationship can be mandatory (shown by a solid line) or optional (a broken line).

26.4.2 Cardinality

For a variety of reasons, some relationships are more stable and easier to maintain than others. (A detailed discussion of the underlying database theory is beyond the scope of this course.) Cardinality, a measure of the related entities’ relative number of occurrences, is an important predictor of the strength of the relationship.

26.4.2.1 One-to-one relationships

In a one-to-one relationship, each occurrence of entity A is associated with one and only one occurrence of entity B, and each occurrence of entity B is associated with one and only one occurrence of entity A.

Figure 26.2  In a one-to-one relationship, each occurrence of entity A is associated with one and only one occurrence of entity B, and each occurrence of entity B is associated with one and only one occurrence of entity A.

For example, imagine that an instructor maintains examination grades for each student in his or her class. There are two entities in this example: Students and Exams. For each Student there is one and only one Exam, and for each Exam there is one and only one Student.

Graphically, a one-to-one relationship is described by drawing short crossing lines at both ends of the line that links the two entities (Figure 26.2). However, some practitioners simply show the relationship line with no embellishment, and other symbols are used as well.

26.4.2.2 One-to-many relationships

In a one-to-many relationship, each occurrence of entity A is associated with one or more occurrences of entity B, but each occurrence of entity B is associated with only one occurrence of entity A.

Figure 26.3  In a one-to-many relationship, each occurrence of entity A is associated with one or more occurrences of entity B, but each occurrence of entity B is associated with only one occurrence of entity A.

For example, a student’s grade in most courses is based on numerous grade factors (such as exams, papers, and projects). A given Student has several different Grade factors, but a given Grade factor is associated with one and only one Student.

Graphically, a one-to-many relationship is shown by drawing a short crossing line (or no extra marking) at the “one-end” and a small triangle (sometimes called a crow’s foot) at the “many-end” of the relationship line (Figure 26.3). Some practitioners use other symbols, however.

26.4.2.3 Many-to-many relationships

In a many-to-many relationship, each occurrence of entity A is associated with one or more occurrences of entity B, and each occurrence of entity B is associated with one or more occurrences of entity A. For example, a student’s end-of-term Grade report can list several Courses, and a given Course can appear on many students’ Grade reports.

Figure 26.  In a many-to-many relationship, each occurrence of entity is associated with one or more occurrences of entity B, and each occurrence of entity B is associated with one or more occurrences of entity A.

Graphically, a many-to-many relationship is shown by drawing a crow’s foot at both ends of the relationship line (Figure 26.4). Some practitioners use other symbols, however.

26.4.2.4 Other relationships

Although this # will focus on one-to-one, one-to-many, and many-to-many relationships, other types of relationships are possible. Sometimes entities are mutually exclusive, with A linked to either B or C, but not both. In a mutually inclusive relationship, if A is linked to B it must also be linked to C. Zero cardinality implies that an occurrence of A means no occurrence of B. Crosslinks and loops can exist, too. A recursive relationship is shown by drawing a semicircle from the entity back to itself.

26.4.3 Analyzing relationships

For a variety of reasons, one-to-many relationships tend to be the most stable. Consequently, a primary objective of entity-relationship modeling is to convert one-to-one and many-to-many relationships into one-to-many relationships.

26.4.3.1 Resolving one-to-one relationships

One-to-one relationships can often be merged. Generally, entities that share a one-to-one relationship are really the same entity and should be merged unless there is a good reason to keep them separate.

Note that not all one-to-one relationships can be merged, however. For example, imagine a relationship between athletes and drug tests. There is one Drug test per Athlete and one Athlete per Drug test, so the relationship is clearly one-to-one. In this case, however, because merging the entities would probably violate security requirements (and possibly the law), there is a good logical reason to maintain separate entities.

26.4.3.2 Resolving many-to-many relationships

Many-to-many relationships can cause maintenance problems. For example, Figure 26.5 shows a many-to-many relationship between Inventory and Supplier. Each product in Inventory can have more than one Supplier, and each Supplier can carry more than one product. If a list of suppliers were stored in Inventory, adding or deleting a supplier might mean updating several Inventory occurrences. Likewise, listing products in Supplier could mean changing several Supplier occurrences if a single product were added or deleted.


Figure 26.5  A many-to-many relationship can often be converted to two one-to-many relationships.

One solution is to create a new entity that has a one-to-many relationship with both original entities. For example, imagine a new entity called Item ordered (Figure 26.5). Given such a design, a given product in Inventory can appear on several active Items ordered, but each Item ordered is for one and only one product. Likewise, a given supplier can appear on several active Items ordered, but each Item ordered lists one and only supplier. Note that a given Item ordered links a specific product in Inventory with a specific occurrence of Supplier. The many-to-many relationship has been converted to two one-to-many relationships.

26.4.4 Creating an entity-relationship diagram

Assume a preliminary analysis of a retail sales application suggests four primary entities: customer, sales, inventory, and supplier.

The Sales, Customer, and Inventory entities are related as follows:

Customer initiates Sales.

Sales are drawn from Inventory.

The first relationship is one-to-many (Figure 26.6); a given Customer can have many Sales transactions, but a given Sale is associated with one and only one Customer. However, the second relationship is many-to-many because a given Sale can include several products from Inventory and a given product in Inventory can appear in many Sales.

To resolve the many-to-many relationship, create a new entity, Item sold, that has a one-to-many relationship with both Sales and Inventory (Figure 26.7). A given Sales transaction can list many Items sold, but a given Item sold is associated with one and only one Sales transaction. A given product in Inventory can appear in many Items sold, but a given Item sold lists one and only one product. (Think of an Item sold as one line in a list of products purchased on a sales invoice.)


Figure 26.6  Customer has a one-to-many relationship with sales. The relationship between sales and inventory is many-to-many.


Figure 26.7  Resolving the many-to-many relationship calls for a new entity.

There is one possible source of confusion about the Inventory entity that might need clarification. A specific 19-inch color television set is an example of a single occurrence of that entity, but Inventory might hold numerous virtually identical television sets. For inventory control purposes, tracking television sets (a class of occurrences) is probably good enough. However, the Customer purchases a specific television set (identified, perhaps, by concatenating the serial number to the stock number). Thus a given Item sold lists one and only one occurrence of Inventory.

The relationship between Inventory and Supplier (Figure 26.8) is many-to-many because a given product can have many suppliers and a given supplier can supply many different products. Many-to-many relationships must be resolved, so add a new entity called Item ordered to the model, yielding two one-to-many relationships.


Figure 26.8  The many-to-many relationship between supplier and inventory can be resolved by creating a new entity called item ordered.


Figure 26.9  The finished entity-relationship model.

Finally, the Inventory entity is related to both Item sold and Item ordered, so combine the two partial diagrams to form a single entity-relationship model (Figure 26.9).

26.4.5 Documenting composites and attributes

After the entities have been identified, each one is recorded in the data dictionary (# 25) as a data composite and the attributes (or data elements) it contains are defined. Many analysts prepare inverted-L charts (# 27) to graphically represent the entities’ contents.

26.5 Key terms

Attribute —
A property of an entity.
Cardinality —
A measure of the relative number of occurrences of two entities.
Composite —
A set of related data elements.
Data element —
An attribute that cannot be logically decomposed.
Data structure —
A set of related data elements; a composite.
Entity —
An object (a person, group, place, thing, or activity) about which data are stored.
Entity-relationship diagram —
A diagram that shows how a system’s primary data entities are related.
Many-to-many relationship —
A relationship in which each occurrence of entity A is associated with one or more occurrences of entity B, and each occurrence of entity B is associated with one or more occurrences of entity A.
Occurrence —
A single instance of an entity.
One-to-many relationship —
A relationship in which each occurrence of entity A is associated with one or more occurrences of entity B, but each occurrence of entity B is associated with only one occurrence of entity A.
One-to-one relationship —
A relationship in which each occurrence of entity A is associated with one occurrence of entity B and each occurrence of entity B is associated with one occurrence of entity A.
Relationship —
A link between two data structures.

26.6 Software

The entity-relationship diagrams in this # were prepared using Visio. Other graphing tools, such as Micrografx’s Flowcharter provide comparable support. Additionally, many CASE products support entity-relationship models.

26.7 References

1.  Barker, R.,Case Method: Entity Relationship Modelling, Addison-Wesley, Reading, MA, 1990.
2.  Chen, P., The entity-relationship model — towards a unified view of data, ACM Trans. Data. Syst., 1(1), 9, 1976.
3.  Date, C. J., An Introduction to Database Systems, vol. 1, 6th ed., Addison-Wesley, Reading, MA, 1994.
4.  Davis, W. S., Business Systems Analysis and Design, Wadsworth, Belmont, CA, 1994.
5.  Dutka, A. F. and Hanson, Fundamentals of Data Normalization, Addison-Wesley, Reading, MA, 1989.
6.  Martin, J. and McClure, C., Structured Techniques: The Basis for CASE, Prentice-Hall, Englewood Cliffs, NJ, 1988.
7.  McDermid, D. C., Software Engineering for Information Systems, Blackwell Scientific Publications, Oxford, U.K., 1990.
<ch25 toc ch27>