Database Design

Lessons

Senior 4 · ICT

Database Design

5 lessons

Lesson 3 of 5

Entity-Relationship Diagrams

25 min

A plan before the building

Nobody builds a house without a plan, and nobody should build a database without one. An entity-relationship diagram (ERD) is that plan: a drawing of the entities, their attributes and the relationships between them, which the people who will use the database can check before a single table exists. A mistake caught on paper costs an eraser; the same mistake caught after a year of data entry costs weeks.

The ER model was published by Peter Chen in 1976 and is still the most common way to teach and plan relational databases. You will meet two notations: Chen notation, good for the first, conceptual sketch, and crow's foot notation, which most design tools use.

Definition

Entity-relationship diagram (ERD): a diagram of the entities in a system, their attributes, and the relationships between them, with their cardinality and participation.

Conceptual model: an ERD that shows what must be stored, in terms the users understand, without deciding tables or data types. Logical model: the same design turned into tables, keys and fields (Lesson 4).

Weak entity: an entity that cannot be identified by its own attributes alone, only together with the entity it depends on: a book Copy numbered 1, 2, 3 within each book. Chen notation draws it with a double rectangle.

Chen notation

Symbol	Means
Rectangle	Entity (double rectangle: weak entity)
Diamond	Relationship, named with a verb
Oval joined to an entity or relationship	Attribute; underlined for a key, double oval for multi-valued, dashed oval for derived
1, N, M beside the lines	Cardinality
Double line	Total (mandatory) participation: every instance must take part

Notice that DateBorrowed belongs to neither Student nor Book: it is a fact about one particular borrowing. Attributes of an M:N relationship are a sign that the relationship will become a table of its own (Lesson 4).

Crow's foot notation

Crow's foot notation, which goes back to an article by Gordon Everest in 1976, draws each entity as a box with its attributes listed inside and the primary key marked PK. The relationship is a plain line, and the symbols at each end give the minimum and maximum: a bar means one, a circle means zero, and the three-pronged "crow's foot" means many. Read each end at the entity it touches: in the diagram below, the end touching Student says how many students one class has.

Drawing an ERD, step by step

Read the requirements and underline the nouns. Nouns that have several facts recorded about them are candidate entities (Student, Book); nouns that are single facts are attributes (title, date).
Find the verbs that link entities. These are the relationships: a student borrows a book.
Decide cardinality and participation for each relationship, asking the two questions in both directions.
Add the attributes and choose a key attribute for each entity.
Check events and M:N relationships. If something happens many times between the same two instances (a member delivers coffee every week), or the relationship has its own attributes, model it as an entity.
Check the diagram against the requirements with the users, reading every relationship aloud in both directions.

You can draw ERDs on paper or with a free tool such as draw.io, which has ready-made ER and crow's-foot shapes.

Worked example 1: a coffee cooperative

"The cooperative records each member's ID, name and village, and each washing station's ID and name. Members deliver coffee cherries to the station of their zone; each delivery records the date and the kilograms delivered. Each member delivers to one station, often several times a season." (A scenario written for this lesson.)

Nouns: member, washing station, delivery, date, kilograms, village, name. Member and WashingStation have several facts each: entities. Date and kilograms describe a delivery.
Verb: members deliver to stations.
Is a delivery an attribute or an entity? The same member delivers to the same station many times, and each delivery has its own date and weight. One link between a member and a station cannot hold many dates, so Delivery becomes an entity.
Cardinality: a member makes zero or many deliveries; each delivery is by exactly one member. A station receives zero or many deliveries; each delivery goes to exactly one station.

Worked example 2: read a diagram aloud

Take the crow's foot diagram of Class, Student and Book above and say each relationship in two sentences.

Class–Student: "Each student belongs to exactly one class" (two bars at the Class end). "Each class has zero or many students" (circle and crow's foot at the Student end).
Student–Book: "Each student borrows zero or many books." "Each book is borrowed by zero or many students." Many at both ends: M:N.

If a sentence sounds wrong to the librarian ("a class with zero students? not in our school!"), the diagram is changed now, while it is still cheap to change.

Worked example 3: find the four mistakes

A student's first ERD for a clinic shows: an entity Patient (Name, Phone); an entity Phone number joined to Patient; a relationship diamond labelled Appointment book between Patient and Doctor; and no line ends marked.

No key: Patient needs an identifier such as PatientID, because two patients can share a name.
"Phone number" is a property, not a thing with its own facts: it is an attribute of Patient (multi-valued if a patient may have several).
A relationship should be a verb: "Patient books Doctor". And since each appointment has a date and time, Appointment is better as an entity between Patient and Doctor, like Delivery above.
Cardinality is missing: without it, the diagram cannot be turned into tables.

Lucid Software: Entity Relationship Diagram (ERD) Tutorial, Part 1. Watch entities, attributes and relationships being drawn, and how the crow's foot line endings show one, many, zero or one.

Warning

Do not mix notations in one diagram: either Chen symbols or crow's feet.
Attributes are not entities. A single fact (a phone number, a date) is an attribute; a thing with several facts of its own is an entity.
Every line needs both ends marked. An unmarked end hides the most important design decision.

Tip

Nouns are entities, verbs are relationships, adjectives and facts are attributes. Then read every line aloud in both directions: "Each A has … B; each B has … A."

✦

Practice

Name the Chen symbol for (a) an entity, (b) a relationship, (c) a key attribute, (d) a derived attribute.
Draw the four crow's foot line endings and write what each means.
Draw a Chen ERD for: "A teacher teaches many subjects and a subject is taught by many teachers. Each teacher has a TeacherID and name; each subject has a SubjectCode and title."
Draw a crow's foot ERD for: "A hospital has several wards. Each ward has many beds; each bed is in one ward. A bed may be empty or have one patient; a patient occupies at most one bed."
In a sales ERD, the end of the line touching Order shows a bar and a crow's foot, and the end touching Customer shows two bars. Write the two sentences this line means.
A library wants to record each borrowing's date and return date. Explain why a simple M:N line between Student and Book is not enough, and what to draw instead.
Evaluate this ERD decision: "District is an attribute of School, since every school is in one district."

Show answersHide answers

(a) rectangle (b) diamond (c) oval with the name underlined (d) dashed oval.
Two bars: exactly one. Bar and circle: zero or one. Bar and crow's foot: one or many. Circle and crow's foot: zero or many.
Rectangles Teacher and Subject joined through a diamond "teaches", with M beside Teacher's line and N beside Subject's; ovals TeacherID (underlined) and Name on Teacher, SubjectCode (underlined) and Title on Subject.
Ward to Bed: two bars at Ward, bar and crow's foot (or circle and crow's foot) at Bed. Bed to Patient: zero or one at both ends (a circle and bar at each end).
"Each order is placed by exactly one customer." "Each customer places one or many orders."
A borrowing happens many times between the same student and book and has its own dates, which no single line can hold. Draw a Loan entity between Student and Book: one student has zero or many loans, one book has zero or many loans, and each loan is of exactly one student and one book.
Acceptable if the only fact needed is the district's name. If the database also stores facts about districts (province, population, education officer), District should be an entity with a 1:N relationship to School, so those facts are stored once, not repeated for every school.

Quick check

Match each Chen symbol to what it shows.

Tap one on the left, then its partner on the right.

Key points

An ERD is the plan of a database: entities, attributes and relationships with cardinality and participation.
Chen notation: rectangles (entities), diamonds (relationships), ovals (attributes, key underlined), 1, N, M on the lines; double lines for mandatory participation.
Crow's foot notation: boxes listing attributes with PK; line ends bar (one), circle (zero) and crow's foot (many), read at the entity they touch.
Steps: nouns → entities, verbs → relationships, cardinality both ways, attributes and keys, turn repeated events into entities, check with the users.

Sources and credits

Entity–relationship model (history, Chen and crow's foot notations), Wikipedia contributors, Wikipedia, licensed under CC BY-SA 4.0. Adapted by Ganzaa.
Screenshot: Entity-relationship-diagram-luna-modeler, Vasek Frolik, Wikimedia Commons, CC BY 4.0.
Video: Entity Relationship Diagram (ERD) Tutorial, Part 1, Lucid Software, embedded from YouTube.
Diagrams, scenarios (the cooperative and clinic are invented for teaching), worked examples and exercises: written by Ganzaa.

This lesson adapts CC BY-SA material and is shared under CC BY-SA 4.0.

1A plan before the building

2Chen notation

3Crow's foot notation

4Drawing an ERD, step by step

A plan before the building

Chen notation

Crow's foot notation

Drawing an ERD, step by step