← ACARA Digital Technologies Years 9–10

Drawing an ERD from a scenario

Find the entities, list their attributes, pick primary keys, then work out each relationship and draw it, with the foreign key on the many side.

Part of: ACARA Digital Technologies Years 9–10 — Entities, Relationships and Queries — Module 16, QCE Digital Solutions Unit 2 — Data-Driven Problems and Solution Requirements — Module 69, QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 98, QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 14, QCE Digital Solutions Unit 3 — Interactions Between Users, Data and Digital Systems — Module 107, WA Computer Science ATAR Year 11 — Data Management — Module 35

Understanding Drawing an ERD from a scenario in ACARA Digital Technologies Years 9–10

What it does: teacherId is the foreign key in CLASS because many classes can share one teacher. The many side holds the link. Start by finding the entities. Underline the nouns in the scenario, then work out attributes, keys and relationships. Classes, students and teachers are things the school stores data about, so they are entities. Name and Room are attributes. Title describes a book, so it is an attribute that becomes a field. Loan is a separate entity. In Loan, memberId and bookId point to the primary keys in Member and Book, so they are foreign keys. After finding an entity, you list its attributes and choose a primary key that uniquely identifies each member.

Common mistakes: Entities are nouns (things we store data about). Borrow is a verb, so it describes an action, not an entity. It should be Entity: Member. Every entity needs a primary key. Book has no PK, so it should be Book(bookId PK, title).

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Drawing an ERD from a scenario to a class →

This module's key terms are also in a printable crossword (Modules 433–451) and word search (Modules 413–451).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
A library scenario says: 'Members borrow books.' What is the best first step in drawing the ERD?
Draw the relationship lines before listing any entitiesFind the entities by underlining the nouns, such as member and book
Matches
TEACHER(teacherId, name, classId)
↓
The foreign key is on the wrong side. One teacher has many classes, so classId should not be in TEACHER. Put teacherId in CLASS instead.
Applying
Meanings
Term
ERD from a scenario
Meaning
Find the entities, list their attributes, pick primary keys, then work out each relationship and draw it, with the foreign key on the many side.
Practice
Member(memberId PK, name, email)
Testing
Gaps
CREATE TABLE (
Check Answer
Spots
1Entity: Member
2Entity: Borrow

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Manta Ray' badge — Graceful Coder, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. A library scenario says: 'Members borrow books.' What is the best first step in drawing the ERD?
A. Draw the relationship lines before listing any entities
B. Find the entities by underlining the nouns, such as member and book ✓
Start by finding the entities. Underline the nouns in the scenario, then work out attributes, keys and relationships.
2. Which of these is the best set of entities for 'A school has classes, students and teachers'?
A. Class, Student, Teacher ✓
B. School, Name, Room
Classes, students and teachers are things the school stores data about, so they are entities. Name and Room are attributes.
3. In the library ERD, which is an attribute of the Book entity?
A. title ✓
B. Loan
Title describes a book, so it is an attribute that becomes a field. Loan is a separate entity.
4. A member can borrow many books over time, and a book can be borrowed by many members over time. What should you add to the library ERD?
A. A direct many-to-many line with no extra entity
B. A Loan entity between Member and Book ✓
A many-to-many relationship is split with a linking entity. Loan links Member and Book, and each Member and Book has a one-to-many relationship with Loan.
5. One teacher teaches many classes, and each class has one teacher. Where does the foreign key go?
A. In the Class table, as teacherId ✓
B. In the Teacher table, as classId
The foreign key goes on the many side. Many classes belong to one teacher, so Class holds teacherId.

Match the Pairs

Scenario: A library lends books to members. Entities: MEMBER, BOOK → Underline the nouns in the scenario to find the entities, which become the boxes on the ERD
MEMBER(memberId, name, email) → An entity with its attributes listed and the primary key (memberId) chosen to identify each record
One teacher teaches many classes, so the foreign key goes on the many side (the class table) → CLASS(classId, subject, teacherId)
A member can have many loans and a book can have many loans, so a loan entity links them with two foreign keys → LOAN(loanId, memberId, bookId, dateOut)
TEACHER(teacherId, name, classId) → The foreign key is on the wrong side. One teacher has many classes, so classId should not be in TEACHER. Put teacherId in CLASS instead.
Scenario: Students enrol in classes. ERD: STUDENT only, with a className field → Class was not identified as its own entity. Underline all the nouns: STUDENT and CLASS should be separate entities with a relationship line between them.

Key Term

ERD from a scenario
Find the entities, list their attributes, pick primary keys, then work out each relationship and draw it, with the foreign key on the many side.

Fill in the Blanks

1. -- Entity: people who join the library CREATE TABLE members ( memberId INT PRIMARY KEY, name TEXT);
Hint: Type the plural table name for the entity described in the comment
2. CREATE TABLE books ( bookId INT PRIMARY KEY, title VARCHAR(50));
Hint: Type the two-word keyword that makes bookId the unique identifier of each record
3. CREATE TABLE loans (loanId INT PRIMARY KEY, memberId INT, FOREIGN KEY (memberId) REFERENCES members(memberId));
Hint: Type the two-word keyword that links memberId in the many-side table to another table
4. -- One teacher has many classes CREATE TABLE classes (id INT PRIMARY KEY, teacherId INT REFERENCES teachers(id));
Hint: Type the name of the many-side table that must hold the foreign key
5. SELECT members.name, loans.loanDate FROM members JOIN loans ON members.memberId = loans.memberId;
Hint: Type the operator that matches the primary key to the foreign key in the join

Spot the Error

Question 1
A Entity: Member
B Entity: Borrow
C Entity: Loan
D Member 1-M Loan
Line B — Entities are nouns (things we store data about). Borrow is a verb, so it describes an action, not an entity. It should be Entity: Member.
Question 2
A Member(memberId PK, name)
B Book(bookId PK, title)
C Book(title, author)
Line C — Every entity needs a primary key. Book has no PK, so it should be Book(bookId PK, title).
Question 3
A Class 1-M Teacher
B Class(classId PK, name)
C Teacher(teacherId PK, name)
D FK teacherId goes in Class
Line A — One teacher teaches many classes, so the one side is Teacher and the many side is Class. It should be Teacher 1-M Class.
Question 4
A Member 1-M Loan
B Member(memberId PK, name)
C Member(memberId PK, loanId FK)
Line C — The foreign key goes on the many side. One member has many loans, so Loan holds memberId FK. Member should not hold loanId.
Question 5
A Entity: Student
B Entity: Class
C Student M-M Class
D Enrolment(studentId, name)
Line D — A many-to-many relationship needs a link table holding the keys of both entities. It should be Enrolment(studentId, classId), not name.

Student with a class code? Join your class →