Entity-relationship diagrams

Creating an entity-relationship (ER) model is to visually represent the structure of a business database, where the data is equivalent to entities (or objects) that are linked by defined relationships that express dependencies and requirements. By nature, it is an abstract visualization, the first step in the design process toward creating a logical and functional database.

The original concept was formalized by Peter Chen, the influential computer scientist who wrote The Entity-Relationship Model – Towards a Unified View of Data. This document provided structured notation for database models; it remains a widely used standard to this day, but other notations, such as Bachman, Barker, or Martin, have been devised to handle specific database requirements.

Entity-relationship diagrams (ERDs) follow the common three-scheme approach to software engineering, with three levels of abstraction used to define an ER model.

Conceptual Data Model: A conceptual data model is the most abstract view of a data model. Provides a complete description of a business area, including all major business entities and describes their relationships.

It omits more levels of detail, including data types and interface definitions, making it suitable as a means of presenting an initial business idea to a wide range of stakeholders.

Logical Data Model: This model establishes a higher level of detail, with data entities described as master, operational, or transaction, and the relationships between them more precisely defined. This is the testing phase of a data model, where the functionality can be observed independently of the physical specifications.

Physical Data Model – At this point, data modelers begin to take into account the physical limitations of the database area. Important factors to consider are database performance, physical storage space, and indexing strategy. The diagram notations will fully define all tables and columns, indexes, constraint definitions, and any links or partitioned tables.

Creating an ERD requires choosing a specific set of notations that are best suited to the specific database being designed. Chen’s original notation had a linguistic approach, where the boxes representing entities could be thought of as nouns, and the relationships between them were in verb form, shown in a diagram like a diamond.

Eventually, Chen’s style was adapted to crow’s-tooth notation, where relations were shown as single labeled lines; this quickly became the popular standard, due to better legibility in diagrams, with more efficient use of space on the page.

Most other notation systems are adaptations or use part of these original styles. Here is a short list of some of the other standards that have been developed:

• Bachman’s notation
• Barker’s notation (ERD for Oracle)
• EXPRESS and EXPRESS-G notation
• DEFX1
• UML class diagrams
• Merise
• Object Role Modeling
• (min – max) rating

ERDs are an invaluable tool for software engineers, particularly as computing power, and therefore data storage capacity, has increased dramatically in recent years. They allow you to manage, test, and communicate all aspects of database design prior to implementation. Because ERDs have such a wide scope of influence on database activities, it is imperative that the standardized notation can be used and interpreted by a wide variety of stakeholders.

To create an ERD, software engineers will primarily rely on dedicated drawing software, which will contain all the notation resources for their specific database design.