CS计算机代考程序代写 database ER The Enhanced ER Model and Business Rules

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