Module 2: Conceptual Data Modeling with ERD

1
Module 2 Conceptual Data Modeling with ERD

• Entities
• ERD Modeling
• Identify and Model Entities
• Define Relationships
• Entity Relationship Layout
• ERD Modeling Relationship and Frequencies
• Special Relationship Types

2
Entities

• An entity, like a noun, is a person, place,
  thing, or event about which the system is to
  maintain, correlate, and display information.
• An entity must be within the scope of the system
  not all nouns are entities.
• Entities versus Associated Information
• Entity Table

3
Instance of an Entity

• Instance of an Entity An entity instance
  refers to one distinct entity occurrence. (
  Record)
• Examples
• Joe Smith, along with his address and so on, is
  an occurrence of STUDENT.
• Algebra 101 is an instance of COURSE.

4
Instance of an Entity (continued)
Practice Identify the entities in the following
description by circling the nouns. Remember,
entities must be within the scope of the
system. Buffalo Records and CDs has been in
business for only three years and we have already
expanded our business to five stores. Weve
decided it might be about time to get a computer
to keep track of some of these things that we
keep losing information on. For instance, just
last week when we did our item inventory, I was
sure we had a copy of that new Guns and Roses CD,
but a customer came in just yesterday looking for
it and it was nowhere to be found? Thought we
better get a bit more organized. Anyway, we have
20 employees now. We need to keep track of their
names, addresses, phone numbers, social security
numbers, and birth dates. We like to send our
employees birthday cards, or cards for other
special occasions. Like Joe over there - his wife
just had a baby yesterday. Question What are
some examples of the entities defined above?
5
Classifying Entities
Entities are classified by three types
Question What are other examples of the above
types of entities?
6
ERD Modeling

• Entity
• Relationship
• Attribute
• Domain
• Modeling Conventions - Entities

7
Modeling Conventions - Entities
Entities have singular unique names. They are
pictorially represented by a rounded box, as in
the following
Employee
Employee
Customer
Customer
Order
Order
8
Identifying and Modeling an Entity

• Determine the significant nouns.
• Determine a logical name for the entity.
• Write a description for the entity.

9
Defining a Relationship

• Types of Relationships
• Naming Relationships
• Relationships Naming Syntax

10
Types of Relationships

• A relationship is a two-directional, significant
  association between two entities, or an entity
  and itself
• Types of Relationships
• Pictorially represented by a line between two
  entities.
• Three types of relationships exist
• One to One (11)
• One to Many (1M)
• Many to Many (MM)
• One to One (11)

Entity B
Entity A
An instance of Entity A relates to one and only
one instance of Entity B. Entity B relates to
one and only one instance of entity A.
11
Types of Relationships (continued)
One to Many (1M)
Entity B
Entity A
An instance of Entity B relates to one or more
instances of Entity A. An instance of Entity A
relates to one and only one instance of Entity B.
12
Types of Relationships (continued)
Many to Many (MM)
Entity B
Entity A
An instance of Entity A relates to one or more
instance of Entity B. An instance of Entity B
relates to one or more instance of Entity A.
13
Naming Relationships
Each end of a relationship must have a name to
describe the relationship, an optionality, and a
degree. Appropriate naming helps to understand
the relationship, exposing potential problems
early on.
for
SALE
SALE ITEM
Made up of
Each SALE must be made up of one or more SALE
ITEMs. Each SALE ITEM must be for one and only
SALE. Relationship Naming Syntax Each ENTITY A
must be/may be relationship end name one and
only one/one or more ENTITY B
14
Conditional Relationships
A relationship may not always exist.
for
Ticket
Passenger
Holder of
Each Passenger may be the holder of one or more
Tickets. Each Ticket must be for one and only one
Passenger.
15
Conditional Relationships (continued)
Read the Relationship in Each Direction First
read left to right
for
Ticket
Passenger
Each Ticket must be for one and only one
Passenger. Then read right to left
Ticket
Passenger
Holder of
Each Passenger may be the holder of one or more
Tickets.
16
Practice Defining a Relationship
In this practice exercise, you will describe a
relationship. Read and define the following
relationships
Enrolled in
Student
Course
Attended by
for
Treatment
Patient
receive
17
Entity Relationship Layout
Always draw crows-feet pointing up or to the
left.
18
ERD Modeling Relationships and Frequencies
(11) Rare (11) Rare (1M) Very
Rare (1M) Common (1M) Rare (1M) Very
Common (1M) Rare (MM) Common (MM) Common (MM)
Rare
19
Reading a Diagram
In this practice exercise, you will read a
diagram and describe the relationships.Read the
following diagram and describe the relationships
Sale Item
identifier of
Part of
Entity Relationship Diagram
contained in
made up of
Product
Part of
Comprised of
Managed by
Sold by
Employee
Sale
Located in
Initiated by
Manager of
The location for
The initiator of
Located in
State
Customer
Located of
20
Special Relationship Types

• Dependent Relationships
• Associative Relationships
• Intersection Entity

21
Dependent Relationships (1M)
A One to Many relationship my be represented as
1M or 1 to M and specifies one or more in one
direction and only one in the other direction.
1M relationships can be called dependent,
hierarchical, or parent/child.
Treatment
Patient
for
receive
1M relationships are very common. 1M
relationships that are mandatory in both
directions are uncommon. Model multi-level
hierarchical data as several 1M relationships
City
within
Made up of
Country
within
Made up of
Continent
22
Associative Relationships (MM)
A Many to Many, or MM relationship indicates a
degree of one or more in both directions.
Student
Course
Enrolled in
Attended by
Each student may be enrolled in one or more
courses. Each Course may be attended by one or
more Students.
Automobile
Mechanic
Serviced by
services
MM relationships are very common. MM
relationships are usually optional in both
directions. MM relationships will be resolved
by using intersection entities because relational
models do not directly accommodate them.
23
Intersection Entity (Associative Entity)
Intersection (or Associative) Entities are
created to decompose a MM into two 1M
relationships, with the Intersection Entity being
the child of both original parent entities.
Service
Mechanic
Serviced by
services
Performed on
receives
Automobile
24
Review

• Entities
• ERD Modeling
• Identify and Model Entities
• Define Relationships
• Entity Relationship Layout
• ERD Modeling Relationships and Frequencies
• Special Relationship Types