03 entity relationship modelling

Designing relational database schemas using entity-relationship diagrams, cardinality, primary keys, and foreign keys.

image-00-big-question.png

How do you draw a blueprint for data? Before a single line of code is written or a table is created, a Systems Architect or Database Administrator must create a detailed plan. This plan, an Entity-Relationship Diagram (ERD), acts as the master blueprint, showing exactly how different pieces of information connect to each other. Today, you'll learn to become that architect, using a formal visual language to design robust and logical database structures from scratch. This is a core skill of the 'Modeller' persona - turning real-world complexity into a clean, abstract plan.

Learning Outcomes

Factual (Knowing that)

Recall the symbols used in Crow's Foot notation for entities, attributes, and relationships.

Describe the three types of relationship cardinality: one-to-one, one-to-many, and many-to-many.

Conceptual (Knowing why)

Explain why ER modelling is a critical step in the database design process before implementation.

Analyse a real-world scenario to identify the core entities and the logical relationships that connect them.

Procedural (Knowing how)

Apply Crow's Foot notation to visually represent a given data model.

Create a complete Entity-Relationship Diagram from a textual description of a system's requirements.

Entity-Relationship (ER) Modelling

From Ideas to Blueprints

Before constructing a building, an architect creates a detailed blueprint. In the world of databases, a Database Administrator or Systems Architect does the same thing, but their blueprint is called an Entity-Relationship Diagram (ERD). An ERD is a visual map that shows the logical structure of a database. It allows designers to model the real-world system they are trying to represent, ensuring that all data is organised logically before any tables are actually created in a Database Management System (DBMS). This process is a classic example of abstraction, a core tenet of computational thinking, where we hide complex details to focus on the overall structure. It embodies the Modeller persona, creating a simplified, formal representation of a complex system.

The Core Components of an ERD

An ERD has three main components. We typically use a standardised set of symbols called Crow's Foot Notation to draw them.

((An animated GIF showing the three components of an ERD being drawn one by one: an entity box, attributes being listed inside it, and a relationship line connecting to another entity.))

Entities: An entity is a real-world object or concept about which we store data. It's a noun, like a `Customer`, a `Product`, or an `Appointment`. In an ERD, we draw an entity as a rectangle with its name at the top.

Attributes: These are the properties or characteristics of an entity - the specific pieces of data we want to store. For a `Customer` entity, attributes might include `CustomerID`, `FirstName`, `LastName`, and `Email`. Attributes are listed inside the entity's rectangle. The attribute that serves as the primary key is always underlined.

Relationships: These define how two entities are associated with each other. For example, a `Customer` can 'place' an `Order`. A relationship is drawn as a line connecting two entities. The nature of this connection is defined by its cardinality.

Understanding Cardinality

Cardinality describes the number of instances of one entity that can be associated with instances of another entity. Crow's Foot notation gives us simple symbols to show this.

One-to-One (1:1): Each record in Table A can be linked to only one record in Table B, and vice-versa. For example, a `Country` has one `CapitalCity`. This is rare in database design.

One-to-Many (1:M): A single record in Table A can be linked to many records in Table B, but each record in Table B can only be linked to one record in Table A. This is the most common relationship type. For example, one `Author` can write many `Books`, but each `Book` has only one `Author`.

Many-to-Many (M:N): A record in Table A can be linked to many records in Table B, and a record in Table B can also be linked to many records in Table A. For example, a `Student` can enrol in many `Courses`, and a `Course` can have many `Students`.

In Crow's Foot Notation:

A single perpendicular line across the relationship line means 'one'.

A three-pronged 'crow's foot' shape means 'many'.

((A clear diagram showing the three relationship types side-by-side using simple entities like `Driver`-`Car` (1:1), `Mother`-`Child` (1:M), and `Student`-`Subject` (M:N), with clear Crow's Foot notation on each line.))

When we have a many-to-many relationship, it cannot be directly implemented in a relational database. We must resolve it by creating a third table, known as a linking table or 'associative entity'. For our `Student` and `Course` example, we would create a new table called `Enrolment`. This table would hold the primary key from the `Student` table and the primary key from the `Course` table, turning the one M:N relationship into two 1:M relationships.

Active Learning Directive
Construct a strict hierarchical concept map. Place the broadest overarching theory, 'Database Design', at the top. Link downwards to 'ER Modelling' and then branch to its core components: 'Entity', 'Attribute', and 'Relationship'. From 'Relationship', create final branches for each type of 'Cardinality', adding a small sketch of the Crow's Foot symbol for each. You must label the relationship on every connecting arrow.

Task 7.3 From Words to Blueprints

Every successful digital system, from your school's registration software to Netflix's vast library, started as a plan. Today, you're the architect. You'll take a simple, real-world scenario for a veterinary surgery and turn it into a professional, logical blueprint - an ER Diagram.

Get Organised!

In your workbook or on a new digital document, create a main heading: `ERD for a Vet Surgery`.

USE the Existing Model

Analyse the simple ERD below, which models the relationship between pet owners and their pets. It shows that one Owner can have many Pets, but each Pet belongs to only one Owner. Notice how `OwnerID` in the `Pet` entity acts as the foreign key, linking it back to the `Owner` table.

((An image of a simple ERD with two entities. The 'Owner' entity has attributes `OwnerID` (underlined), `FirstName`, `LastName`, `Phone`. The 'Pet' entity has attributes `PetID` (underlined), `Name`, `Species`, `DateOfBirth`, `OwnerID`. A line connects them with a 'one' symbol at the Owner side and a 'many' crow's foot at the Pet side.))

MODIFY the Model

Now, let's introduce a new requirement. The vet needs to track appointments.

A `Pet` can have many `Appointments`, but each `Appointment` is for only one `Pet`.

A `Vet` can have many `Appointments`, but each `Appointment` is assigned to only one `Vet`.

Draw two new entities: `Vet` and `Appointment`.

For the `Vet` entity, add the attributes: `VetID` (Primary Key), `FirstName`, `LastName`, `Specialisation`.

For the `Appointment` entity, add the attributes: `AppointmentID` (Primary Key), `AppointmentDate`, `AppointmentTime`, `Reason`.

Add the correct relationship lines and cardinality symbols (Crow's Foot notation) to connect `Vet` and `Pet` to the new `Appointment` entity.

Crucially, think about which foreign keys you will need to add to the `Appointment` table to create these links. Add them to your `Appointment` entity.

CREATE the Final Piece

The surgery now wants to track which treatments are given during an appointment. This is a classic many-to-many relationship:

One `Appointment` can involve many different `Treatments` (e.g., a vaccination, a check-up, a nail clip).

One `Treatment` (e.g., 'Annual Vaccination') can be given at many different `Appointments`.

Draw a new entity called `Treatment` with attributes `TreatmentID` (Primary Key), `Name`, and `Cost`.

You cannot directly link `Appointment` and `Treatment` with a many-to-many line. You must create a new linking table (an associative entity) to resolve this. Call it `AppointmentTreatment`.

What attributes will this new table need to link the other two? Hint: It will need a composite primary key made from two foreign keys.

Draw the `AppointmentTreatment` entity and connect it to `Appointment` and `Treatment` with the correct one-to-many relationships.

Hungry for more?

Search the web: Database ERD linking table example

Ask an AI: Click on the carefully written prompt below to learn more...

Act as a senior database architect. Explain the concept of cardinality in an ER diagram to a junior developer. Use a simple analogy based on a library (books, members, loans). Explain one-to-many and many-to-many relationships. The explanation must be under 150 words. NO intro, NO outro, NO deviation from the topic, NO follow-up questions.

Extend the schema: Add a 'Medication' entity to your diagram. A treatment might require multiple medications, and a medication can be used in many treatments. How would you model this?

Outcome: You will have produced a complete ER diagram for the veterinary surgery, correctly modelling entities, attributes, primary/foreign keys, and resolving a many-to-many relationship with a linking table.

Sticky Note Challenge

On a sticky note, draw the relationship between two entities: `Film` and `Actor`. What is the cardinality? Now, draw the linking table you would need to implement this relationship and list its attributes.