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