Data Modeling: Entity-Relationship (E-R)

This short description of E-R models demonstrates them from the perspective of application types: OLTP (Online Transaction Processing) and DSS (Decision Support Systems). It also relates E-R notations to other notations.

The entity-relationship (E-R) model (or relational model) is the classic, fully normalized relational schema used in many online transaction processing (OLTP) systems. The relationships between entities (tables) signify relationships between data, not necessarily business relationships.

ER modeling technique was first developed by Peter Chen in 1976 to ensure:

  • data sharing culture (for instance between users and developers)
  • data quality

The relational model of data prescribes how the data should be represented in terms of:

  • structure,
  • integrity,
  • and manipulation.

The rules of the relational model are widely accepted in the information technology industry, though actual implementations may vary.

 

E-R diagram in a DSS system

With the growth of 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.

 

ERD Graphical Notations and elements

ERD is the most common technique for drawing data models.

The building blocks of the ERD are:

  • Data Modeling - Entity (Object, Table): describe people, places, or things
  • Data Modeling - Attribute: capture information about the entity
  • Data Modeling - Relationships: associate data across entities

 

ERD: Alternative Notations in other Modeling languages


Source: Gerard Nico, https://gerardnico.com/data/modeling/entity_relationship
Creative Commons License This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 License.

Last modified: Thursday, December 17, 2020, 3:48 PM