Моделирование бизнес-процессов / Моделирование бизнес-процессов / ER-диаграмы / 0849315484 Entity-Relationship Diagrams

4.What is an overlap constraint? What symbol shows the overlap constraint in EER diagrams?

5.What does the subclass symbol signify?

6.Why would you create a generalization/specialization relationship rather than creating a "weak entity"?

7.How does "inheritance" play into the superclass/subclass relationship? Discuss.

Methodology and Grammar for Generalization/Specialization Relationships

We need to revisit step 6 in the ER Design Methodology to cover the possibility of generalization/specialization relationships. The previous version of step 6 was:

Step 6: State the exact nature of the relationships in structured English from all sides. For example, if a relationship is A:B::1:M, then there is a relationship from A to B, 1 to Many, and from B back to A, Many to 1.

For ternary and higher-order (n—ary) relationships, state the relationship in structured English, being careful to mention all entities for the n—ary relation. State the structural constraints as they exist.

We add the following sentence to step 6:

For specialization/generalization relationships, state the relationship in structured English, being careful to mention all entities (subclasses or specializations). State the structural constraints as they exist.

The grammar that we propose for specializations/generalizations relationships is similar to that we used in weak relationships. We add to the grammar to include the participation, overlapping/disjointness constraints:

The grammatical description for weak entities was:

For each weak entity, we do not assume that any attribute will be unique enough to identify individual entities. Because the weak entity does not have a candidate key, each weak entity will be indentified by key(s) belonging to the strong entity.

In the case of our athlete, a first attempt to describe the subclass identified by a superclass becomes:

For each ATHLETE in a SPORT, we do not assume that any sport attribute will be unique enough to identify individual SPORT entities. Because the SPORT does not have a candidate key, each SPORT will be identified by inheriting key

(s) belonging to ATHLETE.

So, a more complete EER diagram grammatical pattern would say:

For each specialization, we do not assume that any attribute will be unique enough to identify individual entities. Because the specialization does not have a candidate key, each specialization will be identified by key(s) inherited from the generalization. Further, specializations overlap [or are disjoint].

[Explain the overlap/disjoint feature]. The individual specialization is identified by a defining predicate, attribute name, which will be contained in generalization.

For Figure 8.3A, the pattern becomes:

For each sport, we do not assume that any attribute will be unique enough to identify individual entities. Because the sport does not have a candidate key, each sport will be identified by key(s) inherited from ATHLETE. Further, the sports overlap. Athletes may play more than one sport. The individual sport is

identified by a defining predicate attribute (sport) that will be contained in ATHLETE. The sports we will record are golf, tennis, and football.

Mapping Rules for Generalizations and

Specializations

In this section we present mapping rules to map generalizations and specializations to a relational database:

M7 — For each generalization/specialization entity situation, create one table for the generalization entity (if you have not done so already per the earlier steps) and create one table for each specialization entity. Add the attributes for each entity in their each respective tables. Add the key of the generalization entity into the specialization entity. The primary key of the specialization will be the same primary key as the generalization.

For example, refer to Figure 8.3A. The generalization/specialization relationship between the ATHLETE and TENNIS, GOLF, and FOOTBALL would be mapped as follows:

ATHLETE

weight	name	gender	height	SS#		sport
140	Kumar	M	5.95	239-92-0983		golf
200	Kelvin	M	6.02	398-08-0928		football
135	Sarah	F	5.6	322-00-1234		tennis
165	Arjun	M	6.01	873-97-9877		golf
145	Deesha	F	5.5	876-09-9873		tennis
		TENNIS
ss#	state ranking		national ranking
322-00-1234		23		140
876-09-9873		47		260

GOLF

ss# handicap

239-92-0983 3

873-97-9877 1

FOOTBALL

ss#	position
398-08-0928	tackle
239-92-0983	quarter back

398-08-0928

full back

The key of the generalization entity (ss#) is added to the specialization entities TENNIS, GOLF, FOOTBALL. ss# also becomes the primary key of the specialization entities.

So, our ER design methodology (with one component of the EER model — the generalization/specialization component) has finally evolved to the following:

ER Design Methodology

Step 1: Select one, primary entity from the database requirementsdescription and show attributes to be recorded for that entity. Label keys if appropriate and show some sample data.

Step 2: Use structured English for entities, attributes, and keys to describe the database that has been elicited.

Step 3: Examine attributes in the existing entities (possibly with user assistance) to find out if information about one of the entities is to be recorded.

(We change "primary" to "existing" because we redo step 3 as we add new entities.)

Step 3a: If information about an attribute is needed, make the attribute an entity, and then

Step 3b: Define the relationship back to the original entity.

Step 4: If another entity is appropriate, draw the second entity with its attributes. Repeat steps 2 and 3 to see if this entity should be further split into more entities.

Step 5: Connect entities with relationships (one or more) if relationships exist.

Step 6: State the exact nature of the relationships in structured English from all sides. For example, if a relationship is A:B::1:M, then there is a relationship from A(1) to B(M) and from B(M) back to A(1).

For ternary and higher-order (n-ary) relationships, state the relationship in structured English, being careful to mention all entities for the n-ary relationship. State the structural constraints as they exist.

For specialization/generalization relationships, state the relationship in structured English, being careful to mention all entities (subclasses or specializations). State the structural constraints as they exist.

Step 6a: Examine the list of attributes and determine whether any of them need to be identified by two (or more) entities. If so, place the attribute on an appropriate relationship that joins the two entities.

Step 6b: Examine the diagram for loops that might indicate redundant relationships. If a relationship is truly redundant, excise the redundant relationship.

Step 7: Show some sample data.

Step 8: Present the "as designed" database to the user, complete with the English for entities, attributes, keys, and relationships. Refine the diagram as necessary.

Checkpoint 8.2

1.How are the mapping rules for generalizations/specializations different from the mapping rules for weak entities?

2.Map Figure 8.4 to a relational database and show some sample data.

Chapter Summary

In this chapter we described the concepts of specialization/generalization. The concepts of overlap and disjoint were also presented. This chapter approached EER diagrams as discussed by Elmasri and Navathe (2000) and Connolly et al. (1998). Some authors, for example Sanders (1995), use a close variation of this model, and call the specialization/generalization relationship an "IsA" relationship. Although we do not discuss "unions" and "categories," "hierarchies and lattices," these are further, uncommon extensions of a generalization/specialization relationship as presented by Elmasri and Navathe (2000).

This chapter also concluded the development of the EER design methodology. In the next chapter we will discuss mapping ER and EER diagrams to the relational model as well as reverse-engineering.

Chapter 8 Exercises

Exercise 8.1

Draw an ER diagram for a library for an entity called "library holdings." Include as attributes the call number, name of book, author(s), and location in library. Add a defining predicate of "holding type," and draw in the disjoint, partial specializations of journals and reference books, with journals having the attribute "renewal date" and reference books having the attribute "checkout constraints." Map this to a relational database and show some sample data.

Exercise 8.2

Draw an ER diagram for computers at a school. Each computer is identified by an id number, make, model, date acquired, and location. Each computer is categorized as a student computer or a staff computer. For a student computer, an attribute is "hours available." For a staff computer, an attribute is "responsible party" ("owner" if you will). Map this to a relational database and show some sample data.

References

Connolly, T., Begg, C., and Strachan, A., Database Systems, A Practical Approach to Design, Implementation, and Management, AddisonWesley, Harlow, England, 1998.

Elmasri, R and Navathe, S.B., Fundamentals of Database Systems, 3rd ed., Addison-Wesley, Reading, MA, 2000.

Sanders, L., Data Modeling, Boyd & Fraser Publishing, Danvers, MA, 1995.

Teorey, T.J., Database Modeling and Design, Morgan Kaufman, San Francisco, CA, 1999.

Case Study: West Florida Mall (continued)

Thus far in our case study, we have developed the major entities and relationships, and mapped these to a relational database (with some sample data). Then, upon reviewing step 7, which says:

Step 7: Present the "as-designed" database to the user, complete with the English for entities, attributes, keys, and relationships. Refine the diagram as necessary.

Suppose we obtained some additional input from the user:

A PERSON may be an owner, employee, or manager. For each PERSON, we will record the name, social security number, address, and phone number.

Upon reviewing these additional specifications, we came up with one new entity, PERSON.

Now, repeating step 2 for PERSON:

The Entity

This database records data about a PERSON. For each PERSON in the database, we record a person's name (pname), person's social security number (pssn), person's phone (pphone), and person's address (padd).

The Attributes for PERSON

For each PERSON there will always be one and only one pname (person's name) recorded for each PERSON. The value for pname will not be subdivided.

For each PERSON, there will always be one and only one pssn (person's social security number) recorded for each PERSON. The value for pssn will not be subdivided.

For each PERSON there will always be one and only one pphone (person's phone) recorded for each PERSON. The value for pphone will not be subdivided.

For each PERSON there will always be one and only one padd (person's address) recorded for each PERSON. The value for padd will not be subdivided.

The Keys

For each PERSON, we will assume that the pssn will be unique.

These entities have been added to the diagram in Figure 8.5.