The Entity-Relationship Diagram (ERD), or entity relationship model, is a visual depiction of database tables (entities) and how they are linked through primary and foreign keys (relationships) to each other. This article describes ERDs in more detail, and highlights the free ERD wizard IRI provides for multiple databases connected in Eclipse within its Workbench IDE.

Table Attributes

Within each table in the diagram are the column names, their data types and lengths, and whether they are a primary key, foreign key, or both. The column’s key status determines not only the order the table is maintained in, but how that value is linked to, or dependent on, the value in another table. Additionally, the diagram indicates whether that field is an index or is nullable.

 A primary key is one or more columns that uniquely identify a row. Every table should have a primary key, and can only have one.

A foreign key is a one or more columns whose values must have matching values in the primary key of another (or the same) table. A foreign key thus references its primary key, and is the way data or referential integrity is maintained across linked tables.

The key attributes are used in the model to define how the tables map to each other and whether their column values are uniquely linked to other tables or not. These relationships are known as mapping cardinalities.

Mapping Cardinalities

ERDs typically display one-to-one, one-to-many, and many-to-many relationships between columns across tables.

A one-to-one relationship is where a value in a table is uniquely linked to a value in another and vice versa. For example, consider a student database where each student in table A is linked to only one student ID in table B.

A one-to-many relationship exists where a value in table A can be linked to one or many values in table B, but those in table B link back to the table A value. For example, schools in table A may be linked to many students in table B, but not vice versa.

A many-to-many relationship is where values in table A can be related to one or many values in table B and vice versa. For example, for a company in which all of its employees work on multiple projects, each instance of an employee (A) is associated with many instances of a project (B), and at the same time, each instance of a project (B) has multiple employees (A) associated with it.

Mapping Modality

ERDs can also display required versus optional relationships.  The difference is represented by the type of line between the tables: solid for required and dashed for an optional relationship.

IRI ERDs

In the IRI Workbench GUI, built on Eclipse™, any relational database connected in the Data Source Explorer via a JDBC driver can be modeled. The New ER Diagram Model Wizard is one of several free data profiling facilities available in the Workbench (including a database profiling wizard for statistical reporting and value searching) and is accessed from the toolbar’s Data Discovery Menu.

The wizard guides you through the creation of the diagram.

Select the tables required for diagramming and a new image available for download is produced that shows the table structures and their key relationships:

Some of the mapping cardinalities can be deduced by the key information in the entities. If a Foreign Key (FK) is not also a Primary Key (PK) or Unique value in the connected table, it can link to multiple instances. However,  if the field in the connected table is a PK, there can only be one value in that table associated with it.

The ERD in the Workbench is a useful way to model any database or data warehouse schema built on relational databases. This activity serves DBAs and data architects who are working on ETL, data masking, database migration, and test data generation projects using IRI data management or protection products in the Workbench.