Senior 7 min · March 06, 2026

ER Model in DBMS Explained — Entities, Attributes, and Relationships

Q: What is the difference between an ER model and an ER diagram?

The ER Model is the theoretical framework — the set of rules and concepts (entities, attributes, relationships, cardinality) for describing data. An ER diagram is the actual visual drawing you create by applying those rules to a specific system. The model is the language; the diagram is the sentence you write in that language.

Q: Why do we need an ER diagram if we're just going to write SQL tables anyway?

Because fixing a bad database design after data is already in it is enormously expensive and painful. An ER diagram lets you spot structural problems — missing relationships, wrongly placed attributes, forgotten entities — at the planning stage when changes cost nothing. It's the same reason architects draw blueprints instead of just starting to pour concrete.

Q: What is the difference between cardinality and participation constraints in an ER model?

Cardinality answers 'how many?' — specifically, how many instances of entity A can relate to how many instances of entity B (1:1, 1:N, M:N). Participation answers 'is it mandatory?' — total participation means every single instance MUST be in the relationship (e.g., every order item must belong to an order), while partial participation means it's optional (e.g., a professor may or may not advise a student project). Both appear on the same relationship line but describe different things.

Q: Can an ER diagram have more than one relationship between two entities?

Yes, absolutely. For example, a Person can be both an Employee and a Manager. The same two entities can have multiple diamonds between them to represent different associations. In a company database, Employee and Department may have a 'works_in' relationship (1:N) and a 'manages' relationship (1:1). Each relationship is distinct and may have its own attributes.

Q: What happens if I skip the ER diagram and go straight to table design?

You'll likely miss entities, misplace attributes, misunderstand cardinality, and create a schema full of redundancy and anomalies. The first time you try to add a feature like 'track which student borrowed which book copy on which date', you'll need a painful migration. ER diagrams are cheap insurance against expensive rework.

ER Model in DBMS explained from scratch — entities, attributes, relationships, cardinality, and how to draw your first ER diagram with real-world examples.

Naren · Founder

Plain-English first. Then code. Then the interview question.

About

● Production Incident 🔎 Debug Guide

⚡Quick Answer

ER Model is a conceptual framework for designing database schemas visually before writing SQL
Entities (rectangles) represent real-world objects; Attributes (ovals) describe them
Relationships (diamonds) connect entities with cardinality (1:1, 1:N, M:N)
Performance insight: Getting cardinality wrong leads to costly schema redesigns that take weeks
Production insight: Inconsistent relationship participation can cause orphaned data or NULL constraint violations
Biggest mistake: Treating M:N relationships as directly storable instead of creating a junction table

Plain-English First

Imagine you're building a school's record system on paper before touching any computer. You'd first sketch out: 'What are the THINGS I need to track?' (students, teachers, classes), 'What do I know ABOUT each thing?' (a student has a name, age, ID), and 'How do these things CONNECT?' (a student ENROLLS IN a class). That sketch — those boxes, bubbles, and lines — is literally an ER diagram. The ER Model is just the rulebook for drawing that sketch in a way every database designer on the planet understands.

Every app you've used — Instagram, your bank's website, your school's portal — stores data somewhere. Before a single line of code is written, a database designer sits down and asks: 'What exactly am I storing, and how does it all fit together?' Get that wrong and you're rebuilding the entire database six months later. The ER Model exists precisely to prevent that painful, expensive mistake.

The Entity-Relationship Model, introduced by Dr. Peter Chen in 1976, gives you a visual language to plan a database at a high level — before you worry about tables, SQL, or any specific database software. It's like an architect drawing blueprints before construction begins. You wouldn't build a house without blueprints, and you shouldn't build a database without an ER diagram.

By the end of this article you'll be able to identify entities, attributes, and relationships in any real-world scenario, understand the rules around cardinality and participation, read and draw a basic ER diagram from scratch, and confidently explain all of this in a technical interview. Let's build it up piece by piece.

Entities and Attributes — The 'Things' and Their 'Details'

An entity is any real-world object or concept that you need to store data about and that can be uniquely identified. In a university system, STUDENT, COURSE, and PROFESSOR are all entities. Think of an entity like a category — not a specific student named 'Alice', but the concept of 'a student' in general.

An entity instance is one specific member of that category. Alice (Student ID: 101) is an instance. Bob (Student ID: 102) is another instance.

Attributes are the properties or details that describe an entity. A STUDENT entity might have attributes: StudentID, FirstName, LastName, DateOfBirth, and Email. In an ER diagram, entities are drawn as rectangles and attributes as ovals connected to their entity by a line.

Attributes come in important flavours you need to know. A simple attribute stores a single value (like Age). A composite attribute is made of smaller parts — FullName can be split into FirstName and LastName. A multi-valued attribute can hold more than one value — a student can have multiple PhoneNumbers. A derived attribute is calculated from another — Age can be derived from DateOfBirth, so it's drawn with a dashed oval.

The key attribute uniquely identifies each entity instance. StudentID is a key attribute — no two students share it. In diagrams, key attributes are underlined.

university_entities.sqlSQL

-- ============================================================
-- Translating ER Entities and Attributes into real SQL tables
-- Using io_thecodeforge schema for production naming
-- ============================================================

-- ENTITY: Student
-- KEY ATTRIBUTE:    student_id  (underlined in ER diagram -- must be unique)
-- SIMPLE ATTRS:     first_name, last_name, email
-- DERIVED ATTR:     age (we store date_of_birth and calculate age when needed)
-- COMPOSITE ATTR:   full_name is split into first_name + last_name
-- MULTI-VALUED:     phone_numbers gets its OWN separate table (shown below)

CREATE TABLE io_thecodeforge.Student (
    student_id    INT          PRIMARY KEY,
    first_name    VARCHAR(50)  NOT NULL,
    last_name     VARCHAR(50)  NOT NULL,
    date_of_birth DATE         NOT NULL,
    email         VARCHAR(100) UNIQUE NOT NULL
);

-- Multi-valued attribute 'PhoneNumbers' becomes its own table.
CREATE TABLE io_thecodeforge.StudentPhone (
    phone_id      INT         PRIMARY KEY,
    student_id    INT         NOT NULL,
    phone_number  VARCHAR(15) NOT NULL,
    FOREIGN KEY (student_id) REFERENCES io_thecodeforge.Student(student_id)
);

-- ENTITY: Course
CREATE TABLE io_thecodeforge.Course (
    course_id     INT          PRIMARY KEY,
    course_name   VARCHAR(100) NOT NULL,
    credits       INT          NOT NULL
);

-- Insert sample data
INSERT INTO io_thecodeforge.Student VALUES (101, 'Alice', 'Morgan', '2001-04-15', 'alice@uni.edu');
INSERT INTO io_thecodeforge.Student VALUES (102, 'Bob',   'Chen',   '2000-09-22', 'bob@uni.edu');

INSERT INTO io_thecodeforge.StudentPhone VALUES (1, 101, '555-0101');
INSERT INTO io_thecodeforge.StudentPhone VALUES (2, 101, '555-0202');
INSERT INTO io_thecodeforge.StudentPhone VALUES (3, 102, '555-0303');

INSERT INTO io_thecodeforge.Course VALUES (1, 'Database Systems', 3);
INSERT INTO io_thecodeforge.Course VALUES (2, 'Data Structures',  4);

-- Query to see Alice's full record including derived age
SELECT
    student_id,
    first_name || ' ' || last_name  AS full_name,
    EXTRACT(YEAR FROM AGE(date_of_birth)) AS age,
    email
FROM io_thecodeforge.Student
WHERE student_id = 101;

Output

student_id | full_name | age | email

------------+--------------+-----+----------------

101 | Alice Morgan | 23 | alice@uni.edu

Pro Tip: Key Attributes vs. Primary Keys

In ER diagrams you underline the key attribute. In the actual database table this becomes the PRIMARY KEY constraint. They represent the same concept at different stages — ER diagram is the plan, the SQL table is the built result. Interviewers love asking this distinction.

Production Insight

Overloading an entity with too many attributes creates wide tables that hit row size limits and slow scans.

Storing derived attributes instead of base data causes stale values and recalculation bugs.

Rule: Store raw data; derive computed values at query time.

Key Takeaway

Entities map to tables; attributes map to columns.

Composite attributes split into multiple columns; multi-valued attributes become separate tables.

Never store derived attributes — compute them on the fly.

Relationships and Cardinality — How Entities Connect to Each Other

Entities don't exist in isolation — they interact. A STUDENT enrolls in a COURSE. A PROFESSOR teaches a COURSE. These interactions are relationships, and in ER diagrams they're drawn as diamonds connecting two or more entity rectangles.

Relationships also have attributes. The ENROLLS_IN relationship between STUDENT and COURSE might have an EnrollmentDate attribute — that date doesn't belong to the student alone or the course alone, it belongs to the act of enrolling.

Cardinality is the most critical concept in ER modeling. It defines HOW MANY instances of one entity can relate to how many instances of another. There are three types.

One-to-One (1:1): One entity instance links to exactly one instance on the other side. A PERSON has exactly one PASSPORT. One passport belongs to exactly one person.

One-to-Many (1:N): One instance on side A relates to many instances on side B, but each instance on side B relates to only one on side A. One PROFESSOR teaches many COURSES, but each course is taught by one professor.

Many-to-Many (M:N): Many instances on side A relate to many on side B. A STUDENT can enroll in many COURSES, and a COURSE can have many STUDENTS enrolled.

Participation constraints tell you whether participation is mandatory. Total participation (double line) means every instance MUST participate. Partial participation (single line) means it's optional.

university_relationships.sqlSQL

-- ============================================================
-- Translating ER Relationships and Cardinality into SQL
-- ============================================================

-- Relationship: PROFESSOR teaches COURSE  (One-to-Many)
-- We implement 1:N by placing the foreign key on the 'many' side (Course table)

CREATE TABLE io_thecodeforge.Professor (
    professor_id  INT          PRIMARY KEY,
    full_name     VARCHAR(100) NOT NULL,
    department    VARCHAR(50)  NOT NULL
);

ALTER TABLE io_thecodeforge.Course
    ADD COLUMN professor_id INT,
    ADD CONSTRAINT fk_course_professor
        FOREIGN KEY (professor_id) REFERENCES io_thecodeforge.Professor(professor_id);

-- Relationship: STUDENT enrolls in COURSE  (Many-to-Many)
-- M:N relationships ALWAYS become their own 'junction' table
CREATE TABLE io_thecodeforge.Enrollment (
    enrollment_id   INT  PRIMARY KEY,
    student_id      INT  NOT NULL,
    course_id       INT  NOT NULL,
    enrollment_date DATE NOT NULL,
    grade           CHAR(2),
    FOREIGN KEY (student_id) REFERENCES io_thecodeforge.Student(student_id),
    FOREIGN KEY (course_id)  REFERENCES io_thecodeforge.Course(course_id),
    UNIQUE (student_id, course_id)
);

-- Relationship: PERSON has PASSPORT  (One-to-One)
-- UNIQUE foreign key enforces 1:1
CREATE TABLE io_thecodeforge.Person (
    person_id  INT         PRIMARY KEY,
    full_name  VARCHAR(100) NOT NULL
);

CREATE TABLE io_thecodeforge.Passport (
    passport_id     INT         PRIMARY KEY,
    passport_number VARCHAR(20) UNIQUE NOT NULL,
    person_id       INT         UNIQUE NOT NULL,
    expiry_date     DATE        NOT NULL,
    FOREIGN KEY (person_id) REFERENCES io_thecodeforge.Person(person_id)
);

-- Sample data
INSERT INTO io_thecodeforge.Professor VALUES (1, 'Dr. Sarah Hill',  'Computer Science');
INSERT INTO io_thecodeforge.Professor VALUES (2, 'Dr. James Okafor','Mathematics');

UPDATE io_thecodeforge.Course SET professor_id = 1 WHERE course_id = 1;
UPDATE io_thecodeforge.Course SET professor_id = 1 WHERE course_id = 2;

INSERT INTO io_thecodeforge.Enrollment VALUES (1, 101, 1, '2024-01-10', 'A');
INSERT INTO io_thecodeforge.Enrollment VALUES (2, 101, 2, '2024-01-10', 'B+');
INSERT INTO io_thecodeforge.Enrollment VALUES (3, 102, 1, '2024-01-11', NULL);

-- Query: Alice's courses with grades
SELECT
    s.first_name || ' ' || s.last_name  AS student_name,
    c.course_name,
    e.enrollment_date,
    COALESCE(e.grade, 'Not graded')    AS grade
FROM io_thecodeforge.Enrollment e
JOIN io_thecodeforge.Student  s ON e.student_id = s.student_id
JOIN io_thecodeforge.Course   c ON e.course_id  = c.course_id
WHERE e.student_id = 101
ORDER BY c.course_name;

Output

student_name | course_name | enrollment_date | grade

---------------+-------------------+-----------------+------------

Alice Morgan | Data Structures | 2024-01-10 | B+

Alice Morgan | Database Systems | 2024-01-10 | A

Watch Out: M:N Relationships Don't Map Directly to Tables

A Many-to-Many relationship in an ER diagram can never be directly stored as-is in a relational database. You MUST create a junction table (also called a bridge table or associative entity) that holds foreign keys from both sides. Forgetting this is one of the most common beginner database design mistakes.

Production Insight

One-to-Many relationships cause the most foreign key mistakes — putting the FK on the wrong side breaks referential integrity.

Many-to-Many without a junction table creates duplicate data and query nightmares.

Rule: For 1:N, FK goes on the 'N' side; for M:N, always create a junction table.

Key Takeaway

Cardinality drives FK placement.

1:1 uses UNIQUE FK on either side.

1:N uses FK on the many side.

M:N requires a junction table with composite PK.

Weak Entities, Specialisation, and the Full ER Diagram Picture

Most real systems have an important special case: a weak entity. A weak entity is one that cannot be uniquely identified by its own attributes alone — it depends on another entity (its owner entity) to be identified.

Think of ORDER_ITEM. An order item has an item number (item 1, item 2, item 3...), but that number only makes sense WITHIN a specific ORDER. Item 3 of Order #500 is completely different from item 3 of Order #501. ORDER_ITEM depends on ORDER for its identity. In ER diagrams, weak entities use double rectangles, and the relationship connecting them to their owner uses a double diamond.

The partial key of a weak entity (the attribute that identifies it within its owner) is shown with a dashed underline rather than a solid one.

Specialisation and Generalisation add inheritance-like thinking. Suppose you have a PERSON entity. An EMPLOYEE and a STUDENT are both persons but have extra specific attributes. You can generalise EMPLOYEE and STUDENT into PERSON (they share common attributes like Name, Age), and specialise PERSON into the subtypes. This is identical to how class inheritance works in object-oriented programming. In ER diagrams this is drawn as a triangle labelled 'ISA'.

With all these building blocks — entities, attributes, relationships, cardinality, weak entities, and specialisation — you can model virtually any real-world system before writing a single line of SQL.

weak_entity_and_specialisation.sqlSQL

-- ============================================================
-- Weak Entity: ORDER_ITEM depends on ORDER for its identity
-- Specialisation: EMPLOYEE and STUDENT are subtypes of PERSON
-- ============================================================

-- Strong entity: ORDER
CREATE TABLE io_thecodeforge.CustomerOrder (
    order_id      INT          PRIMARY KEY,
    order_date    DATE         NOT NULL,
    customer_name VARCHAR(100) NOT NULL
);

-- Weak entity: ORDER_ITEM
-- item_number is the PARTIAL KEY
CREATE TABLE io_thecodeforge.OrderItem (
    order_id     INT          NOT NULL,
    item_number  INT          NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    quantity     INT          NOT NULL,
    unit_price   DECIMAL(8,2) NOT NULL,
    PRIMARY KEY (order_id, item_number),
    FOREIGN KEY (order_id) REFERENCES io_thecodeforge.CustomerOrder(order_id)
        ON DELETE CASCADE
);

-- Specialisation / Generalisation
CREATE TABLE io_thecodeforge.Person (
    person_id   INT          PRIMARY KEY,
    full_name   VARCHAR(100) NOT NULL,
    date_of_birth DATE       NOT NULL,
    email       VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE io_thecodeforge.Employee (
    person_id       INT          PRIMARY KEY,
    employee_code   VARCHAR(20)  UNIQUE NOT NULL,
    hire_date       DATE         NOT NULL,
    salary          DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (person_id) REFERENCES io_thecodeforge.Person(person_id)
);

CREATE TABLE io_thecodeforge.Student_Person (
    person_id       INT         PRIMARY KEY,
    student_id      VARCHAR(20) UNIQUE NOT NULL,
    enrollment_year INT         NOT NULL,
    major           VARCHAR(50),
    FOREIGN KEY (person_id) REFERENCES io_thecodeforge.Person(person_id)
);

-- Sample data
INSERT INTO io_thecodeforge.CustomerOrder VALUES (500, '2024-03-01', 'Greenleaf Supplies');
INSERT INTO io_thecodeforge.CustomerOrder VALUES (501, '2024-03-02', 'BlueStar Corp');

INSERT INTO io_thecodeforge.OrderItem VALUES (500, 1, 'Printer Paper A4',  10, 5.99);
INSERT INTO io_thecodeforge.OrderItem VALUES (500, 2, 'Black Ink Cartridge', 3, 18.50);
INSERT INTO io_thecodeforge.OrderItem VALUES (500, 3, 'Stapler',             1, 12.00);
INSERT INTO io_thecodeforge.OrderItem VALUES (501, 1, 'Whiteboard Markers',  5, 3.25);

INSERT INTO io_thecodeforge.Person   VALUES (1, 'Carol White', '1985-07-20', 'carol@company.com');
INSERT INTO io_thecodeforge.Employee VALUES (1, 'EMP-00421', '2015-03-01', 72000.00);

-- Retrieve a full order with line items
SELECT
    o.order_id,
    o.customer_name,
    i.item_number,
    i.product_name,
    i.quantity,
    i.unit_price,
    (i.quantity * i.unit_price) AS line_total
FROM io_thecodeforge.CustomerOrder o
JOIN io_thecodeforge.OrderItem i ON o.order_id = i.order_id
WHERE o.order_id = 500
ORDER BY i.item_number;

Output

----------+--------------------+-------------+------------------------+----------+------------+-----------

500 | Greenleaf Supplies | 1 | Printer Paper A4 | 10 | 5.99 | 59.90

500 | Greenleaf Supplies | 2 | Black Ink Cartridge | 3 | 18.50 | 55.50

500 | Greenleaf Supplies | 3 | Stapler | 1 | 12.00 | 12.00

Interview Gold: Why Weak Entities Matter

Interviewers frequently ask 'Can you give an example of a weak entity?' The ORDER_ITEM example is perfect. Follow it up by explaining that a weak entity's primary key in SQL is always a composite key combining the owner's primary key with the partial key — never just the partial key alone.

Production Insight

Weak entities without ON DELETE CASCADE accumulate orphaned data that bloats storage and skews analytics.

Specialisation subtypes missing the FK to the parent entity break the ISA relationship.

Rule: Weak entity's PK must be composite; use ON DELETE CASCADE.

Key Takeaway

Weak entity identity = owner PK + partial key.

Specialisation maps to separate tables with shared PK.

Always cascade deletes from owner to weak entity.

Drawing an ER Diagram from a Real Scenario: The Library System

Let's apply everything to a concrete system: a library. You need to track books, members, and loans.

First, identify the entities. BOOK, MEMBER, and LOAN are clear. But don't stop there — a library may have multiple copies of the same book, so you need BOOK_COPY. Each loan can include multiple copies? No, each loan is for one copy at a time, but a member can borrow many copies over time. So the relationship is MEMBER borrows BOOK_COPY. But a BOOK has many copies, and a copy belongs to one book.

Let's define attributes. BOOK: ISBN (key), Title, Author, Publisher. BOOK_COPY: CopyID (partial key within BOOK), Condition, ShelfLocation. MEMBER: MemberID (key), Name, Email, Phone. LOAN: LoanID (key), LoanDate, DueDate, ReturnDate.

Now relationships: BOOK has many BOOK_COPY (1:N). MEMBER borrows many BOOK_COPY (1:N) — each loan is for one copy, but a member can have multiple active loans. LOAN links MEMBER and BOOK_COPY? Actually a loan is a relationship between MEMBER and BOOK_COPY with attributes (LoanDate, etc). That's a M:N relationship between MEMBER and BOOK_COPY resolved into the LOAN entity (associative entity). So LOAN is both an entity (has its own key) and a relationship.

Cardinalities: A member can have many loans (1:N from MEMBER to LOAN). A book copy can be in many loans over time (1:N from BOOK_COPY to LOAN). But a loan involves exactly one member and one copy. So it's a M:N between MEMBER and BOOK_COPY with extra attributes, often modeled as an associative entity.

Weak entity? Not in this simple model, but if we wanted to track loan items separately, we could. Alternatively, BOOK_COPY is a weak entity dependent on BOOK because CopyID only unique per book.

Now draw the diagram: rectangles for BOOK, BOOK_COPY, MEMBER, LOAN. Ovals for attributes. Diamonds for relationships: BOOK-HAS-BOOK_COPY, MEMBER-borrows-BOOK_COPY via LOAN? Actually LOAN is the relationship entity. Connect MEMBER to LOAN and BOOK_COPY to LOAN.

Specialisation: Maybe MEMBER can be STUDENT_MEMBER or FACULTY_MEMBER with different borrowing limits. Could add an ISA triangle.

This exercise shows how a seemingly simple system reveals entities, weak entities, and relationship entities once you think deeply.

library_system.sqlSQL

-- ============================================================
-- Library System: Full ER to SQL translation
-- ============================================================

-- Strong entity: Book
CREATE TABLE io_thecodeforge.Book (
    isbn        VARCHAR(20)  PRIMARY KEY,
    title       VARCHAR(200) NOT NULL,
    author      VARCHAR(100) NOT NULL,
    publisher   VARCHAR(100)
);

-- Weak entity: BookCopy (depends on Book)
CREATE TABLE io_thecodeforge.BookCopy (
    isbn           VARCHAR(20)  NOT NULL,
    copy_id        INT          NOT NULL,
    condition      VARCHAR(50),
    shelf_location VARCHAR(20),
    PRIMARY KEY (isbn, copy_id),
    FOREIGN KEY (isbn) REFERENCES io_thecodeforge.Book(isbn) ON DELETE CASCADE
);

-- Strong entity: Member
CREATE TABLE io_thecodeforge.Member (
    member_id  INT          PRIMARY KEY,
    full_name  VARCHAR(100) NOT NULL,
    email      VARCHAR(100) NOT NULL,
    phone      VARCHAR(15)
);

-- Associative entity: Loan (relationship with attributes)
CREATE TABLE io_thecodeforge.Loan (
    loan_id      INT  PRIMARY KEY,
    member_id    INT  NOT NULL,
    isbn         VARCHAR(20) NOT NULL,
    copy_id      INT  NOT NULL,
    loan_date    DATE NOT NULL,
    due_date     DATE NOT NULL,
    return_date  DATE,
    FOREIGN KEY (member_id) REFERENCES io_thecodeforge.Member(member_id),
    FOREIGN KEY (isbn, copy_id) REFERENCES io_thecodeforge.BookCopy(isbn, copy_id),
    UNIQUE (member_id, isbn, copy_id, loan_date)
);

-- Sample data
INSERT INTO io_thecodeforge.Book VALUES ('978-0134685991', 'Effective Java', 'Joshua Bloch', 'Addison-Wesley');
INSERT INTO io_thecodeforge.Book VALUES ('978-1491950357', 'Designing Data-Intensive Applications', 'Martin Kleppmann', 'OReilly');

INSERT INTO io_thecodeforge.BookCopy VALUES ('978-0134685991', 1, 'Good', 'A1-01');
INSERT INTO io_thecodeforge.BookCopy VALUES ('978-0134685991', 2, 'Fair', 'A1-02');
INSERT INTO io_thecodeforge.BookCopy VALUES ('978-1491950357', 1, 'Excellent', 'B2-10');

INSERT INTO io_thecodeforge.Member VALUES (201, 'Alice Morgan', 'alice@lib.edu', '555-1111');
INSERT INTO io_thecodeforge.Member VALUES (202, 'Bob Chen', 'bob@lib.edu', '555-2222');

INSERT INTO io_thecodeforge.Loan VALUES (1, 201, '978-0134685991', 1, '2024-04-01', '2024-04-15', NULL);
INSERT INTO io_thecodeforge.Loan VALUES (2, 202, '978-1491950357', 1, '2024-04-02', '2024-04-16', NULL);

-- Find all current loans for Alice
SELECT m.full_name, b.title, l.loan_date, l.due_date
FROM io_thecodeforge.Loan l
JOIN io_thecodeforge.Member m ON l.member_id = m.member_id
JOIN io_thecodeforge.BookCopy bc ON l.isbn = bc.isbn AND l.copy_id = bc.copy_id
JOIN io_thecodeforge.Book b ON bc.isbn = b.isbn
WHERE m.full_name = 'Alice Morgan' AND l.return_date IS NULL;

Output

full_name | title | loan_date | due_date

--------------+--------------------------------------+------------+------------

Alice Morgan | Effective Java | 2024-04-01 | 2024-04-15

Pro Tip: Spotting Entities vs Attributes

If something has its own attributes or can be related to multiple other entities, it's an entity, not an attribute. For example, 'ShelfLocation' on BookCopy is an attribute, but if you need to track shelf capacity or floor, then 'Shelf' becomes its own entity.

Production Insight

Treating book copies as attributes of Book (e.g., a list of copy IDs in a JSON column) makes it impossible to track individual copy history or condition.

Rule: When an object exists independently and has its own lifecycle, make it an entity, not an attribute.

Key Takeaway

Real-world systems reveal entities, weak entities, and relationship entities.

Always look for 'thing vs. property' — if it has properties or relationships, it's an entity.

Practice with familiar domains to internalise the pattern.

From ER Diagram to Relational Schema: The Transformation Rules

Once your ER diagram is complete, you need to transform it into a relational schema. This is a systematic process with well-defined rules.

Rule 1: Each regular entity becomes a table. The key attribute becomes the PRIMARY KEY.

Rule 2: Each composite attribute is split into its component attributes as separate columns.

Rule 3: Each multi-valued attribute becomes a separate table with a foreign key to the original entity's table and the attribute value column. The primary key is typically a composite of the foreign key and a unique value.

Rule 4: Each weak entity becomes a table with a composite primary key: the primary key of the owner entity plus the weak entity's partial key. Include a foreign key to the owner table with ON DELETE CASCADE.

Rule 5: For a 1:1 relationship, add a foreign key in one of the tables (preferably the one with total participation) and make it UNIQUE.

Rule 6: For a 1:N relationship, add a foreign key in the table representing the 'N' side referencing the '1' side.

Rule 7: For an M:N relationship, create a junction table with foreign keys to both entity tables. The primary key is a composite of those two foreign keys (or include a surrogate key if relationship attributes exist).

Rule 8: For specialisation (ISA), create a table for the superclass with its primary key. For each subclass, create a table with the same primary key (shared primary key) and additional attributes. Add foreign key from subclass to superclass.

These rules guarantee a lossless and standardised translation from conceptual model to physical schema.

transformation_rules.sqlSQL

-- ============================================================
-- Summary: ER to Relational Transformation Rules
-- Each rule corresponds to a specific ER construct
-- ============================================================

-- Rule 1: Entity -> Table, Key attribute -> PK
CREATE TABLE io_thecodeforge.EntityTable (
    entity_id INT PRIMARY KEY,   -- Key attribute
    attr1 VARCHAR(100),
    attr2 VARCHAR(100)
);

-- Rule 2: Composite attribute -> multiple columns
-- Already done above: Person (full_name split into first_name, last_name)

-- Rule 3: Multi-valued attribute -> separate table with FK
CREATE TABLE io_thecodeforge.MultiValued (
    entity_id INT NOT NULL,
    value_id  INT NOT NULL,       -- Partial key or surrogate
    value     VARCHAR(100) NOT NULL,
    PRIMARY KEY (entity_id, value_id),
    FOREIGN KEY (entity_id) REFERENCES io_thecodeforge.EntityTable(entity_id)
);

-- Rule 4: Weak entity -> composite PK
-- Already done: OrderItem (order_id, item_number)

-- Rule 5: 1:1 relationship -> UNIQUE FK (shown in Person/Passport)

-- Rule 6: 1:N relationship -> FK on N side (shown in Professor/Course)

-- Rule 7: M:N relationship -> junction table (shown in Enrollment)

-- Rule 8: Specialisation -> shared PK (shown in Person/Employee)

-- Example: Applying all rules to a university schema
-- This single script creates a full schema from an ER diagram
CREATE TABLE io_thecodeforge.Department (
    dept_id   INT PRIMARY KEY,
    name      VARCHAR(100) NOT NULL
);

CREATE TABLE io_thecodeforge.Professor (
    prof_id   INT PRIMARY KEY,
    name      VARCHAR(100) NOT NULL,
    dept_id   INT NOT NULL,
    FOREIGN KEY (dept_id) REFERENCES io_thecodeforge.Department(dept_id) -- 1:N: many professors in one department
);

CREATE TABLE io_thecodeforge.Course (
    course_id INT PRIMARY KEY,
    title     VARCHAR(200) NOT NULL,
    credits   INT NOT NULL,
    prof_id   INT NOT NULL,
    FOREIGN KEY (prof_id) REFERENCES io_thecodeforge.Professor(prof_id) -- 1:N: one professor teaches many courses
);

CREATE TABLE io_thecodeforge.Student (
    student_id INT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    email      VARCHAR(100) UNIQUE NOT NULL
);

-- M:N: many students enroll in many courses -> junction table
CREATE TABLE io_thecodeforge.Enrollment (
    enrollment_id INT PRIMARY KEY,
    student_id    INT NOT NULL,
    course_id     INT NOT NULL,
    grade         CHAR(2),
    semester      VARCHAR(10) NOT NULL,
    FOREIGN KEY (student_id) REFERENCES io_thecodeforge.Student(student_id),
    FOREIGN KEY (course_id)  REFERENCES io_thecodeforge.Course(course_id),
    UNIQUE (student_id, course_id, semester)  -- a student can enroll in same course only once per semester
);

Output

-- Success: No errors, schema created.

Mental Model: The Assembly Line

Entity → Table: The raw material becomes a container.
Attribute → Column: Each property gets a slot in the container.
Relationship → Foreign Key or Junction Table: Connections between containers are modeled through references.
Weak Entity → Composite Key: Dependencies are captured by combining identifiers.
Specialisation → Shared PK: Inheritance is modeled by using the same primary key in multiple tables.

Production Insight

Skipping the transformation step leads to a schema that doesn't match the intended relationships.

Without explicit rules, developers might place foreign keys arbitrarily, breaking referential integrity.

Rule: Always apply the rules mechanically — don't guess where FK should go.

Key Takeaway

ER diagram is the blueprint; transformation rules are the assembly instructions.

Follow rules 1-8 in order for a lossless translation.

A correct transformation eliminates most design errors before you write a single INSERT.

● Production incidentPOST-MORTEMseverity: high

The Missing Junction Table That Cost $50,000 in Lost Revenue

Symptom

The revenue dashboard showed inconsistent totals. Some orders reported zero revenue, others were double-counting products. The inventory system showed products out of stock even though only one unit was sold.

Assumption

The team assumed that an order could contain multiple products but thought they could store product IDs directly in a single column using a JSON array. They believed it would simplify the schema.

Root cause

The ER diagram they drew initially showed a Many-to-Many relationship between ORDER and PRODUCT, but during implementation they skipped the junction table. They used a simple array column for product IDs, which broke normalization, made queries impossible to optimize, and allowed data corruption (duplicate entries, missing products).

Fix

They reverted the schema: created a proper ORDER_ITEM table with composite primary key (order_id, product_id), added quantity and price at the time of order, and migrated the existing six months of data using a script that parsed the old JSON arrays. They also added foreign key constraints and a unique constraint on (order_id, product_id).

Key lesson

Never shortcut a Many-to-Many relationship — a junction table is mandatory.
An ER diagram is cheap insurance; skipping it leads to costly rework.
If data looks like it belongs in a comma-separated list, stop and create a separate table.

Production debug guideDiagnose common schema issues rooted in ER model mistakes5 entries

Symptom · 01

Duplicate rows in what should be a unique relationship

→

Fix

Check if you forgot a UNIQUE constraint on the composite foreign keys. In ER terms, your relationship's cardinality is not enforced.

Symptom · 02

Slow queries that join many tables with no index hits

→

Fix

Verify that all foreign keys have indexes. In ER terms, relationships become FK columns — those columns need indexing for joins.

Symptom · 03

Unable to delete a parent record due to foreign key constraint

→

Fix

Check if you used ON DELETE CASCADE. In ER terms, if the relationship has total participation from the child (weak entity), you must cascade delete.

Symptom · 04

Data redundancy: same customer name stored in multiple orders

→

Fix

Promote 'Customer' from an attribute of Order to its own entity. In ER terms, any concept that appears repeatedly should be an entity, not an attribute.

Symptom · 05

Many NULL columns in a table that should be in separate subtypes

→

Fix

Check if you merged specialisation into a single table. In ER terms, each subclass should be its own table with shared PK.

★ ER Design Quick FixesImmediate checks when your database schema doesn't match the intended ER model

Rows in a 'weak' table without a parent−

Immediate action

Check for missing ON DELETE CASCADE or orphaned rows.

Commands

SELECT * FROM io_thecodeforge.OrderItem WHERE order_id NOT IN (SELECT order_id FROM io_thecodeforge.CustomerOrder);

SELECT COUNT(*) FROM io_thecodeforge.OrderItem WHERE order_id IS NULL;

Fix now

Add ON DELETE CASCADE to the foreign key and delete orphans with a DELETE JOIN.

Duplicate entries in a junction table+

Table with way too many columns (wide table)+

Missing foreign key constraint allows orphan rows+

ER Diagram Symbols vs SQL Equivalents

Concept	ER Diagram Symbol	SQL Equivalent	Real Example
Entity	Rectangle	Table (CREATE TABLE)	STUDENT, COURSE, PROFESSOR
Key Attribute	Oval with underline	PRIMARY KEY column	student_id, course_id
Simple Attribute	Oval	Regular column	first_name, email
Composite Attribute	Oval with sub-ovals	Multiple columns	full_name → first_name + last_name
Multi-valued Attribute	Double oval	Separate table with FK	phone_numbers → StudentPhone table
Derived Attribute	Dashed oval	Calculated column / formula	age derived from date_of_birth
Relationship (1:1)	Diamond + 1 and 1 labels	UNIQUE foreign key	PERSON has one PASSPORT
Relationship (1:N)	Diamond + 1 and N labels	Foreign key on 'N' side	PROFESSOR teaches many COURSEs
Relationship (M:N)	Diamond + M and N labels	Junction/bridge table	STUDENT enrolls in COURSE
Weak Entity	Double rectangle	Composite primary key	ORDER_ITEM depends on ORDER
Total Participation	Double line to relationship	NOT NULL foreign key	Every order MUST have a customer
Partial Participation	Single line to relationship	Nullable foreign key	A professor MAY have a department
Specialisation (ISA)	Triangle with dividing lines	Shared primary key across tables	Person / Employee / Student

Key takeaways

An ER diagram is your database blueprint

draw it before writing a single line of SQL, or risk rebuilding everything later when data relationships don't match reality.

Cardinality (1:1, 1:N, M:N) dictates the table structure

specifically, M:N relationships always need a separate junction table; there is no shortcut.

A weak entity's full identity = owner's primary key + its own partial key; in SQL this always becomes a composite PRIMARY KEY, never a standalone one.

Multi-valued attributes and M:N relationships both become their own tables in SQL

any time one cell would need to hold a list of values, a new table is the answer.

Derived attributes should never be stored

always compute them from base attributes at query time. Storing age instead of birth date is a classic trap.

Specialisation (ISA hierarchy) maps to separate tables with a shared primary key

never merge all subclasses into one wide table with nullable columns.

Common mistakes to avoid

4 patterns

Treating a Many-to-Many relationship as directly storable

Symptom

You try to store multiple product IDs in a single order row using a JSON array or comma-separated list. Queries become impossible, data integrity breaks, and reporting is unreliable.

Fix

Always create a junction table (like OrderItem) that holds foreign keys from both sides and allows relationship attributes. Each row represents one pairing.

Confusing an entity with an attribute

Symptom

You model 'Department' as just a text column on Employee. Then you can't store department phone, location, or manager without duplicating data.

Fix

If a concept has its own properties or connects to multiple entities, promote it to a full entity with its own table. Apply the 'has properties?' test.

Forgetting that a weak entity's identifier is always composite

Symptom

You give ORDER_ITEM its own auto-incremented ID and never link the item number to its parent order. Item #3 now exists in a vacuum.

Fix

The primary key of a weak entity's table must be a composite of (owner_pk, partial_key). Use ON DELETE CASCADE to automatically clean up when the owner is deleted.

Using a single table for all subtypes in a specialisation hierarchy

Symptom

You have a Person table with many nullable columns for employee-specific fields (salary, hire_date) and student-specific fields (major, enrollment_year). Table becomes wide, constraints are lost, and subsets are hard to query.

Fix

Create a superclass table with shared columns and separate subclass tables with the same primary key. Use shared primary key and FK to enforce the ISA relationship.

INTERVIEW PREP · PRACTICE MODE

Interview Questions on This Topic

Q01SENIOR

What is the difference between a strong entity and a weak entity? Can yo...

Q02SENIOR

How do you convert a Many-to-Many relationship from an ER diagram into a...

Q03JUNIOR

If 'Age' is a derived attribute in an ER diagram, should you store it as...

Q04SENIOR

Explain the difference between total and partial participation with a re...

Q05SENIOR

How would you represent a ternary relationship (involving three entities...

Q01 of 05SENIOR

What is the difference between a strong entity and a weak entity? Can you give a real-world example of each?

ANSWER

A strong entity can be uniquely identified by its own attributes — it exists independently. Example: ORDER (order_id is sufficient). A weak entity depends on another entity for identification — its full key includes the owner's primary key. Example: ORDER_ITEM (item_number + order_id). The weak entity is drawn with a double rectangle and the identifying relationship with a double diamond. In SQL, weak entities have a composite primary key and a foreign key with ON DELETE CASCADE.

FAQ · 5 QUESTIONS

Frequently Asked Questions

What is the difference between an ER model and an ER diagram?

Why do we need an ER diagram if we're just going to write SQL tables anyway?

What is the difference between cardinality and participation constraints in an ER model?

Can an ER diagram have more than one relationship between two entities?

What happens if I skip the ER diagram and go straight to table design?

🔥

That's DBMS. Mark it forged?

7 min read · try the examples if you haven't