Transcript Enhanced E-R Model and Business Rules

Chapter 3
The Enhanced E-R Model
Jason C. H. Chen, Ph.D.
Professor of MIS
School of Business Administration
Gonzaga University
Spokane, WA 99258
[email protected]
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-1
Objectives
• Define terms
• Understand use of supertype/subtype relationships
• Understand use of specialization and generalization
techniques
• Specify completeness and disjointness constraints
• Develop supertype/subtype hierarchies for realistic
business situations
• Develop entity clusters
• Explain universal (packaged) data model
• Describe special features of data modeling project
using packaged data model
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-2
What is the Enhanced E-R Model
(EE-R Model) and Why?
• The Enhanced E-R model
(EE-R) is an extended E-R
model with new modeling
constructs.
• Why the EE-R model?
– The business environment has
changed dramatically.
– Business relationships and data are
more complex.
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-3
Business Rules
• The EE-RD are used to capture important
business rules such as constraints in
supertype/sbubtype relationship.
• The rules automatically become
constraints that are forced by the DBMS
and are used to maintain the
organizational database in a consistent
and valid state.
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-4
PARADIGMS
(Definition)
• A paradigm is a way
of viewing things and
thinking about things.
L
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-5
Supertypes and Subtypes
• Subtype: A subgrouping of the entities in an entity type
which has attributes that are distinct from those in other
subgroupings
• Supertype: An generic entity type that has a
relationship with one or more subtypes (Fig. 3-1, 2)
• Inheritance:
– Subtype entities inherit values of all attributes of the
supertype
– An instance of a subtype is also an instance of the
supertype
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-6
Figure 3-1 Basic notation for supertype/subtype notation
a) EER
notation
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-7
Figure 3-1 Basic notation for supertype/subtype notation (cont.)
b) Microsoft
b)
Visio
Microsoft
Notation
Visio
Notation
Different modeling tools may have different notation for the same
modeling constructs
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-8
Fig. 3-2: Employee supertype with three subtypes
All employee subtypes
will have employee
number, name, address,
and date-hired
Each employee subtype will
also have its own attributes
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-9
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-10
Attributes
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
Operations/
behavior/
methods
TM 3-11
UML notation:
An example of
Object-Oriented
(O-O) Approach
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-12
Relationships and Subtypes
• Relationships at the supertype level indicate that
all subtypes will participate in the relationship
• The instances of a subtype may participate in a
relationship unique to that subtype. In this
situation, the relationship is shown at the subtype
level (Fig. 3-3)
• Subtype entities inherit values of all attributes of
the supertype.
• An occurrence of a subtype is also an occurrence
of the supertype
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-13
Figure 3-3 - Supertype/subtype relationships in a hospital
Both
outpatients
and resident
patients are
cared for by
a responsible
physician
Only resident patients are assigned to a bed
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-14
Generalization and Specialization
• Generalization: The process of defining a
more general entity type from a set of more
specialized entity types. BOTTOM-UP
(Fig. 3-4)
• Specialization: The process of defining one
or more subtypes of the supertype, and
forming supertype/subtype relationships.
TOP-DOWN (Fig. 3-5)
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-15
Figure 3-4 Example of generalization
a) Three entity types: CAR, TRUCK, and MOTORCYCLE
All these types of vehicles have common attributes
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-16
Figure 3-4 Example of generalization (cont.)
b) Generalization to VEHICLE supertype
So we put
the shared
attributes in
a supertype
MOTORCYCLE
WAGON
Note: no subtype for motorcycle, since it has no unique attributes
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-17
Figure 3-5 Example of specialization
a) Entity type PART
Only applies to
manufactured parts
Applies only to purchased
parts
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-18
Figure 3-5 Example of specialization (cont.)
b) Specialization to MANUFACTURED PART and PURCHASED PART
Created 2
subtypes
Note: multivalued attribute was replaced by an
associative entity relationship to another entity
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-19
Constraints in Supertype/
Subtype Relationships
• Completeness Constraints: Whether an
instance of a supertype must also be a
member of at least one subtype.
– Total Specialization Rule: Yes (Fig. 3-6a double line convention)
– Partial Specialization Rule: No (Fig. 3-6b single line convention)
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-20
Figure 3-6 Examples of completeness constraints
a) Total specialization rule
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-21
Figure 3-6 Examples of completeness constraints (cont.)
b) Partial specialization rule
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-22
Constraints in Supertype/
Disjointness constraint
• Disjointness Constraints: Whether an
instance of a supertype may simultaneously
be a member of two (or more) subtypes.
– Disjoint Rule: An instance of the supertype can
be only ONE of the subtypes
– Overlap Rule: An instance of the supertype
could be more than one of the subtypes
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-23
Figure 3-7 Examples of disjointness constraints
a) Disjoint rule
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-24
Figure 3-7 Examples of disjointness constraints (cont.)
b) Overlap rule
A part may be both a
Purchased Part and a
Manufactured Part at the
same time, but must be
one or the other due to
Total Specialization
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-25
Figure 3-7 Examples of disjointness constraints (cont.)
b) Overlap rule
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-26
Constraints in Supertype/
Subtype Discriminators
• Subtype Discriminator: An attribute of the
supertype whose values determine the target
subtype(s)
– Disjoint – a simple attribute with alternative values to
indicate the possible subtypes (Fig. 3-8)
– Overlapping – a composite attribute whose subparts
pertain to different subtypes. Each subpart contains a
boolean value to indicate whether or not the instance
belongs to the associated subtype (Fig. 3-9)
– Summary Example (Fig. 3-10)
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-27
Figure 3-8 Introducing a subtype discriminator (disjoint rule)
Implementation:
C++:
switch(Employee_Type)
{
case ’C’:
..
}
SQL:
IF/THEN/ELSE
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-28
Figure 3-9 Subtype discriminator (overlap rule)
A composite
attribute with subattributes
indicating “yes” or
“no” or both (“yes”
and “no”) to
determine whether
it is of each
subtype
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-29
Figure 3-10 Example of supertype/subtype hierarchy
Common
attributes for
all levels
?
Inherit all
attributes from
all supertypes
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-30
Break ! (Ch. 3 - Part I)
In class exercise:
#12, 13 (p. 143-144)
HW
- #9 (p.143)
-turn in a hardcopy
using “Visio”/Word
-Revise your MVC
ER/M
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-31
Entity Clusters
• EER diagrams are difficult to read when
there are too many entities and relationships
• Solution: group entities and relationships
into entity clusters
• Entity cluster: set of one or more entity
types and associated relationships grouped
into a single abstract entity type
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-32
Figure 313a
Possible
entity
clusters for
Pine Valley
Furniture in
Microsoft
Visio
Related
groups of
entities
could
become
clusters
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-33
Figure 3-13b EER diagram of PVF entity clusters
More readable,
isn’t it?
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-34
Figure 3-14 Manufacturing entity cluster
Detail for a single cluster
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-35
Packaged Data Models
• Predefined data models
• Could be universal or industry-specific
• Universal data model = a generic or
template data model that can be reused as a
starting point for a data modeling project
(also called a “pattern”)
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-36
Advantages of Packaged Data Models
•
•
•
•
•
•
•
•
•
Use proven model components
Save time and cost
Less likelihood of data model errors
Easier to evolve and modify over time
Aid in requirements determination
Easier to read
Supertype/subtype hierarchies promote reuse
Many-to-many relationships enhance model flexibility
Vendor-supplied data model fosters integration with vendor’s
applications
• Universal models support inter-organizational systems
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
37
TM 3-37
Figure 3-15 PARTY, PARTY ROLE, and ROLE TYPE in a universal data model
(a) Basic PARTY universal data model
Packaged data models
are generic models that
can be customized for a
particular
organization’s business
rules
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-38
Figure 3-15 PARTY, PARTY ROLE, and ROLE TYPE in a
universal data model
(b) PARTY supertype/subtype hierarchy
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-39
Figure 3-16 Extension of a universal data model to include PARTY RELATIONSHIPS
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-40
MVC_Hospital HW
Phase I - Logical Design Phase
Draw a entity-relationship diagram (enterprise model) for Mountain
View community Hospital, based on the narrative description of the case
and this handout (but the entities are from the five (5) figures/reports
shown above). You should create a file and turn in with a hardcopy
(called MVC_PhaseI_ERD_Lname_Fname.docx) contains the following
materials:
1. Read and employ materials from chapters 3 and 4
2. Include entities, associations (with detail multiplicity), and attributes.
3. Determine and draw the order of entering data
Next phase -- implementation, create SQL script file for table structure
and data base (values).
Upload ONLY the .docx file to the Bb.
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-41
MVC_Hospital
Create two script files:
Phase II.
(a) a script file (MVC_Hospital_Lastname_Firstname.SQL) that contains a set of commands
of DROP, CREATE, and INSERT that performs the same functions as in the script file
of Northwoods.sql
(b) Save your spooled file (both script file and result from SQL) as
MVC_PhaseII_Spool_Lastname_Firstname.LST Upload the spooled file to the Bb.
Phase III.
(a) Second script file (MVC_PhaseIII_QUERIES_Lastname_Firstname.SQL) containing a
set of SQL commands that answer the questions. Test the query one/time successfully.
Note that you may need other SQL commands and create database views for the purpose of
answering questions easily. You may need to read other references related the SQL
from the text book (e.g., Chapters 6 & 7 of the main text).
(b) Save the spooled file as MVC_PhaseIII_Spool_Lastname_Firstname.LST. Finally, you
create a new file (*.docx) containing all work done from Parts I, II and save them in
the file MVC_Hospital_Complete_Lastname_Firstname.docx.
(c) The file should contain your class and personal information, information for each
question (re-type with question number) as well as each individual query and result.
Upload ONLY the .docx file to the Bb.
Copyright © Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems
TM 3-42