Before architects build, they draw blueprints — plans showing which rooms connect and how large each should be. Database designers do the same before writing a single line of SQL. An entity-relationship diagram (ERD) is the database blueprint: it maps out what things the database will store and how those things relate to one another.
What is an entity?
An entity is any real-world object or concept about which the database will store information. In a school database, typical entities include:
StudentTeacherSubjectClassroom
Each entity becomes a table in the finished database. In an ERD, an entity is drawn as a rectangle labelled with the entity's name.
Entities have attributes — the specific pieces of information stored about each entity. In an ERD, attributes are drawn as ovals connected to their entity rectangle, although at GCSE level you will more commonly list them as column names inside the table box.
What are relationships and how are they drawn?
A relationship describes how two entities are connected. In an ERD, relationships are drawn as a line between two entity rectangles, labelled with the nature of the connection (e.g. "teaches", "enrols in").
The critical information at either end of the line is the cardinality — how many instances of each entity can participate in the relationship:
| Symbol at line end | Meaning |
|---|---|
1 |
Exactly one |
M (or * or ∞) |
Many (zero or more) |
Three types of relationship exist:
| Relationship type | Meaning | Example |
|---|---|---|
| One-to-one (1:1) | One record in table A relates to exactly one record in table B | One student has one student ID card |
| One-to-many (1:M) | One record in table A relates to many records in table B | One teacher teaches many students |
| Many-to-many (M:M) | Many records in A relate to many records in B | Many students enrol in many subjects |
How do you draw an ERD step by step?
Scenario: A school wants a database to track which students are enrolled in which subjects, and which teacher delivers each subject.
Step 1 — Identify the entities:
Student, Subject, Teacher
Step 2 — Identify the relationships:
- A Student enrols in many Subjects; a Subject is taken by many Students → many-to-many
- A Teacher teaches many Subjects; a Subject is taught by one Teacher → one-to-many
Step 3 — Draw the diagram:
[Student] ----M---------enrols in---------M---- [Subject]
|
taught by
|
1
[Teacher]
Step 4 — Resolve the many-to-many:
Databases cannot directly store a many-to-many relationship. You must introduce a linking table (also called a junction table or associative entity):
[Student] ---1----M--- [Enrolment] ---M----1--- [Subject]
|
taught by (1:M)
|
[Teacher]
The Enrolment table stores one row per student-subject pair, with foreign keys to both Student and Subject.
What attributes does each entity have?
Student
| Field | Type | Key? |
|---|---|---|
| student_id | Integer | Primary key |
| first_name | Text | |
| last_name | Text | |
| year_group | Integer |
Subject
| Field | Type | Key? |
|---|---|---|
| subject_id | Integer | Primary key |
| subject_name | Text | |
| teacher_id | Integer | Foreign key → Teacher |
Teacher
| Field | Type | Key? |
|---|---|---|
| teacher_id | Integer | Primary key |
| teacher_name | Text | |
| department | Text |
Enrolment (linking table)
| Field | Type | Key? |
|---|---|---|
| enrolment_id | Integer | Primary key |
| student_id | Integer | Foreign key → Student |
| subject_id | Integer | Foreign key → Subject |
What is the relationship between an ERD and a relational database?
An ERD is a design tool — it exists on paper (or in software) before any database is created. Once the ERD is finalised:
- Each entity rectangle becomes a table.
- Each attribute becomes a column in that table.
- One-to-many relationships are implemented via a foreign key in the "many" table.
- Many-to-many relationships are resolved by creating a linking table with two foreign keys.
The ERD is not written in SQL — it is a conceptual diagram. SQL comes next, in the implementation phase.
How do ERD questions appear in GCSE exams?
Examiners may ask you to:
- Interpret a given ERD — state the entities, relationships, and cardinalities shown.
- Complete a partial ERD by adding a missing entity, relationship, or cardinality symbol.
- Identify whether a relationship is 1:1, 1:M, or M:M for a described scenario.
- Explain why a many-to-many relationship requires a linking table.
Frequently asked questions
What is the difference between an ERD and a database schema?
An ERD is a high-level conceptual diagram showing entities and relationships. A schema is a more detailed, technical specification of the database structure, including all tables, columns, data types, primary keys, and foreign keys. The ERD precedes and informs the schema.
Can an entity relate to itself?
Yes. This is called a recursive relationship. For example, an Employee entity might have a "manages" relationship with other Employee records — one employee manages many others. These appear in ERDs as a relationship line that loops back to the same entity rectangle.
Do I need to draw an ERD in my GCSE programming project?
It depends on your exam board and project brief. AQA GCSE Computer Science projects that involve database elements often benefit from an ERD in the design section. Including one demonstrates understanding of the database structure before implementation.
What software can I use to draw an ERD?
Free tools include draw.io (diagrams.net), Lucidchart (free tier), and dbdiagram.io. For exam answers, neatly hand-drawn rectangles and lines with clear cardinality labels are perfectly acceptable.
Ready to design your own database from scratch? Visit aitutors.me — Professor Turing will walk you through identifying entities, mapping relationships, and resolving many-to-many links step by step.