knowledge-kitchen / course-notes
+ - 0:00:00
Notes for current slide
Notes for next slide

Entity-Relationship Diagrams

Database Design

1 / 37

Overview

3 / 37

Overview

Introduction

Entity-Relationship Diagrams (ERDs) provide a way to document the entities in a database along with the attributes that describe them.

3 / 37

Overview

Introduction

Entity-Relationship Diagrams (ERDs) provide a way to document the entities in a database along with the attributes that describe them.

In practice, there are several styles of ER diagrams:

3 / 37

Overview

Introduction

Entity-Relationship Diagrams (ERDs) provide a way to document the entities in a database along with the attributes that describe them.

In practice, there are several styles of ER diagrams:

3 / 37

Overview

Introduction

Entity-Relationship Diagrams (ERDs) provide a way to document the entities in a database along with the attributes that describe them.

In practice, there are several styles of ER diagrams:

3 / 37

Overview

Introduction

Entity-Relationship Diagrams (ERDs) provide a way to document the entities in a database along with the attributes that describe them.

In practice, there are several styles of ER diagrams:

3 / 37

Overview

Introduction

Entity-Relationship Diagrams (ERDs) provide a way to document the entities in a database along with the attributes that describe them.

In practice, there are several styles of ER diagrams:

3 / 37

Overview

Introduction

Entity-Relationship Diagrams (ERDs) provide a way to document the entities in a database along with the attributes that describe them.

In practice, there are several styles of ER diagrams:

We will [more-or-less] follow Chen notation.

3 / 37

Entities

4 / 37

Entities

Concept

An entity is something about which we store data.

Entity symbol

4 / 37

Entities

Concept

An entity is something about which we store data.

Entity symbol

  • For example, a customer is an entity, as is a merchandise item stocked by shop.
4 / 37

Entities

Concept

An entity is something about which we store data.

Entity symbol

  • For example, a customer is an entity, as is a merchandise item stocked by shop.

  • Entities are not necessarily tangible. For example, a concert or a doctor's appointment is an entity.

4 / 37

Entities

Concept

An entity is something about which we store data.

Entity symbol

  • For example, a customer is an entity, as is a merchandise item stocked by shop.

  • Entities are not necessarily tangible. For example, a concert or a doctor's appointment is an entity.

  • In relational databases, we typically create a separate table for each entity.

4 / 37

Entities

Concept

An entity is something about which we store data.

Entity symbol

  • For example, a customer is an entity, as is a merchandise item stocked by shop.

  • Entities are not necessarily tangible. For example, a concert or a doctor's appointment is an entity.

  • In relational databases, we typically create a separate table for each entity.

  • In entity relationship diagrams, entities are represented as rectangles with the singular entity name inside.

4 / 37

Entities

Example

An example of two entities: a dog and a person.

Dog entity

Person entity

5 / 37

Attributes

6 / 37

Attributes

Concept

Entities have data that describe them... their attributes.

Attribute symbol

6 / 37

Attributes

Concept

Entities have data that describe them... their attributes.

Attribute symbol

  • For example, a customer entity is usually described by a customer number, first name, last name, street, city, state, zip code, and phone number.
6 / 37

Attributes

Concept

Entities have data that describe them... their attributes.

Attribute symbol

  • For example, a customer entity is usually described by a customer number, first name, last name, street, city, state, zip code, and phone number.

  • A concert entity might be described by a title, date, location, and name of the performer.

6 / 37

Attributes

Concept

Entities have data that describe them... their attributes.

Attribute symbol

  • For example, a customer entity is usually described by a customer number, first name, last name, street, city, state, zip code, and phone number.

  • A concert entity might be described by a title, date, location, and name of the performer.

  • In relational databases, we typically create a fields within the table representing the entity for each attribute that describes it.

6 / 37

Attributes

Concept

Entities have data that describe them... their attributes.

Attribute symbol

  • For example, a customer entity is usually described by a customer number, first name, last name, street, city, state, zip code, and phone number.

  • A concert entity might be described by a title, date, location, and name of the performer.

  • In relational databases, we typically create a fields within the table representing the entity for each attribute that describes it.

  • In entity relationship diagrams, attributes are represented as ellipses with the attribute name inside.

6 / 37

Entities

Example

An example of attributes of a dog:

Dog entity

7 / 37

Entities

Example

An example of attributes of a person:

Person entity

8 / 37

Attributes

Primary Keys

In a logical model, some attributes are indicated as primary keys - attributes that can be used to uniquely identify each entity.

Key attribute symbol

9 / 37

Attributes

Primary Keys

In a logical model, some attributes are indicated as primary keys - attributes that can be used to uniquely identify each entity.

Key attribute symbol

  • For example, a customer entity might have a customer id field that holds an arbitrary unique identifier of that customer.
9 / 37

Attributes

Primary Keys

In a logical model, some attributes are indicated as primary keys - attributes that can be used to uniquely identify each entity.

Key attribute symbol

  • For example, a customer entity might have a customer id field that holds an arbitrary unique identifier of that customer.

  • In entity relationship diagrams, primary key attribute names are underlined.

9 / 37

Attributes

Example

An example of attributes of a dog, with the primary key underlined:

Key attribute symbol

10 / 37

Attributes

Example

An example of attributes of a person, with the primary key underlined:

Key attribute symbol

11 / 37

Attributes

Foreign Keys

Entity Relationship Diagramming purists would say that foreign key fields should not be included in the diagrams.

Key attribute symbol

12 / 37

Attributes

Foreign Keys

Entity Relationship Diagramming purists would say that foreign key fields should not be included in the diagrams.

Key attribute symbol

12 / 37

Attributes

Foreign Keys

Entity Relationship Diagramming purists would say that foreign key fields should not be included in the diagrams.

Key attribute symbol

  • See this discussion if you find this curious or interesting.

  • However, many often find it useful to do so.

12 / 37

Attributes

Foreign Keys

Entity Relationship Diagramming purists would say that foreign key fields should not be included in the diagrams.

Key attribute symbol

  • See this discussion if you find this curious or interesting.

  • However, many often find it useful to do so.

  • Some people put double underlines under the foreign key field, although this is non-standard.

12 / 37

Attributes

Foreign Keys

Entity Relationship Diagramming purists would say that foreign key fields should not be included in the diagrams.

Key attribute symbol

  • See this discussion if you find this curious or interesting.

  • However, many often find it useful to do so.

  • Some people put double underlines under the foreign key field, although this is non-standard.

  • Feel free to follow this non-standard if it helps you explain your ideas.

12 / 37

Relationships

13 / 37

Relationships

Concept

Entities sometimes have relationships with one another.

Relationship symbol

13 / 37

Relationships

Concept

Entities sometimes have relationships with one another.

Relationship symbol

  • For example, imagine we have two kinds of entities in a store: a Customer and a Product.
13 / 37

Relationships

Concept

Entities sometimes have relationships with one another.

Relationship symbol

  • For example, imagine we have two kinds of entities in a store: a Customer and a Product.

  • A Customer in a Store might purchase a Product.

13 / 37

Relationships

Concept

Entities sometimes have relationships with one another.

Relationship symbol

  • For example, imagine we have two kinds of entities in a store: a Customer and a Product.

  • A Customer in a Store might purchase a Product.

  • A Student might register for a Course.

13 / 37

Relationships

Concept

Entities sometimes have relationships with one another.

Relationship symbol

  • For example, imagine we have two kinds of entities in a store: a Customer and a Product.

  • A Customer in a Store might purchase a Product.

  • A Student might register for a Course.

  • An Employee works for a Company.

13 / 37

Relationships

Concept

Entities sometimes have relationships with one another.

Relationship symbol

  • For example, imagine we have two kinds of entities in a store: a Customer and a Product.

  • A Customer in a Store might purchase a Product.

  • A Student might register for a Course.

  • An Employee works for a Company.

  • Relationships are represented as a rhombus (diamond) symbol.

13 / 37

Attributes

Example

An example of relationship between a dog and a person:

Relationship symbol

14 / 37

Attributes

Example

Another example of relationship between a dog and a person:

Relationship symbol

15 / 37

Attributes

Example

Both relationships in one diagram.

Relationship symbol

16 / 37

Attributes

Example

An ERD play on the phrase, "It's a dog eat dog world."

Relationship symbol

17 / 37

Attributes

Entity or Attribute?

Sometimes it may seem unclear whether a given term should be represented as an entity or as an attribute.

18 / 37

Attributes

Entity or Attribute?

Sometimes it may seem unclear whether a given term should be represented as an entity or as an attribute.

The following questions may help determine the answer to this question:

18 / 37

Attributes

Entity or Attribute?

Sometimes it may seem unclear whether a given term should be represented as an entity or as an attribute.

The following questions may help determine the answer to this question:

  • Is the term of direct interest on its own?
18 / 37

Attributes

Entity or Attribute?

Sometimes it may seem unclear whether a given term should be represented as an entity or as an attribute.

The following questions may help determine the answer to this question:

  • Is the term of direct interest on its own?

  • Does the term have sub-terms of its own?

18 / 37

Attributes

Entity or Attribute?

Sometimes it may seem unclear whether a given term should be represented as an entity or as an attribute.

The following questions may help determine the answer to this question:

  • Is the term of direct interest on its own?

  • Does the term have sub-terms of its own?

  • Could there be multiple instances of the object for a single record?

18 / 37

Attributes

Entity or Attribute?

Sometimes it may seem unclear whether a given term should be represented as an entity or as an attribute.

The following questions may help determine the answer to this question:

  • Is the term of direct interest on its own?

  • Does the term have sub-terms of its own?

  • Could there be multiple instances of the object for a single record?

  • Is the term often nonexistent or unknown?

18 / 37

Attributes

Entity or Attribute?

Sometimes it may seem unclear whether a given term should be represented as an entity or as an attribute.

The following questions may help determine the answer to this question:

  • Is the term of direct interest on its own?

  • Does the term have sub-terms of its own?

  • Could there be multiple instances of the object for a single record?

  • Is the term often nonexistent or unknown?

If the answer to any of these questions is yes, then the matter in question should be an entity.

18 / 37

Cardinality

19 / 37

Cardinality

Concept

Cardinality is a jargon term meaning how many entities of each type are involved in a given relationship.

Cardinality symbol

19 / 37

Cardinality

Concept

Cardinality is a jargon term meaning how many entities of each type are involved in a given relationship.

Cardinality symbol

The possibilities are:

19 / 37

Cardinality

Concept

Cardinality is a jargon term meaning how many entities of each type are involved in a given relationship.

Cardinality symbol

The possibilities are:

  • one-to-one
19 / 37

Cardinality

Concept

Cardinality is a jargon term meaning how many entities of each type are involved in a given relationship.

Cardinality symbol

The possibilities are:

  • one-to-one

  • one-to-many

19 / 37

Cardinality

Concept

Cardinality is a jargon term meaning how many entities of each type are involved in a given relationship.

Cardinality symbol

The possibilities are:

  • one-to-one

  • one-to-many

  • many-to-many

19 / 37

Cardinality

Concept

Cardinality is a jargon term meaning how many entities of each type are involved in a given relationship.

Cardinality symbol

The possibilities are:

  • one-to-one

  • one-to-many

  • many-to-many

Cardinality is indicated by numbers or letters next to each entity in the relationship.

19 / 37

Cardinality

Concept

Cardinality is a jargon term meaning how many entities of each type are involved in a given relationship.

Cardinality symbol

The possibilities are:

  • one-to-one

  • one-to-many

  • many-to-many

Cardinality is indicated by numbers or letters next to each entity in the relationship. Numbers represent a distinct number of the given entity involved in the relationship.

19 / 37

Cardinality

Concept

Cardinality is a jargon term meaning how many entities of each type are involved in a given relationship.

Cardinality symbol

The possibilities are:

  • one-to-one

  • one-to-many

  • many-to-many

Cardinality is indicated by numbers or letters next to each entity in the relationship. Numbers represent a distinct number of the given entity involved in the relationship. The letters, M and N, indicate multiples of the given entity are involved in the relationship.

19 / 37

Cardinality

Example

A Cyclops has only one eye.

Cardinality example

20 / 37

Cardinality

Example

A Cyclops has only one eye.

Cardinality example

  • This diagram also shows that each eye belongs to only one cyclops.
20 / 37

Cardinality

Example

In the ancient Greek myth of the Graeae, three sisters named Pephredo, Enyo, and Dino shared one eye and one tooth.

Cardinality example

21 / 37

Cardinality

Example

In the ancient Greek myth of the Graeae, three sisters named Pephredo, Enyo, and Dino shared one eye and one tooth.

Cardinality example

  • Each of the Graeae has one eye, indicated by the 1 next to the Eye.
  • Each of the Graeae has one tooth, indicated by the 1 next to the Tooth.
  • Each eye belongs to multiple Graeae, indicated by the M next to the Graeae.
  • Each tooth belongs to multiple Graeae, indicated by the M next to the Graeae.
21 / 37

Cardinality

Example

Each painter paints many paintings.

Cardinality example

22 / 37

Cardinality

Example

Each painter paints many paintings.

Cardinality example

  • Note that in this model, any given painting must have been painted by only one painter.
22 / 37

Cardinality

Example

Each painter paints many paintings.

Cardinality example

  • Note that in this model, any given painting must have been painted by only one painter.

  • If painters can collaborate on paintings, then we would need to indicate that with a many-to-many relationship:

22 / 37

Cardinality

Example

Each painter paints many paintings.

Cardinality example

  • Note that in this model, any given painting must have been painted by only one painter.

  • If painters can collaborate on paintings, then we would need to indicate that with a many-to-many relationship:

Cardinality example

22 / 37

Cardinality

Example

Of course painters and paintings most likely have attributes of interest to us.

Cardinality example

23 / 37

Cardinality

Example

A painter might also own one or more dogs.

Cardinality example

24 / 37

Cardinality

Example

But beware... a painter's dog may tear up their painting.

Cardinality example

25 / 37

Physical Implementation of Relationships

26 / 37

Physical Implementation of Relationships

Overview

While not all Entity-Relationship diagrams show how cardinality will be "physically" implemented in a real relational database, let us explore this for a minute.

26 / 37

Physical Implementation of Relationships

One-to-one

A one-to-one relationship is implemented by having a foreign key in one table pointing to the other... it doesn't matter which table has the foreign key.

27 / 37

Physical Implementation of Relationships

One-to-one

A one-to-one relationship is implemented by having a foreign key in one table pointing to the other... it doesn't matter which table has the foreign key.

Image a cyclopses table in a world where each cyclops has only one eye:

id name email
1 Brontes brontes@greek.io
2 Steropes steropes@jourrapide.com
3 Arges arges@mythology.org
4 Polyphemus polyphemus@bmoney.com
... ... ...
27 / 37

Physical Implementation of Relationships

One-to-one

A one-to-one relationship is implemented by having a foreign key in one table pointing to the other... it doesn't matter which table has the foreign key.

Image a cyclopses table in a world where each cyclops has only one eye:

id name email
1 Brontes brontes@greek.io
2 Steropes steropes@jourrapide.com
3 Arges arges@mythology.org
4 Polyphemus polyphemus@bmoney.com
... ... ...

Related to an eyes table, where each eye belongs to one cyclops:

id color cyclops_id
1 blue 1
2 hazel 4
3 blue 3
4 brown 2
... ... ...
27 / 37

Physical Implementation of Relationships

One-to-many

In a one-to-many relationship, it is always the "many" table that has the foreign key pointing to the other.

28 / 37

Physical Implementation of Relationships

One-to-many

In a one-to-many relationship, it is always the "many" table that has the foreign key pointing to the other.

Image a cyclopses table in a world where multiple cyclopses share one eye:

id name email eye_id
1 Deino deino@greek.io 1
2 Enyo enyo@jourrapide.com 1
3 Pemphredo pemphredo@mythology.org 1
... ... ... ...
28 / 37

Physical Implementation of Relationships

One-to-many

In a one-to-many relationship, it is always the "many" table that has the foreign key pointing to the other.

Image a cyclopses table in a world where multiple cyclopses share one eye:

id name email eye_id
1 Deino deino@greek.io 1
2 Enyo enyo@jourrapide.com 1
3 Pemphredo pemphredo@mythology.org 1
... ... ... ...

Related to an eyes table:

id color
1 blue
2 hazel
3 blue
4 brown
... ...
28 / 37

Physical Implementation of Relationships

Many-to-many

A many-to-many relationship requires a new "linking" table that contains only foreign keys pointing to the two records involved in each relationship.

29 / 37

Physical Implementation of Relationships

Many-to-many

A many-to-many relationship requires a new "linking" table that contains only foreign keys pointing to the two records involved in each relationship.

E.g. a cyclopses_eyes linking table:

cyclops_id eye_id
1 1
2 1
3 1
1 3
2 2
... ...
29 / 37

Physical Implementation of Relationships

Many-to-many

A many-to-many relationship requires a new "linking" table that contains only foreign keys pointing to the two records involved in each relationship.

E.g. a cyclopses_eyes linking table:

cyclops_id eye_id
1 1
2 1
3 1
1 3
2 2
... ...
  • These are not "cyclopses" anymore, since each can have multiple eyes!
29 / 37

Physical Implementation of Relationships

Many-to-many

A many-to-many relationship requires a new "linking" table that contains only foreign keys pointing to the two records involved in each relationship.

E.g. a cyclopses_eyes linking table:

cyclops_id eye_id
1 1
2 1
3 1
1 3
2 2
... ...
  • These are not "cyclopses" anymore, since each can have multiple eyes!

  • The cyclopses and eyes tables do not require any foreign keys.

29 / 37

Physical Implementation of Relationships

Many-to-many

A many-to-many relationship requires a new "linking" table that contains only foreign keys pointing to the two records involved in each relationship.

E.g. a cyclopses_eyes linking table:

cyclops_id eye_id
1 1
2 1
3 1
1 3
2 2
... ...
  • These are not "cyclopses" anymore, since each can have multiple eyes!

  • The cyclopses and eyes tables do not require any foreign keys.

  • These tables typically use both fields as a composite primary key.

29 / 37

Perspectives

30 / 37

Perspectives

Concept

How you diagram a model depends on whether you're designing the conceptual model, the logical model, or the physical model.

30 / 37

Perspectives

Concept

How you diagram a model depends on whether you're designing the conceptual model, the logical model, or the physical model.

  • For small projects, it is sometimes more convenient to jump directly to physical modeling.
30 / 37

Perspectives

Concept

How you diagram a model depends on whether you're designing the conceptual model, the logical model, or the physical model.

  • For small projects, it is sometimes more convenient to jump directly to physical modeling.

  • For larger, more complex projects, creating an abstract conceptual or logical model is usually beneficial prior to physical modeling and implementation.

30 / 37

Perspectives

Conceptual model

Conceptual modeling involves creating a model of a real world problem that is independent of any physical considerations.

31 / 37

Perspectives

Conceptual model

Conceptual modeling involves creating a model of a real world problem that is independent of any physical considerations.

  • This is very high-level and only shows the most important entities and their relationships.
31 / 37

Perspectives

Conceptual model

Conceptual modeling involves creating a model of a real world problem that is independent of any physical considerations.

  • This is very high-level and only shows the most important entities and their relationships.

  • Typically this is a first-cut model, with insufficient detail to build an actual database.

31 / 37

Perspectives

Conceptual modeling process

Building a conceptual model involves:

32 / 37

Perspectives

Conceptual modeling process

Building a conceptual model involves:

  • ER diagramming in a simple rough sketch manner, just the entities with optionally some relationships and attributes
32 / 37

Perspectives

Conceptual modeling process

Building a conceptual model involves:

  • ER diagramming in a simple rough sketch manner, just the entities with optionally some relationships and attributes

  • Removing redundancy (i.e. normalization).

32 / 37

Perspectives

Conceptual modeling process

Building a conceptual model involves:

  • ER diagramming in a simple rough sketch manner, just the entities with optionally some relationships and attributes

  • Removing redundancy (i.e. normalization).

  • Making sure all intended uses of the data are supported by the design.

32 / 37

Perspectives

Logical model

Compared to conceptual modeling, a logical model shows more detail of all entities and relationships

33 / 37

Perspectives

Logical model

Compared to conceptual modeling, a logical model shows more detail of all entities and relationships

  • It is still general and abstract from how the database is actually implemented.
33 / 37

Perspectives

Logical model

Compared to conceptual modeling, a logical model shows more detail of all entities and relationships

  • It is still general and abstract from how the database is actually implemented.

  • Logical models show the primary key fields, the attributes, and cardinality of relationships (more on these later).

33 / 37

Perspectives

Logical model

Compared to conceptual modeling, a logical model shows more detail of all entities and relationships

  • It is still general and abstract from how the database is actually implemented.

  • Logical models show the primary key fields, the attributes, and cardinality of relationships (more on these later).

  • Foreign keys are optional.

33 / 37

Perspectives

Logical modeling process

Building a logical model involves:

34 / 37

Perspectives

Logical modeling process

Building a logical model involves:

  • Further refine the conceptual model to include all entities and attributes and relationships.
34 / 37

Perspectives

Logical modeling process

Building a logical model involves:

  • Further refine the conceptual model to include all entities and attributes and relationships.

  • Determine which attributes will serve as primary keys, and [optionally] also foreign keys.

34 / 37

Perspectives

Logical modeling process

Building a logical model involves:

  • Further refine the conceptual model to include all entities and attributes and relationships.

  • Determine which attributes will serve as primary keys, and [optionally] also foreign keys.

  • Establishing the cardinality among all entities.

34 / 37

Perspectives

Logical modeling process

Building a logical model involves:

  • Further refine the conceptual model to include all entities and attributes and relationships.

  • Determine which attributes will serve as primary keys, and [optionally] also foreign keys.

  • Establishing the cardinality among all entities.

  • Normalization of those tables, following the standard relational database normal forms.

34 / 37

Perspectives

Logical modeling process

Building a logical model involves:

  • Further refine the conceptual model to include all entities and attributes and relationships.

  • Determine which attributes will serve as primary keys, and [optionally] also foreign keys.

  • Establishing the cardinality among all entities.

  • Normalization of those tables, following the standard relational database normal forms.

  • We will aim to achieve the logical level of diagramming.

34 / 37

Perspectives

Physical model

The physical model includes all the same properties as the conceptual and logical models, in addition to more details of how the database will actually be implemented in a particular type of relational database management system.

35 / 37

Perspectives

Physical model

The physical model includes all the same properties as the conceptual and logical models, in addition to more details of how the database will actually be implemented in a particular type of relational database management system.

  • This is not abstract, but very concrete and specific to an actual database system.
35 / 37

Perspectives

Physical modeling process

Building a physical modeling involves:

36 / 37

Perspectives

Physical modeling process

Building a physical modeling involves:

  • Developing the baseline logical model first.
36 / 37

Perspectives

Physical modeling process

Building a physical modeling involves:

  • Developing the baseline logical model first.

  • Establishing database indices for increased efficiency.

36 / 37

Perspectives

Physical modeling process

Building a physical modeling involves:

  • Developing the baseline logical model first.

  • Establishing database indices for increased efficiency. An index is an order or organization of the data in some fields of a table to make retrieval faster and more efficient.

36 / 37

Perspectives

Physical modeling process

Building a physical modeling involves:

  • Developing the baseline logical model first.

  • Establishing database indices for increased efficiency. An index is an order or organization of the data in some fields of a table to make retrieval faster and more efficient. Creating indices becomes increasingly important for larger data sets.

36 / 37

Perspectives

Physical modeling process

Building a physical modeling involves:

  • Developing the baseline logical model first.

  • Establishing database indices for increased efficiency. An index is an order or organization of the data in some fields of a table to make retrieval faster and more efficient. Creating indices becomes increasingly important for larger data sets.

  • Determining data types for each attribute.

36 / 37

Perspectives

Physical modeling process

Building a physical modeling involves:

  • Developing the baseline logical model first.

  • Establishing database indices for increased efficiency. An index is an order or organization of the data in some fields of a table to make retrieval faster and more efficient. Creating indices becomes increasingly important for larger data sets.

  • Determining data types for each attribute.

  • Imposing constraints on the data for increased integrity and security.

36 / 37

Conclusions

37 / 37

Conclusions

Thank you. Bye.

37 / 37
Paused

Help

Keyboard shortcuts

, , Pg Up, k Go to previous slide
, , Pg Dn, Space, j Go to next slide
Home Go to first slide
End Go to last slide
Number + Return Go to specific slide
b / m / f Toggle blackout / mirrored / fullscreen mode
c Clone slideshow
p Toggle presenter mode
t Restart the presentation timer
?, h Toggle this help
Esc Back to slideshow