Is ER diagram a data model?

Importance of ERDs and their uses

Entity relationship diagrams provide a visual starting point for database design that can also be used to help determine information system requirements throughout an organization. After a relational database is rolled out, an ERD can still serve as a reference point, should any debugging or business process re-engineering be needed later.

However, while an ERD can be useful for organizing data that can be represented by a relational structure, it can't sufficiently represent semi-structured or unstructured data. It's also unlikely to be helpful on its own in integrating data into a pre-existing information system.

How to create an ERD

ERDs are generally depicted in one or more of the following models:

• A conceptual data model, which lacks specific detail but provides an overview of the scope of the project and how data sets relate to one another.
• A logical data model, which is more detailed than a conceptual data model, illustrating specific attributes and relationships among data points. While a conceptual data model does not need to be designed before a logical data model, a physical data model is based on a logical data model.
• A physical data model, which provides the blueprint for a physical manifestation -- such as a relational database -- of the logical data model. One or more physical data models can be developed based on a logical data model.

There are five basic components of an entity relationship diagram. Similar components will be designated by the same shape. For example, all entities types might be enclosed in a rectangle, while all attributes are enclosed in a diamond. The components include:

1. Entities, which are objects or concepts that can have data stored about them. Entities refer to tables used in databases.
2. Attributes, which are properties or characteristics of entities. An ERD attribute can be denoted as a primary key, which identifies a unique attribute, or a foreign key, which can be assigned to multiple attributes.
3. The relationships between and among those entities.
4. Actions, which describe how entities share information in the database.
5. Connecting lines

For example, an ERD representing the information system for a company's sales department might start with graphical representations of entities such as the sales representative, the customer, the customer's address, the customer's order, the product and the warehouse. (See diagram above.) Then lines or other symbols can be used to represent the relationship between entities, and text can be used to label the relationships.

A cardinality notation can then define the attributes of the relationship between the entities. Cardinalities can denote that an entity is optional (for example, a sales rep could have no customers or could have many) or mandatory (for example, there must be at least one product listed in an order.)

The three main cardinalities are:

1. A one-to-one relationship (1:1). For example, if each customer in a database is associated with one mailing address.
2. A one-to-many relationship (1:M). For example, a single customer might place an order for multiple products. The customer is associated with multiple entities, but all those entities have a single connection back to the same customer.
3. A many-to-many relationship (M:N). For example, at a company where all call center agents work with multiple customers, each agent is associated with multiple customers, and multiple customers might also be associated with multiple agents.

While there are tools to help draw entity relationship diagrams, such as computer-aided software engineering (CASE) tools, some relational database management systems (RDBMS) also have design capabilities built in.

ERD stands for entity relationship diagram. People also call these types of diagrams ER diagrams and Entity Relationship Models. An ERD visualizes the relationships between entities like people, things, or concepts in a database. An ERD will also often visualize the attributes of these entities.

By defining the entities, their attributes, and showing the relationships between them, an ER diagram can illustrate the logical structure of databases. This is useful for engineers hoping to either document a database as it exists or sketch out a design of a new database.

Why Make an ERD?

An ER diagram can help businesses document existing databases and thereby troubleshoot logic or deployment problems or spot inefficiencies and help improve processes when a business wants to undertake business process re-engineering. ERDs can also be used to design and model new databases and make sure that engineers can identify any logic or design flaws before they're implemented in production.

• Document an existing database structure
• Debug, troubleshoot, and analyze
• Design a new database
• Gather design requirements
• Business process re-engineering (BPR)

When documenting a system or process, looking at the system in multiple ways increases the understanding of that system. ERD diagrams are commonly used in conjunction with a data flow diagram to display the contents of a data store. They help us to visualize how data is connected in a general way, and are particularly useful for constructing a relational database.

The History of Entity Relationship Diagrams

Peter Chen developed ERDs in the 1970s and published his proposal for entity relationship modeling in a 1976 paper titled "The Entity-Relationship Model: Toward a Unified View of Data". Peter Chen was a computer scientist who worked on improving database design. His entity relationship model was a way to visualize a database that unified other existing models into a single understanding that removed ambiguities. Prior to ERDs, there were three data models for databases: the network model, the relational model, and the entity set model. Each had their own strengths and weaknesses, but none provided a complete view of the database. With an ERD, Chen could provide a unified framework for database modeling.

Peter Chen's work was greatly influenced by scientists and engineers who came before him, specifically Charles Bachman, who worked on visualizing databases in the 1960s and his data structure diagrams became known as Bachman diagrams.

Chen's entity relationship model is in many ways the foundation for later practices like Unified Modeling Language or UML in information systems.

In the 1980s, another computer scientist named James Martin, worked to further refine Chen's ER model and introduced what's known today as the IE notation. IE notation uses Crow's foot to express cardinality (one to many relationship) instead of Chen's notation to epxress the same.

Types of ERD

ER diagrams will differ on how they express cardinality. They will also differ in how they display entities and their attributes and whether or not they show relationships or attributes as separate symbols.

Traditional ERD

The traditional Chen ERD is like a flowchart with connected symbols for entities, relationships, and attributes. Since its first introduction by Chen in the 70s, others have proposed different cardinality notations, but the basic symbols used and how they're connected tend to look the same.

IDEF1X Notation ERD - Relational Schema

IDEF1X stands for integrated definition for data modeling. This type of ER diagram will show entities connected to each other without relationship symbols. The attributes for any entity will be listed as part of a table inside each entity shape instead of separate symbols. Some also call this type of ER diagram a Relational Schema diagram.

Common ERD Symbols

An ER diagram has three main components: entities, relationships, and attributes connected by lines.

• Entities, which are represented by rectangles. An entity is an object or concept about which you want to store information.
  
  A weak entity is an entity that must defined by a foreign key relationship with another entity as it cannot be uniquely identified by its own attributes alone.
  
• Relationships, which are represented by diamond shapes, show how two entities share information in the database.
  
  In some cases, entities can be self-linked.
  
  
• Attributes, which are represented by ovals. A key attribute is the unique, distinguishing characteristic of the entity. For example, an employee's social security number might be the employee's key attribute.
  
  
  A multivalued attribute can have more than one value. For example, an employee entity can have multiple skill values.
  
  A derived attribute is based on another attribute. For example, an employee's monthly salary is based on the employee's annual salary.
  
• Connecting lines, solid lines that connect attributes and show the relationships of entities in the diagram.
• Cardinality specifies the numerical attribute of the relationship between entities. It can be one-to-one, many-to-one, or many-to-many.

Styles of Cardinality

Cardinality is the mathematical sense just means the number of values in a set. In relationship to databases and ERD, cardinality specifies how many instances of an entity relate to one instance of another entity. Ordinality is also closely linked to cardinality. While cardinality specifies the occurrences of a relationship, ordinality describes the relationship as either mandatory or optional. In other words, cardinality specifies the maximum number of relationships and ordinality specifies the absolute minimum number of relationships.

In other words, there will be multiple instances of each entity in a database. Cardinality allows you express the number of each entity that can be associated with another entity. For example, in an employee database, a manager will have multiple employee reports (in a one to many relationship), but an employee will only have one ID number (a one to one relationship). There are three main types of relationships in a database expressed using cardinality notation in an ER diagram.

• one-to-one
• one-to-many
• many-to-many

ERD Models

Entity Relationship Models can also vary based on the level of abstraction visualized. There are usually three models people refer to based on the level of detail you want to show: conceptual ERD, logical ERD, and physical ERD.

• Conceptual ERD or data model: This model has the most abstraction and least amount of detail, as such it's appropriate for large projects that need a higher level view used by business analysts. A typical conceptual ERD will contain entities and relationships, but offer no details on specific database columns or cardinalities. It's a general, high-level view of database design.
• Logical ERD or data model: This model adds more detail to the conceptual model by defining additional entities that are operational and transactional.
• Physical ERD or data model: This model serves as the actual design or blueprint of the database with lots of technical details including defining cardinality and showing primary and foreign keys of entities instead of just their abstract semantic names. For this type of ERD, attributes will often be listed to represent the columns of the real database table.

Documenting an Existing Database from Data

There are two reasons to create a database diagram. You're either designing a new schema or you need to document your existing structure.

If you have an existing database you need to document, you can create a database diagram using data directly from your database. You can export your database structure as a CSV file (there are some scripts on how to this here), then have a program generate the ERD automatically.

This will be the most accurate portrait of your database and will require no drawing on your part.

Here's an example of a very basic database structure generated from data.

This type of ERD will be considered a physical data model and will contain all the technical details of your database using IDEF1X notation.

If you want to create a new plan, you can edit the generated diagram and collaborate with your team on what changes to make.

Which data model is related to the ER diagram?

What type of diagram is ER diagram?

Which diagram is used for data modeling?

What is the main role of an ER diagram in data Modelling?