Conceptual database design

Entity relationship modeling

Citation
, XML
Authors

Abstract

This is an overview of conceptual database design using entity relationship modeling. It’s focused on “how-to” rather than theory. THIS IS A WORK IN PROGRESS. FIGURES STILL NEED TO BE CONVERTED AND RENUMBERED

Note: John Day and Raymond Frost of Ohio University co-authored this document.

Objectives

· Define terms related to entity relationship modeling, including entity, entity instance, attribute, relationship, cardinality, and foreign key.

· Describe the entity modeling process.

· Discuss how to draw an entity relationship diagram.

· Describe how to recognize entities, attributes, relationships, and cardinalities.

Overview

The ability of a database designer to understand and model the information that an organization uses is a critical design skill. Data modelers use a variety of tools and techniques to understand an organization’s data. In order to understand how to properly model data, you must become familiar with a modeling approach known as

entity relationship modeling, which is the subject to of this chapter.

Entity-relationship (E-R) modeling is one approach to

semantic modeling. When database designers attempt to understand and represent meaning, they are engaged in semantic modeling, which can help in making database design more systematic (Date, 1990). Although a number of approaches to semantic modeling exist, this chapter focuses on entity relationship modeling. More specifically, we use E-R modeling to carry out conceptual data modeling

ER modeling, introduced by Chen (1976) consists of a number of activities that help database designers understand the objects the organization wants to store information about, the important characteristics of these objects and the associations among various objects.

The Entity Relationship Diagram

The end result of E-R modeling is the

E-R diagram (ERD), a graphical representation of the logical structure of a database. An ERD serves several purposes. First, the database analyst/designer gains a better understanding of the information to be contained in the database through the process of constructing the ERD. Second, the ERD serves as a documentation tool. Finally, the ERD is used to communicate the logical structure of the database to users. useruser In particular, the ERD effectively communicates the logic of the database to users.

Entities

The E-R modeling process identifies three basic elements: entities, attributes and relationships.

An

entityis a thing that can be distinctly identified (Chen, 1976). In database design, entities are the “things” about which the database stores information. Entities can include, but are not limited to

· tangible items, such as equipment,

· concepts, such as accounts,

· people, such as employees

· events, such as sales, or

· places, such as business locations.

The term

entity typerefers to a number of related items, while anentity instancerefers to a single occurrence of an entity type. For example, employee number 123-45-6789 refers to a single occurrence, or entity instance, of the entity EMPLOYEE. The term entity refers to entity type. In addition, note that entity occurrence is sometimes used rather than entity instance. In ER diagrams, rectangles represent entities, as shown in Figure 1.

Figure 1: Entity Examples

Attributes

An

attributeis a single data value that describes a characteristic of an entity. Other terms such as data item and field, describe the same essential concept.

Each entity has a corresponding set of attributes that represent the information about the entity that the organization is interested in. For example, a university may wish to know the name, address, phone number, and major of each student. Put in database terms, STUDENT is the entity of interest, and NAME, ADDRESS, PHONE, and MAJOR are the attributes of interest.

Attributes are represented by ellipses in the ER diagram. The name of the attribute is shown inside the ellipse and a line is drawn from the attribute to its entity.

Primary Keys

Every entity

must have aprimary key. The primary key is an attribute or combination of attributes that uniquely identifies an instance of the entity. In other words, no two instances of an entity may have the same value for the primary key. Factors database designers must consider when choosing a primary key are discussed later in this chapter.

Sometimes it is useful to use more than one attribute to form a primary key. When a primary key for an entity is made up of more than one attribute the key is called a

composite key. The terms composite key and compound key are also used to describe primary keys that contain multiple attributes.

When dealing with a composite primary key it is important to understand that it is the combination of values for all attributes that must be unique. It is not necessary for each attribute in the key to be unique. For example, the entity ENROLLMENT has a composite primary key comprised of the attributes STUDENT_ID and COURSE_ID. Each instance of ENROLLMENT must contain a unique combination of values for StudentID and CourseID. However, there can be duplications of StudentID or CourseID. So, it is possible for many instances of ENROLLMENT to have the value MIS100 for CourseID, but each of those instances must contain different values for StudentID. Figure 2 shows entities with attributes and primary keys indicated.

Figure 2: Entities with Attributes

Underlined attributes in Figure 3 indicate primary keys in the E-R diagram. Each attribute involved in making up a composite primary key is underlined. In Figure 3, the entity STUDENTS has nine attributes, including the primary key, which is StudentID. The entity ENROLLMENTS has four attributes, and a composite primary key made up of the attributes CourseID, StudentID and Term.

Relationships

A

relationshipis an association among two or more entities. For example, a STUDENTS entity might be related to a COURSES entity, or an EMPLOYEES entity might be related to an OFFICES entity. An ellipse connected by lines to the related entities indicates a relationship in an ERD, as shown in Figure 3. The line connecting the ORDERS and CUSTOMERS entities indicates that these two entities are related to one another.

Figure 3: Entities and Relationship

Different relationship degrees exist. The degree of a relationship refers to the number of entities involved in the relationship. Although others exist, it is often sufficient to understand the meaning of three relationship degrees, unary, binary and ternary. A

unary relationship(also called a recursive relationship) is a relationship involving a single entity. A relationship between two entities is called abinary relationship. When three entities are involved in a relationship, aternary relationshipexists. Relationships that involve more than three entities are referred to as n-ary relationships, where n is the number of entities involved in the relationship. Examples are provided for unary, binary and ternary relationship later in this chapter.

In some cases, attributes may be attached to a relationship, rather than an entity. For example, a GRADE attribute is a function of the combination of STUDENTS and COURSES, but is not strictly a function of either entity by itself. Attaching GRADE to STUDENT would not indicate that a STUDENT has a GRADE for a particular COURSE, while attaching GRADE to COURSES doesn’t show that the GRADE is for a particular STUDENT. Attaching the GRADE attribute to the relationship between COURSES and STUDENTS shows that a value of GRADE is dependent on an intersection of COURSES and STUDENTS. A similar argument can be made for TERM. Figure 4 illustrates how to show this on an E-R Diagram.

Figure 4: Relationship with Attributes


Cardinality

Relationships can also differ in terms of their cardinality.

Maximum cardinalityrefers to the maximum number of instances of one entity that can be associated with a single instance of a related entity.Minimum cardinalityrefers to the minimum number of instances of one entity thatmustbe associated with a single instance of a related entity. The following examples of binary relationships illustrate the concept of maximum cardinality (Fig. 6). Minimum cardinality is discussed in more detail later in the next section. If one CUSTOMER can be related to only one ACCOUNT and one ACCOUNT can be related to only a single CUSTOMER, the cardinality of the CUSTOMER-ACCOUNT relationship isone-to-one(1:1). [Note that each cardinality type is followed by a shorthand notation in parentheses.]

If an ADVISOR can be related to one or more STUDENTS, but a STUDENT can be related to only a single ADVISOR, the cardinality is

one-to-many(1:N).

Finally, the cardinality of the relationship is

many-to-many(M:N) if a single STUDENT can be related to zero or more COURSES and a single COURSE can be related to zero or more STUDENTS. Further examples are provided later in this chapter.

In E-R diagrams, cardinality is represented by symbols attached to the relationship line. A single vertical line intersecting the relationship line indicates a “one” cardinality. A crowfoot symbol indicates a “many” cardinality. Figure CARDINALITY shows ER diagrams with cardinality symbols. Figure 5(a) shows the E-R diagram for a 1:1 relationship, Figure 5(b) shows a 1:N relationship, and Figure 5(c) shows a M:N relationship. The symbols closest to the entities are the maximum cardinality symbols. We’ll cover the symbols for minimum cardinalities later.

Figure 5: (a) 1:1 relationship (b) 1:N relationship (c) M:N relationship Cardinality Symbols

When determining the cardinality of relationships, it is important to remember that cardinality specifies how many instances an entity can be related to a

singleinstance of a related entity. The trick to determining the cardinality of a relationship is to determine the cardinality of one side at a time.

For example, suppose you want to determine the cardinality of the STUDENTS to ADVISORS relationship. The first step is to determine how many STUDENTS

oneADVISOR can be related to. One ADVISOR can be related to many STUDENTS. To represent this on an E-R diagram, show the “many” symbol (the crowfoot) next to the STUDENT entity. Next, you need to determine how many ADVISORS one STUDENT can be related to. While this may vary from school to school, in this case the answer is one. To show this on the E-R diagram, put the “one” symbol (vertical line) next to the ADVISOR entity. Figure 6 illustrates the process.

Figure 6: Determining Cardinalities

If you have trouble determining the cardinality of a relationship, the following method may help. Assign a name to the entity instance you want to hold to one. For example, if you were having trouble determining the cardinality of the ADVISOR to STUDENT relationship you could ask yourself, “How many ADVISORS can Jan Smith have?”, or “How many STUDENTS can Dr. Smith advise?”

Creating an E-R Diagram

A number of steps are required to create an E-R diagram that accurately represents the organizational data. These steps are summarized in Figure 7 and discussed in this section.

Figure 7: Steps in Building an E-R Diagram


E-R Modeling Example: An ORDER ENTRY FORM

To illustrate the steps in building an E-R diagram, we’ll analyze an Order Entry Form. A sample of the Order Entry Form is shown in Figure 8.

ORDER-NO: 44-44-4444 CUSTOMER-ID: 1002
DATE: 10/31/98 CUST-NAME: ABC Inc.
PROD-ID DESCRIPTION PRICE QTY EXT
A123 STEREO SYSTEM 375.00 2 750.00
C235 8″ SPEAKER 150.00 8 1,200.00
X002 SPEAKER WIRE 10.00 5 50.00
TOTAL 2,000.00

Figure 8: Order Entry Form


Step 1: Model the Entities

The first step in creating an E-R diagram is to model the entities. Recall that an entity is simply something about which the organization wishes to store data. A number of information sources may be helpful when identifying entities, including forms, data entry screens, reports and user interviews.

It is important to realize that an entity is basically defined by its attributes, so when identifying entities look for groups of related attributes. It is particularly helpful to look for possible primary keys for an entity. Generally, when a form has an identifier for a possible entity it is likely to be the entity. For example, if a form contains a space for a customer number, then the database (and the E-R diagram) probably needs to contain a CUSTOMER entity.

In user interviews, be particularly aware of nouns that the user mentions. Generally, entities are named with nouns such as CUSTOMER, STUDENT, and EQUIPMENT. Nouns that crop up often in the course of a user interview are good candidates for entities.

As you identify candidate entities, also try to determine attributes for the entities. An effective technique is to write down any attributes you identify next to the entity to which they belong. This may provide a means for you to distinguish between entities and attributes, which we discuss later in this section.

Another useful E-R modeling technique employed by database analysts is to highlight each item on a form, report, transcript or other information source to indicate that the item has been modeled. For example, when analyzing a form mark each item on the form as you write it on your list of possible entities and attributes. This can serve as a useful check. You can go back through each information source and make sure each important item is marked.

At this stage be liberal in identifying entities. If there is any possibility that some item on a form or some sentence in an interview identifies an entity, include it in the list of possible entity. It is more difficult to identify entities that are missed than it is to remove candidate entities that turn out not to be entities required in the database. Most of the potential entities that are later rejected turn out to be attributes of an entity rather than entities in their own right. These can be culled out later.

In the case of the Order Entry example, there are several candidate entities. The initial analysis of the Order Entry Form indicates that there are three entities that are clearly represented on the form: ORDER, CUSTOMER, and PRODUCT.

After identifying the entities, it may be necessary to make some assumptions. For example, when deciding which attributes to place with each entity, we must make an assumption about PRICE. If the price of a product does not change from one order to another, then PRICE is a function of PRODUCT. If, however, different orders for the same product have different prices, then PRICE is a function of the relationship between PRODUCT and ORDER. In actual practice, the analyst must consult with the user representative to determine which view is correct. We will make the assumption that PRICE varies for all orders—PRICE is an attribute of PRODUCT. Note that the attribute QTY (quantity) is purposely absent from the list in order to illustrate the process of checking the ERD.

Table 1 shows the initial list of entities and attributes identified in the analysis of the Order Entry form.

Entity Attributes
ORDER ORDER-NO, DATE, TOTAL
PRODUCT PROD-ID, DESCRIPTION, PRICE
CUSTOMER CUSTOMER-ID, CUST-NAME

Table 1: Entity List for the Order Entry Form

Once the list of candidate entities and their attributes is complete, the database analyst must determine which entities need to be in the database and which should be excluded. These decisions are based on whether a candidate entity is an entity or an attribute. Many of the candidate entities clearly belong in the database the most likely candidate entities that should be rejected are those for which no attributes have been identified. If you are unable to find any attributes for an entity, that entity is probably in reality an attribute for another entity. For example, CUSTOMER_NAME might be identified as a possible entity. However, no attributes can be identified for CUSTOMER_NAME. This leads to the conclusion that CUSTOMER_NAME is not an entity, but is better considered an attribute of the CUSTOMER entity.

Not all cases are so clear, however. Take the example of ADDRESS. Perhaps a number of attributes were identified for this candidate entity, such as NUMBER, STREET, CITY, STATE, and POSTAL_CODE. Does the presence of these potential attributes of ADDRESS indicate that ADDRESS is an entity? Although in some situations this will be the case, more often all of these should be attributes of some other entity, such as CUSTOMER. Resolving these types of situations becomes easier with more experience in data modeling and greater knowledge of the organization.


Step 2: Choose Primary Keys

After identifying and modeling each entity and its attributes, primary keys must be chosen for each entity. For many entities, the primary key is obvious or already in place in the organization’s existing information systems. For example, a university may already be using a student identification number. In such cases, the best course of action is to retain the existing key.

When a primary key does not exist and is not obvious, candidate keys must be identified. The major requirement for an attribute to be a primary key is that the attribute uniquely identifies instances of the entity. In other words, for each instance of the entity the value of the attribute must be unique. In addition, the proper functioning of the database requires that primary keys can never be null. The primary key for each entity must always have a unique, valid value for each instance of the entity. Combining these two requirements results in each instance of an entity always having a one and only one primary key (even if it’s a composite primary key), and each primary key is unique.

Other characteristics of primary keys are also desirable. In general, a good primary key is what is sometimes called “data-less.” This means that no actual information is contained in the primary key. An account number, for example, typically contains no useful information and serves no purpose other than to identify an account. Primary keys that are data-less also posses another desirable characteristic—they never change. Primary keys whose values change over time lead to a number of problems with maintaining the database.

An example illustrates the problem with using a primary key that does not meet the characteristics in Table 2.

Desirable Primary Key Characteristics
1. Uniquely identifies an entity instance.
2. Non-null (always has a value)
3. Data-less
4. Never changes

Table 2: Characteristics of a Good Primary Key

Suppose that a database designer decided to make the primary key of the entity CUSTOMER the customer’s phone number. Assume for the moment that each customer has a phone number and that no two customers can have the same phone number. In other words, assume that the attribute PHONE is unique and non-null. While this is enough for PHONE to serve as the primary key of CUSTOMER, problems may occur because PHONE is not data-less, and is also subject to change. What happens if a customer changes his or her phone number? Should the database be updated to reflect a new value for a primary key? This causes a number of database maintenance problems. These maintenance problems stem from using the primary key to link records in different tables. If the primary key value is changed for a record in the CUSTOMER table, related records in other tables must also be changed. Then, it is better to simply leave the old phone number in, but this introduces an inaccuracy in the database.

When no suitable primary key can be found among the existing attributes for an entity, it is acceptable, and in fact usually a good idea, to simply create a new attribute. These newly created primary keys are counters that increment with each new instance of an entity in the same manner in which a check number increments from one check to the next.

For the Order Entry example, we need to identify primary keys for three entities: ORDER, PRODUCT, and CUSTOMER. Fortunately, all of these entities have attributes that make acceptable primary keys. For ORDER, ORDER-NO is a good choice for the primary key, for PRODUCT, PROD-ID makes an acceptable key, and CUSTOMER-ID is a good key for CUSTOMER. Notice that all of these satisfy our requirements for a primary key. They are unique for each occurrence of the entity. They will never be null. They are data-less, and they are not subject to change.

Once all of the entities and their attributes and primary keys are identified, the actual drawing of the E-R diagram can begin. Simply draw a rectangle for each entity, labeling the rectangle with the entity name. Then add an ellipse for each attribute, making sure each is labeled and connected to the proper entity. Finally, indicate primary keys by underlining each primary key attribute. Figure 9 shows the results for the Order Entry example.

Figure 9: Order Entry Form Entities and Attributes

When all of the entities, attributes and primary keys are modeled, it is time to identify and model the relationships between entities.


Step 3: Model Relationships

Relationships among entities are a critical part of the Entity Relationship Diagram. When these relationships are implemented in the database, they provide the links among the various tables that give the database its flexibility. To maximize the flexibility of a database, relationships must be properly identified and modeled.

Many relationships are relatively easy to recognize, such as those between ORDERS and CUSTOMERS, or between STUDENTS and COURSES. Others, however, are less clear. Although becoming truly proficient at recognizing relationships requires experience and practice, there are some general guidelines that can help the database analyst recognize relationships.

When examining forms, reports and entry screens, be on the lookout for entities whose attributes appear on the same form, report or screen. Analysis of the Order Entry Form (Figure 9) reveals three entities: CUSTOMER, ORDER, and PRODUCT. We can conclude that these entities may be related to one another. But how are they related? In cases where the form being analyzed only represents two entities, the relationship is fairly obvious—the relationship is between the two entities. However, with the Order Entry Form, there are three entities represented. Further consideration is required to determine how these entities are related. Knowing something about the organization is particularly helpful in such situations.

In the case of the Order Entry Form example, ORDER and CUSTOMER are related, as are ORDER and PRODUCT. However, we know that there may not necessarily be a relationship between PRODUCT and CUSTOMER. Because both are related to ORDER we can report which products are ordered by a particular customer.

In some cases, the analyst works from user interviews, rather than forms. In these cases, the analyst can examine transcripts of user interviews to determine the relationships among entities. Often users mention related entities in the same sentence. Consider the excerpt from a user interview shown in Figure 10. Notice how the user talks about products and orders in the same sentence. Once CUSTOMERS and ORDERS are identified as entities, mentioning both in the same comment is a good indication that the entities are related. This interview also tells us that we need to store some additional information about products. It also lets us know that there are two prices, a selling price and a retail price. We’ll deal with these later.

ANALYST: What information do you need to know about orders?USER: Well, for each order , we need to know the order identifier and date as well as the customer placing the order. We also need to know what products are included in each order along with the quantity and selling price for each product on the order.ANALYST: What information do you need to know about products, beyond the information needed for an order?USER: Of course, we need a product ID and description. Oh yea, we also need to store a standard selling price, you know, a retail price.

Figure 10: Excerpt from a User Interview

As you identify relationships, note them on the E-R diagram as discussed earlier. Assign some meaningful name to the relationship and add the name to the relationship diamond. When having difficulty in coming up with a relationship name, many analysts simply combine the names of the entities on either side of the relationship. For example, a relationship between STUDENT and MAJOR could be called STUDENT-MAJOR. The diagram for the Order Entry Form is shown in Figure 11. Note that we still need to add cardinalities, which we’ll do in the next step.

Figure 11: Relationships for Order Entry Database

After all of the relationships are modeled in the E-R diagram, the cardinalities of the relationship must be determined.


Step 4: Determine Cardinalities

Recall that there are both maximum and minimum cardinalities. The maximum cardinality of a relationship is the number of instances of one entity in a relationship that can be related to a single instance of another entity in the relationship. In contrast, the minimum cardinality is the number of instances of one entity that

mustbe related to a single instance of the related entity. Many novice database analysts find determining the cardinalities of relationships more confusing than identifying entities, attributes and relationships. Thankfully there are ways to make this task easier.

It is common to determine maximum cardinalities before minimum cardinalities. There are two parts to the maximum cardinality of a binary relationship, one for each entity. Recall the example of the ADVISOR-STUDENT relationship discussed earlier in this chapter. One ADVISOR can be related to many STUDENTS. This indicates that the STUDENT side of the relationship has a many cardinality. This is only half of the relationship’s cardinality, however. To complete the cardinality, we must recognize that one STUDENT can be related to only a single ADVISOR. This tells us that the ADVISOR side of the relationship has a cardinality of 1. Thus, the cardinality of the ADVISOR-STUDENT relationship is one-to-many.

One technique that often helps analysts determine the proper cardinality of a relationship is to give the instance of the single instance side of the relationship a name. For example, consider trying to determine the cardinality of the STUDENT-COURSE relationship. First, hold the STUDENT entity to a single instance to determine the cardinality of the COURSE side of the relationship. This is easier to do if, rather than saying “How many COURSES can a STUDENT be related to?” say “How many COURSES can Mary Wilson be related to?” The answer, of course, is many, so the COURSE side of the relationship has a many cardinality. To determine the cardinality of the STUDENT side of the relationship, ask “How many students can be related to MIS380?” Once again, the answer is many, indicating that the STUDENT side of the relationship also has a many cardinality. When asking these cardinality questions, remember that you are always determining what cardinality symbol to draw next to the entity you are

notholding to one instance.

Let’s return to the Order Entry Form example. There are two relationships, one between PRODUCT and ORDER, and the other between ORDER and CUSTOMER. One instance of ORDER can be related to many instances of PRODUCT. In other words, one ORDER can contain many PRODUCTS. Thus, the cardinality from ORDER to PRODUCT is many. Turning to the other side of the relationship, we can see that one instance of PRODUCT can be related to many instances of ORDER—a single PRODUCT can be on many ORDERS. So, the cardinality from PRODUCT to ORDER is many. Combining the two sides gives us a many-to-many cardinality between ORDER and PRODUCT.

Now we must analyze the relationship between CUSTOMER and ORDER. A single ORDER can be related to only one CUSTOMER. In other words, a single ORDER can’t be placed by more than one CUSTOMER. This means that the cardinality from ORDER to CUSTOMER is one. On the other side, a single CUSTOMER can place many ORDERS, so the cardinality from CUSTOMER to ORDER is many, and we can say that the cardinality of the CUSTOMER-ORDER relationship is one-to-many.

Now the minimum cardinalities must be determined. Both maximum and minimum cardinalities are determined by

business rules. However, cardinalities are sometimes less obvious than others when the analyst does not have good knowledge of the organization. For example, it is relatively easy to determine that an ORDER must be related to at least one CUSTOMER, indicating that the minimum cardinality from ORDER to CUSTOMER is one. The same can be said for ORDER and PRODUCT. But, must a CUSTOMER be related to at least one ORDER? This is less clear. Maybe the organization allows customers to set up accounts prior to placing their first order. In this case, the minimum cardinality from CUSTOMER to ORDER is zero. We will make this assumption for the Order Entry Form example. The situation is similar for the minimum cardinality from PRODUCT to ORDER. It is reasonable to assume that we have products that have not been ordered yet. If this is allowed, then the minimum cardinality from PRODUCT to ORDER is zero.

Assumptions should only be temporary in actual practice. The analyst may have to make assumptions in order to proceed with the analysis, but it is critical that the validity of the assumptions be checked with the users before completing the analysis [and definitely before implementation!].

Figure 12 shows the Order Entry ER Diagram with maximum and minimum cardinalities indicated. It is now time for the last step in the E-R Diagramming process, checking the model.

Figure 12: Incomplete Order Entry ERD with Cardinalities


Step 5 – Check the Model

The final step in creating an E-R diagram is often overlooked, but is just as important as any of the previous steps. Analysts who fail to carefully check their ERD often produce diagrams of poor quality, which of course should be avoided.

In order to check the ERD, you must return to your original information sources, the forms, reports, and interviews with users. The basic idea is to go back to the original documents and make sure that the structure represented in the ERD can satisfy the requirements. For example, the representations in the ERD must be able to reproduce any forms or reports required. We will use the Order Entry Form, which for convenience is reproduced below, as an example.

ORDER ENTRY FORM

ORDER-NO: 44-44-4444 CUSTOMER-ID: 1002
DATE: 10/31/98 CUST-NAME: ABC Inc.
PROD-ID DESCRIPTION PRICE QTY EXT
A123 STEREO SYSTEM 375.00 2 750.00
C235 8″ SPEAKER 150.00 8 1,200.00
X002 SPEAKER WIRE 10.00 5 50.00
TOTAL 2,000.00

Figure 8: Order Entry Form (repeated)

The first step in using the Order Entry Form to check the ERD is to make sure that all of the information contained in the form is also represented on the ERD. The easiest way to do this is to take a copy of the form and check off each item as you verify that the item is on the ERD.

Examination of the Order Entry Form shows that there are three items that are not represented on the ERD: EXT, TOTAL, and QTY. Both EXT, which is short for Extended, and TOTAL can be computed, so it is not necessary to store these in the database, or represent them on the ERD. In most cases, it is not necessary to store attributes that can be computed. There are times, however, when it may make sense to store an item that can be computed. For example, TOTAL can be computed, but doing so required retrieving data from multiple records. It may be that storing this value improves performance enough to justify storing TOTAL.

Unlike EXT and TOTAL, QTY (Quantity) can not be computed, and therefore must be stored in the database. Once the analyst decides that QTY should be represented on the ERD, the question becomes how to represent it. Initial ideas might include representing QTY as an attribute of ORDER, or of PRODUCT. However, QTY is not really an attribute of either of these, but is properly represented as an attribute of the

relationshipbetween ORDER and PRODUCT. It is not unusual to have attributes attached to relationships with a many-to-many cardinality.

There is an additional, more subtle, decision that must be made. This regards the price shown in the order form. We must decide if price is a function of the product alone (as identified by ProductID), or if the price depends on both the product and the order. To make this decision, we must ask the following question. Is it possible for the same product to have different prices on different orders? If the answer is no, then price depends on the product alone and the price attribute should be included in the PRODUCTS entity. If the answer is yes, then price is a function of both product and order, and should be placed on the relationship between PRODUCTS and ORDERS. Typically, the latter is the case, so we’ll place the price attribute on the relationship. Note that we are calling this attribute “SellingPrice” to indicate that is the price for which a product was sold a particular order. If we wanted to store a standard price, we would probably call it “RetailPrice” or something similar. Using more specific attribute names leads to less confusion when reading the ERD or resulting database.

Adding Quantity to the ERD results in the complete ERD, which is shown in Figure 13.

Figure 13: Complete Order Entry ERD

About these ads
Follow

Get every new post delivered to your Inbox.

Join 115 other followers