Kroenke-Auer-DBP-E12-IM-Appendix-C

Full file at Solution-Manual-for-Database-Processing,-12E-David-M.-Kroenke-David-Auer--

INSTRUCTOR’S MANUAL

TO ACCOMPANY

Database Processing

Fundamentals, Design, and Implementation

12th Edition

APPENDIX C

E-RDIAGRAMS AND THE IDEF1X STANDARD

Prepared By

David J. Auer

Western Washington University

Instructor’s Manual — Appendix C — 10 9 8 7 6 5 4 3 2 1

Appendix C – E-R Diagrams and the IDEF1X Standard

CHAPTER OBJECTIVES

To understand IDEF1X standard E-R diagrams
To be able to model non-identifying connection relationships, identifying connection relationships, nonspecific relationships, and categorization relationships using IDEF1X E-R model
To understand the differences between E-R generalization/subtype relationships and IDEF1X categorization relationships
To understand the use of domains in the IDEF1X E-R model

ERRATA

There are no known errors at this time. Any errors that are discovered in the future will be reported and corrected in the Online DBP e12 Errata document, which will be available at

TEACHING SUGGESTIONS

Figure C-1 (reproduced below) provides a good transition between the enhanced E-R model and the IDEF1X model. Use it to introduce the IDEF1X model and compare it to the enhanced E-R model.
You can organize your lecture around E-R model HAS-A and IS-A relationships, stressing the corresponding IDEF1X equivalents. Teach 1:1, 1:N, and N:M relationships as varieties of HAS-A relationships. Teach categorization clusters as IS-A relationships.
Domains are probably the most useful but most overlooked element of database design. Stress the importance of domains to clarify attributes and reduce the maintenance requirements.

Figure C-1 – Correspondence of Terms between the Extended E-R Model and the IDEF1X Version of the E-R Model

Page C-1

Appendix C – E-R Diagrams and the IDEF1X Standard

ANSWERS TO REVIEW QUESTIONS

C.1.Why is the IDEF1X model important?

IDEF1X became a national standard and companies that sold data modeling tools had to conform to IDEF1X in order to sell to the government. Such a large market could not be ignored, so most of today’s popular data modeling products, such as ERWin and Visio, use IDEF1X. This means that IDEF1X is the E-R version you are most likely to encounter, even more so than the extended E-R model.

C.2.Name four types of IDEF1X relationships.

The four types of IDEF1X relationships are (1) non-identifying connection relationship, (2) identifying connection relationship, (3) nonspecific relationship, and (4) categorization relationship.

C.3.What is a non-identifying connection relationship? How do such relationships relate to the extended E-R model described in Chapter 5?

Non-identifying connection relationships are 1:1 or 1:N relationships between two non-ID dependent entities. These are the same as the 1:1 and 1:N HAS-A relationships in the extended E-R model.

C.4.What is an identifying connection relationship? How do such relationships relate to the extended E-R model described in Chapter 5?

Identifying connection relationships are relationships between to ID dependent entities. These are the same as ID-dependent relationships in the extended E-R model.

C.5.What is a nonspecific relationship? How do such relationships relate to the extended E-R model described in Chapter 5?

Nonspecific relationships are many-to-many relationships. These are the same as the N:M HAS-A relationships in the extended E-R model.

C.6.Explain the major differences between categorization relationships and supertype/subtype relationships in the extended E-R model described in Chapter 5.

There are two major differences between categorization relationships and supertype/subtype relationships:

(1) In categorization relationships, category entities (subtypes) are always grouped into mutually exclusive categorization clusters, and

(2) There are two specific types of categorization clusters – complete (every possible category in the cluster is shown) and incomplete (not all categories are shown).

In the E-R model, subtypes are not mutually exclusive, and there is no distinction between complete and incomplete groups of subtypes.

C.7.What are the major advantages of the modeling of domains?

A domain is a named set of value that can be assigned to an attribute. The advantages are:

(1) Domains reduce ambiguity for attributes with values that look similar but are not the same. For example, the work LANGUAGE may mean a computer language such as C# or Java, or a spoken/written language such as English. By defining a domain, no inappropriate values will be entered in the attribute.

(2) Domains are efficient. If several attributes share one domain, a change in the set of values in that domain immediately affects all the variables. Without this feature, each attribute’s allowed values would have to be changed individually.

(3) Domains provide a reference of determining the definition of an attribute. If two or more attributes appear to be similar, their definitions can be checked by checking their domains.

C.8.Redraw the E-R diagram in Figure 5-52 using IDEF1X.

Note that Erwin always requires the user to switch between Information Engineering [IE] and IDEF1X notation. From the menu bar, choose Model | Model Properties | Notation Tab. The student can either draw the model in IDEF1X originally, or duplicate Figure 5-52 in IE crow’s foot style and then switch to the IDEF1X notation.

C.9.Answer question 5.58, but use IDEF1X instead of the crow's foot model.

RQ 5-58 – Examine the subscription form shown in Figure 5-53. Using the structure of this form, do the following:

Figure 5-53 – Subscription Form

Create a model with one entity. Specify the identifier and attributes.

Create a model with two entities, one for customer and a second for subscription. Specify identifiers, attributes, relationship name, type, and cardinalities.

Under what conditions do you prefer the model in A to that in B?

Model A would be the best model if a Customer can only have one subscription.

Under what conditions do you prefer the model in B to that in A?

Model B would be the best model if a Customer can have one or more (i.e., multiple) subscriptions.

C.10.Answer question 5.62, but use IDEF1X instead of the crow's foot model. Redraw Figure 5-58 in IDEF1X notation.

Figure 5-58 is shown redrawn in the question below.

RQ- 5.62.Figure 5-57 shows the specifications for single-stage air compressor products. Note that there are two product categories that are based on Air Performance: The A models are at 125 pounds per square inch of pressure, and the E models are at 150 pounds per square inch of pressure. Using the structure and example data items in this list, do the following:

Figure 5-57 – Air Compressor Specifications

Figure 5-58 – Alternate Model for Compressor Data

ACreate a set of exclusive subtypes to represent these compressors. The supertype will have attributes for all single-stage compressors, and the subtypes will have attributes for products having the two different types of Air Performance. Assume that there might be additional products with different types of Air Performance. Specify the entities, identifiers, attributes, relationships, type of category cluster, and possible determinant.

The first entity will be SS_COMPRESSOR, with an identifier of Model.

Note that the date in Figure 5-57 shows model numbers with an “A” to indicate the type A =125 PSI Air Performance Characteristics – for example F12A-17. The data sheet notes that type E = 150 PSI units are indicated by substituting an “E” for the “A” – for example, F12E-17. In our data model, we will design the identifier Model to specify models with an “x” as an indeterminate place holder for model type – “x” as in F12x-17.

An attribute named ModelType (with values “A”, “E” and others when available) will be a determinant of the subtypes. An instance diagram looks like this:

The entities, identifiers, attributes, and relationships are shown in the diagram. This is an incomplete category cluster – according to the question, there may be “additional products with different types of Air Performance” which means there may be more models then just the A and E models shown.

BFigure 5-58 shows a different model for the compressor data. Explain the entities, their type, the relationship, its type, and its cardinality. How well do you think this model fits the data shown in Figure 5-57?

Figure 5-58 – Alternate Model for Compressor Data

In the model in Figure 5-60, SS_COMPRESSOR is a strong entity and AIR_PERFORMANCE_TYPE is an ID-Dependent weak entity. The relationship is one-to-many since an SS_COMPRESSOR may have more than one AIR_PERFORMANCE_TYPE. However, the maximum cardinality of the one-to-many relationship is currently two (2) since there are currently only two AIR_PERFORMANCE_TYPEs. The relationship is ID-dependent since the AIR_PERFORMANCE TYPE entity is meaningless without an associated SS_COMPRESSOR.

Unfortunately, the model misses the fact that each AIR_PERFORMANCE_TYPE is related to many SS_COMPRESSORs, and therefore the relationship should be N:M (or N:2 given the limit of only two (2) existing AIR_PERFORMANCE_TYPESs. This can be seen in the following instance diagram:

Further, each combination will have at least one additional attribute associated with it – namely Price. Therefore a better model would be an ID-Dependent associate model, with two strong entities – SS_COMPRESSOR and AIR_PERFORMANCE TYPE. That model would look like this:

CCompare your answer in question A with the model in Figure 5-58. What are the essential differences between the two models? Which do you think is better?

Model A models the ModelType as an incomplete category cluster currently with two subtypes. The model will allow additional types because it is incomplete but the additional types will just repeat the PumpRPM, CFMDisp, and DELDAir attributes. Unless there are other attributes to differentiate and distinguish between the subtypes, this is an inappropriate use of subtypes. Subtypes are intended to capture varying sets of additional attributes, not repetitions of the same set. Further, this model also creates repeated sets of basic SS_COMPRESSOR values since there must be one set for each AirPerformanceType.

Model B models the MODELTYPE entity (as AIR_PERFORMANCE_TYPE) as an ID-dependent weak entity, which allows for an unlimited number of types even if they all have exactly the same set of attributes. This is a better way of modeling repeating sets of attributes. Unfortunately, the model missed the N:M nature of the basic relationship between SS_COMPRESOR and AIR_PERFORMANCE_TYPE.

Model B uses a better model for repeated sets of attributes, but fails by being a 1:N model rather than an N:M model, or, specifically an ID-Dependent associative pattern. Since both models have serious flaws, ultimately neither is better than the other.

DSuppose you had the job of explaining the differences in these two models to a highly motivated, intelligent end user. How would you accomplish this?

I would focus the discussion on:

(1) The ability of model A to use supertype/subtypes to handle different sets of attributes for a basic supertype, and

(2) The ability of model B to allow an unlimited number of air compressor characteristics. This makes the model much more versatile. But,

(3) I would then have to point out that neither does the complete job and that model C is needed.

Page C-1