MySQL + SQL · Lesson 23

ER Diagram Examples (Step by Step)

In this lesson: Step-by-step ER diagram examples for school, library and online shopping systems — entities, keys and relationships.

Learning from examples

The fastest way to understand ER diagrams is to see real ones. We will model three familiar systems — a school, a library and an online shop — as entities, attributes and relationships, then map each to tables.

💡 For each example: find the entities, add attributes, connect with relationships, then turn into tables.

Example 1: School

Entities: Student, Teacher, Course.
Attributes: Student(roll [key], name), Course(code [key], title).
Relationships: Student enrolls in Course (many-to-many); Teacher teaches Course (one-to-many).

students(roll PK, name)
courses(code PK, title, teacher_id FK)
enrollments(roll FK, code FK)   -- link table for M:N

Example 2: Library

Entities: Member, Book.
Attributes: Member(id [key], name), Book(isbn [key], title).
Relationship: Member borrows Book (many-to-many, with a borrow date).

members(id PK, name)
books(isbn PK, title)
borrowings(id FK, isbn FK, borrow_date)   -- link table

The many-to-many "borrows" relationship becomes a borrowings table holding both keys and extra info like the date.

Example 3: Online shop

Entities: Customer, Product, Order.
Attributes: Customer(id [key], name), Product(id [key], price), Order(id [key], date).
Relationships: Customer places Order (one-to-many); Order contains Product (many-to-many).

customers(id PK, name)
products(id PK, price)
orders(id PK, date, customer_id FK)
order_items(order_id FK, product_id FK, qty)   -- link table

Turning them into tables

The pattern is the same every time:

Entity → table; key attribute → primary key.
One-to-many → foreign key on the "many" side (like teacher_id in courses).
Many-to-many → a link table with both foreign keys (like enrollments, order_items).

Once you spot the cardinality, the tables almost write themselves.

Steps to draw your own

List the entities (the main nouns of the system).
Add attributes to each and underline the key.
Find the relationships (the verbs connecting entities).
Decide each relationship's cardinality.
Convert to tables using the rules above.

Common mistakes

Missing a link table for M:N. Many-to-many always needs one.
Putting the foreign key on the wrong side. It goes on the "many" side.
Forgetting extra info on a link. Data like a date or quantity belongs in the link table.
Skipping cardinality. Decide it before making tables.

Summary

Model systems as entities, attributes and relationships.
School, library and shop all follow the same pattern.
Entity → table; key → primary key.
One-to-many → foreign key; many-to-many → link table.
Decide cardinality, then the tables follow naturally.

Next, revise the pieces in the ER model, or build a full one in the school database case study.

Frequently Asked Questions

How do I make an ER diagram for a school?

Identify entities (Student, Teacher, Course), give each attributes (like roll, name), and draw relationships (a Student enrolls in a Course, a Teacher teaches a Course). Mark cardinality, then map entities to tables and relationships to keys.

What entities are in a library ER diagram?

A library typically has Member, Book and Librarian entities. The main relationship is Member "borrows" Book, which is many-to-many, so it becomes a separate borrowing table linking members and books.

How does an ER diagram become tables?

Each entity becomes a table, each attribute a column, the key attribute the primary key. One-to-many relationships add a foreign key on the many side, and many-to-many relationships become a link table with two foreign keys.

What is a many-to-many relationship example?

A Student can take many Courses and a Course can have many Students — that is many-to-many. In tables it becomes a link table (like enrollments) holding student_id and course_id together.

What steps should I follow to draw an ER diagram?

List the entities, add their attributes and mark the key, identify relationships between entities, decide the cardinality of each, then convert to tables. Working in that order keeps the design clean and complete.