Transcript Kroenke-DBP-e10-PPT-Chapter05

Chapter Five
Data Modeling with the
Entity-Relationship Model
5-1
The Data Model
• A data model is a plan, or blueprint, for a
database design.
• It is more generalized & abstract than a
database design.
• It is easier to change a conceptual data model
than it is to change an implemented database
design.
• Modeling is not easy
– You must abstract the important details of an
organization.
5-2
Entity-Relationship Model
• The ER model consists of a set of graphical symbols
that are used to create conceptual models of databases.
• There are several versions
– Original E-R model — Chen (1976).
– Extended E-R model — Chen model extensions.
– Information Engineering (IE) — Martin (1990); it uses “crow’s
foot” notation.
– IDEF1X — A national standard developed by the National
Institute of Standards and Technology.
– Unified Modeling Language (UML) — The Object Management
Group; it supports object-oriented methodology.
5-3
Entities
• Things that can be identified and that
users want to track
– Entity class — a collection of entities of a
given type
– Entity instance — the occurrence of a
particular entity
• There are usually many instances of an
entity in an entity class.
• Entities translate into database tables.
5-4
CUSTOMER:
The Entity Class and Two Entity Instances
5-5
Attributes
• Attributes are an entity’s characteristics.
• All entity instances of a given entity class
have the same attributes, but vary in the
values of those attributes.
• Shown in Chen data models as ellipses.
• In crow’s foot model, they’re listed within
a rectangular form.
5-6
EMPLOYEE:
Attributes in Chen
5-7
EMPLOYEE:
Attributes in Crow’s Foot
5-8
Identifiers
• Identifiers are attributes that uniquely identify entity
instances.
• May be simple or composite.
• Identifiers in data models become keys in database
implementations:
– Entities have identifiers.
– Tables (or relations) have keys.
– We often use the terms interchangeably.
5-9
Relationships
• Entities are associated with one another in
relationships:
– Relationship classes: associations among entity
classes
– Relationship instances: associations among entity
instances
• A relationship class can involve two or
more entity classes.
– If two, it’s binary
– If three, it’s ternary
5-10
Binary Relationship
5-11
Ternary Relationship
PRESCRIPTION
DOCTOR
MEDICINE
PATIENT
5-12
Relationship Cardinality
• The maximum cardinality is the
maximum number of entity instances that
can participate in a relationship.
• The minimum cardinality is the minimum
number of entity instances that must
participate in a relationship.
5-13
Maximum Cardinality
• Maximum cardinality is the maximum
number of entity instances that can
participate in a relationship.
• Three categories:
– One-to-One [1:1]
– One-to-Many [1:N]
– Many-to-Many [N:M]
5-14
The Three Types of
Maximum Cardinality
5-15
Minimum Cardinality
• Minimum cardinality is the minimum number of
entity instances that must participate in a
relationship.
• Minimums are generally stated as either 0 or 1:
– If 0, participation in the relationship by the entity is
optional
• Ie, no entity instance must participate in the relationship.
– If 1, participation in the relationship by the entity is
mandatory
• Ie, at least one entity instance must participate in the
relationship.
5-16
The Three Types of
Minimum Cardinality
5-17
Data Modeling Notation
5-18
Data Modeling Notation:
ERwin
5-19
Data Modeling Notation:
N:M and O-M
Note that:
(1) ERwin cannot
indicate true
minimum
cardinalities on
N:M relationships
(2) Visio introduces
the intersection
table instead of
using a true N:M
model
5-20
ID-Dependent Entities
• An ID-dependent entity is an entity
whose identifier includes the identifier of
another entity.
• The ID-dependent entity is a logical
extension of the independent entity:
– BUILDING : APARTMENT
– PAINTING : PRINT
• The minimum cardinality from the IDdependent entity is always one.
5-21
ID-Dependent Entities
A solid line
indicates an
identifying
relationship
5-22
Weak Entities
• A weak entity is an entity whose existence
depends upon another entity.
• All ID-Dependent entities are weak.
• But not all weak entities are ID-Dependent.
– The identifier of the parent does not appear in
the identifier of the weak child entity.
5-23
Weak Entities (Continued)
A dashed line
indicates a
nonidentifying
relationship
Weak entities must be
indicated by an
accompanying text box in
Erwin – There is no
specific notation for a
nonidentifying but weak
entity relationship
5-24
ID-Dependent and Weak Entities
5-25
Subtype Entities
• A subtype entity is a special case of a
supertype entity:
– STUDENT :
UNDERGRADUATE or GRADUATE
• The supertype contains all common
attributes, while the subtypes contain
specific attributes.
• The supertype may have a discriminator
attribute that indicates the subtype.
5-26
Subtypes with a Discriminator
5-27
Subtypes: Exclusive or Inclusive
• If subtypes are exclusive, one supertype
relates to at most one subtype.
• If subtypes are inclusive, one supertype
can relate to one or more subtypes.
5-28
Subtypes: Exclusive or Inclusive
(Continued)
5-29
Subtypes: IS-A relationships
• Relationships connecting supertypes and
subtypes are called IS-A relationships,
because a subtype IS A supertype.
• The identifer of the supertype and all of its
subtypes must be identical, i.e., the
identifier of the supertype becomes the
identifier of the related subtype(s).
• Subtypes are used to avoid valueinappropriate nulls.
5-30
ERwin Symbol Summary
5-31
ERwin Symbol Summary (Continued)
5-32
David M. Kroenke’s
Database Processing
Fundamentals, Design, and Implementation
(10th Edition)
End of Presentation:
Chapter Five Part One
5-33