Lesson 2 of 4
Theory hub Databases Lesson 2
Databases: Lesson 2

Relational Design and ER Diagrams

Connect tables using foreign keys, understand one-to-one, one-to-many and many-to-many relationships, and learn how to draw and read entity-relationship diagrams.

50 minutes All exam boards
Why this matters

Splitting data into separate tables only works if those tables can be connected. A foreign key is the bridge between tables - it is how a student record knows which course they are on, and how a course record knows which teacher runs it.

Think about it: Netflix has millions of users and millions of titles. How does it know which shows each user has watched, and in what order? It does not store the full show title in the user's record - it stores an ID that links to a separate shows table. That link is a foreign key.
Key terms
Terms you need to know
Foreign key
A field in one table that matches the primary key of another table, creating a link between the two.
Relationship
The connection between two entities in a database. Relationships are categorised by how many records on each side can be linked.
One-to-one
One record in table A links to exactly one record in table B. Example: one person has one passport.
One-to-many
One record in table A links to many records in table B. The most common relationship type. Example: one teacher has many students.
Many-to-many
Many records in table A link to many records in table B. Requires a junction table to implement. Example: students and courses (a student takes many courses; a course has many students).
Entity
A thing or object that the database stores information about. Represented as a table. Example: Student, Course, Teacher.
ER diagram
Entity-Relationship diagram. A visual diagram showing entities, their attributes and the relationships between them.
Junction table
A table used to resolve a many-to-many relationship. Contains two foreign keys - one pointing to each of the tables being linked.
Foreign keys
Linking tables together

A foreign key is a field in one table whose value matches the primary key of another table. It creates a reference - a way for the database to follow a connection from one table to another.

Students

StudentID PKNameCourseID FK
1001Alice Smith201
1002Bob Brown201
1003Clara Davis202

Courses

CourseID PKCourseNameTeacherName
201GCSE MathsMr Jones
202GCSE CSMs Patel
Students.CourseID Courses.CourseID - the foreign key points to the primary key of the other table

Alice and Bob both have CourseID = 201. Following that ID into the Courses table tells us both are on GCSE Maths with Mr Jones. The course data is stored once only.

Exam angle

A common exam question asks you to identify the primary key and foreign key in a given table. The foreign key is always in the "many" side of the relationship - in this example, many students can have the same CourseID, but each CourseID exists only once in the Courses table.

Types of relationships
How records link across tables
1:1
One-to-one
One record in table A is linked to exactly one record in table B, and vice versa. Rare in practice.
Person - Passport: each person has one passport; each passport belongs to one person.
1:M
One-to-many
One record in table A links to many records in table B. The most common type - implemented with a foreign key on the "many" side.
Teacher - Students: one teacher teaches many students; each student has one teacher (for that class).
M:M
Many-to-many
Many records in A link to many records in B. Cannot be implemented directly - requires a junction table with two foreign keys.
Students - Courses: a student takes many courses; a course has many students enrolled.
Many-to-many in practice

If asked to implement a many-to-many relationship in a table structure, create a junction table. For Students and Courses, create a StudentCourse table with two fields: StudentID (FK) and CourseID (FK). Each row in this table records one enrolment - one student on one course.

ER diagrams
Drawing and reading entity-relationship diagrams

An ER diagram shows all the entities in a database, their attributes, and how they connect. You need to be able to draw them from a description and interpret them from a diagram.

ER diagram conventions (GCSE level)

Student StudentID Name enrolled M Course M CourseName
Rectangle = Entity (table) Diamond = Relationship Ellipse = Attribute (field) M / 1 = Multiplicity (many or one)

The diagram above shows a many-to-many relationship between Student and Course - indicated by M on both sides of the relationship diamond. This tells a database designer they need a junction table.

Exam angle

When asked to draw an ER diagram, always: label the entities (rectangles), name the relationship (diamond), and add multiplicity labels (1 or M). If asked to describe a relationship from a diagram, state both sides: "One teacher teaches many students, but each student (in that class) has one teacher."

Quick quiz

Test yourself

1. A library system has a Books table and a Members table. A member can borrow many books, and a book can be borrowed by many members over time. What type of relationship is this?

Correct. A member can borrow many books, and a book can be borrowed by many members - this is many-to-many. To implement it, you would need a Loans junction table with MemberID (FK) and BookID (FK), plus loan date and return date fields.

2. In a Students table, the field CourseID matches the primary key of the Courses table. What type of key is CourseID in the Students table?

Correct. A foreign key is a field that references the primary key of another table. CourseID is the primary key in the Courses table, but in the Students table it is a foreign key - it exists there only to create the link.

3. On an ER diagram, what shape is used to represent an entity (table)?

Correct. Rectangles represent entities (tables). Ellipses represent attributes (fields). Diamonds represent relationships (verbs like "enrolled in", "teaches", "purchases"). The lines between shapes show which entities have which attributes and which relationships connect them.
Think deeper
Challenge question

A school database needs to store which students attend which after-school clubs, and on which days. A student can attend multiple clubs, and a club can have multiple students. Describe the table structure you would use, including all keys.

This is a many-to-many relationship requiring a junction table. You need: Students (StudentID [PK], Name, Year), Clubs (ClubID [PK], ClubName, Day), and StudentClub (StudentID [FK], ClubID [FK], Day). The StudentClub junction table resolves the many-to-many relationship. Its primary key could be a composite of StudentID + ClubID + Day (since a student could attend the same club on different days).
Lesson 1 Lesson 3: SQL Basics
Printable Worksheets

Practice what you have learned

Three levels of worksheet for this lesson. Download, print and complete offline.

Recall
Foreign Keys and Relationships
Definitions of foreign key and relationship types with examples. One-to-many and many-to-many notation practice.
Download
Apply
ER Diagram Practice
Read scenarios and identify relationship types. Draw simple ER diagrams from written descriptions.
Download
Exam Style
Exam-Style Questions
Design questions on relational databases, foreign keys and ER diagram interpretation. Mark scheme included.
Download
Lesson 2 - Databases
Relational Design and ER Diagrams
Starter activity
Show students a Students table with CourseID as a column. Ask: "Where would you look to find out what GCSE Maths means?" Lead them to realise the ID needs to be looked up in another table. This is the concept of a foreign key - a reference rather than a repeated value.
Lesson objectives
1
Define foreign key and explain how it links two tables.
2
Distinguish between one-to-one, one-to-many and many-to-many relationships with examples.
3
Describe what a junction table is and when it is needed.
4
Draw a simple ER diagram using correct notation (rectangles, diamonds, ellipses, multiplicity).
5
Interpret an ER diagram and describe the relationship in words.
Key vocabulary
Foreign key
Field in one table matching the primary key of another. Creates a link. Always on the "many" side of a 1:M relationship.
One-to-many
The most common relationship type. One record in table A links to multiple records in table B.
Junction table
Resolves a many-to-many relationship. Contains two foreign keys - one per linked table.
Discussion questions
Can you think of a real many-to-many relationship in school life? What would the junction table look like?
Why is it impossible to implement a many-to-many relationship with just two tables and a foreign key?
Exit tickets
State the difference between a primary key and a foreign key. [2 marks]
A music streaming service stores users and playlists. A user can have many playlists but each playlist belongs to one user. What type of relationship is this? [1 mark]
Draw an ER diagram for: Students enrol on Courses. A student can enrol on many courses; a course can have many students. [3 marks]
Homework suggestion
Choose a real system (e-commerce store, school library, hospital). Identify three or more entities, list their attributes and primary keys, and draw a complete ER diagram showing all relationships with correct multiplicity labels.
Resources
ER Diagram Builder Worksheet 1 - Recall Worksheet 2 - Apply Worksheet 3 - Exam Style Unit Exam Paper (all 4 lessons)