Relational Data Modeling - Entity-Relationship Diagram (ERD)

Data System Architecture

About

The entity-relationship (E-R) diagram is a diagram that helps visualize relational data models and shows relationships between relation (ie the entities)

ER modeling technique was first developed by Peter Chen in 1976 and described in two papers

A lot of graphical variation has been developed but they resolve all around the same entity-relationship concept..

Example

Entity Relationship Diagram

Syntax

Erd Graphical Notation

The building blocks of the ERD in a relational model are:

Chen definition:

E-R diagram in a DSS system

With the growth of the machine power, this point of view is now not always true

Typically, E-R schemas have many tables, sometimes hundreds or even thousands. There are many tables because the data has been carefully taken apart—normalized, in database terminology—with the primary goal of:

  • reducing data redundancy
  • and bringing about fast update performance.

E-R models are very efficient for OLTP databases. When E-R databases are queried, joins are usually predetermined and can be optimized. E-R databases are usually queried by applications that know exactly where to go and what to ask. These applications typically query small units of information at a time, such as a customer record, an order, or a shipment record.

E-R schemas generally do not work well for queries that perform historical analysis due to two major problems—poor performance and difficulty in posing the question in SQL:

  • Performance problems persist with historical E-R queries because the queries require the database to put the data back together again; this is a slow, complex process. Furthermore, because the cost-based optimizers in database management systems are not designed for the level of complexity in such a query, they can generate query plans that result in poor performance.
  • A Database Analyst (DBA) who is very familiar with the data might be able to write a SQL query against an E-R schema that can theoretically answer a business question, but such detailed knowledge of the data is generally beyond the scope of the end user business analyst. Even when the SQL is crafted properly, there is often an unacceptably high response time in returning results to the user.

Alternative Notations

Alternative Notations

Cardinality

This sections shows the different Entity-Relationship graphical variations in terms of cardinality

Cardinality Representation Erd

List

FYI:

Erd Elements

Software / Library

Mermaid

Mermaid offers a simple markup syntax to create ERD that you can create with their live editor or embed in your web application

Example:

erDiagram
    CUSTOMER ||--o{ ORDER : places
    ORDER ||--|{ LINE-ITEM : contains
    CUSTOMER }|..|{ DELIVERY-ADDRESS : uses
erDiagram CUSTOMER ||--o{ ORDER : places ORDER ||--|{ LINE-ITEM : contains CUSTOMER }|..|{ DELIVERY-ADDRESS : uses

Documentation / Reference

Papers





Discover More
Card Puncher Data Processing
Business - Online transaction processing (OLTP)

Online transaction processing (or OLTP) refers to a class of application that support a business transaction process. Databases (SAP, Oracle, SQL) supporting Online Transaction Processing The...
Star Schema
Dimensional Data Modeling - Dimensional Schemas

This section is dimensional data modeling, That is the building of a cube (hypercube) A dimensional schema is a schema based on dimension. The dimensional schema can modeled: in a relational database...
Snowflakeschema
Dimensional Data Modeling - Snowflake schema

A snowflake schema is a star schema with fully normalised (3NF) dimensions. It gets its name from that it has a similar shape than a snowflake. A snowflake is a dimensional model : in which a central...
Association Entity Example
Logical Data Modeling - Association

An association is a relationship between two or more entities. It's represented by a link in the data model. An association: answers to the question why two entity need to know one another. specifies...
Entity Set Graph Model
Logical Data Modeling - Visualization / Language

Data Modeling Language helps visualize data model. - The ERD is the most common technique for drawing data models. (UML) - The class diagrams adds the behaviours of each entity to the model....
Classes Relationship
Object - Class Diagram (UML)

A class diagram is a diagram specified in the UML standard by showing: the classes (the class is the entity) with its attributes and methods. and the relationships between them By comparison with...
Datavault
Relational Data Modeling - Data Model

data model in a relational environment The difference between model (ER, Dimensional, Vault) has everything to do with terminology and method. There are two basic types of physical models : (E-R)...
Erd Graphical Notation
Relational Data Modeling - Visualization

The vizualization of a relational data model is done as a sepecialized Entity relationship diagram. A best practice is to draw the child table above the parent table. One way to see the different...



Share this page:
Follow us:
Task Runner