Docstoc

Chapter 4

Document Sample
Chapter 4 Powered By Docstoc
					Data Modeling and Database Design
Chapter 4: Enhanced Entity-Relationship (EER) Modeling

Remember!
• Presentation Layer ER Diagram  Contains hashes and oval • Design-specific ER Diagram (Coarse-granularity)  Uses (min, max) notation  Maps deletion rules • Design-specific ER Diagram (Fine-granularity)  Maps attribute characteristics into ER diagram  Decomposes multi-valued attributes  Decomposes m:n relationships

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

2

Enhanced Entity-Relationship (EER) Model
• Enhanced Entity-Relationship (EER) modeling is an extension to the ER modeling that incorporates additional constructs • Central construct Superclass/subclass (SC/sc) relationship • More specifically: – Specialization/generalization – Categorization – Aggregation

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

3

Superclass/Subclass Relationship (SC/sc)
Inter-Entity Class Relationship (Has-a Relationship) A STORE has a relationship with FURNITURE

STORE

Sells

FURNITURE

There are 3 SC/sc relationships shown here.
A CHAIR is a FURNITURE. A TABLE is a FURNITURE. A SOFA is a FURNITURE.

Intra-Entity Class Relationship (Is-a Relationship)
A CHAIR is a FURNITURE.

CHAIR

TABLE

SOFA

CHAIR, TABLE, and SOFA are entity types that belong to the entity class FURNITURE.

Figure 4.4 Inter-entity and intra-entity class relationships

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

4

An Instance Diagram
CHAIR
e1

FURNITURE
e4

e5 e1

. .
TABLE
e2

e2

e3

e4

e5

e7

e6

. .
SOFA

e7

e8

. . . . .

e3

e8

Figure 4.5 Superclass/subclass entity instances

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

5

Think About It…
• Theoretically, one has three choices of modeling furniture/chair/table/sofa – Model three separate entity types for chair, table, and sofa, and create three separate relationship types with the entity type store – Model furniture as an entity type with an attribute called furniture_type; then chair, table, and sofa would be values of that attribute – Model furniture as a superclass/subclass

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

6

SC/sc Relationships
• There are two basic kinds of Sc/sc relationships – Specialization/Generalization: One superclass (SC) is related to one or more subclasses (sc) – Categorization: One subclass (sc) is related to one or more superclasses (SC)

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

7

Vignette 1

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

8

Modeling Vignette 1: Alternative 1

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

9

Modeling Vignette 1: Alternative 2

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

10

Modeling Vignette 1: Alternative 3

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

11

Properties of SC/sc Relationships
• An entity that exists in a subclass can be associated with only one superclass entity • An entity cannot exist in the database merely by being a member of a subclass; it must also be a member of an associated superclass • An entity that is a member of a superclass can be optionally included as a member of any number of its subclasses • It is not required that every member of a superclass be a member of a subclass

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

12

Properties of SC/sc Relationships (continued)
• Type Inheritance Property: A subclass inherits all the attributes of the superclass to which it is related – In addition, it will also inherit all the relationship types in which the superclass participates • A subclass may also have its own specific attributes in addition to the attributes inherited – Likewise, a subclass may have its own specific relationship(s) with other entity types (i.e., interentity class relationships)
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling 13

Note That…
• The cardinality ratio of any SC/sc relationship is always 1:1 • The participation of the subclass in a SC/sc relationship is always total • A subclass inherits all attributes as well as all relationship types that a superclass possesses (type inheritance property)

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

14

Specialization and Generalization
• Specialization is the process of generating subgroups („sc‟s) of a generic entity class (SC) by specifying the distinguishing properties (attributes) of the subgroups (= top-down approach) • Generalization, on the other hand, crystallizes the common properties (attributes) shared by a set of entity types („sc‟s) into a generic entity type (SC) (= bottom-up approach) • Notation: circle + fork (indicating subset) • Read: “is-a”
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling 15

Specialization and Generalization (continued)
• Completeness Constraint The participation of the superclass in a specialization/ generalization is referred to as the completeness constraint and can assume one of two values: total or partial
– Total specialization means that every entity of the superclass must participate in this specialization/ generalization relationship (indicated by a solid line from the superclass to the specialization/generalization symbol (i.e., the circle)) – Partial specialization means that there may be entities present in the superclass that do not participate in this specialization/generalization (indicated by a dotted line)
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling 16

Specialization and Generalization (continued)
• Disjointness Constraint Used to specify that the subclasses of a specialization must be:
– Disjointed (indicated by „D‟), i.e., an entity of the superclass cannot be a member of more than one subclass – Overlapped across subclasses (indicated by „O‟)

• Please note that you have to specify both: completeness and disjointness constraints!

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

17

Notation

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

18

An EER Model of Vignette 1

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

19

Sample Data Sets For EER Model of Vignette 1

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

20

An Extension of Vignette 1: Multiple Specializations

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

21

Sample Data Sets For Vignette 1 Extension

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

22

An EER Model of Vignette 2

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

23

Specialization/Generalization Hierarchy
• Example of a Hierarchy: [STUDENT -> STUDENT_ATHLETE -> FOOTBALL_PLAYER -> {DEFENSIVE_PLAYER, OFFENSIVE_PLAYER}] shows a 3-level hierarchy • Inheritance A subclass inherits the attributes and relationship types of not just the immediate parent, but also of the predecessor superclasses in the hierarchy all the way up to the root of the specialization hierarchy

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

24

An EER Model of Vignette 3

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

25

Specialization/Generalization Lattice
• In a specialization lattice, an entity type can participate as a subclass in more than one specialization (i.e., a child can have more than one parent) • Inheritance The subclass will inherit all the attributes and relationship types from the superclasses of all the specializations participating in the lattice and the predecessor hierarchy of all these superclasses – This is called multiple type inheritance, and the subclass in the lattice is referred to as a shared subclass
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling 26

The Categorization Construct
• A categorization occurs when a subclass is associated with more than one superclass of different entity types – The subclass is called category • An entity that is a member of the category (subclass) must exist in ONLY ONE of the superclasses in the categorization relationship • Example: A financial donor can be an individual, a company, or a foundation  no “is-a” relationship! • Notation: „U‟ (= union, the subclass is a subset of the union of the superclasses)
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling 27

Categorization Example

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

28

Characteristics of a Categorization
• There is only one subclass in each categorization • The cardinality ratio is 1:1 within and across the SC/sc relationship • The participation of the subclass in the categorization is always total

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

29

Characteristics of a Categorization (continued)
• Each superclass may exhibit either total or partial participation • A category can either be:
– A total category (i.e., the category is the union of all the superclass entities) – A partial category (i.e., the category is a true subset of the union of all the superclass entities)

• A category possesses the property of selective type inheritance (i.e., it inherits attributes of one entity type only) • Often a unique identifier for a category must be manufactured (which is called a surrogate key)
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling 30

Sample Data Sets For Categorization Example

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

31

Choosing the Appropriate EER Construct
Exercise: • Part1: Consider an automobile dealership in the state of Texas; the dealership typically stocks cars, trucks, vans, and sport utility vehicles (SUVs) • Part 2: Not all vehicles in the dealership are registered vehicles though – How would we model REGISTERED_VEHICLE? • One exception exists in which a categorization and a generalization/specialization are mutually substitutable constructs: in the case of a total category!
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling 32

Choosing the Appropriate EER Construct (continued)

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

33

Choosing the Appropriate EER Construct (continued)

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

34

Choosing the Appropriate EER Construct (continued)

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

35

Choosing the Appropriate EER Construct (continued)

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

36

The Aggregation Construct
• An aggregation allows us to model a “whole/part” relationship as an “is-a-part-of” relationship between a subclass and a superclass • An entity in the aggregate contains superclass entities from ALL SC/sc relationships in which it participates • Inheritance: Selective type inheritance connotes the inheritance of attributes and relationships from ALL superclass entities contained in the specific aggregation • Notation: „A‟ • Read: “is-part-of” • Note that an aggregate can never be partial
Chapter 4 – Enhanced Entity-Relationship (EER) Modeling 37

An Example of Aggregation

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

38

An Aggregation Hierarchy

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

39

Aggregation Versus Categorization

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

40

A Fine-granular Design-Specific EER Diagram for Vignette 3
[N,6] Student# [A,30] Name [A,25] Major

STUDENT

[N,1.2] Gpa

U
[A,1] F_b_b [N,2] Height

STUDENT_ ATHLETE F_b_b_value

[N,3] Weight

d1
Batting_avg [N,1.3] Home_runs [N,2] Errors [N,2]

d3

U d2

[N,2.2] Speed

[N,2] Touchdowns

"Football"

"Basketball"

"Baseball"

U

U

U

U
[N,2] Uniform#

FOOTBALL_ PLAYER

BASKETBALL_ PLAYER

U

League

Bpid

BASEBALL_ PLAYER

TEAM_ CAPTAIN

VARSITY_ PLAYER

INTRAMURAL_ PLAYER N

Auto

Pts_per_game [N,2.1] Uniform# [N,2]

o

Position [A,15]

Assists_per_game [N,2.1]

(1,n) (1,n) Rebounds_per_game [N,2.1] Plays_ position

[A,15] Team

(0,1) Scholarship [A,1] Redshirt [A,1] Sponsored_by

Plays_ Plays_ position position (1,1)

U

U

U

(1,1)

(1,n) [N,3] Walks

C DEFENSIVE_ PLAYER OFFENSIVE_ PLAYER POSITION_BK

C POSITION_BA C
(0,1) Plays_pitcher (1,1)

PITCHER

[N,3] Strikeouts [N,3.1] Innings_pitched

ORGANIZATION

No_of_interceptions No_of_tackles Receptions [N,2] [N,3] [N,3]

Yards_gained [N,4]

[A,15] Position -----------

[A,15] Position -----------

[N,2.2] Era

Name [A,20] Pitching_speed [N,3] Type [A,2]

Figure 4.20 A Fine-granular Design-Specific ER Diagram

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

41

A Presentation Layer EER Diagram For Bearcat, Inc.
Lname Name_tag Minit Name Fname Address Pnumber Salary Emp# worker EMPLOYEE Gender Date_hired manager 1 Supervisee Building No_of_dependents Supervisor Supervised_by Holder of 1 Held_by_E m Related_how Belongs to Dname Dependent -------------Account# Gender Description Bank# Birthdate 1 Account_id Acc_type n Account of Hb_name n Usage Hrs_per_wk Account Holder Annual_cost Held_by_D Participates 1 Contracted_to Description Participant m DEPENDENT Depends_on n Dependent_of 7 Assigned Assignment
IN_HOUSE_PROJECT

Pl_name

No_of_employees

n

Works_in

1 employer PLANT managed by 1 1 Undertaken_by n Controlled Pnumber Pr_name PROJECT Budget Responsible

Managed_by

Mg_start_dt

1

20 Having 1

Assignee

m

Plocation

U

d

U
Hours
OUTSOURCED_PROJECT

BANK_ACCOUNT

Supports n

m

n HOBBY

Status VENDOR Gi_activity

lo_activity SPONSOR

U
V_name U

U
Pastor

V_address V_phone

Budget Name
NOT_FOR_PROFIT_ ORGANIZATION

CHURCH Denomination

SCHOOL

INDIVIDUAL

Ssn

U

Type

Name Phone# Address Principal

U
Name PUBLIC_ SCHOOL Size

Exempt_id

District

Tax_base

Figure 4.21 Presentation Layer ER diagram for Bearcat, Incorporated

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

42

A Fine-granular Design-Specific EER Diagram For Bearcat, Inc.
[A,20] Lname [A,1] Minit [A,20] Fname [A,1] Emp_a [A,1] Gender [Dt,8] Date_hired [N,2] No_of_dependents (1,m) Held_by_E [A,12] Related_how Dependent_of [A,15] Dname (0,1) [X,6] Account# Dependent -------------[A,1] Gender [Dt,8] Birthdate [A,1] Acc_type C BANK_ACCOUNT C (0,1) (1,1) [A,20] Hb_name (1,1) Receives (0,n) HOBBY [N,2,1] Hrs_per_wk R (0,m) Contracted_to (1,1) C DEPENDENT (1,1) C
ASSIGNMENT

[N,1] Name_tag Name [X,50] Address [N,6] Salary EMPLOYEE D R R (0,7) (0,20) (0,1) (0,n) [Dt,8] Mg_start_dt Uses [A,20] Building ---------(1,1) BUILDING Houses R (0,1) Managed_by (1,1) (3,n) (1,1) Works_in (100,n) R

[A,30] Pl_name [N,3] No_of_employees [N,2] Pnumber

[N,5] Emp_n

Emp#

PLANT R (0,m)

[N,7] Budget

Undertaken_by (0,1) N (1,1) PROJECT

[N,2] Pnumber [A,20] Pr_name [A,15] Plocation

Supervised_by

d

U

Belongs_to

(1,m)

IN_HOUSE_PROJECT

[N,3] Hours

U

[N,2] Bank#

(0,n) (0,n) Held_by_D

[X,50] Description [N,6] Annual_cost

OUTSOURCED_PROJECT

(1,n) Includes_D (1,1) C PARTICIPATION [X,50] Description

Account_id

SUPPORT C (1,1)

C

Includes_H

(0,1) N

[A,1] Status [A,1] lo_activity (1,m) [A,1] Gi_activity [A,30] V_name U [X,50] V_address

VENDOR

Provides

SPONSOR

U

U

[N,10.0] Budget

[A,30] Pastor

[A,30] Name
NOT_FOR_PROFIT_ ORGANIZATION

[X,10] V_phone# SCHOOL INDIVIDUAL Ssn [N,9] [A,30] Name Name [A,30] Size [N,4] [X,10] Phone# [X,50] Address

CHURCH

Type [A,1] Exempt_id [X,6] District [A,30]

Denomination [A,20]c

U

U

PUBLIC_ SCHOOL

Principal [A,30]

Tax_base [N,10.0]

Figure 4.22 Fine-granular Design-Specific ER diagram for Bearcat, Incorporated

Chapter 4 – Enhanced Entity-Relationship (EER) Modeling

43


				
DOCUMENT INFO