Unit 11.2A · Term 2

Entity Relationship Diagrams

An Entity Relationship Diagram (ERD) is a visual blueprint of a database. It shows the entities (tables), their attributes (fields), and the relationships between them — before you write a single line of SQL.

Learning Objectives

11.4.1.6 Define connections between tables
11.4.1.7 Create an ER model

Lesson Presentation

11.2A-er-diagrams.pdf · Slides for classroom use

View Download

Conceptual Anchor

The Family Tree Analogy

An ERD is like a family tree for data. Each person (entity) has properties (name, age, etc.). Connections between people show relationships (parent → child, married to). Just like a family tree helps you see how people are connected, an ERD shows how tables relate.

Rules & Theory

ER Diagram Components

Component	Shape	Description
Entity	Rectangle	A table: Student, Course, Teacher
Attribute	Oval (connected to entity)	A field: Name, Age, StudentID
PK Attribute	Underlined oval	The primary key field
Relationship	Diamond	How entities connect: "enrols in", "teaches"
Cardinality	1, M (or *)	How many of each entity can participate

Types of Relationships (Cardinality)

Type	Notation	Meaning	Example
One-to-One	1 : 1	One record in A → exactly one in B	Student ↔ StudentCard
One-to-Many	1 : M	One record in A → many in B	Teacher → Classes
Many-to-Many	M : M	Many in A → many in B	Students ↔ Courses

Example: School Database ERD

  ┌──────────┐         ┌───────────┐         ┌──────────┐
  │ STUDENT  │         │ ENROLMENT │         │  COURSE  │
  ├──────────┤   1:M   ├───────────┤   M:1   ├──────────┤
  │ StudentID│◄───────│ StudentID │───────►│ CourseID │
  │ Name     │         │ CourseID  │         │ Title    │
  │ Email    │         │ Grade     │         │ Credits  │
  │ Class    │         │ Date      │         │ TeacherID│
  └──────────┘         └───────────┘         └──────────┘
        PK                PK: (StudentID +        PK
                          CourseID) = Composite

  Many-to-Many resolved through junction table (ENROLMENT):
  - One Student can take MANY Courses
  - One Course can have MANY Students
  - The Enrolment table bridges them

Resolving Many-to-Many

PROBLEM:  Student ←──M:M──► Course  (can't implement directly!)

SOLUTION: Create a JUNCTION TABLE:

  Student ◄──1:M──► Enrolment ◄──M:1──► Course

  The junction table (Enrolment) contains:
  - FK to Student (StudentID)
  - FK to Course (CourseID)
  - Composite PK: (StudentID + CourseID)
  - Additional data: Grade, EnrolDate

Crow's Foot Notation

In exams, you may see crow's foot notation: a line ending in a "fork" (like a crow's foot) means "many". A single line means "one". This is the most common ERD style in industry.

Common Pitfalls

Leaving M:M Unresolved

You cannot implement a many-to-many relationship directly in a relational database. You must create a junction table (bridge table) to break it into two one-to-many relationships.

Tasks

Remember

Name the 5 components of an ER diagram and their shapes.

Apply

Draw an ERD for a hospital database with entities: Doctor, Patient, Appointment. Show keys and cardinalities.

Analyze

A student draws a direct M:M relationship between Book and Author. Explain why this is wrong and show how to fix it with a junction table.

Self-Check Quiz

Q1: What are the 3 types of cardinality?

One-to-One (1:1), One-to-Many (1:M), Many-to-Many (M:M).

Q2: How do you resolver a many-to-many relationship?

Create a junction (bridge) table that contains foreign keys to both tables, turning M:M into two 1:M relationships.

Q3: What shape represents a relationship in an ERD?

A diamond shape, placed between the connected entities.