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

Smith	123 4th Street	St. Helens
Smith	123 4th Street	Mountain
Smith	123 4th Street	Volcano
Jones	222 2nd St	Manatee U
Jones	222 2nd St	Everglades High
Sudip	887 Mirabelle	PCA
Sudip	887 Mirabelle	Pensacola High
Sudip	887 Mirabelle	UWF
Pradeep	248 Shillingford	Cuttington
Pradeep	248 Shillingford	UT

Note that rule M1c is an application of the non-1NF to 1NF transformation discussed in Chapter 1.

Checkpoint 2.3

1.How do you map multi-valued attributes?

2.How do you map composite attributes?

3.What is a unique identifier? Is it a candidate key? Is it "the" primary key? Discuss.

[4]These mapping rules are adapted from Elmasri and Navathe (2000).

Chapter Summary

The main focus in this chapter was on developing the concept of the entity and developing a one-entity diagram (using the Chen-like model). The concept of attributes was also discussed, and the final section focused on how a one-entity diagram could be mapped to a relational database. The grammar for a one-entity diagram and its attributes was also developed. This grammar will be further developed in subsequent chapters. Chapter 3 discusses developing a second entity, and the relationship between this second entity and the "primary entity."

Chapter 2 Exercises

[Note: You should filter out and clarify the assumptions you made when you report your work.]

Exercise 2.1

You want to create a database about businesses. Each business will have a name, an address, the business phone number, the owner's phone number, and the first names of the employees who work at the business. Draw the ER diagram using the Chen-like model, and then write the English description for your diagrams. Compare the English to your diagrams, and state any assumptions you made when drawing the diagrams. Map your diagrams to a relational database.

Which attributes would you consider composite attributes in this database? Which attributes would you consider multi-valued attributes in this database? Could there be any derived attributes? What would be good keys?

Exercise 2.2

You want to create a database about the books on your shelf. Each book has authors (assume last name only is needed), title, publisher, courses used in (course number only). Draw the ER diagram using the Chen-like model, and then write out the English description for your diagrams.

Compare the English to your diagrams and state any assumptions you made when drawing the diagrams.

Which attributes would you consider composite attributes in this database? Which attributes would you consider multi-valued attributes in this database? Could there be any derived attributes? What would be good keys? Map your diagram to a relational database.

References

Batini, C., Ceri, S., and Navathe, S.B. Conceptual Database Design, Benjamin Cummings, Redwood City, CA, 1992.

Chen, P.P. "The Entity Relationship Model — Toward a Unified View of Data," ACM Transactions on Database Systems, 1(1), 9–37, March 1976.

Chen, P.P. "The Entity-Relationship Model: A Basis for the Enterprise View of Data," Proceedings IFIPS NCC 46, No. 46, 76–84, 1977.

Codd, E. Relational Model for Data Management – Version 2, AddisonWesley, Reading, MA, 1990.

Date, C.J. An Introduction to Database Systems, 5th ed., AddisonWesley, Reading, MA, 1995.

Earp, R. and Bagui, S. "Building an Entity Relationship Diagram: A Software Engineering Approach," Database Management, Auerbach Publications, Boca Raton, FL, 22-10-41, 1–16, December 2000.

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

McFadden, F.R. and Hoffer, J.A. Modern Database Management, 4th ed., Benjamin Cummings, Menlo Park, CA, 1994.

Navathe, S. and Cheng, A. "A Methodology for Database Schema Mapping from Extended Entity Relationship Models into the Hierarchical Model," The Entity-Relationship Approach to Software Engineering, G.C. Davis et al., Eds., Elsevier, North-Holland, Amsterdam, 1983.

Scheuermann, P., Scheffner, G., and Weber, H. "Abstraction Capabilities and Invariant Properties Modeling within the EntityRelationship Approach," Entity-Relationship Approach to System Analysis and Design, P. Chen, Ed., Elsevier, North-Holland, Amsterdam, 121–140, 1980.

Teorey, T.J., Yang, D., and Fry, J.P. "A Logical Design Methodology for Relational Databases Using the Extended Entity-Relationship Model," ACM Computing Surveys, 18(2), 197–222, June 1986.

Valacich, J.S., George, J.F., and Hoffer, J.A., Essentials of Systems Analysis and Design, Prentice Hall, Upper Saddle River, NJ, 2001.

Case Study: West Florida Mall

A new mall, West Florida Mall, just had its grand opening three months ago in Pensacola, Florida. This new mall is attracting a lot of customers and stores. West Florida Mall, which is part of a series of malls owned by a parent company, now needs a database to keep track of the management of the mall in terms of keeping track of all its stores as well as the owners and workers of the stores. Before we build a database for this system of malls, the first step will be to design an ER diagram for the mall owner. We gathered the following initial user specifications about the malls, with which we can start creating our the ER diagram:

We need to record information about the mall and each store in the mall. We will need to record the mall's name and address. A mall, at any point in time, must contain one or more stores.

For each store, we will need to keep the following information: store number (which will be unique), the name of the store, the location of the store (room number), departments, the owner of the store, and manager of the store. Each store may have more than one department, and each department is managed by a manager. Each store will have only one store manager. Each store is owned by only one owner. Each store is located in one and only one mall.

A store manager can manage only one store. We have to record information on the store manager: the name, social security number, which store he or she is working for, and salary.

The store owner is a person. We have to record information about the store owner, such as name, social security number, address, and office phone number. A store owner has to own at least one store, and may own more than one store.

Developing the Case Study

As per step 1 in designing the ER diagram, we must select our primary entity, and then the attributes for our primary entity (step 1 is shown below):

Step 1: Select one primary entity from the database requirements description and show attributes to be recorded for that entity.

We will choose MALL as our primary entity. For the MALL we will record a name, an address, and store_names.

Our next step will be to translate the diagram into English.

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

The Entity

This database records data about a MALL. For each MALL in the database, we record a name, an address, and store_names.

The Attributes for MALL

For each name, there always will be one and only one name for the mall. The value for name will not be subdivided.

For each address, there always will be one and only one address for the mall. The value for address will not be subdivided.

For each MALL, we will record store_names. There may be more than one store_name recorded for each MALL. The value of each store_name will not be subdivided.

The Keys

For each MALL, we will assume that the mall name (name) will be unique.

The MALL entity is shown in Figure 2.9. So far for this case study, we selected one primary entity (MALL), showed its known attributes, and used structured English to describe the entity and its attributes. Next, we will map this entity diagram to a relational database.

Figure 2.9: The MALL Entity

Mapping the Entity to a Relational Database

MALL is a strong entity, so using mapping rule M1 which states:

M1 — for strong entities: develop a new table for each strong entity and make the indicated key of the strong entity the primary key of the table. If more than one candidate key is indicated on the ER diagram, choose one for the primary key.

We will develop a new relation for the entity MALL (as shown in Figure 2.9), and name will be our primary key. Data that would be represented by Figure 2.9 might look like the following:

	MALL
name	address	store_names
West Florida	N Davis Hwy,	Penney's, Sears, Dollar Store,
Mall	Pensacola, FL	Rex
Cordova Mall	9th Avenue,	Dillards, Parisian, Circuit City,
	Pensacola, FL	Radio Shack
Navy Mall	Navy Blvd,	Belks, Wards, Pearle Vision,
	Pensacola, FL	McRaes, Sears
BelAir Mall	10th Avenue,	Dillards, Sears, Penney's, Best
	Mobile, AL	Buy, Pizza Hut

We can see that MALL has a multi-valued attribute, store_names. This does not make the above table a relational table because store_names is not atomic — it is multi-valued. For multi-valued attributes, the mapping rule is:

M1c. For multi-valued attributes, form a separate table for the multi-valued attribute. Record a row for each value of the multivalued attribute together with the key from the original table. Remove the multi-valued attribute from the original table.

Using this mapping rule, the above data would be mapped to two relations: a relation with the multi-valued attribute and a relation with the multi-valued attribute excised.

Relation with the Multi-Valued Attribute:

MALL–Store

name store_name

West Florida Mall	Penney's
West Florida Mall	Sears
West Florida Mall	Dollar Store
West Florida Mall	Rex
Cordova Mall	Dillards
Cordova Mall	Parisian
Cordova Mall	Circuit City
Cordova Mall	Radio Shack
Navy Mall	Belks
Navy Mall	Wards
Navy Mall	Pearle Vision

MALL–Store name store_name

Navy Mall	McRaes
Navy Mall	Sears
BelAir Mall	Dillards
BelAir Mall	Sears
BelAir Mall	Penney's
BelAir Mall	Best Buy
Bel Air Mall	Pizza Hut

Relation with the Multi-Valued Attribute Excised

MALL

name	address
West Florida Mall	N Davis Hwy, Pensacola, FL
Cordova Mall	9th Avenue, Pensacola, FL
Navy Mall	Navy Blvd, Pensacola, FL
BelAir Mall	10th Avenue, Mobile, AL

Our relational database maps to:

[Note: The primary keys are underlined.] MALL–Store

name store_name MALL

name address

This case study will be continued at the end of Chapter 3.

Chapter 3: Beyond the First Entity

Diagram

Overview

Now that we have devised a method for drawing, interpreting, and refining one primary entity, we need to move to more complex databases. To progress from here, we continue with our primary entity and look for other information that would be associated with (related to) that entity.

The first technique employed in this chapter is methodical; we test our primary entity to see whether or not our "attributes" should be entities themselves. We will then look for other pieces of information in our description, add them to (1) an existing entity and examine the existing ER diagram, or (2) create a new entity directly. After creating the new entities, we look to see what kind of relationships exist between the two entities. This chapter develops steps 3, 4, and 5 of the ER design methodology presented in this book. Step 3 examines the attributes of the primary entity, step 4 discusses what to do if another entity is needed, and step 5 discusses developing the relationship between the two entities.

Although the concept of relationships is introduced in this chapter, we do not include any new mapping rules in this chapter because mapping rules can be better understood after the development of structural constraints on relationships, which is discussed in Chapter 4. At the end of this chapter, we continue with the case study that began in Chapter 2.

Examining an Entity — Changing an Attribute to an Entity

Consider Figure 3.1. In this figure, we have a student with the following attributes: name (a composite attribute), student number (an atomic attribute and key), schools (a multi-valued attribute). Suppose that during our first session with the user, we show the diagram, the English, and the sample data, and the user says, "Wait, I want to record all schools that a student attended and I want to record not only the name of the school, but also the location (city and state) and school type (community college, university, high school, etc.)."

Figure 3.1: A STUDENT Entity with a Multi-Valued

Attribute

What the user just told us was that the attribute, schools, should really be an entity. Remember that the definition of entity was something about which we wanted to record information. Our original thought was that we were recording schools attended, but now we are told that we want to record information about the schools. The first indicator that an attribute should be considered an entity is that we need to store information about the attribute. What we do then is migrate from Figure 3.1 to Figure 3.2. In Figure 3.2, SCHOOL is now an entity all by itself, so now we have two separate entities: SCHOOL and STUDENT. The next step is to define a relationship between the two entities. We assume school-name to be unique and choose the name of the school as the key for the entity, SCHOOL.