The Enhanced ER Model and Business Rules
Enhanced E-R Model
Data Modeling-Part 2
© 2013 Pearson Education
1
Modern Database Management
11th Edition, International Edition
Jeffrey A. Hoffer, V. Ramesh,
Heikki Topi
Chapter 3
© 2013 Pearson Education
Objectives
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
2
Chapter 3
© 2013 Pearson Education
Entities
Entity type – a collection of entities that share common properties or characteristics
Entity instance – A single occurrence of an entity type
Although the entity instances that compose an entity type are similar, we do not expect them to have exactly the same attributes.
Entity types that share common properties but also have one or more distinct properties that are of interest to the organization.
3
Chapter 3
© 2013 Pearson Education
Supertypes and Subtypes
Enhanced ER model: extends original ER model with new modeling constructs
Subtype: A subgrouping of the entities in an entity type that has attributes distinct from those in other subgroupings
Supertype: A generic entity type that has a relationship with one or more subtypes
Attribute Inheritance:
Subtype entities inherit values of all attributes of the supertype
An instance of a subtype is also an instance of the supertype
4
Chapter 3
© 2013 Pearson Education
EMPLOYEE
Employee_Number
Employee_Name
Address
Date_Hire
Hourly_Rate
Annual_Salary
Job_Type
Contract_Number
Billing_Rate
Hourly EMPLOYEE
SALERIED EMPLOYEE
CONSULTANTE
An example
Chapter 3
© 2013 Pearson Education
6
Chapter 3
© 2013 Pearson Education
7
Figure 3-1 Basic notation for supertype/subtype notation
7
Chapter 3
© 2013 Pearson Education
Different modeling tools may have different notation for the same modeling constructs.
Chapter 3
© 2013 Pearson Education
8
Different modeling tools may have different notation for the same modeling constructs.
Figure 3-1 Subtypes inside supertypes
8
Chapter 3
© 2013 Pearson Education
Chapter 3
© 2013 Pearson Education
9
Figure 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
9
Chapter 3
© 2013 Pearson Education
Chapter 3
© 2013 Pearson Education
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
10
Chapter 3
© 2013 Pearson Education
11
Figure 3-3 Supertype/subtype relationships in a hospital
11
Chapter 3
© 2013 Pearson Education
Chapter 3
© 2013 Pearson Education
Generalization and Specialization
Generalization: The process of defining a more general entity type from a set of more specialized entity types. BOTTOM-UP
Specialization: The process of defining one or more subtypes of the supertype and forming supertype/subtype relationships. TOP-DOWN
12
Chapter 3
© 2013 Pearson Education
13
Figure 3-4 Example of generalization
a) Three entity types: CAR, TRUCK, and MOTORCYCLE
All these types of vehicles have common attributes
13
Chapter 3
© 2013 Pearson Education
Chapter 3
© 2013 Pearson Education
14
Figure 3-4 Example of generalization (cont.)
So we put the shared attributes in a supertype
Note: no subtype for motorcycle, since it has no unique attributes
b) Generalization to VEHICLE supertype
14
Chapter 3
© 2013 Pearson Education
Chapter 3
© 2013 Pearson Education
15
Figure 3-5 Example of specialization
a) Entity type PART
Only applies to manufactured parts
Applies only to purchased parts
15
Chapter 3
© 2013 Pearson Education
Chapter 3
© 2013 Pearson Education
16
b) Specialization to MANUFACTURED PART and PURCHASED PART
Note: multivalued composite attribute was replaced by an associative entity relationship to another entity
Created 2 subtypes
Figure 3-5 Example of specialization (cont.)
16
Chapter 3
© 2013 Pearson Education
Chapter 3
© 2013 Pearson Education
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 (double line)
Partial Specialization Rule: No (single line)
17
Chapter 3
© 2013 Pearson Education
Figure 3-6 Examples of completeness constraints
a) Total specialization rule
18
Chapter 3
© 2013 Pearson Education
Chapter 3
© 2013 Pearson Education
19
b) Partial specialization rule
Figure 3-6 Examples of completeness constraints (cont.)
19
Chapter 3
© 2013 Pearson Education
Chapter 3
© 2013 Pearson Education
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
20
Constraints in Supertype/SUBTYPE RELATIONSHIPS
20
Chapter 3
© 2013 Pearson Education
Chapter 3
© 2013 Pearson Education
21
a) Disjoint rule
Figure 3-7 Examples of disjointness constraints
21
Chapter 3
© 2013 Pearson Education
Chapter 3
© 2013 Pearson Education
22
b) Overlap rule
Figure 3-7 Examples of disjointness constraints (cont.)
22
Chapter 3
© 2013 Pearson Education
Chapter 3
© 2013 Pearson Education
EMPLOYEE
Employee_Number
Employee_Name
Address
Date_Hire
Hourly EMPLOYEE
Hourly_Rate
SALERIED EMPLOYEE
Annual_Salary
Job_Type
CONSULTANTE
Contract_Number
Billing_Rate
Another example
Chapter 3
© 2013 Pearson Education
Chapter 3
© 2013 Pearson Education
Chapter 3
© 2013 Pearson Education
Constraints summary
Completeness constraints – decide whether a supertype instance must also be a member of at least one subtype. The total specialization rule demands that every entity in the superclass belong to some subclass. Just as with a regular ERD, total specialization is symbolized with a double line connection between entities (our textbook notation style). The partial specialization rule allows an entity to not belong to any of the subclasses. It is represented with a single line connection (our textbook notation style).
26
Chapter 3
© 2013 Pearson Education
Constraints summary
Disjointness constraints – You will need to decide whether a supertype instance may simultaneously be a member of two or more subtypes. The disjoint rule forces subclasses to have disjoint sets of entities. The overlap rule forces a subclass (also known as a supertype instance) to have overlapping sets of entities.
27
Chapter 3
© 2013 Pearson Education
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
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
28
Constraints in Supertype/SUBTYPE RELATIONSHIPS
28
Chapter 3
© 2013 Pearson Education
Chapter 3
© 2013 Pearson Education
29
Figure 3-8 Introducing a subtype discriminator (disjoint rule)
29
Chapter 3
© 2013 Pearson Education
Chapter 3
© 2013 Pearson Education
30
Figure 3-9 Subtype discriminator (overlap rule)
30
Chapter 3
© 2013 Pearson Education
Chapter 3
© 2013 Pearson Education
31
Figure 3-10 Example of supertype/subtype hierarchy
31
Chapter 3
© 2013 Pearson Education
Chapter 3
© 2013 Pearson Education
32
Copyright © 2013 Pearson Education
Chapter 3
© 2013 Pearson Education
/docProps/thumbnail.jpeg