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 systems data.
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 models 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.
Before creating an entity-relationship diagram, the analyst must have at least a preliminary sense of the systems 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).
Entity-relationship diagrams are used to plan and design a database and to model a systems data.
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.
Products make up sales transactions.
The act of creating such sentences is a good test of the relationships 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).
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.
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.
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 students 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 crows foot) at the many-end of the relationship line (Figure 26.3). Some practitioners use other symbols, however.
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 students 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 crows foot at both ends of the relationship line (Figure 26.4). Some practitioners use other symbols, however.
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.
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.
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.
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.
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).
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.
The entity-relationship diagrams in this # were prepared using Visio. Other graphing tools, such as Micrografxs Flowcharter provide comparable support. Additionally, many CASE products support entity-relationship models.