Reading an ER Diagram

Now that you have seen entities, attributes, relationships, and cardinality separately, it is time to read a whole ER diagram as a story.

We will use a small shop model: customers place orders, orders contain order items, and order items point to products.

The full model

Start by looking at the diagram without worrying about SQL syntax. Ask: what are the boxes, what are the keys, and what do the lines say?

This one picture contains most of the design vocabulary we have built. Let's read it slowly.

Step 1: identify the entities

The entity boxes are customers, orders, products, and order_items.

Each box is a kind of thing the system tracks. Each will usually become a table.

Step 2: read the attributes

Inside each box are attributes. For example, customers has id, name, and email. The orders entity has id, customer_id, placed_on, and status.

The markers matter. PK says a column helps identify rows in that entity. FK says a column points to another entity. UK says values must be unique.

Step 3: read the relationships as sentences

Every relationship line can become an English sentence.

The words are just as important as the symbols. If the sentence sounds wrong to the business, the diagram needs work.

Quick check

QuestionSelect one

In the shop diagram, which entity acts as the line between orders and products?

customers.

order_items.

products.

email.

Step 4: connect the keys to the lines

The relationship lines are stored using foreign keys:

• orders.customer_id points to customers.id.
• order_items.order_id points to orders.id.
• order_items.product_id points to products.id.

The diagram tells you where joins will happen, but it also tells you where integrity rules should exist.

The joins follow the foreign keys from the diagram. Reading the diagram helps you predict the query path.

Step 5: notice composite keys

order_items uses (order_id, line_number) as its primary key. That means line 1 can appear in many different orders, but only once inside the same order.

Composite keys are common for line-item tables because the line number only has meaning within its parent order.

Reading checklist

When you meet a new ER diagram, use this order:

This keeps you from staring at symbols randomly. You are translating a picture into plain English and then into schema rules.

Check your understanding

Use this small diagram for the questions below:

QuestionSelect one

According to the diagram, how many books can one author write?

Exactly one book.

Zero or many books.

Exactly two books.

No books, because authors and books are separate entities.

QuestionSelect one

Which column in books stores the relationship to publishers?

books.id.

books.title.

books.publisher_id.

authors.name.

QuestionSelect one

What is the primary key of authors in the diagram?

name.

id.

book_id.

publisher_id.

QuestionSelect one

Which plain-English sentence best matches publishers ||--o{ books : publishes?

One book publishes zero or many publishers.

Every publisher must publish exactly one book.

One publisher can publish zero or many books, and each book points to one publisher.

Publishers and books cannot be joined.