Downloadable Instructor Resources
Help downloading instructor resources
hapter 2 Modeling
Data in the Organization
Chapter Overview
The purpose of
this chapter is to present a detailed description of the entity-relationship
model and the use of this tool within the context of conceptual data modeling.
This chapter presents the basic entity-relationship (or E-R) model, while
advanced features are presented in Chapter 3.
Chapter Objectives
Specific student
learning objectives are included in the beginning of the chapter. From an instructor’s point of view, the
objectives of this chapter are to:
1. Emphasize
the importance of understanding organizational data, and convince your students
that unless they can represent data unambiguously in logical terms, they cannot
implement a database that will effectively serve the needs of management.
2. Present
the E-R model as a logical data model that can be used to capture the structure
and much, although not all, of the semantics (or meaning) of data.
3. Apply
E-R modeling concepts to several practical examples including the Pine Valley
Furniture Company case.
Key Terms
|
Associative
entity
|
Entity-relationship
diagram
(E-R diagram)
|
Relationship
instance
|
|
Attribute
|
Relationship
type
|
|
|
Binary
relationship
|
Entity-relationship
model
(E-R model)
|
Required
attribute
|
|
Business rule
|
Simple (or
atomic) attribute
|
|
|
Cardinality
constraint
|
Fact
|
Strong entity
type
|
|
Composite
attribute
|
Identifier
|
Term
|
|
Composite
identifier
|
Identifying
owner
|
Ternary
relationship
|
|
Degree
|
Identifying
relationship
|
Time stamp
|
|
Derived
attribute
|
Maximum
cardinality
|
Unary
relationship
|
|
Entity
|
Minimum
cardinality
|
Weak entity
type
|
|
Entity instance
|
Multivalued
attribute
|
|
|
Entity type
|
Optional
attribute
|
|
Classroom Ideas
1.
Review the major steps in the
database development process (Figure 1-10) and highlight the importance of data
modeling in determining the overall data requirements of information systems.
Lead a discussion concerning whom in the organization is typically most heavily
involved in each of the steps and how end users may best participate in the
process.
2.
Introduce the concept of
drawing models to represent information in a concise manner by having your
students participate in a small active exercise in map-making. Divide the
students into teams of 3-4 each so that you have an even number of teams in the
class. Instruct each team to work together to investigate and develop a map to
selected campus locations (you develop the list ahead of time; e.g., from this
classroom to the library, from this classroom to a colleague’s office, etc.).
Ask each team to verify the map they draw and then return to the classroom.
Pair up each team with a unique location with another team; ask the teams to
exchange maps. Instruct each team to then verify the map they received by
following it and then returning to the classroom. Conduct a debriefing
discussion about how easy/hard it was to follow the maps, how useful were the
symbols used, how easily understood were the symbols, etc. Use this discussion
to lead into the use of E-R notation used to represent data models and why
standardization is useful to systems development activities.
3.
Use the sample E-R diagram
shown in Figure 1 to “jump-start” your students’ understanding. Ask your
students to explain the business rules represented in this diagram.
4.
Use Figure 2 to summarize the
basic E-R notation used in this chapter (and throughout the remainder of the
text).
5.
Contrast the terms: entity type
and entity instance (see Figure 3). Discuss other examples: STUDENT (with each
student in the classroom as an instance), etc. Warn the students that the term
“entity” is often used either way; the meaning is intended to come from the context in which it is used.
6.
Give examples of common errors
in E-R diagramming, including inappropriate entities (see Figure 4). Ask your
students for other examples.
7.
Compare strong versus weak
entities using Figure 5. Ask your students for other examples.
8.
Discuss the various types of
attributes that are commonly encountered (Figures 7 through 9). Again, ask your
students to think of other examples.
9.
Make sure your students
understand the difference between relationship types and relationship instances
(Figure 10).
10.
Introduce the notion of an
associative entity by using Figure 11. Discuss the four reasons (presented in
the text) for converting a relationship to an associative entity.
11.
Discuss unary, binary, and
ternary relationships (Figure 12). Have the students brainstorm at least two
additional examples for each of these relationship degrees.
12.
Discuss the bill-of-materials
unary relationship (Figure 13). Use a simple and familiar product (such as a
toy) to illustrate this structure.
13.
Introduce the concept and
notation of cardinality constraints in relationships (Figures 16, 17, and 18).
Emphasize that these constraints are important expressions of business rules.
14.
Introduce the problem of
representing time dependent data. Use Figures 19 and 20 to illustrate different
means of coping with time dependencies.
15.
Discuss examples of multiple
relationships between entities (Figure 21). Ask your students to suggest other
examples.
16.
Use the diagram for Pine Valley
Furniture Company (Figure 22) to illustrate a more comprehensive E-R diagram.
Stress that in real-world situations, E-R diagrams are often much more complex
than this example.
17.
As time permits, have your
students work in small teams, 2 or 3 students each, to solve some of the E-R
diagramming exercises at the end of the chapter. We have included a number of
new examples for this purpose. Also, you may assign the project case as a
homework exercise.
Answers to Review Questions
1. Define each of the following terms:
a.
Entity type. A collection of entities
that share common properties or characteristics
b.
Entity-relationship model. A logical
representation of the data for an organization or for a business area
c.
Entity instance. A single occurrence of
an entity type
d.
Attribute. A property or characteristic
of an entity type that is of interest to the organization
e.
Relationship type. A meaningful
association between (or among) entity types
f.
Identifier. An attribute (or combination
of attributes) that uniquely identifies individual instances of an entity type
g.
Multivalued attribute. An attribute that
may take on more than one value for a given entity instance
h.
Associative entity. An entity type that associates the instances
of one or more entity types and contains attributes that are peculiar to the
relationship between those entity instances
i.
Cardinality constraint. Specifies the
number of instances of one entity that can (or must) be associated with each
instance of another entity
j.
Weak entity. An entity type whose
existence depends on some other entity type
k.
Identifying relationship. The
relationship between a weak entity type and its owner
l.
Derived attribute. An attribute whose
values can be calculated from related attribute values
m.
Business rule. A statement that defines
or constrains some aspect of the business
2.
Match the following terms and definitions:
i composite attribute
d associative
entity
b unary
relationship
j weak
entity
h attribute
m entity
e relationship
type
c cardinality
constraint
g degree
a identifier
f entity
type
k ternary
l bill-of-materials
3. Contrast the following terms:
a.
Stored attribute; derived attribute. A
stored attribute is one whose values are stored in the database, while a
derived attribute is one whose values can be calculated or derived from related
stored attributes.
b.
Simple attribute; composite attribute. A
simple attribute is one that cannot be broken down into smaller components,
while a composite attribute can be broken down into component parts.
c.
Entity type; relationship type. An
entity type is a collection of entities that share common properties or
characteristics, while a relationship type is a meaningful association between
(or among) entity types.
d.
Strong entity type; weak entity type. A
strong entity type is an entity that exists independently of other entity
types, while a weak entity type depends on some other entity type.
e.
Degree; cardinality. The degree (of a
relationship) is the number of entity types that participate in that relationship,
while cardinality is a constraint on the number of instances of one entity that
can (or must) be associated with each instance of another entity.
f.
Required attribute; optional attribute. A
required attribute must have a value for each entity instance, whereas an
optional attribute may not have a value for every entity instance.
g.
Composite attribute; multivalued attribute. A composite attribute has component parts that give meaning, whereas
a multivalued attribute may take one or more values for an entity instance.
h.
Ternary relationship; three binary relationships. A ternary relationship is a simultaneous relationship among the
instances of three entity types and often includes attributes unique to that
simultaneous relationship. Three binary relationships reflect the three two-way
relationships between two entity types, and do not depict the same meaning as a
ternary relationship.
4. Three reasons:
a.
The characteristics of data
captured during data modeling are crucial in the design of databases, programs,
and other system components. Facts and rules that are captured during this
process are essential in assuring data integrity in an information system.
b.
Data, rather than processes,
are the most important aspects of many modern information systems and hence,
require a central role in structuring system requirements.
c.
Data tend to be more stable
than the business processes that use the data. Thus, an information system that
is based on a data orientation should have a longer useful life than one based
on a process orientation.
5.
Four reasons:
a.
Business rules are a core
concept in an enterprise since they are an expression of business policy, and
they guide individual and aggregate behavior. Well-structured business rules
can be stated in a natural language for end users and in a data model for
system developers.
b.
Business rules can be expressed
in terms that are familiar to end users. Thus, users can define and then
maintain their own rules.
c.
Business rules are highly
maintainable: they are stored in a central repository and each rule is
expressed only once, then shared throughout the organization.
d.
Enforcement of business rules
can be automated through the use of software that can interpret the rules and
enforce them using the integrity mechanisms of the database management system.
6.
Where can you find business rules?
Business rules appear in descriptions of
business functions, events, policies, units, stakeholders, and other objects.
These descriptions can be found in interview notes from individual and group
information systems requirements collection sessions, organizational documents,
and other sources. Rules are identified by asking questions about the who,
what, when, where, why, and how of the organization.
Appendix A E-R Modeling Tools and Notation
Appendix Overview
The purpose of this appendix is to introduce one of the most popular
methods of modeling relational database systems. There are a variety of
software tools available to assist in the modeling of these types of systems.
This appendix will assist in comparing the notation used in the text with the
notation used in four commonly used packages: CA ERWin Data Modeler r8, Oracle
Designer 10g, Sybase PowerDesigner 16, and Microsoft Visio.
Appendix Objectives
From an instructor’s point
of view, the objectives of this appendix are to:
1.
Expose students to the look and
feel of different modeling tools.
2.
Illustrate the differences in
notation of various modeling tools.
3.
Review the entity symbols and
relationships available with different modeling tools.
4.
Discuss/review relationship and
cardinality types:
a.
1:1
b.
1:M
c.
M:N
d.
Optional participation
e.
Mandatory participation
5.
Discuss and review types:
a.
Supertype
b. Subtype
6.
Discuss and review attributes:
7.
a. Key and nonkey attributes
b.
Null and not null definitions
8.
Discuss and review keys:
a.
Candidate keys
b.
Primary keys
c.
Foreign keys
d.
Secondary keys
Classroom Ideas
1.
Many of the products shown in
this appendix have limited time trial versions. Visit the product Web sites to
find the trial versions, which can then be used in class for illustrations or
by students to explore the capabilities of these tools.
2.
Be sure to review the
definitions of entities; entity types; entity instances; relationship
structures; cardinality and participation constraints; primary, secondary, and
foreign keys; and ways of modeling business rules and constraints.
3.
Students always learn best by
seeing examples. Provide numerous examples using familiar situations: students,
courses, and instructors; customers, orders, and products; waiters, meals, and
bills. Show each situation with several different data modeling tools. If there
are capabilities one product has that another does not have, point out these
differences.
4.
Emphasize the importance of
this model as a tool, both to help the designer understand the system, and to
show the users that the system is all-encompassing.
5.
Divide the class into teams and
have each team take the Pine Valley model for one tool and develop
comments/observations about the data model produced using that tool.
6.
Divide the class into teams and
have each team develop a data model for a case study using different tools.
Have the teams then compare their results and briefly discuss the strengths and
weaknesses of each tool used.
7.
You may have other tools that
you want students to use other than those illustrated in this appendix or
students may know of other tools available for data modeling. Use this appendix
as a “jumping-off” point for an exploration session of available tools and a
discussion of how students might evaluate such options for an employer looking
for a recommendation on what package to use in the “real world.”
8.
This appendix is a good
opportunity to prepare students for the “real world.” By pointing out the
differences in the tools they may have at their disposal at work, the
instructor can demonstrate the importance of understanding general database
concepts and developing flexibility in how they will be able to accomplish the
tasks they are assigned at work.
9.
Use the web to find other tools
that might approximate the drawing templates needed to represent conceptual
data models. In particular, with the
rise of tablet computing, explore some of the “draw by hand” applications that
would support ad-hoc modeling during a quick meeting with a client or
end-user. Coordinate an in-class (or
online) discussion about the pros/cons of various tools that a data analyst may
have at hand to capture data needs.
Modern Database Management, 11e
(Hoffer et al.)
Chapter 3
The Enhanced E-R Model and Business Rules
1)
Which of the following is a generic entity type that has a relationship with
one or more subtypes?
A)
Megatype
B)
Supertype
C)
Subgroup
D)
Class
Answer: B
Diff:
1 Page Ref: 113
Topic: Representing Supertypes and Subtypes
AACSB: Use of Information Technology
2)
Given the following entities, which of the choices below would be the most
complicated?
Automobile: VIN,
EngineSize,NumberOfDoors, NumberOfPassengers,, FuelType, Transmission
SUV: VIN, EngineSize,
NumberOfPassengers, NoWheelDrive, FuelType, Transmission
Truck: VIN, EngineSize, NoWheelDrive,
FuelType, Transmission, Payload
A)
Define one vehicle entity type to hold all entities.
B)
Define a separate entity type for each entity.
C)
Define a supertype called vehicle and make each of the entities subtypes.
D)
Keep only the Truck entity type.
Answer: A
Diff:
1 Page Ref: 115
Topic: Representing Specialization and
Generalization
AACSB: Use of Information Technology
3)
The property by which subtype entities possess the values of all attributes of
a supertype is called:
A)
hierarchy reception.
B)
class management.
C)
attribute inheritance.
D)
generalization.
Answer: C
Diff:
2 Page Ref: 115
Topic: Representing Supertypes and Subtypes
AACSB: Use of Information Technology
Subtopic: Attribute Inheritance
4)
Subtypes should be used when:
A)
there are attributes that apply to some but not all instances of an entity
type.
B)
supertypes relate to objects outside the business.
C)
the instances of a subtype do not participate in a relationship that is unique
to that subtype.
D)
none of the above.
Answer: A
Diff:
1 Page Ref: 116
Topic: Representing Supertypes and Subtypes
AACSB: Analytic Skills, Use of Information
Technology
Subtopic: When to Use Supertype/Subtype Relationships
No comments:
Post a Comment