8.1.4 Modelling Databases with E–R Diagrams
An entity–relationship model describes the data a system needs before its tables are implemented. It helps a designer decide which entities belong in the database, which associations must be stored and how many instances can participate in each association.
By the end of this section, you should be able to:
- Identify suitable entities from a database requirement.
- Choose direct relationships without adding unnecessary connections.
- Determine one-to-one, one-to-many and many-to-many cardinality.
- Use minimum and maximum cardinality to show optional and compulsory participation.
- Read an E–R relationship accurately in both directions.
- Convert a conceptual model into a logical relational design.
- Resolve a many-to-many relationship using a link entity.
- produce table definitions and a key table from a logical E–R model.
A Syllabus-Led Design Process
Database modelling is easier when the designer works from the requirement towards the implementation. The process below separates the decisions so that each one can be checked.
| Stage | Question to answer | Output |
|---|---|---|
| 1. Understand the requirement | What information must the system store and use? | A concise description of the data needs |
| 2. Identify entities | Which categories of person, object, place, event or transaction need their own stored data? | A list of candidate entities |
| 3. Identify direct relationships | Which entity pairs must be associated explicitly? | An initial E–R model |
| 4. Decide cardinality | What are the minimum and maximum numbers at each end? | An annotated conceptual model |
| 5. Prepare the logical design | Where do foreign keys go, and which M:M relationships need link entities? | A model ready to become relational tables |
| 6. Define tables and keys | What are the attributes, primary keys and foreign keys? | Table definitions and a key table |
Choosing Entities from a Requirement
Consider this original scenario:
A useful entity represents a category for which the database will store multiple instances and several properties.
| Candidate | Entity? | Reasoning |
|---|---|---|
| Garden | Yes | The system stores several gardens and details about each one. |
| Plot | Yes | Each garden contains separately identifiable plots. |
| Workshop | Yes | Workshops are events with dates, titles and locations. |
| Volunteer | Yes | The system stores details for many volunteers. |
| City | Usually no | The requirement describes one city; it may be context rather than a repeated category. |
| Workshop title | No | This is likely to be an attribute of Workshop rather than a separate entity. |
| Attend | Not initially | It first describes a relationship. It may later become a link entity when the M:M relationship is resolved. |
Entity instance: one particular example of that category.
Identifying Direct Relationships
A relationship is an association that the database needs to record. The aim is to model the important direct associations, not every pair that can be connected indirectly.
| Entity pair | Direct relationship? | Explanation |
|---|---|---|
| Garden and Plot | Yes | A plot belongs to a particular garden. |
| Garden and Workshop | Yes | A workshop takes place at a garden. |
| Volunteer and Workshop | Yes | The database must record which volunteers attend which workshops. |
| Volunteer and Plot | Not from this requirement | No allocation between volunteers and plots has been stated. |
| Plot and Workshop | Usually no | They are already connected indirectly through Garden, and no direct association has been required. |
Cardinality: How Many Can Participate?
Cardinality describes the possible number of instances at each end of a relationship. The maximum value gives the familiar 1:1, 1:M or M:M classification. The minimum value shows whether participation is optional or compulsory.
| Notation | Meaning | Plain-English reading |
|---|---|---|
| 0..1 | Optional, maximum one | None or one |
| 1..1 | Compulsory, exactly one | One and only one |
| 0..* | Optional, maximum many | None, one or many |
| 1..* | Compulsory, maximum many | One or many |
Examples from the community-garden model
| Relationship | Rule in one direction | Rule in the opposite direction | Overall type |
|---|---|---|---|
| Garden—Plot | One garden can contain zero or many plots. | Each plot belongs to exactly one garden. | 1:M |
| Garden—Workshop | One garden can host zero or many workshops. | Each workshop takes place at exactly one garden. | 1:M |
| Volunteer—Workshop | One volunteer can attend zero or many workshops. | One workshop can have zero or many volunteers. | M:M |
Reading an E–R Diagram in Both Directions
The diagram below uses text-based minimum and maximum notation so that the rules remain clear even without relying on a particular drawing style.
Read it as two complete statements:
- One Garden can contain zero or many Plots.
- One Plot must belong to one and only one Garden.
The symbols are placed near the entity whose participation they describe. When a different diagram convention is used, the same two-direction reasoning still applies.
From a Conceptual Model to a Logical Model
A conceptual E–R model concentrates on the required entities, relationships and cardinalities. It describes the organisation's data without committing to all relational implementation details.
A logical model prepares that design for relational tables. At this stage, the designer decides:
- which entity becomes each table;
- which attribute or attributes form each primary key;
- where foreign keys are stored;
- which many-to-many relationships require link entities;
- which relationship attributes belong in a link table.
| Conceptual question | Logical implementation decision |
|---|---|
| One Garden contains many Plots. | Plot stores GardenID as a foreign key. |
| One Garden hosts many Workshops. | Workshop stores GardenID as a foreign key. |
| Volunteers attend many Workshops and Workshops have many Volunteers. | Add an Attendance link entity containing VolunteerID and WorkshopID. |
Resolving a Many-to-Many Relationship
The conceptual relationship between Volunteer and Workshop is many-to-many:
It cannot be implemented by storing a list of WorkshopIDs in Volunteer or a list of VolunteerIDs in Workshop. Instead, a link entity records one pairing per tuple:
Attendance can store facts that belong to the association rather than to either original entity, such as RegistrationDate, AttendanceStatus or VolunteerRole.
VolunteerID and WorkshopID are foreign keys. Their combination can form a compound primary key because one volunteer–workshop pairing should occur at most once.
Producing the Relational Table Design
The completed logical model can be expressed using table definitions:
Plot(PlotID, AreaM2, GardenIDFK)
Workshop(WorkshopID, Title, StartDate, GardenIDFK)
Volunteer(VolunteerID, FullName, Email)
Attendance(VolunteerIDFK, WorkshopIDFK, RegistrationDate, AttendanceStatus)
The key choices can also be summarised in a key table:
| Table | Primary key | Foreign key or keys | Relationship implemented |
|---|---|---|---|
| Garden | GardenID | — | Parent table for plots and workshops |
| Plot | PlotID | GardenID | Garden 1:M Plot |
| Workshop | WorkshopID | GardenID | Garden 1:M Workshop |
| Volunteer | VolunteerID | — | Parent table for attendance records |
| Attendance | VolunteerID + WorkshopID | VolunteerID, WorkshopID | Resolves Volunteer M:M Workshop |
Interactive: E–R Modelling Studio
The studio preserves the two main activities from the original pages: building a conceptual model and converting it into a logical relational design.
Common Mistakes and Misconceptions
- Selecting every noun as an entity without deciding whether it needs multiple stored instances.
- Adding indirect or invented relationships that are not required by the scenario.
- Reading cardinality in only one direction.
- Ignoring minimum cardinality and therefore missing whether participation is optional.
- Placing the foreign key at the one side of a 1:M relationship.
- Leaving a M:M relationship unresolved in the logical relational design.
- Creating numbered repeating attributes instead of a link entity.
- Forgetting that relationship data, such as RegistrationDate, belongs in the link entity.
- Confusing a relation with a relationship.
Practice
Original practice tasks
- A repair service records customers, devices, repair jobs and engineers. Identify four suitable entities and explain why each one qualifies.
- One customer can own several devices, while each device has one owner. State the relationship type and read it in both directions.
- A new repair centre may have no engineers yet, but every engineer must work at exactly one centre. State the minimum and maximum cardinalities.
- Engineers can work on many repair jobs, and each job may involve several engineers. Explain why this is M:M and suggest a link entity.
- Suggest two attributes that belong in the EngineerJob link entity rather than in Engineer or RepairJob.
- Convert Centre 1:M Engineer into table definitions and identify the foreign key.
- Produce a key table for Customer, Device, RepairJob, Engineer and EngineerJob.
- Explain the difference between a conceptual E–R model and a logical relational model.
Review
| Question | Strong answer should include |
|---|---|
| What is an entity? | A repeated category of thing about which the database stores data. |
| What is a relationship? | A required association between entity types. |
| What does cardinality show? | The minimum and maximum number of instances that may participate at each end. |
| How is a 1:M relationship implemented? | The table at the many side stores a foreign key referencing the one-side primary key. |
| How is a M:M relationship implemented? | A link entity replaces it with two 1:M relationships. |
| What can a link entity store? | Foreign keys to the original tables and attributes belonging to the association. |
| What is the purpose of a key table? | To summarise the primary and foreign keys in the logical design. |