ER Diagrams Explained: Entities, Attributes, Relationships and Cardinality

An ER diagram (entity-relationship diagram) is a picture of the data a system stores: the things it keeps track of, the facts about each thing, and how the things are related. It is the most common way to draw a conceptual or logical data model, and it maps almost directly onto database tables. Database courses call the underlying idea the ER model, first described by Peter Chen in 1976.

This lesson builds one ER diagram for a small library, explains every symbol on the way, and then turns it into SQL tables.

The building blocks

Part	What it is	Library example	Chen notation	Crow’s foot notation
Entity	A thing you store data about	Member, Book, Author	Rectangle	Box with the entity name
Attribute	A fact about an entity	Member’s name, book’s title	Oval joined to the entity	Line inside the box
Key attribute	Attribute that identifies one instance	member_id, book_id	Underlined oval	Marked PK
Relationship	How entities are connected	A member borrows a copy	Diamond	Line between boxes
Cardinality	How many on each side	One member, many loans	1, N, M on the lines	Symbols at the line ends

An entity is a type (Member); one actual member, Ravi, is an instance, which becomes a row.

Types of attributes

• Simple: cannot be split usefully, such as joined_on.
• Composite: made of parts, such as a name made of first and last name. In tables you usually store the parts.
• Multivalued: can hold several values for one instance, such as a member’s phone numbers. Drawn as a double oval in Chen notation. In tables it becomes its own table.
• Derived: can be calculated from other data, such as a member’s age from a birth date or the number of books on loan. Drawn as a dashed oval, and usually not stored.
• Key: identifies each instance uniquely. A key made of several attributes is a composite key.

Cardinality and participation

Cardinality says how many instances can be linked:

• One-to-one (1:1): a member has at most one library card, and each card belongs to one member.
• One-to-many (1:N): a member can have many loans; each loan belongs to one member.
• Many-to-many (M:N): a book can have several authors, and an author can write several books.

Participation says whether the link is required:

• Total (mandatory): every loan must have a member. Chen notation draws a double line; crow’s foot draws a bar (|).
• Partial (optional): a member may have no loans. Crow’s foot draws a circle (o).

In crow’s foot notation the two ideas sit together at each end of a line. Read from the other entity towards the symbol:

Symbol at the line end	Meaning
|| (two bars)	exactly one
o| (circle and bar)	zero or one
|< (bar and crow’s foot)	one or many
o< (circle and crow’s foot)	zero or many

So Member ||——o< Loan reads: “each loan has exactly one member; each member has zero or many loans”.

Weak entities

A weak entity cannot be identified on its own, only together with its owner. A library owns several copies of the same book: “copy 2” means nothing until you say which book. So BookCopy is identified by the book’s key plus its copy number. Chen notation draws a weak entity with a double rectangle and its identifying relationship with a double diamond.

The library ER diagram

Here are the business rules, then the diagram they produce in crow’s foot form:

• A member has a name, a join date and any number of phone numbers.
• A book has a title and a publication year, and is written by one or more authors. An author writes one or more books.
• The library holds one or more copies of each book.
• A member borrows a copy; each loan records the borrow date and, later, the return date.

 Member ||——o< Loan >o——|| BookCopy >|——|| Book >|——|< Author
   |                                                (through Book_Author)
   ||——o< MemberPhone

Reading it: a member has zero or many loans; each loan is for exactly one copy; a copy can be borrowed many times; each copy belongs to exactly one book, and each book has at least one copy; books and authors are many-to-many.

From ER diagram to tables

The conversion follows a few fixed rules:

1. Each entity becomes a table; its key becomes the primary key.
2. A 1:N relationship becomes a foreign key on the “many” side (loan gets member_id).
3. An M:N relationship becomes its own table (a junction or bridge table) holding both keys.
4. A multivalued attribute becomes its own table (member phones).
5. A weak entity’s primary key includes its owner’s key (book_id plus copy number).
6. A 1:1 relationship becomes a unique foreign key on one side, or the two tables are merged.

-- Library ER model as tables. Each entity becomes a table; each 1:N relationship a foreign key;
-- the M:N relationship between books and authors becomes its own table.
CREATE TABLE member (
    member_id   int PRIMARY KEY,
    first_name  text NOT NULL,                   -- composite attribute "name" stored as its parts
    last_name   text NOT NULL,
    joined_on   date NOT NULL
);
CREATE TABLE member_phone (                      -- multivalued attribute: a member can have several phones
    member_id   int  NOT NULL REFERENCES member,
    phone       text NOT NULL,
    PRIMARY KEY (member_id, phone)
);
CREATE TABLE author (author_id int PRIMARY KEY, name text NOT NULL);
CREATE TABLE book   (book_id int PRIMARY KEY, title text NOT NULL, published_year int);
CREATE TABLE book_author (                       -- M:N: a book has many authors, an author many books
    book_id     int NOT NULL REFERENCES book,
    author_id   int NOT NULL REFERENCES author,
    PRIMARY KEY (book_id, author_id)
);
CREATE TABLE book_copy (                         -- weak entity: copy 2 only means something for one book
    book_id     int NOT NULL REFERENCES book,
    copy_no     int NOT NULL,
    PRIMARY KEY (book_id, copy_no)
);
CREATE TABLE loan (                              -- a member borrows a copy
    loan_id     int  PRIMARY KEY,
    member_id   int  NOT NULL REFERENCES member,
    book_id     int  NOT NULL,
    copy_no     int  NOT NULL,
    borrowed_on date NOT NULL,
    returned_on date,                            -- empty while the book is out
    FOREIGN KEY (book_id, copy_no) REFERENCES book_copy
);

NOT NULL on loan.member_id is the total participation from the diagram: a loan cannot exist without a member. Some rules in the diagram, such as “every book has at least one copy”, cannot be expressed with a simple constraint; the application or a data test checks them.

Query it

Joins follow the lines of the diagram:

INSERT INTO member VALUES (1, 'Ravi', 'Menon', '2025-01-10'), (2, 'Sara', 'Lund', '2026-03-02');
INSERT INTO member_phone VALUES (1, '+91 98000 11111'), (1, '+91 98000 22222');
INSERT INTO author VALUES (1, 'A. Mehta'), (2, 'J. Okafor');
INSERT INTO book VALUES (10, 'Rivers of Data', 2021), (11, 'Quiet Tables', 2024);
INSERT INTO book_author VALUES (10, 1), (10, 2), (11, 2);
INSERT INTO book_copy VALUES (10, 1), (10, 2), (11, 1);
INSERT INTO loan VALUES (1, 1, 10, 2, '2026-10-20', NULL),
                        (2, 2, 11, 1, '2026-10-01', '2026-10-15');

-- Which books are out right now, who has them, and who wrote them?
SELECT b.title, l.copy_no, m.first_name AS borrower,
       string_agg(a.name, ', ' ORDER BY a.name) AS authors
FROM loan l
JOIN member m       ON m.member_id = l.member_id
JOIN book b         ON b.book_id = l.book_id
JOIN book_author ba ON ba.book_id = b.book_id
JOIN author a       ON a.author_id = ba.author_id
WHERE l.returned_on IS NULL
GROUP BY b.title, l.copy_no, m.first_name;

     title      | copy_no | borrower |       authors
----------------+---------+----------+---------------------
 Rivers of Data |       2 | Ravi     | A. Mehta, J. Okafor

ER diagrams in data engineering

• Reading source systems. When you integrate a new application database, an ER diagram (often generated from its foreign keys by a database tool) is the fastest way to learn which tables join to which, and which joins multiply rows.
• Designing warehouses. The ER diagram of the source tells you the grain of each table, which helps you design the fact and dimension tables of a star schema. M:N relationships become bridge tables there too.
• Documentation. A current diagram of the main tables saves every new team member hours.

Common mistakes

1. Putting an M:N relationship directly between two tables. It needs a junction table, or you end up with lists in one column.
2. Storing derived attributes such as age, which go stale.
3. Mixing up cardinality and participation. “Many” says how many can be linked; “mandatory” says whether at least one must be.
4. Modelling a weak entity with its own artificial key only, which allows two “copy 1” rows for the same book.
5. Using attributes for things that have their own facts. If you need an author’s country and birth year, Author is an entity, not a text column on Book.

What interviewers look for

Questions such as “draw an ER diagram for a library management system” or “convert this ER diagram to tables” check that you:

• Ask about the business rules before drawing.
• Identify entities, keys and relationships, with correct cardinality in both directions.
• Handle M:N with a junction table and multivalued attributes with a separate table.
• Explain the result in plain words.

Practise with the data engineering interview questions.

Key takeaway

An ER diagram shows entities, their attributes and the relationships between them, with cardinality and participation at each end. It converts to tables with a few rules: entities become tables, 1:N becomes a foreign key, M:N and multivalued attributes become their own tables, and weak entities borrow their owner’s key. Next: data warehouse architecture.