← Course home N5 Computing Science  ·  James Gillespie's
DDD  ·  Database Design & Development

Entity-Relationship Diagrams

Lesson DDD2 of 10 Approx 50 min draw.io required (free)
Learning intentions
  • Understand what an Entity-Relationship Diagram (ERD) is and why it is used
  • Identify entities, attributes, and relationships in a database design
  • Create a simple ERD showing primary keys, foreign keys, and cardinality
  • Design an ERD using draw.io and embed it in your portfolio
Success criteria
  • I can define the terms: entity, attribute, relationship, and cardinality
  • I can identify entities and their attributes from a problem description
  • I can draw an ERD showing primary keys, foreign keys, and 1:N relationships
  • I can use crow's foot notation correctly (single tick for "one", crow's foot for "many")
  • I can create and embed an ERD in my portfolio using draw.io
Warm up — what do you already know?

Answer before the lesson begins. These check prior knowledge — it's fine if you're unsure.

1. In a database, what is a thing (like a student, a book, or a course) called?

2. An ERD is a diagram used to plan a database before building it. What do the letters ERD stand for?

3. In an ERD, what is a link or connection between two entities called?

Key vocabulary

Entity
A thing (person, object, or concept) about which data is stored — becomes a table in the database.
Attribute
A property or characteristic of an entity — becomes a field (column) in the table.
Primary Key (PK)
A field that uniquely identifies each record and is never NULL — shown with underline or PK label.
Foreign Key (FK)
A field in one entity that references the primary key of another — creates a relationship.
Relationship
A connection between two entities showing how they are linked; has a label (verb) and cardinality (1:1 or 1:N).
Cardinality
The number of records: single tick means "one", crow's foot means "many". In 1:N, one entity links to many in the other.
Crow's foot notation
A standard way to draw ERDs using lines and symbols (crow's feet) to show relationships and cardinality.
draw.io
A free web-based tool for creating diagrams, including ERDs — no login required.

Entity-Relationship Diagrams

What is an Entity-Relationship Diagram?

An Entity-Relationship Diagram (ERD) is a visual plan of a database. It shows what data will be stored, how that data is organised into tables (called entities in the ERD), and how tables link to each other. ERDs are drawn before the database is built — they help designers think through the structure and catch mistakes early.

Every table in a relational database is an entity in the ERD. Every column in a table is an attribute in the ERD. An ERD uses crow's foot notation — a standard visual language with boxes, lines, and special symbols to show relationships. This notation is recognised worldwide, so anyone can understand your ERD.

Think of an ERD as a blueprint. When building a house, you draw the blueprint first, share it with stakeholders, and fix problems before construction begins. With databases, the ERD is that blueprint.

Entities and Attributes

An entity is a thing — a person, place, object, or concept — about which we want to store data. In a school database, entities might be Student, Course, and Teacher. In a library, entities might be Book, Author, and Member. Each entity becomes a table in the database.

An attribute is a property of an entity. For the Student entity, attributes might be StudentID, FirstName, LastName, EmailAddress, and EnrollmentDate. Each attribute becomes a field (column) in the table. Attributes are written inside the entity box in the ERD.

One special attribute in each entity is the primary key (PK). The primary key uniquely identifies each record and is never NULL. In an ERD, the primary key is underlined or marked with a PK label. For Student, StudentID would be the primary key. It is often an auto-incrementing integer: 1, 2, 3, etc.

Keys and Relationships

A relationship links two entities, showing how they are connected. Every relationship has a label that describes the connection (e.g., "teaches", "has", "works for"). Relationships are created using foreign keys. A foreign key (FK) is a field in one entity whose value refers to the primary key of another entity.

For example, a Teacher teaches a Student. In the ERD:

  • The Teacher table has a primary key: TeacherID
  • The Student table has a primary key: StudentID
  • The Student table also has a foreign key: TeacherID — this links each student back to their teacher

The foreign key in Student references the primary key in Teacher, creating the relationship.

Cardinality and Crow's Foot Notation

Cardinality describes how many records in one entity can be linked to records in another. For N5, we focus on One-to-Many (1:N) relationships:

  • One-to-Many (1:N) — One record in Entity A is linked to many records in Entity B. Example: One Teacher teaches many Students; each Student is taught by one Teacher.

Crow's foot notation is the standard way to show cardinality. In an ERD, relationships are drawn as lines between entities. The symbols at each end show the cardinality:

  • Single tick (|) — means "one"
  • Crow's foot (⫶) — means "many"

A 1:N relationship from Teacher to Student is drawn as a line with a single tick at the Teacher end and a crow's foot at the Student end. The label on the line (e.g., "teaches") explains the relationship.

Worked examples

Example 1 — Teacher & Student (1:N)

A school has Teachers and Students. One teacher teaches many students; each student has one teacher (for a given subject). Here's the ERD:

Teacher teaches Student
1
Teacher entity has: TeacherID (PK), Name, Email.
2
Student entity has: StudentID (PK), FirstName, TeacherID* (FK).
3
The single tick (|) at Teacher means "one"; the crow's foot (⫶) at Student means "many".
4
The FK goes in Student (the "many" table) because one teacher teaches many students. Each student links back to exactly one teacher.
Example 2 — Department & Employee (1:N)

A company has Departments and Employees. One department has many employees; each employee works in one department. Here's the ERD:

Department employs Employee
1
Department entity has: DeptID (PK), DeptName, Location.
2
Employee entity has: EmpID (PK), EmpName, DeptID* (FK).
3
The single tick (|) at Department means "one"; the crow's foot (⫶) at Employee means "many".
4
The FK goes in Employee (the "many" table) because one department employs many employees. Each employee links back to exactly one department.
Now you try

A library has Books and Members. A member can borrow many books; a book is borrowed by one member at a time. What type of relationship is this? Identify the entities, attributes, and which table holds the foreign key.

Relationship type: 1:N (one-to-many) — one member borrows many books; one book is borrowed by one member.

Entities and attributes:

  • Member: MemberID (PK), Name, Email, JoinDate
  • Book: BookID (PK), Title, ISBN, MemberID (FK)

Foreign key placement: MemberID goes in the Book table (the "many" side). The crow's foot on the line points to Book; the single tick points to Member.

ERD line: Member (single tick) ——— (crow's foot) Book, labelled "borrows"

Your ERD Portfolio

Now it's your turn to design an ERD. Create one on draw.io (free, no login), export as SVG, and paste it below. When you export this lesson as PDF, your diagram will be embedded as evidence of your work.

How to export your ERD from draw.io as SVG code:

  1. Open draw.io (https://draw.io) — no login needed. Create your ERD with entities, attributes, primary keys, and relationships.
  2. Click the File menu (top left) and select Export As → SVG.
  3. A dialog box will appear. Look for a checkbox that says "Include a copy of my diagram" or similar — leave it unchecked (we just want the SVG code, not the draw.io data).
  4. Click "Export". A new browser tab will open showing your ERD as an SVG image.
  5. Right-click on the white canvas where your ERD is drawn and select "View Page Source" (or press Ctrl+U / Cmd+U).
  6. Copy all the code. Press Ctrl+A to select all, then Ctrl+C to copy.
  7. Paste into the text area below and click "Preview My ERD" to see how it renders.
  8. When you print/export this lesson as PDF, your ERD will be embedded as evidence of your work.

⚠️ Troubleshooting: If the preview shows an error, make sure you copied the complete SVG code (it should start with <svg and end with </svg>). Do not export as PNG or PDF — it must be SVG.

Paste your SVG code above and click "Preview My ERD"

💡 Tip: Your ERD will be saved automatically as you type. When you print or export this lesson as PDF, your diagram will be embedded as evidence of your understanding.

Common mistakes
✕
Forgetting cardinality on the crow's foot line. Both ends of the relationship must be labelled. A line without crow's foot notation is meaningless — it doesn't tell you if it's 1:1, 1:N, or M:N. Always mark both ends of every relationship clearly.
✕
Confusing which entity gets the foreign key in a 1:N relationship. The foreign key always goes in the "many" table. If one Teacher teaches many Students, the FK goes in Student (pointing back to Teacher), not the other way around.
✕
Turning an attribute into an entity. Not everything should be an entity. If Student has a "Year" (S3 or S4), that is an attribute of Student, not a separate entity. Ask yourself: "Do I need to store multiple pieces of information about this thing?" If no, it's an attribute.
✕
Not labelling the relationship line. Every relationship line should have a label (like "teaches", "has", "works for") that describes what the relationship means. This makes the ERD clear to other people reading it.
Exam tip

The exam often asks you to "produce an ERD to show a given scenario." To earn full marks:

  1. Identify all entities and write their attributes (at least 2–3 per entity).
  2. Underline or mark the primary key in each entity.
  3. Draw relationships using crow's foot notation (single tick for "one", crow's foot for "many").
  4. Label each relationship line with a verb that describes the connection (e.g., "teaches", "has", "manages").
  5. Place foreign keys in the correct entity (the "many" side).

Common pitfall: An ERD with no crow's foot notation or missing relationship labels will lose marks. Always show 1:N clearly and label every relationship.

Task Set

Questions 1–5 are auto-checked. Questions 6–8 are self-marked — write your answer, then reveal the model answer to check your work.

1. In an ERD, what is a "thing" (person, object, or concept) that data is stored about? TYPE 1

2. What type of relationship is it when one record in Entity A is linked to many records in Entity B? TYPE 1

3. In an ERD, where does the foreign key go in a 1:N relationship between Teacher and Student? TYPE 1

4. A relationship is drawn as a line with a single tick on one end and a crow's foot on the other. What does this tell you? TYPE 1

5. In crow's foot notation, what does the "crow's foot" symbol (⫶) represent? TYPE 1

6. What is the difference between an attribute and a relationship in an ERD? Give an example of each. TYPE 2

An attribute is a property or characteristic of a single entity — it becomes a field (column) in a table. Example: A Student entity has attributes like StudentID, FirstName, LastName, EmailAddress.

A relationship is a connection between two entities — it shows how they are related and is drawn as a line in the ERD. Example: A Student relationship connects Student and Course, showing that students enrol in courses. The relationship type (1:1, 1:N, M:N) is shown using crow's foot notation.

7. Describe the relationship between a School and its Students. Include the entities, attributes, foreign key placement, and crow's foot notation. TYPE 2

Relationship type: 1:N (one school has many students).

Entities and attributes:
    School: SchoolID (PK), SchoolName, Address
    Student: StudentID (PK), FirstName, LastName, SchoolID (FK)

Foreign key: SchoolID is stored in Student (the "many" table).

Crow's foot notation: School (single tick |) ——— (crow's foot ⫶) Student, labelled "has".

8. A cinema has Films and Screenings. One film can have many screenings at different times; each screening shows one film. Describe the ERD for this scenario, including entities, attributes, and crow's foot notation. TYPE 3

Relationship type: 1:N (one film has many screenings).

Entities and attributes:
    Film: FilmID (PK), Title, Director, Duration
    Screening: ScreeningID (PK), FilmID (FK), ScreenTime, Screen

Foreign key: FilmID is stored in Screening (the "many" table).

Crow's foot notation: Film (single tick |) ——— (crow's foot ⫶) Screening, labelled "has". The single tick at Film means "one"; the crow's foot at Screening means "many".
Teacher notes — Shift+T to hide

Suggested timing: 50 minutes. Warm up + vocab 10 min; notes with ERD examples 15 min; worked examples 10 min; now you try 5 min; pupil portfolio 5 min; task set 5 min.

Practical setup: Pupils should be familiar with draw.io before this lesson. You can use the first 5 minutes to walk through creating a simple ERD: open draw.io, drag entities onto the canvas, label attributes, add crow's foot lines, and export as SVG.

Key misconceptions to address: (1) Confusing attributes with entities — a "Year" is not an entity, it's an attribute. (2) Forgetting to label relationships — every line needs a verb like "teaches" or "has". (3) Wrong FK placement — the FK always goes in the "many" table, not the "one" table.

Live demo suggestion: Create a simple 1:N ERD during the lesson. Examples: Teacher/Student, Department/Employee, Library/Book. Draw the entity boxes, add attributes, label the relationship, and show the crow's foot notation clearly.

Pupil portfolio feature: Students design their own ERD on draw.io, export as SVG, and paste it into the lesson. The preview button renders it live. When they export the lesson as PDF, their ERD appears in print. This creates a portfolio of their database design work. Grade their ERDs using the rubric below.

GRADING RUBRIC FOR PUPIL ERDs:
    Excellent (A): 2+ entities with clear attributes, all PKs marked, correct 1:N relationships with crow's foot notation, relationship labels present, FKs in correct place, professional layout.
    Good (B): 2 entities, most attributes listed, PKs marked, 1:N shown with crow's foot, minor labeling issues, FK mostly correct.
    Needs Work (C): <2 entities, sparse attributes, missing PKs/FKs, no crow's foot notation, missing relationship labels, unclear placement of FKs.

Common pupil errors in submitted ERDs: (1) Forgetting to label relationships with verbs (e.g., "teaches", "has"). (2) Missing crow's foot notation on lines. (3) Putting FK in the wrong table (should go in "many" table, not "one"). (4) Forgetting to mark primary keys. (5) Too many entities for a simple scenario — keep it to 2.

Managing submissions: Pupils submit as PDF (lesson exported with ERD embedded). Grade using rubric. Save to: Pupil_Submissions/DDD2_ERD/StudentName_ERD.pdf. Use for formative assessment before DDD3 (Data Dictionary).

Extension questions: (1) What is a composite primary key? (A PK made of 2+ attributes combined.) (2) When would you use a 1:1 relationship instead of just adding the data to one table? (Rare — usually when data is private/sensitive or used by different parts of the system.) (3) What happens if you put the FK in the "one" table instead of the "many" table? (The relationship still works technically, but it's inefficient and violates good database design.)

SQA command words covered: "produce", "identify", "describe", "explain". Remind pupils that "produce" means draw or create an ERD to a given scenario — it requires entities, attributes, keys, and correct cardinality.