ER Diagram — Entity Relationship Diagram Basics
Fast recall
Quick revision
Core answer
Whether the candidate understands ER diagrams as a design tool and knows the core components used to model data structure
Not realizing that M:N relationships in the ER diagram cannot be directly implemented and require a junction table in the actual schema
Show the translation from ER diagram to actual SQL DDL, specifically how a many-to-many relationship becomes a junction table with two foreign keys
Interview-ready guide
Interview answer
An ER diagram is a visual blueprint for a database schema.
It's what you draw before writing SQL. The main components are entities, which are the things you want to store—like Customer or Order—and they become tables. Attributes are properties of those entities—like name or email—and they become columns.
Relationships show how entities connect, like Customer places Order, and you implement them with foreign keys. Cardinality defines how many instances relate: one-to-one, one-to-many, or many-to-many.
For example, one customer can place many orders, that's a one-to-many relationship, so you'd put a customer_id foreign key in the order table. The tricky part for freshers is many-to-many relationships. In the ER diagram you just draw a diamond labeled M:N, but in the actual database you can't implement that directly.
You need a junction table with foreign keys pointing to both sides. For instance, if students enroll in courses, you'd create an enrollments table with student_id and course_id. That's the translation from abstract diagram to concrete schema.
The ER diagram keeps your design clean and helps you catch mistakes before you start coding.
ER Diagram — Entity Relationship Diagram Basics
Direct Answer
An ER (Entity-Relationship) diagram models entity types, their attributes, the relationships between them, and constraints such as cardinality and participation. In a relational design, these usually guide tables, columns, keys, and foreign keys. An ER diagram can be created before DDL or used to document and review an existing schema.
Code Example
-- Entity: Customer (rectangle in ER diagram)
CREATE TABLE customer (
customer_id BIGSERIAL PRIMARY KEY, -- Primary key attribute
name VARCHAR(100) NOT NULL, -- Simple attribute
email VARCHAR(100) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
-- Entity: Order (another rectangle)
CREATE TABLE "order" (
order_id BIGSERIAL PRIMARY KEY,
order_date DATE NOT NULL,
total_amount DECIMAL(10,2),
customer_id BIGINT NOT NULL, -- Foreign key (1:N relationship)
CONSTRAINT fk_customer FOREIGN KEY (customer_id)
REFERENCES customer(customer_id)
);
-- Relationship: Customer "places" Order
-- Cardinality: 1:N (one customer, many orders)
-- Implementation: FK on the N side (order table)
Common Mistakes & Pitfalls
- Skipping ER diagrams entirely: Freshers jump straight to coding JPA entities without designing the schema, leading to denormalized tables, missing foreign keys, and data integrity issues discovered too late.
- Drawing M:N relationships without junction tables: Beginners draw a many-to-many diamond and think it translates directly to SQL, not realizing the ER diagram is abstract and requires a bridge table in the actual schema.
- Confusing attributes with entities: Marking phone_number as an entity instead of an attribute of Customer, creating unnecessary tables and complexity when a simple VARCHAR column would suffice.
Check your understanding
Answer these without looking back at the article.
- How do you represent a many-to-many relationship in SQL?
- What is a junction table?
- What is the difference between an entity and a relationship in ER diagrams?
Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.