Database is absolutely an integral part of software systems. To fully utilize ER Diagram in database engineering guarantees you to produce high-quality database design to use in database creation, management, and maintenance. An ER model also provides a means for communication.
Today we're going to walk you through everything you need to know about ER Diagramming. By reading this ERD guide, you will get the essential knowledge and skills about ER Diagrams and database design. You will learn things like what is ERD, why ERD, ERD notations, how to draw ERD, etc. along with a bunch of ERD examples.
Are you looking for a Free ERD tool for creating data models faster, easier and quicker? Visual Paradigm Community Edition provides you with an ERD editor for database design. It is an international award-winning modeler, and yet it is easy-to-use, intuitive & completely free.
Free DownloadFirst of all, what is an Entity Relationship Diagram?
Entity Relationship Diagram, also known as ERD, ER Diagram or ER model, is a type of structural diagram for use in database design. An ERD contains different symbols and connectors that visualize two important information: The major entities within the system scope, and the inter-relationships among these entities.
And that's why it's called "Entity" "Relationship" diagram (ERD)!
When we talk about entities in ERD, very often we are referring to business objects such as people/roles (e.g. Student), tangible business objects (e.g. Product), intangible business objects (e.g. Log), etc. "Relationship" is about how these entities relate to each other within the system.
In a typical ER design, you can find symbols such as rounded rectangles and connectors (with different styles of their ends) that depict the entities, their attributes, and inter-relationships.
So, when do we draw ERDs? While ER models are mostly developed for designing relational databases in terms of concept visualization and in terms of physical database design, there are still other situations when ER diagrams can help. Here are some typical use cases.
An ER Diagram contains entities, attributes, and relationships. In this section, we will go through the ERD symbols in detail.
An ERD entity is a definable thing or concept within a system, such as a person/role (e.g. Student), object (e.g. Invoice), concept (e.g. Profile) or event (e.g. Transaction) (note: In ERD, the term "entity" is often used instead of "table", but they are the same). When determining entities, think of them as nouns. In ER models, an entity is shown as a rounded rectangle, with its name on top and its attributes listed in the body of the entity shape. The ERD example below shows an example of an ER entity.
Also known as a column, an attribute is a property or characteristic of the entity that holds it.
An attribute has a name that describes the property and a type that describes the kind of attribute it is, such as varchar for a string, and int for integer. When an ERD is drawn for physical database development, it is important to ensure the use of types that are supported by the target RDBMS.
The ER diagram example below shows an entity with some attributes in it.
Also known as PK, a primary key is a special kind of entity attribute that uniquely defines a record in a database table. In other words, there must not be two (or more) records that share the same value for the primary key attribute. The ERD example below shows an entity 'Product' with a primary key attribute 'ID', and a preview of table records in the database. The third record is invalid because the value of ID 'PDT-0002' is already used by another record.
Also known as FK, a foreign key is a reference to a primary key in a table. It is used to identify the relationships between entities. Note that foreign keys need not be unique. Multiple records can share the same values. The ER Diagram example below shows an entity with some columns, among which a foreign key is used in referencing another entity.
A relationship between two entities signifies that the two entities are associated with each other somehow. For example, a student might enroll in a course. The entity Student is therefore related to Course, and a relationship is presented as a connector connecting between them.
Cardinality defines the possible number of occurrences in one entity which is associated with the number of occurrences in another. For example, ONE team has MANY players. When present in an ERD, the entity Team and Player are inter-connected with a one-to-many relationship.
In an ER diagram, cardinality is represented as a crow's foot at the connector's ends. The three common cardinal relationships are one-to-one, one-to-many, and many-to-many.
A one-to-one relationship is mostly used to split an entity in two to provide information concisely and make it more understandable. The figure below shows an example of a one-to-one relationship.
A one-to-many relationship refers to the relationship between two entities X and Y in which an instance of X may be linked to many instances of Y, but an instance of Y is linked to only one instance of X. The figure below shows an example of a one-to-many relationship.
A many-to-many relationship refers to the relationship between two entities X and Y in which X may be linked to many instances of Y and vice versa. The figure below shows an example of a many-to-many relationship. Note that a many-to-many relationship is split into a pair of one-to-many relationships in a physical ERD. You will know what a physical ERD is in the next section.
An ER model is typically drawn at up to three levels of abstraction:
While all the three levels of an ER model contain entities with attributes and relationships, they differ in the purposes they are created for and the audiences they are meant to target.
A general understanding to the three data models is that business analyst uses a conceptual and logical model to model the business objects exist in the system, while database designer or database engineer elaborates the conceptual and logical ER model to produce the physical model that presents the physical database structure ready for database creation. The table below shows the difference between the three data models.
Conceptual model vs Logical model vs Data model:
ERD features | Conceptual | Logical | Physical |
---|---|---|---|
Entity (Name) | Yes | Yes | Yes |
Relationship | Yes | Yes | Yes |
Columns | Yes | Yes | |
Column's Types | Optional | Yes | |
Primary Key | Yes | ||
Foreign Key | Yes |
Conceptual ERD models the business objects that should exist in a system and the relationships between them. A conceptual model is developed to present an overall picture of the system by recognizing the business objects involved. It defines what entities exist, NOT which tables. For example, 'many to many' tables may exist in a logical or physical data model but they are just shown as a relationship with no cardinality under the conceptual data model.
NOTE: Conceptual ERD supports the use of generalization in modeling the 'a kind of' relationship between two entities, for instance, Triangle, is a kind of Shape. The usage is like generalization in UML. Notice that only conceptual ERD supports generalization.
Logical ERD is a detailed version of a Conceptual ERD. A logical ER model is developed to enrich a conceptual model by defining explicitly the columns in each entity and introducing operational and transactional entities. Although a logical data model is still independent of the actual database system in which the database will be created, you can still take that into consideration if it affects the design.
Physical ERD represents the actual design blueprint of a relational database. A physical data model elaborates on the logical data model by assigning each column with type, length, nullable, etc. Since a physical ERD represents how data should be structured and related in a specific DBMS it is important to consider the convention and restriction of the actual database system in which the database will be created. Make sure the column types are supported by the DBMS and reserved words are not used in naming entities and columns.
If you find it difficult to get started with drawing an ER diagram, don't worry. In this section, we will give you some ERD tips. Try to follow the steps below to understand how to draw an ER diagram effectively.
In system analysis and design, Data Flow Diagram (DFD) can be drawn to visualize the flow of information within system processes. In a Data Flow Diagram, there is a symbol called Data Store, which represents a database table that provides the information needed by the system.
Since a physical ER Diagram provides a blueprint of an actual database, the entities in such an ERD are aligned with datastores in a DFD. You can draw ERD as a complement to DFD by representing the structure of information that flows within a system, or, on the contrary, to draw DFD in complementing an ERD by showing how the data will be utilized by the system in runtime.
In business process mapping, BPMN Business Process Diagram (BPD) can be drawn to visualize business workflows. In a Business Process Diagram, there is a symbol called Data Object, which represents the data input into / output from process activities.
Since a conceptual and logical data model provides a high-level view of business objects within a system, the entities in such ERDs are aligned with data objects in BPD. You can draw ERD as a complement to BPD by representing the structure of data objects needed by a business workflow, or, on the contrary, to draw BPD in complementing an ERD by showing how the data will be utilized throughout a business process.
It takes time and effort to develop a data model with ERD. A helpful database design tool should be able to reduce your time and effort spent. Visual Paradigm provides you with not only an ERD tool but also a set of visual modeling features that helps you draw faster and easier. It supports most of the popular relational database management systems in the market today both in terms of database design, database generation, and ERD reversal.
The ERD designer is available in Visual Paradigm Modeler, which costs only US $6 per month. We would recommend you download and have a try. 30 days of FREE evaluation is offered. No credit card required.
You've learned what an ER diagram is and how to create ERD for database design or data modeling. It's time to try it yourself. Get Visual Paradigm Community Edition, a free ERD tool, and develop your own ER model with the free ER Diagram tool. It's easy-to-use and intuitive.
Free Download