An entity relationship diagram (ERD) shows entities as boxes and relationships as lines. To read a line, read it twice, once from each end.
This lesson follows choosing primary and foreign keys. Next you can move on to writing simple SQL queries.
How do I read a relationship line?
Each end of the line shows how many rows on that side relate to one row on the other side. The marks are usually 1 for one and M for many.
| Line | Reads as |
|---|---|
| 1 to 1 | one X relates to one Y |
| 1 to M | one X relates to many Y, each Y to one X |
| M to M | many X relate to many Y |
Worked example: a small school diagram
The diagram is drawn here in text, with the relationship name in the middle.
TEACHER (1) === teaches === (M) CLASS
CLASS (M) === attends === (M) STUDENTLine 1. From Teacher: one teacher teaches many classes. From Class: each class is taught by one teacher. This is one-to-many, so the Class table holds TeacherID as a foreign key.
Line 2. From Class: a class has many students. From Student: a student can attend many classes. This is many-to-many, so it needs an intermediate table that holds both ClassID and StudentID.
The mistake that costs marks
Students read the line in one direction only, and so they write “one-to-many” for both lines.
| Line | One direction only | Both directions |
|---|---|---|
| Class and Student | a class has many students (1 to M) | also a student attends many classes, so M to M |
The fix is the two-sentence check. If both sentences say “many”, the relationship is many-to-many.
Check yourself
Read this diagram and state each relationship type with its two sentences.
CUSTOMER (1) === places === (M) ORDER
ORDER (M) === contains === (M) PRODUCTAnswer
Customer and Order: one customer places many orders, and each order is placed by one customer. This is one-to-many, so Order holds CustomerID as a foreign key.
Order and Product: one order contains many products, and one product appears in many orders. This is many-to-many, so it needs an intermediate table holding OrderID and ProductID.
What to study next
Practise turning diagrams into tables. Continue with resolving a many-to-many relationship with an intermediate table, then try the data and databases practice set.
If you want a teacher to go through diagrams with you, see online one-to-one Computer Science tuition.