Entity Relationship Modelling (ERD Recap)
Entities, attributes, relationships, cardinality, notation, and resolving many-to-many relationships.
Contents
By the end of this chapter you should know how to model the real world using entities, attributes and relationships, and how to draw them as an ER Diagram.
What is an ERD?
An Entity Relationship Diagram (ERD) is a graphical representation of an organization’s data storage requirements.
ERDs are abstractions of the real world: they simplify the problem to be solved while keeping its essential features. The “data” view of a system is modelled with an ERD, which gives a high-level, conceptual view of the database structure.
ERDs are used to:
- identify the data that must be captured, stored and retrieved to support the organization’s business activities; and
- identify the data needed to derive and report on performance measures the organization should be monitoring.
An ERD has three components: Entities Attributes Relationships
Entities
An entity is a person, place or thing that exists in an organization — anything the organization needs to store data about. An entity can be uniquely identified.
Examples of entities organizations store data about:
- A bank stores data about you → you are an entity (CUSTOMER).
- A business stores a piece of paper called an invoice → the invoice is an entity.
- A library stores data about a particular book → the book is an entity.
An actual, real thing or person about which data might be stored is referred to as an entity.
Strictly, STUDENT is an entity type (the category), and “Ali, TP012345” is an entity occurrence (one instance). ERDs show entity types; each row of the eventual table is one occurrence.
Attributes
An attribute (also called a data element) is a data item associated with an entity. Attributes are the smallest units of data that can be described in a meaningful way.
Example: the entity type SUPPLIER is likely to have attributes such as supplier_name, supplier_address, and so on.
One attribute (or a combination) is chosen as the identifier (primary key) that uniquely identifies each occurrence, e.g. supplier_id.
Selecting entities (noun analysis)
Deciding on entities is not always easy. The main danger is identifying attributes as entities, and vice versa.
Identify the nouns in the case narrative, then decide for each noun whether data is likely to be stored about it (→ entity) or whether it merely describes something else (→ attribute).
“Each engineer is allocated one van (which is driven up to a certain mileage and then replaced). Each member has only one address but perhaps many vehicles. Each visit is to deal with only one vehicle. A member can be visited more than once on any given date and there may be many visits to a member on different dates. A member may only be covered for some of the vehicles they own and not for others.”
Nouns: engineer, van, mileage, member, address, vehicle, visit, date.
Entities: ENGINEER, VAN, MEMBER, VEHICLE, VISIT.
Attributes: mileage (of VAN), address (of MEMBER), date (of VISIT).
Relationships
A relationship is a named association between two or more entity types.
- PLAY-FOR between PLAYER and TEAM
- CITIZEN-OF between PERSON and COUNTRY
- EMPLOYEEs work in a DEPARTMENT
- LAWYERs advise CLIENTs
- EQUIPMENT is allocated to PROJECTs
Types of relationship (cardinality)
Between two entities there are three possible relationship types:
One-to-One (1:1)
A single occurrence of one entity is related to just one occurrence of a second entity.
Example: HEAD-OF between MANAGER and DEPARTMENT — a department has at most one head, and a manager is head of at most one department.
One-to-Many (1:M)
A single occurrence of one entity is related to many occurrences of a second entity.
Example: SUPERVISES between MANAGER and EMPLOYEE — a manager may supervise any number of employees, but a given employee is supervised by at most one manager.
Many-to-Many (M:N)
Many occurrences of one entity are related to many occurrences of a second entity.
Example: ASSIGNED-TO between EMPLOYEE and PROJECT — an employee may be assigned to many projects, and each project may have many employees.
The slides use a semantic net (dots for occurrences, lines for links) to clarify cardinality. Draw a few real occurrences on each side and connect them: if every dot on both sides has at most one line → 1:1; if one side’s dots have several lines → 1:M; if both sides do → M:N.
Notation (Information Engineering / Crow’s Foot)
- A plain rectangle represents the entity type (e.g.
INVOICE). - A labelled line represents the relationship (e.g. “is sent by”).
- A short bar | at the end of the line means “one”; a crow’s foot (three prongs) means “many”.
Top: one A relates to one B. Bottom: one A relates to many B (crow’s foot on the “many” side).
Reading examples
- ONE course has enrolled on it ONE or MORE students; many students are enrolled on ONE course.
- ONE loan refers to ONE book; ONE book is referred to on ONE loan.
Resolving Many-to-Many relationships
Relational databases cannot directly support M:N relationships. An M:N relationship implies a missing “link” entity (also called an associative, composite or bridge entity).
Investigation shows that any one supplier might supply more than one kind of part, and any one kind of part might be bought from several suppliers. So SUPPLIER —supplies— PART is M:N.
The M:N relationship is removed by inserting a new entity X between them. Each original entity now has a 1:M relationship to X:
Thinking of a good name for X can be difficult. In that case it is acceptable to form the name from the two original entities — e.g. SUPPLIER_PART.
The link entity’s primary key is normally the composite of both foreign keys — SUPPLIER_PART(supplier_id, part_id, …) — and it is the natural home for attributes that belong to the pair, e.g. unit_price or lead_time for that supplier–part combination.
Guidelines for drawing an ERD
- Select likely entities.
- Select an identifier for each entity.
- Produce an entity relationship grid (a matrix of entity vs entity, marking where a relationship exists).
- Identify the relationships between the selected entities.
- Sketch an ERD based on the above.
- Decompose any M:N relationships. (If M:N relationships are present, two ERDs will be required — the initial one and the resolved one.)
Worked example: Simple Hospital System
In a hospital system, each ward has many patients who are cared for by nurses assigned to the ward. Patients may have more than one disease, thus requiring treatment by more than one specialist doctor.
| Relationship | Between | Cardinality |
|---|---|---|
| accommodates | WARD – PATIENT | 1 : M (a ward has many patients) |
| has assigned | WARD – NURSE | 1 : M (nurses are assigned to a ward) |
| cares for | NURSE – PATIENT | M : N |
| treats | DOCTOR – PATIENT | M : N (more than one specialist doctor) |
| suffers from | PATIENT – DISEASE | M : N (more than one disease) |
Because this initial ERD contains M:N relationships (cares for, treats, suffers from), each must be resolved with a link entity (e.g. NURSE_PATIENT, TREATMENT, PATIENT_DISEASE) in the second ERD.
Worked example: Small College Database
A department has many lecturers. A lecturer belongs to only one department. The department offers many different courses, and many lecturers can teach on a single course. Lecturers can also teach on more than one course. Many students enrol for many courses.
- DEPARTMENT offers COURSE — 1 : M
- LECTURER is_in DEPARTMENT — M : 1
- LECTURER teaches_on COURSE — M : N → resolve (e.g. TEACHING)
- STUDENT enrols on COURSE — M : N → resolve (e.g. ENROLMENT)
Chen notation vs Crow’s Foot notation
Chen notation
- Entities → rectangles (e.g. Automobile, Student)
- Relationships → diamonds (e.g. drive)
- Attributes → ovals attached to the entity (make, year, color…)
- Composite attribute: name split into first/middle/last name ovals
- Multivalued attribute: double oval (e.g. school)
- Key attribute: underlined (e.g. vehicle id)
- Double line = full (total) participation; single line = partial participation
Crow’s Foot notation
- Entities → rectangles; relationships → labelled lines
- Cardinality shown by symbols at the line ends: | one, crow’s foot many, ○ optional (zero)
- Example: SALES REP 1 —serves— 0:M CUSTOMER; CUSTOMER 1 —places— 1:M ORDER; ORDER 1 —lists— 1:M PRODUCT; PRODUCT 0:M —stores— 1 WAREHOUSE
In crow’s foot, each end has two symbols: the inner one is the minimum (○ = 0, | = 1) and the outer one is the maximum (| = 1, crow’s foot = many). So “○<” reads “zero or many” and “||” reads “exactly one”.