Chapter 3Data Modeling
3-2
Chapter Objectives
Explain the concept and practical use of data modeling.
Recognize which relationships in the business environment are unary, binary, and ternary relationships.
Describe one-to-one, one-to-many, and many-to-many unary, binary, and ternary relationships.
3-3
Chapter Objectives
Recognize and describe intersection data.
Model data in business environments by drawing entity-relationship diagrams that involve unary, binary, and ternary relationships.
3-4
Essence of Data Modeling
Exploring the different ways that entities can relate to each other as they always do in the real world
Devising a way of recording, of diagramming, the entities and the ways in which they interrelate in the business environment
3-5
Entity-Relationship (E-R) Model
A diagramming technique
Diagrams entities (with attributes) and the relationship between the entities.
There are many variations of E-R diagrams in use.
3-6
E-R Model Entity (and its attributes)
Rectangular shape
Salesperson = a type of entity
Name of entity is in caps above the separator line.
3-7
E-R Model Entity (and its attributes)
Entity type’s attributes are shown below the separator line.
An asterisk denotes attribute(s) that constitute the entity type’s unique identifier.
3-8
Relationships
Associations between entities
Different kinds:
Binary relationships
Unary relationships
Ternary relationships
3-9
Binary Relationships
Simplest kind of relationship
Relationship between two entity types
Diamond-shaped box represents relationship
A salesperson “sells” products or products are sold by salespersons
3-10
Cardinality
Represents the maximum number of entities that can be involved in a particular relationship.
One-to-One Binary Relationship
One-to-Many Binary Relationship
Many-to-Many Binary Relationship
3-11
One-to-One Binary Relationship
1-1
A single occurrence of one entity type can be associated with a single occurrence of the other entity type and vice versa.
1-1 ER Example
3-12
Two ways of reading this ER
Left to Right: A salesperson works in at most one office.
Right to Left: An Office is occupied by at most one salesperson
How to Read
3-13
Two ways of reading this ER
Left to Right:
1-1 ER Example
3-14
Two ways of reading this ER
Left to Right: A salesperson
1-1 ER Example
3-15
Two ways of reading this ER
Left to Right: A salesperson (action, the diamond shape) works in
1-1 ER Example
3-16
Two ways of reading this ER
Left to Right: A salesperson (action, the diamond shape) works in at most one
1-1 ER Example
3-17
Two ways of reading this ER
Left to Right: A salesperson (action, the diamond shape) works in at most one office.
1-1 ER Example
3-18
Two ways of reading this ER
Left to Right: A salesperson (action, the diamond shape) works in at most one office.
Right to Left: An office is
1-1 ER Example
3-19
Two ways of reading this ER
Left to Right: A salesperson (action, the diamond shape) works in at most one office.
Right to Left: An office is (action, rephrased if needed) occupied by
1-1 ER Example
3-20
Two ways of reading this ER
Left to Right: A salesperson (action, the diamond shape) works in at most one office.
Right to Left: An office is (action, rephrased if needed) occupied by at most one
1-1 ER Example
3-21
Two ways of reading this ER
Left to Right: A salesperson (action, the diamond shape) works in at most one office.
Right to Left: An office is (action, rephrased if needed) occupied by at most one salesperson.
1-1 ER Example
3-22
We read all ER in the same way. Cardinality tells us how many at most (max) involvement of source entity.
3-23
One-to-Many Binary Relationship
1-M
Use “crow’s foot” to represent the multiple association.
“many” = the maximum number of occurrences that can be involved, means a number that can be 1, 2, 3, … n.
3-24
Many-to-Many Binary Relationship
M-M
“many” can be either an exact number or have a known maximum.
3-25
Cardinality
3-26
Modality
The minimum number of entity occurrences that can be involved in a relationship.
“inner” symbol on E-R diagram (“outer” symbol is cardinality)
3-27
Cardinality & Modality
3-28
How to read
Two ways of reading the above ER
Left to Right: A salesperson _____ at least _____ customers, and at most ____ customers.
Right to Left: A customer ______ at least ___ and at most ____ salesperson.
3-29
How to read
Two ways of reading the above ER
Left to Right: A salesperson sells to at least 0 (zero) customers, and at most many customers.
Right to Left: A customer buys* from at least one and at most one salesperson.
*the action “sells to” is not applicable for customer
3-30
How to read
Two ways of reading the above ER
Left to Right: A salesperson sells to at least 0 (zero) customers, and at most many customers.
Right to Left: A customer buys* from at least one and at most one salesperson.
*the action “sells to” is not applicable for customer
3-31
How to read
Two ways of reading this ER
Left to Right: A salesperson sells to at least 0 (zero) customers, and at most many customers.
Right to Left: A customer buys* from at least one and at most one salesperson.
*the action “sells to” is not applicable for customer
3-32
How to read
Remember that
Cardinality takes one of two possible values:
One (denoted with single pipe)
Many (denoted crown foot (delta-like) symbol)
Modality takes one of two possible values:
Zero (denoted O on relationship line)
One (denoted with single pipe)
3-33
How to read
Remember that
Cardinality takes one of two possible values next to entity.
One (denoted with single pipe)
Many (denoted crown foot (delta-like) symbol)
Modality takes one of two possible values, denoted closer to diamond shape
Zero (denoted O on relationship line)
One (denoted with single pipe)
3-34
How to read
Note that all these rules defined by the business needs. In our scenario, we assume that a salesperson sells, for example, at least zero customer. This business rule may be “one” in other businesses.
3-35
Intersection Data
Describes the relationship between two entities.
Used with many-to-many relationships.
Represented on E-R diagram in a special five-sided intersection data box, which is attached to the relationship diamond between the two entity boxes.
3-36
Many-to-Many Binary Relationship with Intersection Data
For example, we know not only that salesperson 137 sold some of product 24013 but also how many units of that product that salesperson sold.
3-37
Associative Entity
Entities can have attributes; many-to-many relationships can have attributes.
Many-to-many relationship may be treated similarly to entities in an E-R diagram.
3-38
Associative Entity
Should you diagram as associative entity or many-to-many relationship?
personal taste
follow company standards
The unique identifier of the associative entity is usually the combination of the unique identifiers of the two entities in the many-to-many relationship.
3-39
Unary Relationships
Associate occurrences of an entity type with other occurrences of the same entity type.
Cardinality:
One-to-One Unary Relationship
One-to-Many Unary Relationship
Many-to-Many Unary Relationship
3-40
Unary Relationships
3-41
Ternary Relationship
Involves three different entity types.
3-42
Ternary Relationship: The General Hardware Company E-R Diagram
Customer Employee, a dependent entity, is distinguished by a diagonal hash mark in each corner of its attribute area.
3-43
Good Reading Bookstores
3-44
World Music Association
3-45
Lucky Rent-A-Car
