knowledge-kitchen

Database Design - Entity-Relationship Diagrams (ERD)

Database Design

1. Overview
2. Entities
3. Attributes
4. Relationships
5. Cardinality
6. Physical Implementation of Relationships
7. Perspectives
8. Conclusions

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:

• Chen notation (named after the originator of ER modeling, Dr. Peter P.S. Chen)

• Information Engineering (IE, or “crow’s feet”)

• Unified Modeling Language (UML)

• Idiosyncratic styles that mix and match features of each of these

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

Entities

Concept

An entity is something about which we store data.

• 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.

Example

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

Attributes

Concept

Entities have data that describe them… their attributes.

• 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.

Example

An example of attributes of a dog:

Example

An example of attributes of a person:

Primary Keys

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

• 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.

Example

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

Example

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

Foreign Keys

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

• 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.

Relationships

Concept

Entities sometimes have relationships with one another.

• 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.

Example

An example of relationship between a dog and a person:

Example

Another example of relationship between a dog and a person:

Example

Both relationships in one diagram.

Example

An ERD play on the phrase, “It’s a dog eat dog world.”

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.

Cardinality

Concept

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

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.

Example

A Cyclops has only one eye.

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

Example

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

• 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.

Example

Each painter paints many paintings.

• 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:

Example

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

Example

A painter might also own one or more dogs.

Example

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

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.

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
…	…	…

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
…	…

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.

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.

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.

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.

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.

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.

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.

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.

Conclusions

Thank you. Bye.