Entity-Relationship Modeling

What Is an Entity-Relationship Model?

An entity-relationship (ER) model is a blueprint of the data a business needs to store and how different pieces of that data connect to one another. Before a single table is created in PostgreSQL, Oracle, or SQL Server, a good ER model prevents costly redesigns later. The diagram you draw — the ERD — communicates that blueprint to developers, stakeholders, and auditors in a common visual language.

ER modeling was introduced by Peter Chen in 1976 and remains the standard starting point for relational database design. Most professional DBA certification exams, including the Oracle DBA CA and the DAMA CDMP Associate, expect you to read and produce ERDs fluently.

Core Building Blocks

Entities are the things the business cares about — people, objects, events, or concepts that need their own stored data. In a library system, Book, Member, and Loan are entities. Each entity becomes a table in the finished database.

Attributes are the facts stored about an entity. The Book entity might have attributes such as ISBN, title, and publication year. Every entity must have a primary key — one attribute (or a minimal set of attributes) whose value uniquely identifies each row. On an ERD, the primary key attribute is underlined or marked with a key symbol.

Relationships describe how two entities are associated. A relationship is always named with a verb phrase that reads naturally in both directions: a Member borrows a Loan; a Loan is borrowed by a Member.

Crow's-Foot Notation

Crow's-foot notation uses line-end symbols to express two things about every side of a relationship: participation (must the entity appear at least once?) and cardinality (what is the maximum number of times it can appear?).

| Symbol at line end | Meaning |

|---|---|

| Single vertical bar ( \| ) | Exactly one (minimum and maximum = 1) |

| Double vertical bar ( \|\| ) | Exactly one on the mandatory side |

| Circle ( ○ ) | Zero (participation is optional) |

| Crow's foot ( ᗕ ) | Many (maximum is unbounded) |

The two symbols closest to an entity together state that entity's role. Read the pair closest to each entity to determine the rule:

• One-to-many (1:M) — One Member may have zero-or-many Loans; each Loan belongs to exactly one Member.
• One-to-one (1:1) — Each Employee is assigned exactly one Parking Spot; each Parking Spot is assigned to at most one Employee.
• Many-to-many (M:N) — Many Students enroll in many Courses. In a relational database, a many-to-many is resolved by adding an associative (junction) entity (e.g., Enrollment) that holds the primary keys of both sides as a composite or surrogate key.

Naming Conventions

Professional ERDs follow consistent conventions that translate cleanly into database objects:

• Entity names: singular noun, PascalCase (e.g., CustomerOrder).
• Attribute names: lowercase with underscores (e.g., order_date).
• Relationship labels: active verb phrase on the line.
• Primary keys: marked explicitly; foreign keys implied by relationships.

Consistent naming is tested on the DAMA CDMP Associate exam under the Data Modeling and Design knowledge area.

Step-by-Step Modeling Process

1. Read the narrative carefully — underline every noun (candidate entity or attribute) and every verb (candidate relationship).

2. Distinguish entities from attributes — if a noun needs more than one fact stored about it, promote it to an entity.

3. List primary keys — confirm each entity has a unique identifier.

4. Draw entities as rectangles in draw.io and list attributes inside.

5. Draw relationship lines and apply crow's-foot symbols at each end.

6. Resolve M:N relationships by inserting associative entities.

7. Validate — read each relationship aloud in both directions and confirm it matches the business narrative.

Working in draw.io Safely

Draw.io is a browser-based tool and poses no physical hazards. However, follow good digital safety habits:

• Save frequently — use File → Save As and keep a local copy; browser sessions can time out.
• Version control — when revising after peer critique, save the original file under a new name (e.g., retail_erd_v1.xml, retail_erd_v2.xml) so you can recover earlier work.
• File naming — avoid special characters in filenames to ensure portability across operating systems.
• Data privacy — if a real business narrative contains customer or employee names, treat that document according to your program's data-handling policy and do not share it outside the classroom network.

These habits mirror real DBA work, where version control of schema design artifacts is a professional expectation and mishandling sensitive metadata can create compliance issues under frameworks such as HIPAA or PCI-DSS.

Common Mistakes to Catch in Peer Review

• Missing primary key on one or more entities.
• Backward cardinality — the crow's foot placed on the wrong end of the line.
• Unresolved M:N — two entities connected directly with many-to-many without a junction entity.
• Attribute promoted incorrectly — storing a phone number as a single-valued attribute when the business allows multiple phone numbers per customer (should become a separate Phone entity).
• Orphan entities — an entity with no relationship to any other entity in the diagram.

Using the provided checklist during the swap session trains the systematic review skill that senior DBAs use during schema design walkthroughs.