Conceptual Design

Introduction to the Entity-Relationship Model

The Entity-Relationship model is the language in which a database design is argued before it is built. This lesson explains what the model offers, what it deliberately ignores, and how to read the notation ERMap uses.

Beginner

Learning objectives

  • Explain what a conceptual model adds that requirements and tables cannot provide
  • Name and define the constructs of the E-R model: entity, attribute, relationship, generalisation
  • Distinguish a schema from an instance at the conceptual level
  • Read the Chen notation used by ERMap and recognise the crow's foot and UML variants
  • State the four quality criteria of a conceptual schema and apply them to a small example

Prerequisites

  • Module 2: Requirements Analysis

Introduction

By the end of Module 2 you own a register of numbered requirements. It is precise, traceable and agreed with the people who pay for the system. It is also, deliberately, written in prose: sentences about members, loans, reservations and fines, each one true, none of them yet structured.

A DBMS cannot execute prose. And a CREATE TABLE statement is far too concrete a place to start: it commits you to data types, to column order, to storage decisions, and — most dangerously — it makes every structural mistake look final. If you begin with tables, you discover that BOOK should have been BOOK plus COPY only after you have written two thousand lines of application code against the wrong shape.

The Entity-Relationship model exists to occupy the space between those two points. It is a conceptual model: it describes the structure of the information in the domain, using constructs that the domain expert can read and argue about, and it says nothing about how that information will be stored.

What a conceptual model must provide

A conceptual model is judged by four things, and the E-R model was designed to satisfy all four.

It must be expressive enough for the domain. If the requirements say "a student may enrol in many courses, and a course may have many students", the model must be able to say that directly — not by inventing a table called student_course and hoping the reader understands why.

It must be understandable by non-programmers. The people who can confirm that the model is right are librarians, not Java developers. If confirming the model requires reading SQL, they cannot confirm it, and the confirmation becomes a formality.

It must be formal enough to be translated mechanically. A model that is only a drawing is a sketch. The E-R model has enough rigour that the translation to relations in Module 5 is a set of rules, not a matter of taste.

It must be implementation-independent. No data types, no indexes, no storage engines, no vendor features. Those belong to physical design, and bringing them forward contaminates the design with decisions that cannot be revisited.

The constructs of the E-R model

The model has a small vocabulary. Everything in this module is a refinement of these four constructs.

Construct Formal idea In the domain In ERMap
Entity A class of objects that have an independent existence and are described by the same properties Student, Course, Book rectangle
Attribute A property of an entity or of a relationship, taking values from a domain Student.surname, Loan.due_date ellipse on a line
Relationship (association) A logical link between two or more entities, itself possibly described by attributes Enrols, Teaches, Supervises diamond or rounded box
Generalisation A hierarchy in which a child entity is a special case of a parent entity Person ⊃ Student, Teacher ISA symbol

The model adds constraints to these constructs — identifiers, cardinalities, participation, coverage — and those constraints are what turn a drawing into a design. A schema without constraints is a glossary of nouns; the constraints are the content.

Entity type versus entity instance

An entity type (or simply entity) is the class: "the set of all students". An entity instance is one member of that class: "the student with matriculation number 2024/00417". In a schema you draw entity types; the database at run time holds instances.

Schema and instance, at the conceptual level

This distinction appeared in Module 1 and it reappears here in a form that matters for the rest of the course.

An E-R schema is the drawing plus its constraints. It is relatively stable: a library's schema changes when the library changes how it works, perhaps once a year. An E-R instance — called a population — is the set of concrete entities and relationships that exist at a given moment: this student, that loan, this reservation. It changes thousands of times a day.

Three consequences follow, and each one is a common source of error.

A schema cannot be validated by looking at one population. A schema that looks fine with three students may collapse with thirty thousand, because the cardinality that seemed obvious becomes wrong at scale. This is why Module 2 insisted on volumes.

An instance is not evidence of a schema rule. The fact that today every loan happens to belong to a different member does not mean a member can have only one loan. The rule comes from the requirements, never from a sample of data — although a sample of data is excellent evidence that a stated rule is false.

Empty is legal. A schema with zero instances is a valid schema. A new library with no loans yet still has a Loan entity. Students routinely delete all rows from a table to "test" a design; that tests nothing.

How the notation works, and which one ERMap uses

The E-R model is a model; the notation is a convention. There are three families you will meet, and reading a schema drawn in a foreign notation is a routine professional skill.

Chen notation (1976) draws entities as rectangles, relationships as diamonds, attributes as ellipses attached by lines, and key attributes with the attribute name underlined. Cardinality is written as a pair (min, max) on the line connecting an entity to a relationship, read as "each instance of this entity participates in at least min and at most max relationship instances". This is the notation used by Italian and Spanish university courses, and it is the one ERMap adopts.

Crow's foot notation (also called IE or Barker notation) draws entities as boxes with the attributes listed inside, and relationships as plain lines whose ends carry symbols: a bar for "exactly one", a circle for "zero", and a three-pronged "crow's foot" for "many". It is compact and dominant in commercial tools.

UML class diagrams use a rectangle divided into name, attributes and operations, with associations annotated by multiplicities such as 0..*. They are widely used in software engineering and are easily confused with E-R; the difference is that UML models the software, while E-R models the domain.

Here is a complete schema in ERMap's notation. Open it and look at it before continuing: every later lesson in this module adds one refinement to what you see.

(0,N)(0,N)StudentEnrollsCourseidfirst_namelast_namebirth_dateemailenrollment_datecodetitlecreditssemester
Student and Course · beginner

Two students, two courses, one Enrols relationship carrying an attribute of its own (enrollment_date). Small enough to hold in your head, and already containing every construct this module will formalise.

What an E-R schema is not

Four confusions cause more damage in this module than any other mistake, and they are worth naming at the start.

It is not a relational schema. Enrols is not a table, and in Module 5 it will become one — or will it? A 1:N relationship becomes a foreign key, not a table. Deciding that Enrols "is a table" now would pre-empt a decision that belongs three modules later, and would be wrong for half the relationships you will draw.

It is not a data model of the software. An E-R schema describes the domain: books, loans, members. If your schema contains Session, AuditLog or NotificationQueue, you have drifted into modelling the application. Sometimes those objects genuinely belong to the domain — an audit trail required by law is domain — but the test is always "does the organisation talk about this?", not "does the code need it?".

It is not a normalisation exercise. Normalisation (Module 6) operates on relations and on functional dependencies. An E-R schema is not in "third normal form"; it is well-designed or badly designed, which is a different judgement with different criteria, given below.

It is not a picture of the current system. Requirements describe what the organisation needs, which is frequently not what its current spreadsheets do. Copying the spreadsheet into an E-R schema is the most reliable way to inherit twenty years of workarounds.

Four criteria for a good conceptual schema

There is no algorithm that produces a correct schema. There are criteria by which a schema can be criticised, and applying them repeatedly is how quality is reached.

Correctness. Every constraint in the schema is true of the domain, and every requirement in the register is represented. Correctness is checked by walking the register, one requirement at a time, and pointing at the construct that satisfies it. A requirement with nothing to point at is a defect in the schema, not in the requirement.

Completeness. No fact required by the register is missing. Completeness is checked in the opposite direction: for every attribute, ask which requirement demanded it, and delete the ones that answer "none".

Minimality. No fact is represented twice. If Course.credits is stored both in Course and inside Enrols, the schema can represent a state in which the two disagree — and the DBMS will not stop you. Redundancy in a conceptual schema is a design error even when, later, it is reintroduced deliberately for performance reasons; the difference is that a deliberate denormalisation is documented and justified, while an accidental one is invisible.

Readability. A domain expert can read the schema without a legend. This is not decoration: it is the criterion that decides whether the model can be validated by the people who know. Names in the organisation's language, no gratuitous abbreviations, one concept per construct.

A first pass on one sentence

Take a single sentence from a requirements register:

R7. A student may enrol in several courses; a course may have several enrolled students. The date of each enrolment must be recorded.

Read it three times, each time asking a different question.

Which nouns have independent existence? Student and Course. They exist whether or not anyone enrols, they are referred to by other requirements, and they have their own attributes. They become entities.

Which facts are properties of one of them? A student's surname belongs to the student alone. A course's title belongs to the course alone. Those become attributes of Student and Course.

Which fact exists only because two of them are connected? The enrolment date belongs to neither the student nor the course: it belongs to the pair. Delete the enrolment and the date has no meaning. That is the definition of a relationship attribute, and it is the reason Enrols is a relationship rather than an entity.

That reasoning — nouns, properties, connection-only facts — is the whole of conceptual design in miniature. The remaining twenty-one lessons of this module take it apart and put it back together with the precision the real cases require.

Common mistakes

Mistake Symptom Correct approach
Starting from tables The first artefact is a CREATE TABLE; entities are named after tables Start from the requirements register; tables appear in Module 5
Drawing without constraints Boxes and lines with no cardinalities, "to be defined later" Cardinality is part of the design; an unconstrained schema is a sketch
Modelling the software Session, Config, Notification entities in a domain schema Ask whether the organisation talks about the object
Reversing cardinality when reading a foreign notation Every relationship inverted Establish, for each notation, which side the pair describes
Treating one population as proof "Every member has at most one loan — look at the data" Rules come from requirements; data can only refute them
Copying the current spreadsheet The schema mirrors Excel columns, workarounds included Model what is needed, then migrate the data into it
Naming in code tbl_stu, stu_nm, fk_id1 Name in the organisation's language
Skipping the readability test The expert nods without reading Explain the schema in sentences, out loud

Summary

  • The E-R model is the conceptual layer between requirements and implementation: expressive, understandable by domain experts, formal enough to translate, independent of any DBMS.
  • Its vocabulary is small: entity, attribute, relationship, generalisation, plus the constraints — identifiers, cardinalities, participation — that carry the actual design content.
  • Schema and instance must not be confused: a schema is stable and cannot be validated by looking at one population.
  • Chen notation, used by ERMap, writes cardinality as a (min, max) pair on the entity side; crow's foot and UML write it with symbols and multiplicities, and reading one as if it were the other inverts every constraint.
  • An E-R schema is not a relational schema, not a model of the software, not a normalisation exercise and not a picture of the current system.
  • Quality is judged on correctness, completeness, minimality and readability — four criteria that can be checked, requirement by requirement.

Verification questions

Beginner

Check your understanding

  1. Give two things a conceptual schema can express that a CREATE TABLE statement cannot express without committing to implementation details.
  2. Why is "the database currently has no rows in Loan" not evidence that the Loan entity is unnecessary?
  3. In Chen notation, where is the cardinality pair written, and what exactly does it describe?
  4. A schema contains Student, Course, Enrols and also a Credits attribute inside Enrols while Course already has credits. Which quality criterion is violated, and what is the practical consequence?
  5. Name one object that would be legitimate in a domain schema and one that would not, and state the test you used.
Show the answers
  1. Cardinality ("a course may have zero students") and participation ("every loan must belong to exactly one member") are constraints on the domain. In SQL they are either unrepresentable — a CHECK cannot count rows of another table in standard SQL — or must be enforced by triggers, application code or conventions. The conceptual schema states them once, declaratively, where they can be reviewed.
  2. Because a schema describes what is possible, not what happens to exist today. The library will lend books; the entity is required by the requirements, and an empty table is a valid state of a valid schema. Deleting the entity because the table is empty destroys the ability to represent a state the domain allows.
  3. On the line connecting an entity to a relationship, on the entity's side. It describes how many relationship instances one instance of that entity participates in: (0, N) on the Student side means "a student participates in at least 0 and at most N enrolments". It does not describe how many students a course has — that is the pair on the other side.
  4. Minimality. The same fact — how many credits a course is worth — is stored in two places. The practical consequence is that the database can represent a state in which Course.credits = 6 and Enrols.credits = 9, and nothing prevents it. Two sources of truth become two different truths; the anomaly surfaces months later as a report that does not add up.
  5. Legitimate: Invoice, because the organisation issues invoices and discusses them. Not legitimate: NotificationQueue, because it is a mechanism of the software — the organisation talks about "informing the customer", not about a queue. The test is whether a domain expert would recognise and discuss the object, and whether it has an independent existence in the organisation's world.

Exercise

Beginner

From sentences to constructs

Below are five statements taken from the requirements register of a small dental practice. For each, decide whether it introduces an entity, an attribute, a relationship, or a constraint on a relationship, and say which construct already in the schema it attaches to. Then draw the schema you obtain — five constructs are enough; do not invent more.

  1. The practice treats patients; each patient has a fiscal code, a surname and a date of birth.
  2. Each patient is assigned to exactly one primary dentist.
  3. A dentist can be the primary dentist of many patients.
  4. An appointment is between one patient and one dentist, on a date and at a time.
  5. A patient cannot have two appointments with the same dentist at the same date and time.
Show the commented solution

Construct by construct:

# Construct Attaches to Why
1 Entity Patient + three attributes new "The practice treats patients" — patients exist independently and are referred to by other statements
2 Relationship HasPrimaryDentist between Patient and Dentist, plus the cardinality (1,1) on the Patient side new + constraint "exactly one" is the minimum and maximum on the patient side
3 Cardinality (0,N) on the Dentist side of the same relationship existing relationship the same relationship, read from the other end
4 Entity or relationship? — see below — the interesting case
5 Constraint (uniqueness) on the appointment construct the construct from 4 a rule that cannot be drawn as a cardinality

Statement 4 is the one worth arguing about. An appointment has its own date and time, and it is referenced by statement 5 as a thing that can exist and be constrained. Two readings are defensible:

  • As a relationship Attends between Patient and Dentist, with attributes date and time. Chen notation allows this, and it is the minimal answer: an appointment is the connection between a patient and a dentist at a moment.
  • As an entity Appointment, with attributes date, time, and two relationships to Patient and Dentist.

Both are correct here, and the choice is decided by what the requirements will later demand. If appointments acquire a room, a treatment code, a cancellation reason and a link to an invoice, the entity reading is clearly better: it has become a thing in its own right. If an appointment is nothing but "who, with whom, when", the relationship reading is simpler and equally expressive. Say which reading you chose and why — that sentence is the deliverable.

Statement 5 cannot be expressed as a cardinality. "A patient cannot have two appointments with the same dentist at the same date and time" constrains a combination of values, not the number of participations. It is an inter-relation constraint, and the conceptual schema can only annotate it. Its implementation belongs to physical design (a unique index on (patient, dentist, date, time)) — and this is the first example in the course of a constraint that exists conceptually but can only be enforced later.

Resulting schema (choosing the relationship reading of statement 4): Patient — (1,1) — HasPrimaryDentist — (0,N) — Dentist, plus Patient — (0,N) — Attends — (0,N) — Dentist with attributes date, time, and the uniqueness annotation of statement 5.

Note what is not in the schema: no id columns, no VARCHAR, no Appointment table. Those are decisions for Modules 5 and 7, and making them now would have hidden the one genuine design question — statement 4 — behind a data-type discussion.

Where to go next

Related lessons