Entities are categories of objects within a system. Entities operationalize within a system with the help of their attributes and relationships with other entities. In the larger context, entities could be basic interrelated units of significance within a domain of interest.
Entity Relationship Diagram – which depicts a combination of entities in an organization and their relationships expresses the data model. In the software engineering domain, for example, the entities and their relationships are expressed at three levels – conceptual, logical, and physical.
In this article, we will cover the following:
Levels of Entity-Relationship models
There are three levels of E-R models. Let us understand what they do.
Conceptual model
The conceptual model is the highest level of design. It describes the semantics and the scope of the data model and expresses the facts or propositions that can be expressed using this model. It only shows the entities (tables, for example) and their relationships. The typical audience for these models is the business stakeholders who are primarily concerned about the high-level business terms and rules.
Logical model
The logical model goes deeper than the conceptual model. It shares the semantic as represented by a data set. In this case, not only are the entities and their relationships depicted, but also the details of the tables and attributed of entities. For example, the primary and foreign keys are also shown for the tables along with the attributes. The primary audience for these models is the business analysts and the architects, who are interested in business rules and data structures.
Physical model
The physical model is the most detailed level of modeling. This schema shares the physical structure of how the data is stored. In this case, tables names, primary and foreign keys, column names and data types are also shown. These models are perused by the DBAs and developers and become the basis for the technical implementation.
These E-R model schemas can be used to map out the entire organization in terms of data and entities.
Components of Entity-Relationship Models
The ERDs comprise the following components:
Entities: are depicted by rectangles. This is the object or concept or basic building block of a model, about which you want to store information about.
Actions: are depicted by diamonds. They show how two or more entities share information with each other.
Attributes: are depicted by an oval. They show a unique, distinguishing characteristic of an entity.
Relationships: are depicted by solid lines that link the entities that have a relationship.
Cardinality and Ordinality: are depicted by branches or small lines on the solid lines that show relationships. While cardinality shows how many instances of an entity relate to one instance of another entity, ordinality shows whether the relationship is mandatory or optional.
Now let us look at the different types of relationships within the E-R Model.
Types of Relationships in an E-R Model
There are different types of relationships between entities. Let us look at these relationships style.
Unary or Recursive
When there are relationships between different entities of a single entity type. In this case, both, the primary key and foreign key are the same but they represent two instances of the same entity, when these instances are in different roles. An employee can at the same time be a supervisor and a supervisee.
Binary
In this relationship, two entities are involved. There are different types of binary relationships – One to One, One to Many and Many to Many.
One to One (1:1)
This is the most basic kind of relationship, where one instance of an entity is linked to only one instance of another entity. For example, an employee and a Social Security Number or an employee and his/her address (unless someone else from the same household works in the organization).
One to Many (1:M)
When one entity can be linked to many instances of the other entity. A good example could be a Company Code and the GL Accounts. One Company Code can have multiple GL Accounts or maybe one department or organization unit can have multiple personnel or employees.
Many to Many (M:M)
These kinds of relationships are not possible for implementation in a relational model. Usually, they are segregated into many 1:M relationships and then entered. These are in different tables, which are linked together with the help of a primary key. An example could be employees working over the years in different groups with different managers.
Ternary
When binary relationships cannot properly define the association between three entities then ternary or triadic semantic is used. In this case, there are three possibilities. (1) where one instance of one entity can be linked to only one instance of two other entities. (2) Many instances of one entity can be linked to one instance of two other entities. (3) Many instances of one entity can be linked to many instances of other entities.
How Entity-Relationship diagrams are used for analysis
ERDs are used by business analysts extensively in their work, specifically for Requirements Analysis. They help a BA understand, define and analyze involved business and data concepts along with their relationships. Missing cardinality can, for example, mean that the BA may need to go back to the stakeholders.
ERDs become the basis of articulating not just current requirements, but future requirements as well and can be easily understood globally by analysts and developers.
Tools to create Entity-relationship diagrams
There are several tools on the market.
Lucidchart
Lucidchart is MS-Visio on steroids with lots of integrations (Atlassian, Slack, Office365, G Suite) and it also has some powerful features like – allowing DBMS import/export to ERD as well as UML sequence from text markup.
ERDPlus
ERDPlus is an online web ERD tool, that can be used to create Entity Relationship Diagrams (ERDs) Relational Schemas (Relational Diagrams) Star Schemas (Dimensional Models). It allows you to convert ER Diagrams into Relational Schemas.
dbdiagram.io
dbdiagram.io is a database design tool that allows you to draw Entity-relationship diagrams by writing code in Domain-specific language. It is created for developers, DBA, data analysts.
Conclusion
The Entity-Relationship model is a very useful way to map out any organization or knowledge domain. One can identify the main entities and the relationships that exist between these entities. Based on the type of relationships and the attributes but detailed characteristics of the entities, one can map out any domain comprehensively.
Of course, the ER model becomes most useful in the software development industry. Specifically, database development. But that is not the only use for the entity-relationship models.
