Drawing an ERD from a scenario

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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

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

3. In the library ERD, which is an attribute of the Book entity?

A title

B Loan

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

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

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

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

Definitions

ERD from a scenario

Fill in the Blanks

Complete each line of code by filling in the missing parts.

-- Entity: people who join the library CREATE TABLE _______ ( memberId INT PRIMARY KEY, name TEXT);

CREATE TABLE books ( bookId INT ___________, title VARCHAR(50));

CREATE TABLE loans (loanId INT PRIMARY KEY, memberId INT, ___________ (memberId) REFERENCES members(memberId));

-- One teacher has many classes CREATE TABLE _______ (id INT PRIMARY KEY, teacherId INT REFERENCES teachers(id));

SELECT members.name, loans.loanDate FROM members JOIN loans ON members.memberId _ loans.memberId;

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A Entity: Member

B Entity: Borrow

C Entity: Loan

D Member 1-M Loan

Question 2

A Member(memberId PK, name)

B Book(bookId PK, title)

C Book(title, author)

Question 3

A Class 1-M Teacher

B Class(classId PK, name)

C Teacher(teacherId PK, name)

D FK teacherId goes in Class

Question 4

A Member 1-M Loan

B Member(memberId PK, name)

C Member(memberId PK, loanId FK)

Question 5

A Entity: Student

B Entity: Class

C Student M-M Class

D Enrolment(studentId, name)

Answer Key

Drawing an ERD from a scenario

Section A: Quick Questions

B — Find the entities by underlining the nouns, such as member and book

A — Class, Student, Teacher

A — title

B — A Loan entity between Member and Book

A — In the Class table, as teacherId

Section B: 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.

Section C: Definitions

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.

Section D: Fill in the Blanks

-- Entity: people who join the library
CREATE TABLE members (
  memberId INT PRIMARY KEY, name TEXT);

CREATE TABLE books (
  bookId INT PRIMARY KEY,
  title VARCHAR(50));

CREATE TABLE loans (loanId INT PRIMARY KEY,
  memberId INT, FOREIGN KEY (memberId)
  REFERENCES members(memberId));

-- One teacher has many classes
CREATE TABLE classes (id INT PRIMARY KEY,
  teacherId INT REFERENCES teachers(id));

SELECT members.name, loans.loanDate
FROM members JOIN loans
ON members.memberId = loans.memberId;

Section E: Spot the Error

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.

C — Every entity needs a primary key. Book has no PK, so it should be Book(bookId PK, title).

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.

C — The foreign key goes on the many side. One member has many loans, so Loan holds memberId FK. Member should not hold loanId.

D — A many-to-many relationship needs a link table holding the keys of both entities. It should be Enrolment(studentId, classId), not name.