What Are the Basics of Entity-Relationship (ER) Modeling?
Learn the basics of entity-relationship modeling — entities, attributes, cardinality — and how ER diagrams map to SQL tables.
Expected Interview Answer
Entity-relationship modeling represents a database's structure using entities (things like Customer or Order), attributes (properties of those entities), and relationships (how entities connect, such as one-to-many or many-to-many), before any of it is translated into actual tables.
An entity becomes a table, its attributes become columns, and a relationship is expressed through foreign keys or, for many-to-many relationships, a separate junction table. The cardinality of a relationship (one-to-one, one-to-many, many-to-many) determines exactly how the foreign keys are placed: a one-to-many relationship puts the foreign key on the many side, while a many-to-many relationship requires an intermediate table holding foreign keys to both entities. ER diagrams let designers reason about the domain visually before committing to a physical schema, catching modeling mistakes early.
- Clarifies business rules before writing any SQL
- Makes relationship cardinality explicit and reviewable
- Provides a shared visual language between engineers and stakeholders
- Directly maps to physical tables, columns, and foreign keys
AI Mentor Explanation
Think of Team and Player as two entities in an ER diagram, connected by a one-to-many relationship because one team has many players but each player belongs to one team at a time. Team has attributes like name and home ground, Player has attributes like name and role. When this diagram becomes a real schema, Player gets a foreign key column pointing back to Team, exactly mirroring the one-to-many arrow drawn on the diagram.
Step-by-Step Explanation
Step 1
Identify entities
List the core nouns in the domain, such as Customer, Order, or Product.
Step 2
Define attributes
Attach properties to each entity, marking the primary key attribute for each.
Step 3
Define relationships and cardinality
Determine whether each relationship is one-to-one, one-to-many, or many-to-many.
Step 4
Translate to tables
Convert entities to tables, attributes to columns, and relationships to foreign keys or junction tables.
What Interviewer Expects
- Clear definitions of entity, attribute, and relationship
- Ability to reason about cardinality (1:1, 1:N, N:N)
- Knowledge that many-to-many relationships need a junction table
- Ability to sketch or describe a simple ER diagram for a given scenario
Common Mistakes
- Placing the foreign key on the wrong side of a one-to-many relationship
- Forgetting that many-to-many relationships require a junction table
- Confusing an attribute with an entity (e.g. treating Address as its own entity unnecessarily)
- Skipping cardinality analysis before jumping to table design
Best Answer (HR Friendly)
“ER modeling is how you sketch out a database before building it: you identify the main things (entities) like Customer and Order, their properties (attributes), and how they relate to each other, like one customer having many orders. Once that's clear, translating it into real tables and foreign keys is straightforward.”
Code Example
-- One-to-many: Customer (1) -> Order (many)
CREATE TABLE Customer (
customer_id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT REFERENCES Customer(customer_id), -- FK on the "many" side
order_date DATE
);
-- Many-to-many: Product <-> Order, via a junction table
CREATE TABLE OrderItems (
order_id INT REFERENCES Orders(order_id),
product_id INT REFERENCES Product(product_id),
quantity INT,
PRIMARY KEY (order_id, product_id)
);Follow-up Questions
- How do you model a one-to-one relationship in a physical schema?
- What is a weak entity and how is it represented in an ER diagram?
- How does ER modeling relate to normalization?
- What is the difference between a composite attribute and a derived attribute?
MCQ Practice
1. In ER modeling, a many-to-many relationship is physically implemented using:
Many-to-many relationships require a separate junction table holding foreign keys to both related entities.
2. In a one-to-many relationship between Customer and Order, the foreign key belongs on:
The foreign key goes on the "many" side (Order), referencing the primary key of the "one" side (Customer).
3. An "attribute" in ER modeling refers to:
Attributes describe properties of an entity, such as a Customer entity having name and email attributes.
Flash Cards
What is an entity in ER modeling? — A distinct object or concept in the domain, such as Customer or Order, that becomes a table.
What is cardinality? — The nature of a relationship between entities: one-to-one, one-to-many, or many-to-many.
How is a many-to-many relationship implemented? — Via a junction table holding foreign keys to both related entities.
How does a one-to-many relationship become a foreign key? — The foreign key is placed on the table representing the "many" side, referencing the "one" side.