Entity Relationship Diagrams

From Knowledge Kitchen
Jump to navigation Jump to search


Entity Relationship (E-R) Diagrams are used to help understand the types of data stored in a system, and the relationships among those pieces of data. They help inform how to construct up database tables to handle the data that will be placed in them. The goal of E-R diagramming was to allow someone to provide a data model that captures the data requirements of a particular project, but is not biased or tied to one particular implementation or variety of relational database.

E-R Diagram Perspective

How you design 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 model is usually beneficial prior to physical modeling and implementation.

Conceptual model

This is very high-level and only shows the most important entities and their relationships. Conceptual modeling involves creating a model of a real world problem that is independent of any physical considerations. Typically this is a first-cut model, with insufficient detail to build an actual database.

This involves:

  • ER diagramming
  • removing redundancy
  • making sure all intended uses of the data are supported by the design

Logical model

Compared to conceptual modeling, this shows more detail of all entities and relationships, but is still general and abstract from how the database is actually implemented. The key difference is modeling in a way that shows how each piece of data can be mapped to a real data storage object in the database.

This step involves:

When people speak of a "data model", they are usually referring to a logical data model.

Physical model

This includes all the above, 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.

It includes:

  • establishing database indices for increased efficiency
  • imposing constraints on the data for increased integrity and security

Basic symbols

Many variations of E-R diagramming are used in practice, but these are mainly differences in notation, and in the symbols used to represent the constructs, rather than in the main concepts.

For simplicity, we use one style of of these diagrams called "Chen Notation":

  • Entities are represented by rectangles
  • Attributes of entities are represented by ovals
  • Relationships between entities are represented by diamonds
Basic shapes of entity relationship diagrams

Entities

An Entity is some identifiable object relevant to the system being built. It represents the kind of "thing" that you need to store data about. For example, a product in a store, a student in a course, an employee in a company, a patient in a hospital, etc.

Attributes

An Entity has Attributes. These are the properties or settings of the given Entity. For example, a Product in a Store might have a price, a title, a description, a manufacturer, etc. A Student in a Course might have a name, email address, phone number, student ID number, etc.

Relationships

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.

Entities and attributes

In E-R Diagrams, attributes are attached to the entity they describe. Attributes that uniquely identify a given Entity are underlined.

Conceptual and logical models only need to include those details that sufficiently describe the purpose of each entity.

Attributes in a conceptual model

Physical models include all implementation details, including every fields that will be in the table in whichever type of database will be used. They also often include all details of each field in each table, including such minutiae as data type, default values, etc.

Attributes, including a few details of a logical model

Entity or attribute

The following questions may help determine whether a particular piece of data belongs as an entity rather than an attribute:

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

Relationships

Relationships are described with verbs that link the related entities. They are diagrammed as rhombi, or diamonds.

Logical models contain only as much detail as necessary to explain the concept in detail.

Example of a relationship in a conceptual model

Physical models contain all the detail necessary to show how the concept will be implemented in the actual database tables.

Example of a relationship with more detail, such as for a logical model

Relationships, of course, can be complex and many relationships may link together several entities.

Example of multiple entity relationships in a more complete logical model including cardinality

But some relationships are quite simple. The recursive relationship is perhaps the simplest of all.

Some relationships are recursive, such as that expressed by the saying, "it's a dog eat dog world."

Cardinality

Cardinality is a jargon term meaning how many entities are involved in a given relationship:

  • 1-to-1
  • 1-to-many
  • many-to-many
Example of cardinality in a conceptual model illustrating a one-to-one relationship
Example of cardinality in a logical model illustrating a one-to-many relationship
Example of cardinality in a conceptual model illustrating a many-to-many relationship


Practical example

Image a database storing information about artworks.

  • Does the following diagram adequately represent the entities, their attributes, and the relationships among them?
  • What are some of the assumptions built into this model?
Logical ER diagram showing entities, attributes, and relationships related to artworks


What links here