DDD2 — ER diagrams: entities, attributes, relationships
📅 To be scheduled
⏱ ~50–60 minutes · single period
Learning intentions
I can identify entities and their attributes from a scenario.
I can describe the purpose of a relationship between two entities.
I can draw an entity-relationship diagram with three or more entities using the correct notation.
Success criteria
I can list appropriate attributes for a given entity, including its primary key.
I can name a sensible relationship between two entities using a verb phrase.
I can draw entities, attributes, primary keys, foreign keys, and relationship lines using SQA's crow's-foot notation.
Warm up — what do you already know?
Answer before the lesson begins — it's fine if you're unsure.
WU1
1. In DDD1 you wrote requirements for a sports club database. Which of these is a functional requirement, not an end-user requirement?
WU2
2. National 5 recap: the field (or combination of fields) that uniquely identifies each record in an entity is called the ___ key.
WU3
3. Which of these is not one of the four entities identified for the Craigmillar Community Sports Club database in DDD1?
Key vocabulary
Entity
A person, place, thing, event, or concept that the database needs to store data about — drawn as a rectangle.
Attribute
A piece of information stored about an entity — for example a Member's surname or a Class's start time.
Relationship
A named connection between two entities, describing how records in one relate to records in the other — for example "teaches" or "makes".
Primary key
The attribute that uniquely identifies each record in an entity. Shown underlined in an ER diagram.
Foreign key
An attribute in one entity that links to the primary key of another entity. Shown with an asterisk (*) in an ER diagram.
Crow's-foot notation
The set of symbols used on the relationship line to show cardinality — a fork ("crow's foot") marks the "many" side.
Entities, attributes, and relationships
What is an entity?
An entity is a person, place, thing, event, or concept that a database needs to hold data about. In the Craigmillar Community Sports Club scenario from DDD1, the entities are Member, Class, Instructor, and Booking — one entity for each distinct "thing" the club's stakeholders talked about. On an ER diagram, an entity is drawn as a labelled rectangle. The Higher course requires every ER diagram to have three or more entities, which the sports club scenario comfortably satisfies with four.
What is an attribute?
An attribute is a single piece of information stored about an entity — the same idea as a field from National 5, just applied at the design stage before any table exists. Each entity needs enough attributes to satisfy the functional requirements identified in DDD1. For example, the requirement "the database must have a query to calculate the average fee per session" tells us Class needs a feePerSession attribute; the requirement "sort members by the date they joined" tells us Member needs a dateJoined attribute. Attributes are traced directly back to requirements, not invented arbitrarily.
Primary keys and foreign keys, briefly revisited
Every entity needs a primary key — a single attribute that uniquely identifies each record, shown underlined. Member's primary key is memberID; Class's is classCode. When one entity needs to refer to a record in another entity, it stores that entity's primary key as a foreign key, marked with an asterisk. Booking needs to know which member made the booking and which class it's for, so it stores memberID* and classCode* as foreign keys. DDD4 covers data dictionaries and compound keys in full detail — this lesson only needs enough of the key concept to draw sensible entity boxes and relationship lines.
Naming a relationship
A relationship is the named line joining two entities on an ER diagram. The name should be a short verb phrase describing what one entity does to, or with, the other — "teaches", "makes", "generates" — not a noun. A relationship name should make sense read in one direction: "Instructor teaches Class" reads naturally; "Instructor Class" does not. Three relationships link the sports club's four entities: Instructor teaches Class, Class generates Booking, and Member makes Booking.
Reading crow's-foot notation
The line joining two entities carries a symbol at each end to show cardinality — how many records on one side can relate to how many on the other. SQA's house style uses crow's-foot notation: a fork (three short diverging lines) at the "many" end of a relationship, and a plain line at the "one" end. In the diagram below, the fork sits at Class's end of the "teaches" line, because one instructor teaches many classes, but each class has only one instructor. This lesson focuses on reading and drawing that notation correctly once you're told which side is "many" — working out which side is many, and why, for a relationship you haven't seen before is the focus of DDD3.
The sports club's entity boxes
Each entity box below lists its attributes exactly as they'll appear on the finished ER diagram: the primary key underlined, and any foreign key marked with an asterisk.
Member
memberID
firstName
surname
address
town
postcode
dateJoined
Class
classCode
className
instructorRef
dayOfWeek
startTime
capacity
feePerSession
Instructor
instructorRef
firstName
surname
qualified
Booking
bookingRef
memberID
classCode
bookingDate
attended
Worked examples
Example 1 — From requirement to entity and attribute
1
Requirement: "The database must have a query to select classes with available spaces, comparing bookings against capacity" (from DDD1's Example 3).
2
This requirement mentions two entities working together: Class (which has a capacity) and Booking (the records being counted against it).
3
It confirms Class needs a capacity attribute, and confirms that Booking must link back to Class — which is exactly what the classCode foreign key does.
✓
Every attribute in the entity boxes above can be traced back to a DDD1 requirement in this way — that's what makes them "correct" rather than guessed.
Example 2 — Drawing the Instructor entity box
1
Draw a rectangle and label it with the entity name: Instructor.
2
List its attributes: instructorRef, firstName, surname, qualified.
3
Underline the primary key: instructorRef uniquely identifies each instructor, so it's underlined. None of Instructor's attributes are foreign keys, so nothing here needs an asterisk.
✓
Compare your box against the Instructor entity box in the notes above — the underlining and attribute list should match exactly.
Example 3 — Naming and drawing the Instructor–Class relationship
1
Identify the two entities involved: Instructor and Class.
2
Choose a verb phrase describing what an instructor does to a class: teaches. "Instructor teaches Class" reads naturally.
3
Draw a line between the two entity boxes, labelled "teaches", with a crow's foot at the Class end — because one instructor teaches many classes, but each class has only one instructor.
✓
See the full diagram below for how all three relationships (teaches, generates, makes) fit together across all four entities.
The crow's foot (fork) always sits at the "many" end of a relationship line — here, at Class, Booking, and Booking again.
Now you try
Draw (in words) the entity box for Booking: list every attribute in the order it should appear, and state which are the primary key and which are foreign keys.
Booking
bookingRef — primary key (underlined)
memberID — foreign key (marked *), links to Member
classCode — foreign key (marked *), links to Class
bookingDate — ordinary attribute
attended — ordinary attribute
⚠️ Common mistakes — examiner feedback
Treating an entity as an attribute. "Member" is a separate entity, not an attribute of Booking — Booking only stores the member's key (memberID) as a foreign key, not the member's full details again.
Forgetting the underline or the asterisk. A primary key must be underlined and a foreign key must be marked with * on every ER diagram — missing either loses marks even if the attribute list itself is correct.
Naming a relationship with a noun instead of a verb phrase. "Class" or "Booking" is not a valid relationship name — a relationship describes an action or connection, like "teaches" or "generates".
Drawing fewer than three entities. The Higher course specifically requires ER diagrams with three or more entities — a two-entity diagram, even if otherwise correct, doesn't meet the specification.
📝 Exam tip
When a question says "exemplify" an ER diagram, it wants an actual labelled diagram — entity boxes, attributes, primary keys underlined, foreign keys marked with *, and named relationship lines with crow's-foot notation — not a written description of what the diagram would contain. A correct written description without the diagram itself will not gain full marks.
Task Set B — Core questions
Work through all questions. Written answers use self-assessment — compare with the model answer.
B1
On an ER diagram, an entity is drawn as a labelled ___.
B2
Which symbol marks a foreign key on an ER diagram entity box?
B3
What is the primary key of the Class entity in the Craigmillar Community Sports Club database?
B4
Name both foreign keys stored in the Booking entity, separated by a comma (e.g. fieldOne, fieldTwo).
B5
Which of these is the most appropriate name for the relationship between Class and Booking?
B6
List the Instructor entity's attributes, in order, and state which is its primary key.
Model answer
B7
Explain where the crow's foot should be drawn on the line between Class and Booking, and why.
Model answer
B8
List the Member entity's attributes, in order, stating its primary key and confirming whether it has any foreign keys.
Model answer
B9
Why does Booking need both a memberID foreign key and a classCode foreign key?
Task Set C — Extension · Beyond the specification
Optional challenge questions. Not required for your exam.
C1
Suppose the club wanted to add a fifth entity, Payment, to record how members pay for their bookings. What attributes might it need, and which existing entity would it most naturally link to?
One possible answer
C2
The foreign key linking Instructor and Class is stored in Class (instructorRef), not in Instructor. Explain why storing it the other way round wouldn't work for this scenario.
One possible answer
C3
Booking links Member and Class together. Is Booking still a "real" entity in its own right, or does it only exist to connect the other two? Justify your answer using its attributes.
One possible answer
📁 File this in OneNote under: Higher Computing Science → Database Design & Development → DDD2
📌 Teacher notes — Shift+T to hide
Suggested timing: 8 min warm up · 15 min notes & entity boxes · 12 min worked examples & ER diagram walkthrough · 5 min now you try · 20 min Task Set B · 5–10 min Task Set C / review, if time allows.
Key misconception: pupils very commonly draw Member as an attribute of Booking (e.g. writing "member" inside Booking's attribute list) rather than recognising Booking only stores the member's key. Worth addressing directly using the C3 extension question or a live example.
Live demo suggestion: project the entity-relationship SVG diagram and physically trace each line while asking "which end is many, and why?" before pupils attempt B7 — this rehearses reading crow's-foot notation live, ahead of DDD3's deeper cardinality-reasoning content.
SQA command words covered: "exemplify" (produce an actual labelled diagram, not a written description — see the exam tip box), "describe".
Extension suggestion: pupils who finish Task Set C early could sketch a rough ER diagram (in words) for a completely different scenario of their choosing, using the same four-part checklist (entities, attributes, primary/foreign keys, named relationships) taught in this lesson.
Deliberate scope limit: this lesson does not ask pupils to derive cardinality from a scenario — all three relationships are given as established facts from DDD1's requirements. Reasoning about why a relationship is one-to-one, one-to-many, or many-to-many (and entity-occurrence diagrams as the tool for showing that reasoning) is DDD3's job, not this lesson's.